Applying CPU Patch in a DataGuard Physical Standby Database Configuration
I seen lot of questions in OTN on patching of DataGuard, Recently I have applied CPU Patch on production database, I like to give step-by-step procedure how to apply CPUJAN2012(13343244).
Standby is in MAXIMUM PERFORMANCE Mode with Data Broker Enabled.When managing with broker, there are some steps which we need to manage with Broker instead of SQL. Which is highly recommended, If not there may be issues in Broker configuration.
1. In Primary disable log shipping to the standby & Stop MRP on standby
2. Shutdown Standby Database & Listener
3. Install CPU patch on Standby
4. Shutdown Primary database & Listener.
5. Apply Patch on Primary & Run Post installation Scripts(catbundle.sql)
6. Re-enable Log shipping on Primary
7. Monitor the redo apply from Primary to Standby
Environment:-
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Linux: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production SQL>
oracle@primary> file /bin/ls
/bin/ls: ELF 64-bit LSB executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped oracle@primary>
Opatch Version
To apply CPUJan2012, OPatch utility version 11.2.0.1.0 or later to apply this patch. Oracle recommends that you use
the latest released OPatch 11.2, which is available for download from My Oracle Support patch 6880880 by
selecting the 11.2.0.0.0 release
oracle-ckpt.com> export PATH=/u00/app/oracle/product/11.2.0/OPatch:$PATH oracle-ckpt.com> opatch lsinventory Invoking OPatch 11.2.0.1.1 Oracle Interim Patch Installer version 11.2.0.1.1 Copyright (c) 2009, Oracle Corporation. All rights reserved. Oracle Home : /u00/app/oracle/product/11.2.0 Central Inventory : /u00/app/oraInventory from : /etc/oraInst.loc OPatch version : 11.2.0.1.1 OUI version : 11.2.0.2.0 OUI location : /u00/app/oracle/product/11.2.0/oui Log file location : /u00/app/oracle/product/11.2.0/cfgtoollogs/opatch/opatch2012-03-03_06-32-39AM.log Patch history file: /u00/app/oracle/product/11.2.0/cfgtoollogs/opatch/opatch_history.txt Lsinventory Output file location : /u00/app/oracle/product/11.2.0/cfgtoollogs/opatch/lsinv/lsinventory2012-03-03_06-32-39AM.txt --------------------------------------------------------------------------------
1) In Primary disable log shipping to the standby & Stop MRP on standby
oracle@primary> dgmgrl / DGMGRL for Linux: Version 11.2.0.2.0 - 64bit Production Copyright (c) 2000, 2009, Oracle. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected. DGMGRL> edit database PSPRIM set state='LOG-TRANSPORT-OFF'; Succeeded. DGMGRL>
Check the remote destination Status:-
oracle@primary> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Apr 28 22:43:55 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string RESET
Check the Archive Log status:-
ID STATUS DB_MODE TYPE RECOVERY_MODE PROTECTION_MODE SRLs ACTIVE ARCHIVED_SEQ#
--- --------- --------------- ---- ----------------------- -------------------- ---- ------ ---------------
1 VALID OPEN ARCH IDLE MAXIMUM PERFORMANCE 0 0 0
2 DEFERRED MOUNTED-STANDBY LGWR MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 10 1 106896
2) Shutdown Standby Database & Listener
oracle@standby> ps -ef|grep pmon oracle 8016 30235 0 02:17 pts/0 00:00:00 grep pmon oracle@standby>> ps -ef|grep tns oracle 8019 30235 0 02:17 pts/0 00:00:00 grep tns oracle@standby>
Take Backup of ORACLE_HOME & Inventory on Standby
oracle@standby> tar -zcpvf 11.2.0_Home_Inventory_Backup_$(date +%Y%m%d).tar.gz /u00 /app/oracle/product/11.2.0 /u00/app/oraInventory /u00/app/oracle/product/11.2.0/ /u00/app/oracle/product/11.2.0/database/ /u00/app/oracle/product/11.2.0/database/cv/ /u00/app/oracle/product/11.2.0/database/cv/cvutl/ /u00/app/oracle/product/11.2.0/database/cv/cvutl/check_nodeadd.pl /u00/app/oracle/product/11.2.0/database/cv/admin/ - - - - - - /u00/app/oraInventory/ /u00/app/oraInventory/oraInstaller.properties /u00/app/oraInventory/install.platform /u00/app/oraInventory/orainstRoot.sh /u00/app/oraInventory/oui/ /u00/app/oraInventory/oui/srcs.lst /u00/app/oraInventory/logs/ /u00/app/oraInventory/logs/OPatch2011-06-22_09-50-15-PM.log /u00/app/oraInventory/logs/oraInstall2011-06-21_12-35-26AM.out /u00/app/oraInventory/logs/installActions2011-06-21_12-35-26AM.log /u00/app/oraInventory/logs/oraInstall2011-06-21_12-35-26AM.err /u00/app/oraInventory/oraInst.loc /u00/app/oraInventory/ContentsXML/ /u00/app/oraInventory/ContentsXML/comps.xml /u00/app/oraInventory/ContentsXML/libs.xml /u00/app/oraInventory/ContentsXML/inventory.xml oracle@standby>
3. Install CPU patch on Standby
oracle@standby> opatch napply -skip_subset -skip_duplicate
Invoking OPatch 11.2.0.1.1
Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation. All rights reserved.
UTIL session
Oracle Home : /u00/app/oracle/product/11.2.0/database
Central Inventory : /u00/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.1
OUI version : 11.2.0.2.0
OUI location : /u00/app/oracle/product/11.2.0/database/oui
Log file location : /u00/app/oracle/product/11.2.0/database/cfgtoollogs/opatch/opatch2012-04-28_22-57-58PM.log
Patch history file: /u00/app/oracle/product/11.2.0/database/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "napply"
Checking conflict among patches...
Checking if Oracle Home has components required by patches...
Checking skip_duplicate
Checking skip_subset
Checking conflicts against Oracle Home...
OPatch continues with these patches: 11830776 11830777 12586486 12586487 12586488 12586489 12586491 12586492 12586493 12586494 12586495 12586496 12846268 12846269 13343244 13386082 13468884
Do you want to proceed? [y|n]
y
User Responded with: Y
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u00/app/oracle/product/11.2.0/database')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NApply' for restore. This might take a while...
Applying patch 11830776...
ApplySession applying interim patch '11830776' to OH '/u00/app/oracle/product/11.2.0/database'
Backing up files affected by the patch '11830776' for rollback. This might take a while...
Verifying the update...
Inventory check OK: Patch ID 13343244 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 13343244 are present in Oracle Home.
Applying patch 13386082...
ApplySession applying interim patch '13386082' to OH '/u00/app/oracle/product/11.2.0/database'
ApplySession: Optional component(s) [ oracle.network.cman, 11.2.0.2.0 ] not present in the Oracle Home or a higher version is found.
Backing up files affected by the patch '13386082' for rollback. This might take a while...
Patching component oracle.network.rsf, 11.2.0.2.0...
Updating archive file "/u00/app/oracle/product/11.2.0/database/lib/libnro11.a" with "lib/libnro11.a/ncrfidr.o"
Patching component oracle.rdbms, 11.2.0.2.0...
ApplySession adding interim patch '13386082' to inventory
Verifying the update...
Inventory check OK: Patch ID 13386082 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 13386082 are present in Oracle Home.
Applying patch 13468884...
ApplySession applying interim patch '13468884' to OH '/u00/app/oracle/product/11.2.0/database'
Backing up files affected by the patch '13468884' for rollback. This might take a while...
Verifying the update...
Inventory check OK: Patch ID 13468884 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 13468884 are present in Oracle Home.
Running make for target client_sharedlib
Running make for target client_sharedlib
Running make for target ioracle
The local system has been patched and can be restarted.
UtilSession: N-Apply done.
OPatch succeeded.
oracle@standby>
Start Standby database in mount status
SQL> startup mount; Total System Global Area 3.2068E+10 bytes Fixed Size 2242320 bytes Variable Size 2.2616E+10 bytes Database Buffers 9395240960 bytes Redo Buffers 55267328 bytes Database mounted. SQL>
DataGuard Broker enabled, So MRP will be started automatically, No need to start MRP manually
4. Shutdown Primary database & Listener.
oracle@primary> ps -ef|grep pmon oracle 8016 30235 0 02:17 pts/0 00:00:00 grep pmon oracle@standby>> ps -ef|grep tns oracle 8019 30235 0 02:17 pts/0 00:00:00 grep tns oracle@primary>
Take Backup of ORACLE_HOME & Inventory on Primary
oracle@Primary> tar -zcpvf 11.2.0_Home_Inventory_Backup_$(date +%Y%m%d).tar.gz /u00 /app/oracle/product/11.2.0 /u00/app/oraInventory /u00/app/oracle/product/11.2.0/ /u00/app/oracle/product/11.2.0/database/ /u00/app/oracle/product/11.2.0/database/cv/ /u00/app/oracle/product/11.2.0/database/cv/cvutl/ /u00/app/oracle/product/11.2.0/database/cv/cvutl/check_nodeadd.pl /u00/app/oracle/product/11.2.0/database/cv/admin/ - - - - - - /u00/app/oraInventory/ /u00/app/oraInventory/oraInstaller.properties /u00/app/oraInventory/install.platform /u00/app/oraInventory/orainstRoot.sh /u00/app/oraInventory/oui/ /u00/app/oraInventory/oui/srcs.lst /u00/app/oraInventory/logs/ /u00/app/oraInventory/logs/OPatch2011-06-22_09-50-15-PM.log /u00/app/oraInventory/logs/oraInstall2011-06-21_12-35-26AM.out /u00/app/oraInventory/logs/installActions2011-06-21_12-35-26AM.log /u00/app/oraInventory/logs/oraInstall2011-06-21_12-35-26AM.err /u00/app/oraInventory/oraInst.loc /u00/app/oraInventory/ContentsXML/ /u00/app/oraInventory/ContentsXML/comps.xml /u00/app/oraInventory/ContentsXML/libs.xml /u00/app/oraInventory/ContentsXML/inventory.xml oracle@primary>
5. Apply Patch on Primary & Run Post installation Scripts(catbundle.sql)
oracle@primary> export PATH=$ORACLE_HOME/OPatch:$PATH:
oracle@primary> cd $ORACLE_HOME/../Patches/ 13343244
oracle@primary> ls -ltr
total 4772
drwxrwxr-x 19 oracle oinstall 4096 Jan 15 12:07 13343244
-rw-r--r-- 1 oracle dba 4823614 Jan 30 10:36 p13343244_112020_Linux-x86-64.zip
oracle@primary> pwd
/u00/app/oracle/product/11.2.0/Patches
oracle@primary> cd 13343244
oracle@primary> opatch napply -skip_subset -skip_duplicate
Invoking OPatch 11.2.0.1.1
Oracle Interim Patch Installer version 11.2.0.1.1
Copyright (c) 2009, Oracle Corporation. All rights reserved.
UTIL session
Oracle Home : /u00/app/oracle/product/11.2.0/database
Central Inventory : /u00/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.2.0.1.1
OUI version : 11.2.0.2.0
OUI location : /u00/app/oracle/product/11.2.0/database/oui
Log file location : /u00/app/oracle/product/11.2.0/database/cfgtoollogs/opatch/opatch2012-04-28_23-46-42PM.log
Patch history file: /u00/app/oracle/product/11.2.0/database/cfgtoollogs/opatch/opatch_history.txt
Invoking utility "napply"
Checking conflict among patches...
Checking if Oracle Home has components required by patches...
Checking skip_duplicate
Checking skip_subset
Checking conflicts against Oracle Home...
OPatch continues with these patches: 11830776 11830777 12586486 12586487 12586488 12586489 12586491 12586492 12586493 12586494 12586495 12586496 12846268 12846269 13343244 13386082 13468884
Do you want to proceed? [y|n]
y
User Responded with: Y
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u00/app/oracle/product/11.2.0/database')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch 'NApply' for restore. This might take a while...
Applying patch 11830776...
ApplySession applying interim patch '11830776' to OH '/u00/app/oracle/product/11.2.0/database'
Backing up files affected by the patch '11830776' for rollback. This might take a while...
Patching component oracle.sysman.console.db, 11.2.0.2.0...
Updating jar file "/u00/app/oracle/product/11.2.0/database/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/admin/rep/AdminResourceBundle.class"
Updating jar file "/u00/app/oracle/product/11.2.0/database/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/admin/rep/AdminResourceBundleID.class"
Updating jar file "/u00/app/oracle/product/11.2.0/database/sysman/jlib/emCORE.jar" with "/sysman/jlib/emCORE.jar/oracle/sysman/eml/admin/rep/UserData.class"
Copying file to "/u00/app/oracle/product/11.2.0/database/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"
Verifying the update...
Inventory check OK: Patch ID 13468884 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 13468884 are present in Oracle Home.
Running make for target client_sharedlib
Running make for target client_sharedlib
Running make for target ioracle
The local system has been patched and can be restarted.
UtilSession: N-Apply done.
OPatch succeeded.
oracle@primary>
Start Database in OPEN mode
oracle@primary> sqlplus "/ as sysdba" SQL> startup ORACLE instance started. Total System Global Area 3.2068E+10 bytes Fixed Size 2242320 bytes Variable Size 2.2616E+10 bytes Database Buffers 9395240960 bytes Redo Buffers 55267328 bytes Database mounted. Database opened. SQL>
Run “catbundle.sql”
SQL> @?/rdbms/admin/catbunldle.sql cpu apply
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u00/app/oracle/product/cfgtoollogs/catbundle/catbundle_CPU_psprim_GENERATE_2012Apr28_23_53_16.log
Apply script: /u00/app/oracle/product/11.2.0/database/rdbms/admin/catbundle_CPU_psprim_APPLY.sql
Rollback script: /u00/app/oracle/product/11.2.0/database/rdbms/admin/catbundle_CPU_psprim_ROLLBACK.sql
PL/SQL procedure successfully completed.
Executing script file...
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/u00/app/oracle/product/cfgtoollogs/catbundle/' || 'catbundle_CPU_' || name || '_APPLY_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> PROMPT Processing EM Repository...
Processing EM Repository...
SQL> ALTER SESSION SET current_schema = SYSMAN;
Session altered.
SQL> @?/sysman/admin/emdrep/sql/db/latest/policy/config_util_pkgdef.sql
SQL> Rem drv: <create type="pkgdefs"/>
SQL> PROMPT Updating registry...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.2',
9 4,
10 'CPU',
11 'CPUJan2012');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u00/app/oracle/product/cfgtoollogs/catbundle/catbundle_CPU_psprim_APPLY_2012Apr28_23_53_21.log
SQL>
Compile Invalid objects by executing “utlrp.sql”.
SQL> @?/rdbms/admin/utlrp.sql TIMESTAMP -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2012-04-28 23:54:20 DOC> The following query reports the number of errors caught during DOC> recompilation. If this number is non-zero, please query the error DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors DOC> are due to misconfiguration or resource constraints that must be DOC> fixed before objects can compile successfully. DOC># ERRORS DURING RECOMPILATION --------------------------- 0 Function created. PL/SQL procedure successfully completed. Function dropped. PL/SQL procedure successfully completed. SQL>
6. Check Patch Registry & Re-enable Log shipping on Primary
SQL> select ACTION_TIME,VERSION,ID,COMMENTS from registry$history;
ACTION_TIME VERSION ID COMMENTS
------------------------------ ---------- ---------- --------------------
05-SEP-10 06.22.14.370943 AM 11.2.0.2 0 Patchset 11.2.0.2.0
20-MAY-11 06.18.50.526828 AM 11.2.0.2 0 Patchset 11.2.0.2.0
28-APR-12 11.53.31.208266 PM 11.2.0.2 4 CPUJan2012
Re-Enable Log Shipping on Primary:-
DGMGRL> edit database psprim set state='ONLINE'; Succeeded. DGMGRL> exit
7. Monitor the redo apply from Primary to Standby
oracle@standby> tail -f /u00/app/oracle/diag/rdbms/psprimsby1/psprim/trace/alert_psprim.log Archived Log entry 48459 added for thread 1 sequence 106898 ID 0x6bf95f8a dest 1: Sun Apr 29 00:07:58 2012 Media Recovery Log /u04/arch/psprim/1_106899_751616314.dbf Sun Apr 29 00:07:58 2012 Archived Log entry 48460 added for thread 1 sequence 106901 ID 0x6bf95f8a dest 1: Media Recovery Log /u04/arch/psprim/1_106900_751616314.dbf Media Recovery Log /u04/arch/psprim/1_106901_751616314.dbf Media Recovery Waiting for thread 1 sequence 106902 (in transit)
Primary:-
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
106916
SQL>
Standby:-
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
106915 SQL>
Check Broker Status:-
DGMGRL> show database verbose PSPRIM
Database - PSPRIM
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
PSPRIM
Properties:
DGConnectIdentifier = 'PSPRIM'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = ''
LogFileNameConvert = '/u01/oradata/PSPRIM, /u01/oradata/PSPRIM'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
SidName = 'PSPRIM'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle-ckpt.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=PSPRIM_DGMGRL)(INSTANCE_NAME=PSPRIM)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u04/arch/PSPRIM'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
TopWaitEvents = '(monitor)'
Database Status:
SUCCESS
DGMGRL>
Make sure Broker configuration is everything fine. Hope this helps…. 🙂
