DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, June 28, 2017

How to change the sender mail id in RHEL 7

Changing sender mail id in "mailx" command before RHEL 7

echo " Running out of space $mountpoint on `date` " | mail -s "Critical Alert: Almost out of disk space $mountpoint" recipient@abc.com -- -f sender@abc.com

Now I receive the alert mail from the mail id "sender@abc.com"

But in RHEL 7 -f is not usable. So to change the sender mail id in RHEL 7 follow the below steps

1. Create a file $HOME/.mailrc and make the below entries in the file

set from = "sender@abc.com"

2. Now use the mailx command like below in RHEL 7

echo " Running out of space $mountpoint on `date` " | mail -s "Critical Alert: Almost out of disk space $mountpoint" recipient@abc.com


3. Now the sender mail id is changed.

Monday, June 26, 2017

Dataguard-Broker-Property–Binding-Overview

Overview  - Dataguard Broker Property – Binding

One of our primary DB was down due to “ORA-00257: archiver error. Connect internal only, until freed”.

As all know most of the time this is due to because of primary DB archive destination  100% full and unable to archive a redo log.

Immediately we went and check, but surprisingly our primary archive destination is 95% free, and we had enough space. Unfortunately, Standby Database destination was 100% full.

We cleared the space on standby db server and Issue is resolved, everyone able to connect to DB now,

I can assume now that whoever reading this blog will thing that standby mode is “MAXIMUM PROTECTION MODE”, due to primary not able to transfer the redo because standby destination was full.

But that’s not the case, our standby mode is “Maximum Performance mode”, so technically it should not be the case.

Here is the Binding Variable comes into Play, we set to `mandatory` (default value:- optional). Why we set this to mandatory to make sure RMAN archive backup will delete archives from primary server only it applied on standy.

Since it set to mandatory, If the archiving operation of a mandatory destination fails, online redo log files cannot be overwritten, so Primary DB hung with error ORA-00257

So, lesson here is, Always double check dataguard broker property – Binding. 

Friday, June 23, 2017

DGMGRL Switchover StaticConnectIdentifier issue

Error:

DGMGRL Switchover not happened automatically because of STD database of StaticConnectIdentifier port is 1521.


DGMGRL> switchover to PROD;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD" on database "prod"
Connecting to instance "PROD"...
Connected as SYSDBA.
New primary database "prod" is opening...
Operation requires start up of instance "STD" on database "std"
Starting instance "STD"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.doy.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=STD)(SERVER=DEDICATED)))

ORA-12541: TNS:no listener



Solution:

Tns entries of PROD and STD database.

[oracle@prod admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_STD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = prod.doy.com)(PORT = 1522))


PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.doy.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PROD)
    )
  )

STD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = prod.doy.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STD)
    )
  )

LISTENER_PROD =
  (ADDRESS = (PROTOCOL = TCP)(HOST = prod.doy.com)(PORT = 1522))


We need to change the StaticConnectIdentifier parameter to 1522 in DGMGRL.Because,its wrongly point through the different port 1521.

DGMGRL> show database verbose STD;

Database - std

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 5.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    STD

  Properties:
    DGConnectIdentifier             = 'std'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD, /u01/app/oracle/oradata/STD'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD, /u01/app/oracle/oradata/STD'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.doy.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=STD)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/fast_recovery_area/prod'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS



[oracle@prod ~]$ echo $ORACLE_SID
STD
[oracle@prod ~]$ dgmgrl sys/oracle@STD;
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.

DGMGRL> edit database 'std' set property 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.doy.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=STD)(SERVER=DEDICATED)))';
Property "StaticConnectIdentifier" updated


STD:

DGMGRL> show database verbose STD;

Database - std

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 3.00 KByte/s
  Active Apply Rate:  0 Byte/s
  Maximum Apply Rate: 0 Byte/s
  Real Time Query:    OFF
  Instance(s):
    STD

  Properties:
    DGConnectIdentifier             = 'std'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    RedoRoutes                      = ''
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'MANUAL'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle/oradata/PROD, /u01/app/oracle/oradata/STD'
    LogFileNameConvert              = '/u01/app/oracle/oradata/PROD, /u01/app/oracle/oradata/STD'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prod.doy.com)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=STD_DGMGRL)(INSTANCE_NAME=STD)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle/fast_recovery_area/prod'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS


Switchover:

[oracle@prod ~]$ dgmgrl sys/oracle@PROD;
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> switchover to STD;
Performing switchover NOW, please wait...
Operation requires a connection to instance "STD" on database "std"
Connecting to instance "STD"...
Connected as SYSDBA.
New primary database "std" is opening...
Operation requires start up of instance "PROD" on database "prod"
Starting instance "PROD"...
ORACLE instance started.
Database mounted.

Switchover succeeded, new primary is "std"
DGMGRL> DGMGRL> 
DGMGRL> switchover to PROD;
Performing switchover NOW, please wait...
Operation requires a connection to instance "PROD" on database "prod"
Connecting to instance "PROD"...
Connected as SYSDBA.
New primary database "prod" is opening...
Operation requires start up of instance "STD" on database "std"
Starting instance "STD"...
ORACLE instance started.
Database mounted.

Switchover succeeded, new primary is "prod"





ORA-04063: rule set "APPLSYS.WF_JAVA_DEFERRED_R" has errors

Scenario:
------------

Got the Forms error while adding responsibility to a user in R12.2.5. So, I did the workflow sync validation and it does not fixed. So, I ran autoconfig in Apps tier and it end up with Error and ran in db node and that too endup with the same error.

1. Auto config in apps Node completed with the following Error:

WARNING: [AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /u01/app/DOYEN/fs2/FMW_Home/webtier/perl/bin/perl -I /u01/app/DOYEN/fs2/FMW_Home/webtier/perl/lib/5.10.0 -I /u01/app/DOYEN/fs2/FMW_Home/webtier/perl/lib/site_perl/5.10.0 -I /u01/app/DOYEN/fs2/EBSapps/appl/au/12.0.0/perl -I /u01/app/DOYEN/fs2/FMW_Home/webtier/ohs/mod_perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi /u01/app/DOYEN/fs2/inst/apps/DOYEN_sysapp02/admin/scripts/adexecsql.pl sqlfile=/u01/app/DOYEN/fs2/inst/apps/DOYEN_sysapp02/admin/install
      afwebprf.sql            INSTE8_PRF         1

AutoConfig is exiting with status 1

2. Autoconfig in the DB node completed with the following error:

WARNING: [AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
      <filename>  <phase>  <return code where appropriate>

  [PROFILE PHASE]
  AutoConfig could not successfully execute the following scripts:
    Directory: /u01/orahome/u01db/11.2.0.4/appsutil/install/DOYEN_system01
      afdbprf.sh              INSTE8_PRF         1

AutoConfig is exiting with status 1


Now when i tried running the script (afwebprf.sql) manually which errored out in apps autoconfig and i faced the same error which we got in forms.

Now i tried running the script (afdbprf.sh) manually which errored out in db node and found the same error:

[orau01@system01:DOYEN_system01]# sh afdbprf.sh

afdbprf.sh started at Thu Jun 22 19:54:29 IDT 2017

The environment settings are as follows ...

       ORACLE_HOME : /u01/orahome/u01db/11.2.0.4
        ORACLE_SID : DOYEN
              PATH : /u01/orahome/u01db/11.2.0.4/perl/bin:/u01/orahome/u01db/11.2.0.4/perl/bin:/u01/orahome/u01db/11.2.0.4/bin:/usr/bin:/usr/sbin:/u01/orahome/u01db/11.2.0.4/appsutil/jre/bin:/bin:/usr/bin/X11:/usr/local/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:.:.
      Library Path : /u01/orahome/u01db/11.2.0.4/lib:/usr/X11R6/lib:/usr/openwin/lib:/u01/orahome/u01db/11.2.0.4/lib:/usr/dt/lib:/u01/orahome/u01db/11.2.0.4/ctx/lib

Executable : /u01/orahome/u01db/11.2.0.4/bin/sqlplus

Enter the APPS username: apps
Enter the APPS password:
SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 22 19:54:33 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Enter value for 1: Enter value for 2: Enter value for 3: Connected.
[ FND_DB_WALLET_DIR ]
Application Id : 0
Profile Value  : /u01/orahome/u01db/11.2.0.4/appsutil/wallet
Level Name: SITE
INFO        : Updated/created profile option value.
.
[ ECX_UTL_XSLT_DIR ]
Application Id : 174
Profile Value  : /usr/tmp
Level Name: SITE
INFO        : Updated/created profile option value.
.
[ ECX_UTL_LOG_DIR ]
Application Id : 174
Profile Value  : /usr/tmp
Level Name: SITE
INFO        : Updated/created profile option value.
.
[ BIS_DEBUG_LOG_DIRECTORY ]
Application Id : 0
Profile Value  : /usr/tmp
Level Name: SITE
INFO        : Updated/created profile option value.
.
[ UTL_FILE_LOG ]
Application Id : 1
Profile Value  : /usr/tmp, /u01/logout/tmp, /tmp/, /u01/oratmp/tempdata,
/u01/logout/Agile, /u01/logout/Agile/in, /u01/logout/Agile/out,
/u01/winshare/APAC/AP, /u01/winshare/crm/scorecard,
/u01/winshare/APAC/WESTPAC, /u01/logout/cm,
/u01/orahome/u01db/11.2.0.4/appsutil/outbound/DOYEN_system01
Level Name  : SITE
INFO        : Error updating/creating profile option value.
.
begin
*
ERROR at line 1:
ORA-04063: rule set "APPLSYS.WF_JAVA_DEFERRED_R" has errors
ORA-06512: at "APPS.WF_EVENT", line 1194
ORA-06512: at "APPS.FND_WF_ENGINE", line 1025
ORA-06512: at "APPS.FND_PROFILE", line 1102
ORA-06512: at "APPS.FND_PROFILE", line 2353
ORA-06512: at "APPS.ADX_PRF_PKG", line 192
ORA-06512: at line 42

The above error seems to be an issue with the WF Ruleset.

Solution:
-----------

Checked the following Rule Set is valid and found to be invalid.

select object_name, object_type, status from dba_objects where object_name='WF_JAVA_DEFERRED_R';

WF_JAVA_DEFERRED_R RULE SET INVALID


This is because of the issues with the Workflow queues. So i did the clean up and enque processes as suggested in the below Note id.

After following the following noteid , It fixed the  issue:

Fixing Invalid Workflow Rule Sets such as WF_DEFERRED_R and Related Errors on Workflow Queues:ORA-24033 (Doc ID 337294.1)

Once it is done, I have checked the status of the Ruleset and it is valid.

select object_name, object_type, status from dba_objects where object_name='WF_JAVA_DEFERRED_R';

WF_JAVA_DEFERRED_R RULE SET VALID


Now , Ran the Autoconfig and it has completed Normal.



Thursday, June 22, 2017

ORA-16857: standby disconnected from redo source for longer than specified threshold

[oracle@prod ~]$ dgmgrl sys/oracle@PROD
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.

DGMGRL> SHOW DATABASE PROD

Database - prod

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    PROD

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE STD

Database - std

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      27 minutes 49 seconds (computed 43 seconds ago)
  Apply Lag:          27 minutes 49 seconds (computed 43 seconds ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    STD

  Database Warning(s):
    ORA-16857: standby disconnected from redo source for longer than specified threshold

Database Status:
WARNING

DGMGRL> disable database STD;
Disabled.
DGMGRL> enable database STD;
Enabled.

DGMGRL> SHOW DATABASE STD

Database - std

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 1.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    STD

Database Status:
SUCCESS

ORA-01156: recovery or flashback in progress may need access to files

While creating standby redo logfiles in standby database,we need to cancel the MRP recovery process in standby.

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/STD/onlinelog/std_redo1.log' size 100m;
alter database add standby logfile group 4 '/u01/app/oracle/oradata/STD/onlinelog/std_redo1.log' size 100m
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

Cancel the MRP:

SQL> alter database recover managed standby database cancel;

Database altered.

Adding Standby logfiles,

SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/STD/onlinelog/std_redo1.log' size 100m;

Database altered.

Wednesday, June 21, 2017

Event Monitor (EMON) slave process is consuming CPU

Cause:

Event Monitor (EMON) slave process is consuming CPU.The emon process is stuck in a network write probably trying to communicate with a client that is not responding and this fix detects this and removes the unreachable client.
connect / as sysdba
oradebug setospid 1379
or use  the following to find EMON process
In 11g ps -ef | grep EMON
In 12c ps-ef |grep ennn

Solution:

The workaround is to kill the emon slave process via
kill -9 pid
The emon slave will automatically restart when it is next required to do so.

To resolve,

connect / as sysdba
alter system set "_client_enable_auto_unregister"=true scope=spfile
shutdown immediate
startup

Tuesday, June 20, 2017

ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed


SYS@APPLE7DR>startup;


ORA-16024: parameter LOG_ARCHIVE_DEST_1 cannot be parsed



There are 3 issues:

log_archive_dest_1 = 'LOCATION=/u01/app/oracle/apple/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES)DB_UNIQUE_NAME=appledr'

1.The log_archive_dest_n (log_archive_dest_2) should not end with a "/"

2.The syntax for log_archive_dest_n is:

log_archive_dest_1='LOCATION=/u01/app/oracle/oradata'
log_archive_dest_2='LOCATION=/u01/app/oracle/oraarch'

LOCATION= is compulsory.

3.Should be containing a space before db)unique_name:

log_archive_dest_1 = 'LOCATION=/u01/app/oracle/apple/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=appledr'

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

LSNRCTL> status APPLE7
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ggdomain)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     APPLE7
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                20-JUN-2017 06:07:39
Uptime                    0 days 0 hr. 37 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ggdomain/apple7/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ggdomain)(PORT=1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
Services Summary...
Service "APPLE7" has 1 instance(s).
  Instance "APPLE7DR", status UNKNOWN, has 1 handler(s) for this service...
Service "APPLE7DR" has 2 instance(s).
  Instance "APPLE7DR", status BLOCKED, has 1 handler(s) for this service...
  Instance "apple7dr", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

How to overcome this?
Add  (UR = A) in the TNSnames.ora
The (UR=A) clause for TNS connect strings has been created as an enhancement request.

TNSnames.ora

APPLE7DR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.150)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = apple7dr)
 (UR = A)
  )
  )

ORA-29702: ERROR OCCURRED IN CLUSTER GROUP                                            SERVICE OPERATION




While starting the RAC instance , after changing the hostname in cluster nodes.

Error:

ora-29702 error occurred in cluster group service operation startup

sqlplus “/as sysdba”
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Oct 8 08:03:07 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORA-29702: error occurred in Cluster Group Service operation
SQL>

Findings :

1) As it is cluster nodes database , cross-checked the cluster parameters like cluster_interconnect,no of threads,cluster_dataabase etc.
2) Cross-checked the all cluster nodes are pinned to the cluster group.


Solution:

Here one node has not been pinned to the cluster group .

[oracle@ordbtest2 bin]$ ./olsnodes -s -t

ordbtest1        Active  unpinned
ordbtest2        Active  pinned
ordbtest3        Active  pinned


So we have to pin the node from the available nodes either ordbtst2 or ordbtest3.

[root@ordbtest2 bin]# ./crsctl pin css -n ordbtest1
CRS-4664: Node ordbtest1 successfully pinned.


And now startup the database , it will start without any error.



ORA-32018: parameter cannot be modified in memory on another instance

Cause: Parameter adjustment can take a very long time

Action: Modify the parameter individually on each instance using the SID clause of the alter system command

Problem Description
In RAC database setting a memory parameter fails with ORA-32018. For example whenever we try to set streams_pool_size to a value then it fails like below.

SQL> alter system set streams_pool_size=100M;
alter system set streams_pool_size=100M
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

SQL> alter system set streams_pool_size=100M sid='*';
alter system set streams_pool_size=100M sid='*'
*
ERROR at line 1:
ORA-32018: parameter cannot be modified in memory on another instance

Cause of the Problem
The error message indicates that may be in one instance in RAC database parameter can be set successfully but in another instance it fails and overall you can't set the parameter across all instances.

Solution of the Problem
In order to know the instance which is causing the failure, try to set the parameter individually on each instance using the SID clause of the alter system command. Ensure the instance name by,

SQL> show parameter instance

NAME                              TYPE      VALUE
--------------------------- -------------- -------------------------
active_instance_count           integer
cluster_database_instances      integer         3
instance_groups                 string
instance_name                   string          3
instance_number                 integer         3
instance_type                   string          
open_links_per_instance         integer         4
parallel_instance_group         string
parallel_server_instances       integer         3

From the parameter value we see we are in 3 nodes RAC database and our instance name is ORA3. So set the parameter in our current instance by specifying a SID value.

SQL> alter system set streams_pool_size=100M sid='ORA3';

System altered.

Similarly set it on rest of the instances ORA2 and ORA1 for example.


SQL> alter system set streams_pool_size=100M sid='ORA2';

System altered.


SQL> alter system set streams_pool_size=100M sid='ORA1';

*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool

So we see ORA1 instance is causing the problem. This is because there is insufficient memory in ORA1 instance so you might try to increase SGA_TARGET/MEMORY_TARGET parameter or shut down ORA1 instance and restart and then we can set.

Monday, June 19, 2017

Enhanced security within Oracle E-Business Suite instances through JAR file signing using a Trusted Certificate Authority

The following describes the implementation of enhanced security within your Oracle E-Business Suite instances through JAR file signing using a Trusted Certificate Authority (CA) utilizing larger and more secure keysizes bits to provide stronger encryption. Signing jar files with a Trusted CA is a requirement for running jar content using the later JRE 7 releases running on the highest security settings. This will also become a requirement when using the Java default security settings in a future release.


1) Generate Keypair and Certificate Signing Request
$ export JRI_DATA_LOC=$NE_BASE/EBSapps/appl/ad/admin

Step 1.1. Source the Environment
Note: The configuration steps can be applied with either the RUN or PATCH File System sourced. If you are currently running an ADOP Online Patching session you must make these changes with the PATCH File System sourced. If this is the case please ensure the cutover phase is run after regenerating the Jar Files in

Regenerate the Jar Files.
. $EBS_ROOT/EBSapps.env run

Step 1.2. Generate a new keypair (private key and public key)
cd $JRI_DATA_LOC
$adjkey -initialize -keysize 2048 -alias <INSTANCE-NAME>

Enter the APPS username: apps
Enter the APPS password:
Successfully created javaVersionFile.
adjkey will now create a signing entity for you.
Enter the COMMON NAME [ ] :

Enter the ORGANIZATION NAME [xxx.corp.local] :

Enter the ORGANIZATION UNIT [ ] : apps

Enter the LOCALITY (or City) [ ] :

Enter the STATE (or Province or County) [ ] :

Enter the COUNTRY (two-letter ISO abbreviation) [ ] : US
Enter keystore password:  Re-enter new password: Enter key password for <INSTANCE-NAME>
        (RETURN if same as keystore password):  Re-enter new password:

The above Java program completed successfully.
Your digital signature has been created successfully and imported into the keystore database. This signature will now be used to sign Applications JAR files whenever they are patched.

adjkey is complete.
$

Run the following to check the generated keystore list:
$ keytool -list -v -keystore adkeystore.dat

Step 1.3. Create a Certificate Signing Request
$ keytool -sigalg SHA256withRSA -certreq -keystore $JRI_DATA_LOC/adkeystore.dat -file  $JRI_DATA_LOC/adkeystore.csr -alias <INSTANCE-NAME>
Enter keystore password: xxxx
Enter key password for <alias_name>: yyyy

Step 1.4. Submit your Certificate Signing Request
Submit your certificate signing request 'adkeystore.csr' to your official certificate authority, for example, Verisign, Thawte etc. or to your own in-house
certificate authority as applicable.

Section 2: Import your Certificate(s)
Step 2.1. My Java Code Signing Certificate is Now Available
The adkeystore.csr file created in Step 1.3 Create a Certificate Signing Request should now have been signed, encoded and formatted to be recognized as a 'signed code certificate' by your certificate authority which will be used to verify the authenticity of downloaded content.

Once you have received the 'signed code certificate' back from your Certificate Authority, continue with the steps below to complete the process.

Step 2.2. Prepare your Oracle E-Business Suite Environment
On the Application tier as the file system owner source your APPS env file.
. $EBS_ROOT/EBSapps.env run

Step 2.3. Add the Root Certificate to cacerts (if required)
If you need to import the 'Root Certificate' into cacerts continue to Step 2.4. Add the Root Certificate to cacerts.

Step 2.4. Import the Root Certificate to the Java Keystore Certificate Store 'cacerts' (if required)
Step 2.4.1. Copy your Root Certificate to the Security Properties Directory
$ cp <root_certificate> <sec_prop_loc>/<root_certificate>


Step 2.4.2. Import the Root Certificate into cacerts
export SEC_PROP_LOC=$OA_JRE_TOP/lib/security/
cd $SEC_PROP_LOC
keytool -import -alias caroot -file $JRI_DATA_LOC/xxxx.cer -trustcacerts -v -keystore cacerts -keypass xxxx
Enter keystore password: zzzzz
Trust this certificate? [no]:  y

keytool -import -alias cainter -file $JRI_DATA_LOC/corp_local_intermediate.cer -trustcacerts -v -keystore cacerts
Enter keystore password: zzzzz
$ keytool -list -keystore cacerts

Step 2.5. Import the Code Signing Certificate into the Keystore
Step 2.5.1. Copy and Rename the 'Code Signing Certificate'
cd $JRI_DATA_LOC
cp -p xxxx.cer $JRI_DATA_LOC/adkeystore.crt

Step 2.5.2. Import your Intermediate Certificate(s) (if required)
keytool -import -file corp_local_intermediate.cer -trustcacerts -alias cainter -keystore adkeystore.dat
Enter keystore password: xxxx

Step 2.5.3. Import your Java Code Signing Certificate
keytool -import -file xxxx.cer -trustcacerts -alias <INSTANCE-NAME> -keystore adkeystore.dat
Enter keystore password: xxxx
Enter key password for <EBSPRD> yyyy



Section 3: Regenerate the Jar Files
Step 3.1.1. Stop the Application Tier
. $EBS_ROOT/EBSapps.env run
cd $ADMIN_SCRIPTS_HOME
sh adstpall.sh

Step 3.1.2. Regenerate the jar files through adadmin
adadmin
Generate Applications Files menu
Generate product JAR files
Do you wish to force regeneration of all jar files? [No] ? yes

Step 3.1.3. Restart the Application Tier
cd $ADMIN_SCRIPTS_HOME
sh adstrtal.sh


Thanks & Regards
Venkatesh RK

ORA-01194: file 1 needs more recovery to be consistent

Error:

ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/PROD/oradata/system01.dbf'

Solution:

If you try to give “alter database open resetlogs” command after cloning the database  using cold backup,you will get the above error,

To troubleshoot this, follow these steps

SQL>  recover database;
ORA-00283: recovery session cancelled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

So, you must recover using the backed up controlfile . Why ? Because the controlfile has information of archives that need to be applied during recovery.

SQL>  recover database until cancel using BACKUP CONTROLFILE;
ORA-00283: recovery session cancelled due to errors

Now shutdown the database


SQL>shut immediate


Go to $ORACLE_HOME/dbs and locate the pfile of respective database.

And  add below hidden parameter in the pfile,

 _allow_resetlogs_corruption=true


SQL> startup mount pfile= ‘$ORACLE_HOME/dbs/initPROD.ora’;

ORACLE instance started.
Total System Global Area 1069252608 bytes
Fixed Size                  2188408 bytes
Variable Size             436214664 bytes
Database Buffers          616562688 bytes
Redo Buffers               14286848 bytes
Database mounted.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST      READ WRITE