DOYENSYS Knowledge Portal




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




Thursday, June 29, 2017

Steps to restart ODA(Oracle Database Appliance) box in Cluster environment

Steps:

Configuration:Single-node rac

Server1: Alice

Server2:Bob

Check which is the master node in the cluster.

[root@Bob]#oakcli show ismaster 

If Bob server is the master node,to move the master node services from Bob server  to Alice server,

[root@Bob]#oakcli restart oak

Check the master from Bob server,

[root@Bob]#oakcli show ismaster

To check all services are up and running,

[root@Bob bin]# ./oakcli validate -a 
INFO: oak system information and Validations 
RESULT: System Software inventory details 
Reading the metadata. It takes a while... 
System Version Component Name Installed Version Supported Version 
-------------- --------------- ------------------ ----------------- 
2.8.0.0.0 
Controller_INT 11.05.03.00 Up-to-date 
Controller_EXT 11.05.03.00 Up-to-date 
Expander 0018 Up-to-date 
SSD_SHARED 9440 Up-to-date 
HDD_LOCAL A31A Up-to-date 
HDD_SHARED A31A Up-to-date 
ILOM 3.2.4.26.a r99980 3.1.2.10.d r83372 
BIOS 17110500 17050100 
IPMI 1.8.10.5 Up-to-date 
HMP 2.2.6.4 Up-to-date 
OAK 2.8.0.0.0 Up-to-date 
OEL 5.9 Up-to-date 
GI_HOME 11.2.0.4.0 Up-to-date 
DB_HOME 11.2.0.3.8(16902043, Up-to-date 
17076717) 
RESULT: System Information:- 
Manufacturer:Oracle Corporation 
Product Name:SUN FIRE X4170 M3 
Serial Number:1318FML0PH 
RESULT: BIOS Information:- 
Vendor:American Megatrends Inc. 
Version:17110500 
Release Date:12/22/2014 
BIOS Revision:11.5 
Firmware Revision:3.2 
SUCCESS: Controller p1 has the IR Bypass mode set correctly 
SUCCESS: Controller p2 has the IR Bypass mode set correctly 
INFO: Reading ilom data, may take short while.. 
INFO: Read the ilom data. Doing Validations 
RESULT: System ILOM Version: 3.2.4.26.a r99980 
RESULT: System BMC firmware version 3.2 
RESULT: Powersupply PS0 V_IN=120 Volts IN_POWER=110 Watts OUT_POWER=0 Watts 
RESULT: Powersupply PS1 V_IN=116 Volts IN_POWER=190 Watts OUT_POWER=180 Watts 
SUCCESS: Both the powersupply are ok and functioning 
RESULT: Cooling Unit FM0 fan speed F0=5700 RPM F1=3200 RPM 
RESULT: Cooling Unit FM1 fan speed F0=5600 RPM F1=3300 RPM 
SUCCESS: Both the cooling unit are present 
RESULT: Processor P0 present Details:- 
Version:Intel(R) Xeon(R) CPU E5-2690 0 @ 2.90GHz 
Current Speed:2900 MHz Core Enabled:2 Thread Count:16 
SUCCESS: All 8 memory modules of CPU P0 ok, each module is of Size:16384 MB Type:DDR3 Speed:1600 MHz manufacturer:Samsung 
RESULT: Processor P1 present Details:- 
Version:Intel(R) Xeon(R) CPU E5-2690 0 @ 2.90GHz 
Current Speed:2900 MHz Core Enabled:2 Thread Count:16 
SUCCESS: All 8 memory modules of CPU P1 ok, each module is of Size:16384 MB Type:DDR3 Speed:1600 MHz manufacturer:Samsung 
RESULT: Total Physical System Memory is 264364028 kB 
SUCCESS: All OS Disks are present and in ok state 
RESULT: Power Supply=20 degrees C 
INFO: Checking Operating System Storage 
SUCCESS: The OS disks have the boot stamp 
RESULT: Raid device /dev/md0 found clean 
RESULT: Raid device /dev/md1 found clean 
RESULT: Physical Volume /dev/md1 in VolGroupSys has 370206.05M out of total 599986.80M 
RESULT: Volumegroup VolGroupSys consist of 1 physical volumes,contains 4 logical volumes, has 0 volume snaps with total size of 599986.80M and free space of 370206.05M 
RESULT: Logical Volume LogVolOpt in VolGroupSys Volume group is of size 60.00G 
RESULT: Logical Volume LogVolRoot in VolGroupSys Volume group is of size 30.00G 
RESULT: Logical Volume LogVolSwap in VolGroupSys Volume group is of size 24.00G 
RESULT: Logical Volume LogVolU01 in VolGroupSys Volume group is of size 100.00G 
RESULT: Device /dev/mapper/VolGroupSys-LogVolRoot is mounted on / of type ext3 in (rw) 
RESULT: Device /dev/md0 is mounted on /boot of type ext3 in (rw) 
RESULT: Device /dev/mapper/VolGroupSys-LogVolU01 is mounted on /u01 of type ext3 in (rw) 
RESULT: Device /dev/mapper/VolGroupSys-LogVolOpt is mounted on /opt of type ext3 in (rw) 
RESULT: / has 23288 MB free out of total 29758 MB 
RESULT: /boot has 47 MB free out of total 99 MB 
RESULT: /u01 has 40703 MB free out of total 99194 MB 
RESULT: /opt has 39632 MB free out of total 59516 MB 
INFO: Checking Shared Storage 
RESULT: Disk HDD_E0_S00_375797224 path1 status active device sda with status active path2 status active device sdy with status active 
SUCCESS: HDD_E0_S00_375797224 has both the paths up and active 
RESULT: Disk HDD_E0_S01_375819804 path1 status active device sdb with status active path2 status active device sdz with status active 
SUCCESS: HDD_E0_S01_375819804 has both the paths up and active 
RESULT: Disk HDD_E0_S02_375795168 path1 status active device sdc with status active


Check the CRS and ASM are automatically up and running,

[root@Bob bin]# crsctl check crs


[oracle@Bob bin]# srvctl status asm

ASM up and running in Alice and Bob

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 db_unique_name 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.