Dataguard : Applying CPU(Cirital Patch Update) to Dataguard Environment

I have One Primary database and 2 Physical Standby Databases under RHEL 5. Here is a process to patch them with JULY CPU update “Critical Patch Update Release 10.2.0.4 for UNIX Released July 13, 2010″

At Primary Site
SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE
———————————— ———– ——————————
dg_broker_start                      boolean     TRUE

SQL> alter system switch logfile;
System altered.

Make sure the Logs are shipped and both Standby Database and applied. Following SQLs could be used to locate the log sequences on all databases.

Locate Primary Log Sequence :   Select Archived_Seq# From V$Archive_Dest_Status Where Dest_Id=1;

Locate Standby Log Sequence :  Select Archived_Seq#, Applied_Seq# From V$Archive_Dest_Status Where Dest_Id=2;

Locate DR Log Sequence :   Select Archived_Seq#, Applied_Seq# From V$Archive_Dest_Status Where Dest_Id=3;

Find out existing Patch details using following queries :

SELECT comments, action_time, id “PATCH_NUMBER”, version FROM sys.registry$history WHERE action = ‘CPU’;

COMMENTS	ACTION_TIME	PATCH_NUMBER	VERSION
view recompilation	42:39.2	6452863
view recompilation	59:20.3	6452863
view recompilation	23:58.7	6452863
view recompilation	56:19.9	6452863

SELECT comments, action_time, id PATCH_NUMBER, version FROM sys.registry$history

COMMENTS	ACTION_TIME	PATCH_NUMBER	VERSION
Upgraded from 10.2.0.1.0	40:28.8		10.2.0.4.0
CPUApr2009	46:06.0	4	10.2.0.4
view recompilation	42:39.2	6452863
CPUOct2009	56:35.7	6	10.2.0.4
view recompilation	59:20.3	6452863
CPUJan2010	01:47.4	6	10.2.0.4
view recompilation	23:58.7	6452863

Backup Primary database  & then shutdown Primary Database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Take TAR backup of ORACLE_HOME

$tar -czvf ORACLE_BKUP.tgz $ORACLE_HOME

At Standby & DR Site
[oracle@Standby bdump]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Mar 29 18:26:46 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> conn / as sysdba
Connected.
SQL> select NAME,open_mode,GUARD_STATUS,DATABASE_ROLE from v$database;

NAME      OPEN_MODE  GUARD_S DATABASE_ROLE
——— ———- ——- —————-
ROD  MOUNTED    NONE    PHYSICAL STANDBY

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down. (Took 5 mins + to shutdown)

Take TAR backup of Standby ORACLE_HOME

$tar -czvf ORACLE_BKUP.tgz $ORACLE_HOME

[oracle@ ~]$ lsnrctl stop LISTENER
[oracle@ ~]$ ps -ef | grep oracle
oracle    3861  3802  0 18:45 pts/0    00:00:00 -bash
oracle    7309  7287  0 20:30 pts/1    00:00:01 -bash
oracle    8907  7309  0 21:19 pts/1    00:00:00 ps -ef
oracle    8908  7309  0 21:19 pts/1    00:00:00 grep oracle

cd $ORACLE_HOME/patches
[oracle@patches]$ ls -lrt
total 12
drwxr-xr-x  5 oracle oracle 4096 Feb 15  2010 9119284
drwxr-xr-x 37 oracle oracle 4096 Feb 15  2010 9119226
drwxr-xr-x 42 oracle oracle 4096 Jul  6 16:53 9655017

[oracle@patches]$ cd 9655017

[oracle@patches]$ $ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate

Stop here go back to Primary now

oracle@ ~]$ lsnrctl stop LISTENER
[oracle@ ~]$ ps -ef | grep oracle
oracle    3861  3802  0 18:45 pts/0    00:00:00 -bash
oracle    7309  7287  0 20:30 pts/1    00:00:01 -bash
oracle    8907  7309  0 21:19 pts/1    00:00:00 ps -ef
oracle    8908  7309  0 21:19 pts/1    00:00:00 grep oracle

cd $ORACLE_HOME/patches
[oracle@patches]$ ls -lrt
total 12
drwxr-xr-x  5 oracle oracle 4096 Feb 15  2010 9119284
drwxr-xr-x 37 oracle oracle 4096 Feb 15  2010 9119226
drwxr-xr-x 42 oracle oracle 4096 Jul  6 16:53 9655017

[oracle@patches]$ cd 9655017

[oracle@patches]$ $ORACLE_HOME/OPatch/opatch napply -skip_subset -skip_duplicate

For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus.

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> — Execute the next statement only if this is the first 10.2.0.4 CPU applied in the Oracle home.
SQL> @utlrp.sql
SQL> QUIT

Run the view recompilation script. Note that this script is run with the database in upgrade mode, which restricts connections as SYSDBA.

cd $ORACLE_HOME/cpu/view_recompile
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> @view_recompile_jan2008cpu.sql
SQL> SHUTDOWN;
SQL> STARTUP;
SQL> QUIT

Startup Primary Listener

At Standby & DR Site

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 2030296 bytes
Variable Size 503317800 bytes
Database Buffers 1627389952 bytes
Redo Buffers 14745600 bytes

SQL> show parameter dg_broker_start
NAME TYPE VALUE
———————————— ———– ——————————
dg_broker_start boolean TRUE

SQL> alter database mount standby database;
Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.

Startup Listeners and monitor alrtlogs at Stanby servers for Log Recovery

Redo Shipping Client Connected as PUBLIC
– Connected User is Valid
RFS[3]: Assigned to RFS process 7804
RFS[3]: Identified database type as ‘physical standby’
RFS[3]: Archived Log: ‘/u06/oradata/prod/arch/1_65479_684936861.arc’
Wed Sep 29 22:08:08 2010
Media Recovery Log /u06/oradata/prod/arch/1_65469_684936861.arc
Wed Sep 29 22:08:08 2010
RFS[1]: Archived Log: ‘/u06/oradata/prod/arch/1_65477_684936861.arc’
Wed Sep 29 22:08:09 2010
Media Recovery Log /u06/oradata/prod/arch/1_65470_684936861.arc
Media Recovery Log /u06/oradata/prod/arch/1_65471_684936861.arc
Media Recovery Log /u06/oradata/prod/arch/1_65472_684936861.arc
Media Recovery Log /u06/oradata/prod/arch/1_65473_684936861.arc
Media Recovery Log /u06/oradata/prod/arch/1_65474_684936861.arc
Media Recovery Log /u06/oradata/prod/arch/1_65475_684936861.arc
Media Recovery Log /u06/oradata/prod/arch/1_65476_684936861.arc

Finally run following commands to make sure logs are appied at standbydatabases:

Locate Primary Log Sequence :   Select Archived_Seq# From V$Archive_Dest_Status Where Dest_Id=1;

Locate Standby Log Sequence :  Select Archived_Seq#, Applied_Seq# From V$Archive_Dest_Status Where Dest_Id=2;

Locate DR Log Sequence :   Select Archived_Seq#, Applied_Seq# From V$Archive_Dest_Status Where Dest_Id=3;

Sample log files attached  opatch_history-1.txt & opatch.log