Thinking Out Loud

November 12, 2017

opatchauto Hell

Filed under: 12c — mdinh @ 8:10 pm

I have been investigating new opatchauto apply for 12.1 RAC system.

Was overjoyed to see services relocated to instance2 when instance1 was being patched.

The joy became dismay to find relocated services on instance2 were stopped to perform datapatch.

I am definitely not an expert, but why shutdown services running on other instances?

What if services are manually relocated, would they still be shutdown?

From Oplan:

In-Place Patching: Apply Patch to Existing Oracle HomesIn this option, the patch will be applied directly to the specified Oracle Homes. 
This is the least time-consuming option. 
The procedures available for this option are outlined below.

    Rolling Mode: Apply Patch to Each Node In A Cluster Sequentially Resources running on each node in the cluster will be stopped, patched and then restarted, one at a time in sequence. 
      By patching only one node at a time, this approach ensures that multi-instance databases remain available during the patching process, but it will take longer to complete.

        Apply Patch In-Place using OPatch Auto Command in Rolling Mode:
            Advantages: No downtime while patching; fewer patching steps.
            Disadvantages: May be time consuming; less-than-optimal diagnosability of issues during patching; slower recovery from failures.
            Total number of steps required: 29
                During the full availability of services: 23
                During the partial availability of services: 6 

Step 2: Patch Apply Phase (Limited services will be available)

Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.

What does Oracle mean by limited? The services are shutdown and not restarted!

Update: services run on correct instance when restarted.

[oracle@racnode-dc1-1 ~]$ srvctl start service -d orclcdb
PRCD-1133 : Failed to start services for database
CRS-5702: Resource 'ora.orclcdb.testsvc26.svc' is already running on 'racnode-dc1-2'
CRS-5702: Resource 'ora.orclcdb.testsvc27.svc' is already running on 'racnode-dc1-2'
CRS-5702: Resource 'ora.orclcdb.testsvc28.svc' is already running on 'racnode-dc1-2'
CRS-5702: Resource 'ora.orclcdb.testsvc29.svc' is already running on 'racnode-dc1-2'

[oracle@racnode-dc1-1 ~]$ date; srvctl status database -d orclcdb -v
Sun Nov 12 21:17:52 CET 2017
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
[oracle@racnode-dc1-1 ~]$

[root@racnode-dc1-1 ~]# $GRID_HOME/OPatch/opatchauto apply /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880


OPatchauto session is initiated at Sun Nov 12 18:07:15 2017

System initialization log file is /u01/app/12.1.0.2/grid/cfgtoollogs/opatchautodb/systemconfig2017-11-12_06-08-14PM.log.

Session log file is /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/opatchauto2017-11-12_06-09-00PM.log
The id for this session is YKRN


Executing OPatch prereq operations to verify patch applicability on home /u01/app/12.1.0.2/grid

Executing OPatch prereq operations to verify patch applicability on home /u01/app/oracle/12.1.0.2/db1
Patch applicability verified successfully on home /u01/app/oracle/12.1.0.2/db1

Patch applicability verified successfully on home /u01/app/12.1.0.2/grid


Verifying SQL patch applicability on home /u01/app/oracle/12.1.0.2/db1
SQL patch applicability verified successfully on home /u01/app/oracle/12.1.0.2/db1


Preparing to bring down database service on home /u01/app/oracle/12.1.0.2/db1
Successfully prepared home /u01/app/oracle/12.1.0.2/db1 to bring down database service


Bringing down CRS service on home /u01/app/12.1.0.2/grid
Prepatch operation log file location: /u01/app/12.1.0.2/grid/cfgtoollogs/crsconfig/crspatch_racnode-dc1-1_2017-11-12_06-20-39PM.log
CRS service brought down successfully on home /u01/app/12.1.0.2/grid


Performing prepatch operation on home /u01/app/oracle/12.1.0.2/db1
Perpatch operation completed successfully on home /u01/app/oracle/12.1.0.2/db1


Start applying binary patch on home /u01/app/oracle/12.1.0.2/db1
Binary patch applied successfully on home /u01/app/oracle/12.1.0.2/db1


Performing postpatch operation on home /u01/app/oracle/12.1.0.2/db1
Postpatch operation completed successfully on home /u01/app/oracle/12.1.0.2/db1


Start applying binary patch on home /u01/app/12.1.0.2/grid

Binary patch applied successfully on home /u01/app/12.1.0.2/grid


Starting CRS service on home /u01/app/12.1.0.2/grid
Postpatch operation log file location: /u01/app/12.1.0.2/grid/cfgtoollogs/crsconfig/crspatch_racnode-dc1-1_2017-11-12_07-20-43PM.log
CRS service started successfully on home /u01/app/12.1.0.2/grid


Preparing home /u01/app/oracle/12.1.0.2/db1 after database service restarted
Prepared home /u01/app/oracle/12.1.0.2/db1 successfully after database service restarted


Trying to apply SQL patch on home /u01/app/oracle/12.1.0.2/db1
SQL patch applied successfully on home /u01/app/oracle/12.1.0.2/db1

OPatchAuto successful.

--------------------------------Summary--------------------------------

Patching is completed successfully. Please find the summary as follows:

Host:racnode-dc1-1
RAC Home:/u01/app/oracle/12.1.0.2/db1
Summary:

==Following patches were SKIPPED:

Patch: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/21436941
Reason: This patch is not applicable to this specified target type - "rac_database"

Patch: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392164
Reason: This patch is not applicable to this specified target type - "rac_database"


==Following patches were SUCCESSFULLY applied:

Patch: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392192
Log: /u01/app/oracle/12.1.0.2/db1/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-12_18-22-11PM_1.log

Patch: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26717470
Log: /u01/app/oracle/12.1.0.2/db1/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-12_18-22-11PM_1.log


Host:racnode-dc1-1
CRS Home:/u01/app/12.1.0.2/grid
Summary:

==Following patches were SUCCESSFULLY applied:

Patch: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/21436941
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-12_18-46-41PM_1.log

Patch: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392164
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-12_18-46-41PM_1.log

Patch: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392192
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-12_18-46-41PM_1.log

Patch: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26717470
Log: /u01/app/12.1.0.2/grid/cfgtoollogs/opatchauto/core/opatch/opatch2017-11-12_18-46-41PM_1.log

OPatchauto session completed at Sun Nov 12 19:34:26 2017
Time taken to complete the session 87 minutes, 11 seconds
[root@racnode-dc1-1 ~]#

[root@racnode-dc1-1 opatchauto]# grep -A8 “Executing command as” opatchauto2017-11-12_06-09-00PM.log


Executing command as oracle:
 /u01/app/12.1.0.2/grid/OPatch/opatchauto  apply /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880 -oh /u01/app/12.1.0.2/grid -target_type cluster -binary -invPtrLoc /u01/app/12.1.0.2/grid/oraInst.loc -jre /u01/app/12.1.0.2/grid/OPatch/jre -persistresult /u01/app/12.1.0.2/grid/OPatch/auto/dbsessioninfo/sessionresult_analyze_racnode-dc1-1_crs.ser -analyze -online
2017-11-12 18:09:24,734 INFO  [53] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction -
Executing command as oracle:
 /u01/app/oracle/12.1.0.2/db1/OPatch/opatchauto  apply /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880 -oh /u01/app/oracle/12.1.0.2/db1 -target_type rac_database -binary -invPtrLoc /u01/app/12.1.0.2/grid/oraInst.loc -jre /u01/app/12.1.0.2/grid/OPatch/jre -persistresult /u01/app/oracle/12.1.0.2/db1/OPatch/auto/dbsessioninfo/sessionresult_analyze_racnode-dc1-1_rac.ser -analyze -online
2017-11-12 18:12:45,263 INFO  [53] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction - Opatchcore binary return code=0
2017-11-12 18:12:45,265 INFO  [53] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction - Opatchcore binary output=Oracle Home : /u01/app/oracle/12.1.0.2/db1

OPatchAuto binary patching Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 13.9.3.0.0
--
Executing command as oracle:
 /bin/sh -c 'cd /u01/app/oracle/12.1.0.2/db1; ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 ORACLE_SID=orclcdb1 /u01/app/oracle/12.1.0.2/db1/OPatch/datapatch -prereq -verbose'
2017-11-12 18:19:52,146 INFO  [85] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'cd /u01/app/oracle/12.1.0.2/db1; ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 ORACLE_SID=orclcdb1 /u01/app/oracle/12.1.0.2/db1/OPatch/datapatch -prereq -verbose'
2017-11-12 18:20:20,415 INFO  [85] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:20,415 INFO  [85] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:20,415 INFO  [85] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:20,415 INFO  [85] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - SQL Patching tool version 12.1.0.2.0 Production on Sun Nov 12 18:19:52 2017
Copyright (c) 2012, 2017, Oracle.  All rights reserved.
--
Executing command as oracle:
 /bin/sh -c 'echo "DECLARE l_count NUMBER(4) :=0; BEGIN SELECT count(*) INTO l_count FROM CDB_PDBS; IF l_count >1 then EXECUTE IMMEDIATE '\''alter pluggable database ALL SAVE STATE'\''; END IF; commit; END;" > /tmp/OraDB12Home1_oracle_orclcdb1.sql'
2017-11-12 18:20:23,665 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'echo "DECLARE l_count NUMBER(4) :=0; BEGIN SELECT count(*) INTO l_count FROM CDB_PDBS; IF l_count >1 then EXECUTE IMMEDIATE '\''alter pluggable database ALL SAVE STATE'\''; END IF; commit; END;" > /tmp/OraDB12Home1_oracle_orclcdb1.sql'
2017-11-12 18:20:23,771 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:23,771 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:23,771 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:23,771 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:23,771 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'echo "/" >> /tmp/OraDB12Home1_oracle_orclcdb1.sql'
2017-11-12 18:20:23,772 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'echo "/" >> /tmp/OraDB12Home1_oracle_orclcdb1.sql'
2017-11-12 18:20:23,873 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:23,873 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:23,873 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:23,873 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:23,873 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'echo "EXIT;" >> /tmp/OraDB12Home1_oracle_orclcdb1.sql'
2017-11-12 18:20:23,873 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'echo "EXIT;" >> /tmp/OraDB12Home1_oracle_orclcdb1.sql'
2017-11-12 18:20:23,958 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:23,958 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:23,958 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:23,958 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:23,958 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'cd /u01/app/oracle/12.1.0.2/db1 ; ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 ORACLE_SID=orclcdb1 /u01/app/oracle/12.1.0.2/db1/bin/sqlplus / as sysdba @/tmp/OraDB12Home1_oracle_orclcdb1.sql'
2017-11-12 18:20:23,958 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'cd /u01/app/oracle/12.1.0.2/db1 ; ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 ORACLE_SID=orclcdb1 /u01/app/oracle/12.1.0.2/db1/bin/sqlplus / as sysdba @/tmp/OraDB12Home1_oracle_orclcdb1.sql'
2017-11-12 18:20:24,268 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:24,268 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:24,268 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:24,268 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 12 18:20:24 2017
--

Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s DBA_TEST -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:24,269 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s DBA_TEST -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:26,771 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:26,771 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:26,771 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:26,809 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:26,809 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc11 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:26,809 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc11 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:28,931 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:28,932 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:28,932 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:28,932 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:28,932 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc12 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:28,932 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc12 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:31,027 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:31,027 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:31,027 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:31,027 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:31,027 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc13 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:31,027 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc13 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:33,346 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:33,346 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:33,346 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:33,346 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:33,346 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc14 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:33,346 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc14 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:35,327 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:35,328 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:35,328 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:35,328 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:35,328 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc15 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:35,328 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl relocate service -d orclcdb -s testsvc15 -i orclcdb1 -t orclcdb2'
2017-11-12 18:20:37,885 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:20:37,885 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:20:37,885 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:20:37,885 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 18:20:37,885 INFO  [125] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.

--
Executing command as root:
 /u01/app/12.1.0.2/grid/perl/bin/perl -I/u01/app/12.1.0.2/grid/perl/lib -I/u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install /u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install/rootcrs.pl -prepatch
2017-11-12 18:20:38,384 INFO  [206] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /u01/app/12.1.0.2/grid/perl/bin/perl -I/u01/app/12.1.0.2/grid/perl/lib -I/u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install /u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install/rootcrs.pl -prepatch
2017-11-12 18:22:05,710 INFO  [206] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:22:05,711 INFO  [206] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:22:05,711 INFO  [206] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:22:05,711 INFO  [206] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Using configuration parameter file: /u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install/crsconfig_params
Oracle Clusterware active version on the cluster is [12.1.0.2.0]. The cluster upgrade state is [NORMAL]. The cluster active patch level is [0].
--
Executing command as oracle:
 /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392192/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/12.1.0.2/db1
2017-11-12 18:22:05,758 INFO  [224] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392192/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/12.1.0.2/db1
2017-11-12 18:22:05,793 INFO  [224] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:22:05,793 INFO  [224] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:22:05,793 INFO  [224] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:22:05,793 INFO  [224] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392192/custom/scripts/prepatch.sh completed successfully.

--
Executing command as oracle:
 /u01/app/oracle/12.1.0.2/db1/OPatch/opatchauto  apply /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880 -oh /u01/app/oracle/12.1.0.2/db1 -target_type rac_database -binary -invPtrLoc /u01/app/12.1.0.2/grid/oraInst.loc -jre /u01/app/12.1.0.2/grid/OPatch/jre -persistresult /u01/app/oracle/12.1.0.2/db1/OPatch/auto/dbsessioninfo/sessionresult_racnode-dc1-1_rac.ser -analyzedresult /u01/app/oracle/12.1.0.2/db1/OPatch/auto/dbsessioninfo/sessionresult_analyze_racnode-dc1-1_rac.ser
2017-11-12 18:46:34,790 INFO  [238] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction - Opatchcore binary return code=0
2017-11-12 18:46:34,801 INFO  [238] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction - Opatchcore binary output=Oracle Home : /u01/app/oracle/12.1.0.2/db1

OPatchAuto binary patching Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 13.9.3.0.0
--
Executing command as oracle:
 /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392192/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/12.1.0.2/db1
2017-11-12 18:46:35,048 INFO  [281] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880/26392192/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/12.1.0.2/db1
2017-11-12 18:46:35,337 INFO  [281] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 18:46:35,337 INFO  [281] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 18:46:35,337 INFO  [281] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 18:46:35,337 INFO  [281] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Reading /u01/app/oracle/12.1.0.2/db1/install/params.ora..
Reading /u01/app/oracle/12.1.0.2/db1/install/params.ora..
--
Executing command as oracle:
 /u01/app/12.1.0.2/grid/OPatch/opatchauto  apply /sf_OracleSoftware/12.1.0.2_PatchSet_Linux_x64/OCT2017PSU/26635880 -oh /u01/app/12.1.0.2/grid -target_type cluster -binary -invPtrLoc /u01/app/12.1.0.2/grid/oraInst.loc -jre /u01/app/12.1.0.2/grid/OPatch/jre -persistresult /u01/app/12.1.0.2/grid/OPatch/auto/dbsessioninfo/sessionresult_racnode-dc1-1_crs.ser -analyzedresult /u01/app/12.1.0.2/grid/OPatch/auto/dbsessioninfo/sessionresult_analyze_racnode-dc1-1_crs.ser
2017-11-12 19:20:43,366 INFO  [361] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction - Opatchcore binary return code=0
2017-11-12 19:20:43,369 INFO  [361] com.oracle.glcm.patch.auto.db.integration.controller.action.OPatchAutoBinaryAction - Opatchcore binary output=Oracle Home : /u01/app/12.1.0.2/grid

OPatchAuto binary patching Tool
Copyright (c)2014, Oracle Corporation. All rights reserved.

OPatchauto Version : 13.9.3.0.0
--
Executing command as root:
 /u01/app/12.1.0.2/grid/rdbms/install/rootadd_rdbms.sh
2017-11-12 19:20:43,522 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /u01/app/12.1.0.2/grid/rdbms/install/rootadd_rdbms.sh
2017-11-12 19:20:43,574 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:20:43,574 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:20:43,574 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:20:43,574 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 19:20:43,574 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as root:
 /u01/app/12.1.0.2/grid/perl/bin/perl -I/u01/app/12.1.0.2/grid/perl/lib -I/u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install /u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install/rootcrs.pl -postpatch
2017-11-12 19:20:43,574 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /u01/app/12.1.0.2/grid/perl/bin/perl -I/u01/app/12.1.0.2/grid/perl/lib -I/u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install /u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install/rootcrs.pl -postpatch
2017-11-12 19:33:19,090 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:33:19,097 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:33:19,098 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:33:19,098 INFO  [414] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Using configuration parameter file: /u01/app/12.1.0.2/grid/OPatch/auto/dbtmp/bootstrap_racnode-dc1-1/patchwork/crs/install/crsconfig_params
2017/11/12 19:20:51 CLSRSC-4015: Performing install or upgrade action for Oracle Trace File Analyzer (TFA) Collector.
--

Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s DBA_TEST -i orclcdb2'
2017-11-12 19:33:19,393 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s DBA_TEST -i orclcdb2'
2017-11-12 19:33:20,976 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:33:20,977 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:33:20,980 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:33:20,980 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 19:33:20,980 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc11 -i orclcdb2'
2017-11-12 19:33:20,980 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc11 -i orclcdb2'
2017-11-12 19:33:22,262 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:33:22,262 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:33:22,262 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:33:22,263 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 19:33:22,263 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc12 -i orclcdb2'
2017-11-12 19:33:22,263 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc12 -i orclcdb2'
2017-11-12 19:33:23,539 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:33:23,539 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:33:23,539 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:33:23,539 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 19:33:23,540 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc13 -i orclcdb2'
2017-11-12 19:33:23,540 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc13 -i orclcdb2'
2017-11-12 19:33:24,764 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:33:24,764 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:33:24,764 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:33:24,764 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 19:33:24,764 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc14 -i orclcdb2'
2017-11-12 19:33:24,764 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc14 -i orclcdb2'
2017-11-12 19:33:26,003 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:33:26,003 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:33:26,003 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:33:26,003 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 19:33:26,003 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.
--
Executing command as oracle:
 /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc15 -i orclcdb2'
2017-11-12 19:33:26,004 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 /u01/app/oracle/12.1.0.2/db1/bin/srvctl stop service -d orclcdb -s testsvc15 -i orclcdb2'
2017-11-12 19:33:27,227 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:33:27,228 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:33:27,228 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:33:27,228 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -
2017-11-12 19:33:27,228 INFO  [454] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Command executed successfully.

--
Executing command as oracle:
 /bin/sh -c 'cd /u01/app/oracle/12.1.0.2/db1;ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 ORACLE_SID=orclcdb1 /u01/app/oracle/12.1.0.2/db1/OPatch/datapatch -verbose'
2017-11-12 19:33:28,659 INFO  [524] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - COMMAND Looks like this: /bin/sh -c 'cd /u01/app/oracle/12.1.0.2/db1;ORACLE_HOME=/u01/app/oracle/12.1.0.2/db1 ORACLE_SID=orclcdb1 /u01/app/oracle/12.1.0.2/db1/OPatch/datapatch -verbose'
2017-11-12 19:34:26,362 INFO  [524] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - status: 0
2017-11-12 19:34:26,362 INFO  [524] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Successfully executed the above command.

2017-11-12 19:34:26,362 INFO  [524] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - Output from the command:
2017-11-12 19:34:26,362 INFO  [524] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor - SQL Patching tool version 12.1.0.2.0 Production on Sun Nov 12 19:33:29 2017
Copyright (c) 2012, 2017, Oracle.  All rights reserved.
[root@racnode-dc1-1 opatchauto]#
Advertisements

November 11, 2017

That’s How We’ve Always Done It Before

Filed under: Uncategorized — mdinh @ 11:13 am

Thanksgiving is coming up and to this day, I still remembered anecdote from former manager.

Daughter is helping mother prepare for Thanksgiving dinner which they do together for many years.

This year, daughter decided to ask, “Why do we cut off the turkey legs and wings before putting it the oven?”

Mom responded, “That’s how we’ve always done it before. Let’s call grandma and find out.”

On the phone with grandma and grandma’s response was, “I can’t believe you’re still doing that! I cut off the legs and wings because my oven was too small.”

Happy Thanksgiving.

 

 

November 5, 2017

GoldenGate Naming Convention P03

Filed under: GoldenGate — mdinh @ 4:44 pm

GoldenGate Naming Convention P01
GoldenGate Naming Convention P02

Here I provide an example for how I would implement 3-way replication.
I used capitalization for some clarity in trail – not tested yet.

N-way Replication calculations:
Primary Extract for each silo: 1
Pump Extract for each silo: N-1 
Replicats for each silo: N-1 
Total proceses for each silo: 2N-1
Sequence start value: (1001-100N), increment by 100

++++++++++

3-way Replication:
Primary Extract for each silo: 1
Pump Extract for each silo: 3-1=2
Replicats for each silo: 3-1=2
Total proceses for each silo: 2*3-1=5
Sequence start value: (1001,1002,1003) increment by 100

++++++++++

(Silo 1 - NYPRD) E_NY(aa) | pump (aB):(aC) | replicate (bA):(cA) | 1001+100 (sequence)
(Silo 2 - DCPRD) E_DC(bb) | pump (bA):(bC) | replicate (aB):(cB) | 1002+100 (sequence)
(Silo 3 - STDBY) E_SB(cc) | pump (cA):(cB) | replicAte (aC)|(bC) | 1003+100 (sequence)

++++++++++

E_NY1 (aa)

-- Pump include all other silos except current silo
P_NY2 (aB)
P_NY3 (aC)  

-- ReplicAt include all other silos except current silo
R_DC2 (bA)
R_SB3 (cA)

++++++++++

E_DC2 (bb)

P_DC1 (bA)
P_DC3 (bC)

R_NY1 (aB)
R_SB3 (cB)

++++++++++

E_SB3 (cc)

P_SB1 (cA)
P_SB2 (cB)

R_DC2 (bC)
R_NY1 (aC)

++++++++++

Stop replication from NYPRD:
(Silo 1) stop *NY* (stops E_NY1/P_NY2/P_NY3)

-- This may be optional depending on requirements.
-- If nothing is extracted, then nothing is replicated.
(Silo 2) stop *NY* (stops R_NY1) 
(Silo 3) stop *NY* (stops R_NY1)

Relocate Services Back To Instance Before Patching

Filed under: 12c,RAC — mdinh @ 1:16 pm

This will only work for 2-nodes RAC!

Prerequisite:
Patching starts at instance1, services failover to instance2.
Patching completed at instance1, restart instance1.
Patching starts at instance2, services failover to instance1.
Patching completed at instance2, restart instance2.
All services are now running at instance1.
Relocate services from instance2 back to where it belongs.

Save existing service configuration before patching.
[oracle@racnode-dc1-2 rac_relocate]$ ./save_service.sh

 

+ srvctl status database -d orclcdb -v
+ srvctl status database -d orclcdb -v
+ awk '-F ' '{print $2}'
+ cat /tmp/services.conf
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
+ cat /tmp/instance.conf
orclcdb1
orclcdb2
++ tail -1 /tmp/services.conf
++ awk '-F ' '{print $11}'
++ awk '{$0=substr($0,1,length($0)-1); print $0}'
+ svc=testsvc26,testsvc27,testsvc28,testsvc29
+ exit
[oracle@racnode-dc1-2 rac_relocate]$

 

Patching completed at instance1 and starting at instance2.
All services are running on instance1 after failover of instance2.

 

[oracle@racnode-dc1-2 rac_relocate]$ srvctl stop instance -db orclcdb -instance orclcdb2 -failover
[oracle@racnode-dc1-2 rac_relocate]$ srvctl status database -d orclcdb -v
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15,testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
Instance orclcdb2 is not running on node racnode-dc1-2
[oracle@racnode-dc1-2 rac_relocate]$

 

Patching completed at instance2, start instance2, all services running from instance1.

[oracle@racnode-dc1-2 rac_relocate]$ srvctl start instance -db orclcdb -instance orclcdb2
[oracle@racnode-dc1-2 rac_relocate]$ srvctl status database -d orclcdb -v
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15,testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2. Instance status: Open.
[oracle@racnode-dc1-2 rac_relocate]$

Verify relocate services will work as intended by testing first – print but not execute commands.

[oracle@racnode-dc1-2 rac_relocate]$ ./test_relocate.sh
================================================================================
++++++ Saved Configuration
-rw-r--r-- 1 oracle oinstall  18 Nov  5 13:01 /tmp/instance.conf
-rw-r--r-- 1 oracle oinstall 291 Nov  5 13:01 /tmp/services.conf
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
orclcdb1
orclcdb2
================================================================================
++++++ Relocate Configuration
newinst=orclcdb2
oldinst=orclcdb1
svc=testsvc26,testsvc27,testsvc28,testsvc29
================================================================================
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15,testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2. Instance status: Open.
srvctl relocate service -db orclcdb -service testsvc26 -oldinst orclcdb1 -newinst orclcdb2
srvctl relocate service -db orclcdb -service testsvc27 -oldinst orclcdb1 -newinst orclcdb2
srvctl relocate service -db orclcdb -service testsvc28 -oldinst orclcdb1 -newinst orclcdb2
srvctl relocate service -db orclcdb -service testsvc29 -oldinst orclcdb1 -newinst orclcdb2
[oracle@racnode-dc1-2 rac_relocate]$

Relocate services to orginal saved configuration.

[oracle@racnode-dc1-2 rac_relocate]$ ./relocate_service.sh
================================================================================
++++++ Saved Configuration
-rw-r--r-- 1 oracle oinstall  18 Nov  5 13:01 /tmp/instance.conf
-rw-r--r-- 1 oracle oinstall 291 Nov  5 13:01 /tmp/services.conf
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
orclcdb1
orclcdb2
================================================================================
++++++ Relocate Configuration
newinst=orclcdb2
oldinst=orclcdb1
svc=testsvc26,testsvc27,testsvc28,testsvc29
================================================================================
+ srvctl status database -d orclcdb -v
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15,testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2. Instance status: Open.
+ IFS=,
+ for s in '${svc}'
+ srvctl relocate service -db orclcdb -service testsvc26 -oldinst orclcdb1 -newinst orclcdb2
+ for s in '${svc}'
+ srvctl relocate service -db orclcdb -service testsvc27 -oldinst orclcdb1 -newinst orclcdb2
+ for s in '${svc}'
+ srvctl relocate service -db orclcdb -service testsvc28 -oldinst orclcdb1 -newinst orclcdb2
+ for s in '${svc}'
+ srvctl relocate service -db orclcdb -service testsvc29 -oldinst orclcdb1 -newinst orclcdb2
+ srvctl status database -d orclcdb -v
Instance orclcdb1 is running on node racnode-dc1-1 with online services DBA_TEST,testsvc11,testsvc12,testsvc13,testsvc14,testsvc15. Instance status: Open.
Instance orclcdb2 is running on node racnode-dc1-2 with online services testsvc26,testsvc27,testsvc28,testsvc29. Instance status: Open.
+ exit
[oracle@racnode-dc1-2 rac_relocate]$

I have rant about hardcoding before.
YES! I hardcoded conf file location to provide a permanent and consistent location for all environments.

I don’t like to have to dig through code for find such information.
ex:
SCRIPT_DIR=/u01/app/oracle/scripts
LOG_DIR=$SCRIPT_DIR/log

save_service.sh


#!/bin/sh -x
srvctl status database -d ${db} -v > /tmp/services.conf
srvctl status database -d ${db} -v|awk -F" " '{print $2}' > /tmp/instance.conf
cat /tmp/services.conf
cat /tmp/instance.conf
svc=`tail -1 /tmp/services.conf | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
exit

 

test_relocate.sh


#!/bin/sh
echo "================================================================================"
echo "++++++ Saved Configuration"
ls -l /tmp/*.conf
cat /tmp/services.conf
cat /tmp/instance.conf
echo "================================================================================"
echo "++++++ Relocate Configuration"
export svc=`tail -1 /tmp/services.conf | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
export oldinst=`head -1 /tmp/instance.conf`
export newinst=`tail -1 /tmp/instance.conf`
env|egrep 'svc|inst'|sort
echo "================================================================================"
srvctl status database -d ${db} -v
IFS=","
for s in ${svc}
do
echo "srvctl relocate service -db ${db} -service ${s} -oldinst ${oldinst} -newinst ${newinst}"
done
exit

 

relocate_service.sh


#!/bin/sh
echo "================================================================================"
echo "++++++ Saved Configuration"
ls -l /tmp/*.conf
cat /tmp/services.conf
cat /tmp/instance.conf
echo "================================================================================"
echo "++++++ Relocate Configuration"
export svc=`tail -1 /tmp/services.conf | awk -F" " '{print $11}'|awk '{$0=substr($0,1,length($0)-1); print $0}'`
export oldinst=`head -1 /tmp/instance.conf`
export newinst=`tail -1 /tmp/instance.conf`
env|egrep 'svc|inst'|sort
echo "================================================================================"
set -x
srvctl status database -d ${db} -v
IFS=","
for s in ${svc}
do
srvctl relocate service -db ${db} -service ${s} -oldinst ${oldinst} -newinst ${newinst}
done
srvctl status database -d ${db} -v
exit

 

12.1 Improved Service Failover

Filed under: 12c,RAC — mdinh @ 12:51 pm

11gR2 Database Services and Instance Shutdown

The thought of having to manually relocate dozens of services was not very appealing.

As it turns out, there is no need to manually relocate services.

srvctl stop instance -db orclcdb -instance orclcdb1 -failover will do the trick.

Comparing the 2 commands, 12c is a lot clearer / cleaner.

12c:
srvctl add service -db orclcdb -service DBA_TEST -preferred orclcdb1 -available orclcdb2 -failovertype SELECT -tafpolicy BASIC

11g:
srvctl add service -d orclcdb -s DBA_TEST -P BASIC -e SELECT -r orclcdb1 -a orclcdb2

DEMO:

$ srvctl config service -d orclcdb -s DBA_TEST|egrep -i 'Service name|Preferred instances|Available instances|failover'

Service name: DBA_TEST
Failover type: SELECT
Failover method:
TAF failover retries:
TAF failover delay:
Preferred instances: orclcdb1
Available instances: orclcdb2

$ srvctl status database -d orclcdb

Instance orclcdb1 is running on node racnode-dc1-1
Instance orclcdb2 is running on node racnode-dc1-2

$ sqlplus mdinh/mdinh@dbatest @t.sql

SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 5 04:17:56 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Nov 05 2017 04:15:29 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options


   INST_ID STARTUP_TIME
---------- -----------------------------
         1 05-NOV-2017 04:12:55
         2 05-NOV-2017 04:14:49


   INST_ID FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ---------- ---
         1 NONE          NONE       NO
         1 SELECT        BASIC      NO
         2 NONE          NONE       NO

04:17:57 MDINH @ dbatest:>host
[oracle@racnode-dc1-1 ~]$ srvctl stop instance -db orclcdb -instance orclcdb1 -failover;date
Sun Nov 5 04:18:34 CET 2017
[oracle@racnode-dc1-1 ~]$ exit
exit

04:18:37 MDINH @ dbatest:>@t.sql

   INST_ID STARTUP_TIME
---------- -----------------------------
         2 05-NOV-2017 04:14:49


   INST_ID FAILOVER_TYPE FAILOVER_M FAI
---------- ------------- ---------- ---
         2 SELECT        BASIC      YES

04:18:40 MDINH @ dbatest:>

November 4, 2017

GoldenGate Naming Convention P02

Filed under: GoldenGate — mdinh @ 1:05 pm

GoldenGate Naming Convention P01

Bidirectional replication:

E_10G (write to aa) | P_10G (read from aa, write to ab) | R_10G (read from ab, write to 12C DB)
E_12C (write to bb) | P_12C (read from bb, write to ba) | R_12C (read from ba, write to 10G DB)

Source (10G DB)     | Target (12C DB)
-------------------------------------
E_10G [>aa]         | E_12C [>bb]
P_10G [>ab]         | P_12C [>ba]
R_12C [<ba]         | R_10G [<ab]

Create same process name for extract, pump, replicat.

Using the example above:
stop 10G replication, stop *10G at source and target.
stop 12C replication, stop *12C at source and target.

Another method.

Source (10G DB)     | Target (12C DB)
-------------------------------------
E_10G [>aa]         | E_12C [>bb]
P_10G [>ab]         | P_12C [>ba]
R_10G [<ba]         | R_12C [<ab]

Process name was created based on DB versions.

Using the example above:
stop 10G replication, stop E_10G,P_10G at source and stop R_12C at target.
stop 12C replication, stop E_12C,P_12C at source and stop R_10G at target.

Of the 2 methods, which do you prefer?

Splitting extracts:

Source      | Target
-------------------------------------
E_USR [>aa] | R_JOE [<az]
P_JOE [>az] | R_SUE [<ay]
P_SUE [>ay] | R_AMY [<ax]
P_AMY [>ax] |

How does one know where source is from? You don’t unless you comment the parameters.

Example: RAC environment where VIP is used for PUMP

EXTRACT e_hawk
-- CHECKPARAMS
-- ADD EXTRACT e_hawk, INTEGRATED TRANLOG, BEGIN NOW
-- ADD EXTTRAIL ./dirdat/aa EXTRACT e_hawk, MEGABYTES 500

EXTRACT p_hawk
-- CHECKPARAMS
-- Target: host03/04
-- ADD EXTRACT p_hawk, EXTTRAILSOURCE ./dirdat/aa
-- ADD RMTTRAIL ./dirdat/ab, EXTRACT p_hawk, MEGABYTES 500
RMTHOST OGG_VIP MGRPORT 7801, TCPBUFSIZE 1048576, TCPFLUSHBYTES 1048576 

REPLICAT r_hawk
-- CHECKPARAMS
-- Source: host01/02
-- REGISTER REPLICAT r_hawk DATABASE
-- ADD REPLICAT r_hawk, INTEGRATED, EXTTRAIL ./dirdat/ab

In conclusion, there is really no best practice, but some thought and planning do help.

Goldengate XAG HAS

Filed under: GoldenGate — mdinh @ 12:53 am

If you install GI for SI DB, then you might as well install XAG for OGG.

Imagine if there is a Vagrant to put all of this together?

[oracle@db-asm-1 xag]$ mkdir -p /u01/app/oracle/xag

[oracle@db-asm-1 xag]$ ./xagsetup.sh --install --directory /u01/app/oracle/xag
Installing Oracle Grid Infrastructure Agents on: db-asm-1
Done.

[oracle@db-asm-1 ~]$ cd /u01/app/oracle/xag/bin/

[oracle@db-asm-1 bin]$ ./agctl query releaseversion
The Oracle Grid Infrastructure Agents release version is 8.1.0

[oracle@db-asm-1 bin]$ ./agctl query deployment
The Oracle Grid Infrastructure Agents deployment is standalone

[oracle@db-asm-1 bin]$ ./agctl add goldengate --help
Adds Goldengate instance to Oracle Clusterware.

[oracle@db-asm-1 bin]$ ./agctl add goldengate ogg_amer \
> --instance_type dual --databases ora.amer.db \
> --gg_home /u01/app/oracle/amer/ogg/12.3.0_ora12c \
>  --oracle_home /u01/app/oracle/12.2.0.1/db1

[oracle@db-asm-1 bin]$ ./agctl status goldengate ogg_amer
Goldengate  instance 'ogg_amer' is not running

[oracle@db-asm-1 bin]$ ./agctl start goldengate ogg_amer

[oracle@db-asm-1 bin]$ ./agctl status goldengate ogg_amer
Goldengate  instance 'ogg_amer' is running on db-asm-1

[oracle@db-asm-1 bin]$ ./agctl config goldengate
XAG-212: Instance '' is not yet registered.

[oracle@db-asm-1 bin]$ ./agctl config goldengate ogg_amer
GoldenGate location is: /u01/app/oracle/amer/ogg/12.3.0_ora12c
GoldenGate instance type is: dual
ORACLE_HOME location is: /u01/app/oracle/12.2.0.1/db1
Databases needed: ora.amer.db
EXTRACT groups to monitor:
REPLICAT groups to monitor:
Critical EXTRACT groups:
Critical REPLICAT groups:
Autostart on DataGuard role transition to PRIMARY: no
Autostart JAgent: no
[oracle@db-asm-1 bin]$

oracle@db-asm-1:+ASM:/home/oracle
$ ./crs_stat.sh
The Oracle base remains unchanged with value /u01/app/oracle
NAME                                          TARGET     STATE           SERVER       STATE_DETAILS
-------------------------                     ---------- ----------      ------------ ------------------
                                              Name       Target          State        Server State
ora.CRS.dg                                    ONLINE     ONLINE          db-asm-1     STABLE
ora.DATA.dg                                   ONLINE     ONLINE          db-asm-1     STABLE
ora.FRA.dg                                    ONLINE     ONLINE          db-asm-1     STABLE
ora.LISTENER.lsnr                             ONLINE     ONLINE          db-asm-1     STABLE
ora.asm                                       ONLINE     ONLINE          db-asm-1     Started,STABLE
ora.ons                                       OFFLINE    OFFLINE         db-asm-1     STABLE
ora.amer.db                                   ONLINE     ONLINE          db-asm-1     Open,HOME=/u01/app/o
ora.cssd                                      ONLINE     ONLINE          db-asm-1     STABLE
ora.diskmon                                   OFFLINE    OFFLINE         STABLE
ora.euro.db                                   ONLINE     ONLINE          db-asm-1     Open,HOME=/u01/app/o
ora.evmd                                      ONLINE     ONLINE          db-asm-1     STABLE
xag.ogg_amer.goldengate                       ONLINE     ONLINE          db-asm-1     STABLE

oracle@db-asm-1:+ASM:/home/oracle
$ crsctl stop has
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.CRS.dg' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.euro.db' on 'db-asm-1'
CRS-2673: Attempting to stop 'xag.ogg_amer.goldengate' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'db-asm-1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.euro.db' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.CRS.dg' on 'db-asm-1' succeeded
CRS-2677: Stop of 'xag.ogg_amer.goldengate' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.amer.db' on 'db-asm-1'
CRS-2677: Stop of 'ora.amer.db' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'db-asm-1'
CRS-2677: Stop of 'ora.DATA.dg' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.evmd' on 'db-asm-1'
CRS-2673: Attempting to stop 'ora.FRA.dg' on 'db-asm-1'
CRS-2677: Stop of 'ora.FRA.dg' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'db-asm-1'
CRS-2677: Stop of 'ora.evmd' on 'db-asm-1' succeeded
CRS-2677: Stop of 'ora.asm' on 'db-asm-1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'db-asm-1'
CRS-2677: Stop of 'ora.cssd' on 'db-asm-1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'db-asm-1' has completed
CRS-4133: Oracle High Availability Services has been stopped.

November 3, 2017

GoldenGate Naming Convention P01

Filed under: GoldenGate — mdinh @ 10:33 pm

I had a nice discussion with teammates about standards.

It’s wrong if there are no standards or naming conventions; otherwise, let your imagination run wild.

Hence, before you embark, think about it as it will make life much easier.

For prompt: I like to know what ORACLE_SID for environment.

oracle@db-asm-1:amer:/home/oracle
oracle@db-asm-1:euro:/home/oracle

For Goldengate: I did it this way because there are 2 DBs / 2 GGs for the same host.

Why ora12c? There are 2 options when installing Goldengate: ORA11g|ORA12c

/u01/app/oracle/amer/ogg/12.3.0_ora12c
/u01/app/oracle/euro/ogg/12.3.0_ora12c

There was discussion ogg/gg/ggs – doesn’t really matter.

ogg may seem redundant if gg is installed for Oracle DB / Client.

Where should it reside? I had planned for /u02 and /u03 but Vagrant was not being nice to me.

Why different mount? There are GG directories and trails which will fill up.

I like to KISS and avoid soft links.

One thing that does annoy is using $GGHOME.

This may raise the question, “Why GG_HOME and not GGHOME?”

Why? ORACLE_BASE, ORACLE_HOME, ORACLE_SID, GRID_HOME, etc…

There’s a pattern here.

Make life simple, use aliases.

oracle@db-asm-1:+ASM:/home/oracle
$ cat .bash_profile

# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi
# User specific environment and startup programs
export PATH=$PATH:$HOME/.local/bin:$HOME/bin
. ~/.alias

oracle@db-asm-1:+ASM:/home/oracle
$ cat .alias

alias amer='source ~/.amer'
alias euro='source ~/.euro'
alias ggs='cd $GG_HOME'

oracle@db-asm-1:+ASM:/home/oracle
$ cat .amer

export LD_LIBRARY_PATH=/lib:/usr/lib
export ORACLE_SID=amer
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
export GG_HOME=/u01/app/oracle/amer/ogg/12.3.0_ora12c
export LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "

oracle@db-asm-1:+ASM:/home/oracle
$ amer

The Oracle base remains unchanged with value /u01/app/oracle
oracle@db-asm-1:amer:/home/oracle
$ env|egrep 'ORACLE|HOME'
ORACLE_SID=amer
ORACLE_BASE=/u01/app/oracle
GG_HOME=/u01/app/oracle/amer/ogg/12.3.0_ora12c
PS1=\u@\h:${ORACLE_SID}:${PWD}\n$
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/12.2.0.1/db1

oracle@db-asm-1:amer:/home/oracle
$ cat .euro

export LD_LIBRARY_PATH=/lib:/usr/lib
export ORACLE_SID=euro
ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK
export GG_HOME=/u01/app/oracle/euro/ogg/12.3.0_ora12c
export LD_LIBRARY_PATH=$GG_HOME:$LD_LIBRARY_PATH
export PS1="\u@\h:\${ORACLE_SID}:\${PWD}\n$ "

oracle@db-asm-1:amer:/home/oracle
$ euro

The Oracle base remains unchanged with value /u01/app/oracle
oracle@db-asm-1:euro:/home/oracle
$ env|egrep 'ORACLE|HOME'
ORACLE_SID=euro
ORACLE_BASE=/u01/app/oracle
GG_HOME=/u01/app/oracle/euro/ogg/12.3.0_ora12c
PS1=\u@\h:${ORACLE_SID}:${PWD}\n$
HOME=/home/oracle
ORACLE_HOME=/u01/app/oracle/12.2.0.1/db1

oracle@db-asm-1:euro:/u01/app/oracle/euro/ogg/12.3.0_ora12c
$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.

GGSCI (db-asm-1) 1> exit

$ grep ORA oggcore.rsp

# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
#         ORA11g for installing Oracle GoldenGate for Oracle Database 11g

November 2, 2017

Monitoring Standby – SQLPlus or DGMGRL

Filed under: Dataguard — mdinh @ 3:00 am

Here is an example using dgmgrl


DGMGRL> show database roverdb SendQEntries
PRIMARY_SEND_QUEUE
        STANDBY_NAME       STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs) 
           roverstby     ARCHIVED        936921167                1                13019  10/31/2017 10:47:04  10/31/2017 10:48:31     746413367424     746413483999          1819004 
           roverstby     ARCHIVED        936921167                1                13023  10/31/2017 10:51:40  10/31/2017 10:52:19     746413767648     746413883688          1809094 
           roverstby     ARCHIVED        936921167                1                13031  10/31/2017 10:57:02  10/31/2017 10:57:44     746414728981     746414851377          1924909 
           roverstby     ARCHIVED        936921167                1                13032  10/31/2017 10:57:44  10/31/2017 10:58:23     746414851377     746414967877          1815042 
           roverstby     ARCHIVED        936921167                1                13033  10/31/2017 10:58:23  10/31/2017 10:59:02     746414967877     746415089206          1798857 
           roverstby     ARCHIVED        936921167                1                13034  10/31/2017 10:59:02  10/31/2017 10:59:41     746415089206     746415217514          1818919 
                          CURRENT        936921167                1                13036  10/31/2017 11:29:41                          746415239037                               628 
                          CURRENT        936921167                2                12359  10/31/2017 11:29:05                          746415238854                               864 

DGMGRL> show database roverstby RecvQEntries
STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID           THREAD              LOG_SEQ       TIME_GENERATED       TIME_COMPLETED    FIRST_CHANGE#     NEXT_CHANGE#       SIZE (KBs) 
         NOT_APPLIED        936921167                1                13020  10/31/2017 10:48:31  10/31/2017 10:48:49     746413483999     746413509640           385949 
         NOT_APPLIED        936921167                1                13021  10/31/2017 10:48:49  10/31/2017 10:50:19     746413509640     746413636246          1885417 
         NOT_APPLIED        936921167                1                13022  10/31/2017 10:50:19  10/31/2017 10:51:40     746413636246     746413767648          1944637 
         NOT_APPLIED        936921167                1                13024  10/31/2017 10:52:19  10/31/2017 10:52:58     746413883688     746413999759          1819116 
         NOT_APPLIED        936921167                1                13025  10/31/2017 10:52:58  10/31/2017 10:53:40     746413999759     746414124264          1868420 
         NOT_APPLIED        936921167                1                13026  10/31/2017 10:53:40  10/31/2017 10:54:22     746414124264     746414244619          1890478 
         NOT_APPLIED        936921167                1                13027  10/31/2017 10:54:22  10/31/2017 10:55:02     746414244619     746414363387          1843514 
         NOT_APPLIED        936921167                1                13028  10/31/2017 10:55:02  10/31/2017 10:55:41     746414363387     746414484244          1818826 
         NOT_APPLIED        936921167                1                13029  10/31/2017 10:55:41  10/31/2017 10:56:20     746414484244     746414605367          1813344 
         NOT_APPLIED        936921167                1                13030  10/31/2017 10:56:20  10/31/2017 10:57:02     746414605367     746414728981          1904385 
         NOT_APPLIED        936921167                1                13035  10/31/2017 10:59:41  10/31/2017 11:29:41     746415217514     746415239037            79395 
   PARTIALLY_APPLIED        936921167                2                12352  10/31/2017 10:40:04  10/31/2017 10:47:07     746413130730     746413371576             1980 
         NOT_APPLIED        936921167                2                12353  10/31/2017 10:47:07  10/31/2017 10:50:22     746413371576     746413640990             1658 
         NOT_APPLIED        936921167                2                12354  10/31/2017 10:50:22  10/31/2017 10:53:01     746413640990     746414010894             1774 
         NOT_APPLIED        936921167                2                12355  10/31/2017 10:53:01  10/31/2017 10:55:04     746414010894     746414371654             1541 
         NOT_APPLIED        936921167                2                12356  10/31/2017 10:55:04  10/31/2017 10:57:04     746414371654     746414736501             1532 
         NOT_APPLIED        936921167                2                12357  10/31/2017 10:57:04  10/31/2017 10:59:04     746414736501     746415097318             1485 
         NOT_APPLIED        936921167                2                12358  10/31/2017 10:59:04  10/31/2017 11:29:05     746415097318     746415238854             6101 

October 28, 2017

Use ORACLE_UNQNAME for DataGuard Environment

Filed under: 11g,Dataguard — mdinh @ 2:25 pm

If you are running only 1 database on the host, then it may not be useful.

However, if you run multiple databases, then it makes it easier to automate provided there are consistencies and/or conventions.

DB configuration

HOST01:(SYS@qa):PHYSICAL STANDBY> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      qa
db_unique_name                       string      qadr

OS configuration

$ env|grep ORACLE
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=qa
ORACLE_UNQNAME=qadr
ORACLE_HOME=/u01/app/oracle/db/11g
$ ps -ef|grep pmon
  oracle  9896050        1   0 16:11:12      -  0:03 asm_pmon_+ASM
  oracle 10354862        1   0 20:06:31      -  0:02 ora_pmon_qa

Check DB status using srvctl

srvctl status database -d $ORACLE_UNQNAME -v
Database qadr is running with online services qarosvc
#!/bin/sh -e
. /opt/oracle/oracle_qa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
exit
$ ./d.sh
DGMGRL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration

Configuration - dgqa

  Protection Mode: MaxPerformance
  Databases:
    qa   - Primary database
    qadr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database qa

Database - qa

  Enterprise Manager Name: qa_cluster
  Role:                    PRIMARY
  Intended State:          TRANSPORT-ON
  Instance(s):
    qa_1
    qa_2

Database Status:
SUCCESS

DGMGRL> show database qadr

Database - qadr

  Enterprise Manager Name: qa1
  Role:                    PHYSICAL STANDBY
  Intended State:          APPLY-ON
  Transport Lag:           0 seconds (computed 0 seconds ago)
  Apply Lag:               0 seconds (computed 1 second ago)
  Apply Rate:              937.00 KByte/s
  Real Time Query:         ON
  Instance(s):
    qa

Database Status:
SUCCESS

DGMGRL> exit

crsctl stat res -w “STATE = ONLINE”|egrep “db$|TYPE=ora.database.type”

NAME=ora.qadr.db
TYPE=ora.database.type
NAME=ora.qa2dr.db
TYPE=ora.database.type
NAME=ora.stageqadr.db
TYPE=ora.database.type
NAME=ora.testdr.db
TYPE=ora.database.type

dg_show.sh

#!/bin/sh -e
. /opt/oracle/oracle_qa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_qa2_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_stageqa_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
. /opt/oracle/oracle_test_env
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
exit

Improved dg_show.sh using function.

#!/bin/sh -e
check_dg()
{
  dgmgrl -echo << END
  connect /
  show configuration
  show database ${ORACLE_SID}
  show database ${ORACLE_UNQNAME}
  exit
  END
}
. /opt/oracle/oracle_qa_env
check_dg
. /opt/oracle/oracle_qa2_env
check_dg
. /opt/oracle/oracle_stageqa_env
check_dg
. /opt/oracle/oracle_test_env
check_dg
exit
Next Page »

Create a free website or blog at WordPress.com.