Tuesday, 19 June 2012

Applying CPU patch on DataGuard Physical Standby Configuration.


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>

No comments:

Post a Comment