Applying Patch Sets with Data Guard in Place

Starting in Oracle 9i, DBAs had the option to apply major patch sets to an existing configuration that may include one or more physical and logical standby database environments (9.0.1 for physical standby and 9.2.0 for logical standby). Although the methods remain the same, the steps have evolved with the new features and commands.

The steps that follow only apply to 10.1.0.2 and higher installations. As with all patch sets, these steps should be used to supplement the latest edition of the patch set README file. For the latest patch set README, please visit MetaLink or OTN.

If your Data Guard environment also uses Real Application Clusters (RAC), the following procedures should be performed on only one node of the system unless otherwise noted in a particular step.

If you are using the Data Guard Broker, you will have to disable the Broker prior to running these procedures. You can disable the Broker via the command line utility dgmgrl or by changing the db_broker parameter to FALSE.

Patching a Physical Database

It's a good idea to review the patch set's known-issues document from MetaLink. The known-issues document details information that was not included or was missed in the patch set README or instructions document. The known-issues document also includes bugs that are introduced in the specific patch.

Following are the steps required to apply any necessary patches if you are using a physical standby database in your Data Guard environment:

1.	Log in to the Oracle account on both the primary and standby hosts. Ensure that you have your ORACLE_HOME and ORACLE_SID environment variables set properly.
2.	On both the primary and standby hosts, uncompress and untar the patch set in your new patch directory.
3.	Perform a clean shutdown of the existing primary instance (normal or immediate) and stop all listeners, agents, and processes running under the ORACLE_HOME. SQL> shutdown immediate $ lsnrctl stop $ emctl stopall If running RAC, perform these steps on all nodes. If running Automatic Storage Management (ASM), shut down all databases that run on ASM and then shut down the ASM instance: Shut down all RAC instances. Shut down ASM instances. Shut down all processes running the ORACLE_HOME on each node. Shut down CRS processes on all nodes under the CRS_HOME. $ /etc/init.d/init.crs stop
4.	Cancel managed recovery on the standby database: SQL> recover managed standby database cancel;
5.	Shut down the standby instance on the standby host. Stop all instances, listeners, agents, and other processes by following the instructions in step 3.
6.	Install the patch set on both the primary and standby host using the installer. Be sure to read the patch set instructions and README prior to installing the patch. $ ./runInstaller
7.	When the patch set has been completed on all hosts and nodes, start up the standby listener and mount the standby database: $ lsnrctl start $ sqlplus /nolog SQL> connect / as sysdba SQL> startup mount
8.	Place the standby database in managed recover mode: SQL> recover managed standby database nodelay disconnect;
9.	Start up the primary listener and instance in upgrade mode on the primary host: $ lsnrctl start $ sqlplus /nolog SQL> connect / as sysdba SQL> startup upgrade
10.	Verify that remote archiving to the standby database is functioning correctly by checking the STATUS column from V$ARCHIVE_DEST view. You may also want to switch the log files on the primary database in case you notice any discrepancy. SQL> alter system archive log current SQL> select dest_id, status from v$archive_dest
11.	On the primary instance, run catpatch.sql to update the metadata from the patch install: SQL> spool catpatch_10.1.0.3.log SQL> @?/rdbms/admin/catpatch.sql SQL> spool off
12.	Review the catpatch spool log and check for errors. If needed, correct any errors and execute catpatch.sql again.
13.	When catpatch.sql completes, make note of the log sequence number and issue the following: SQL> alter system archive log current
14.	Restart the primary database: SQL> shutdown immediate SQL> startup
15.	Verify that the standby database has been recovered to the log sequence number from step 13: SQL> select max(sequence#) from v$log_history;

From this point on, you should complete any remaining tasks from the post install actions from the patch set instructions or known-issues document. Understand that it is not necessary to shut down the standby database in conjunction with the primary during any post install action task.

If needed, you can check the max log sequence number to ensure that the standby has been recovered to the last archive log produced by the primary. From the primary, run the following command:

 SQL> select max(sequence#) from v$archived_log; 

From the standby, run the following command:

 SQL> select max(sequence#) from v$log_history; 

Patching a Logical Database

Until a new Oracle patch is made available that will allow you to use the new 10g SQL Apply features to apply the patch without any downtime, the following steps need to be taken to patch your database with a logical standby in place:

1.	Log in to the Oracle account on both the primary and standby hosts. Ensure that you have your ORACLE_HOME and ORACLE_SID environment variables set properly.
2.	On both the primary and standby hosts, uncompress and untar the patch set in your new patch directory.
3.	On your primary host, stop all user activity on the primary database. If you are running RAC, shut down all but one instance (normal or immediate). On your remaining instance, identify and record the current log thread and sequence number, and then archive the current log to ensure that all available redo data from your primary is shipped over to the standby database: SQL> select thread#, sequence# from v$log where status = 'CURRENT'; SQL> alter system archive log current;
4.	On both standby hosts, if you are using RAC, shut down all but one instance (normal or immediate). On your remaining instance, verify that you have received the redo log from step 3 by querying DBA_LOGSTDBY_LOG view. Use the thread and sequence numbers to qualify your command. SQL> select file_name from dba_logstdby_log where thread#=5 and sequence#=51;
5.	Verify that all remaining redo logs have been applied by querying the DBA_LOGSTDBY_PROGRESS view. When the values in the applied_scn and newest_scn columns are equal, all available data from the primary has been received by the standby. SQL> select applied_scn, newest_scn from dba_logstdby_progress;
6.	Stop SQL Apply operations on the standby database: SQL> alter database stop logical standby apply;
7.	Perform a clean shutdown of the standby instance (normal or immediate) and stop all listeners, agents, and processes running under the ORACLE_HOME. SQL> shutdown immediate $ lsnrctl stop $ emctl stopall If running RAC on your standby, then do the following: Shut down all RAC instances. Shut down ASM instances. Shut down all processes running the ORACLE_HOME. Shut down CRS processes on all nodes under the CRS_HOME. $ /etc/init.d/init.crs stop
8.	On your primary host, perform a clean shutdown of the primary instance (normal or immediate) and stop all listeners, agents, and processes running under the ORACLE_HOME. SQL> shutdown immediate $ lsnrctl stop $ emctl stopall If running RAC on your standby: Shut down all RAC instances. Shut down ASM instances. Shut down all processes running the ORACLE_HOME. Shut down CRS processes on all nodes under the CRS_HOME. $ /etc/init.d/init.crs stop
9.	Install the patch set on the primary database as outlined in the patch set instructions. Follow any post-install steps if required by Oracle support.
10.	After the patch set has been installed, start up the primary database in upgrade mode, disable remote archiving, and run catpatch.sql: $ sqlplus /nolog SQL> connect / as sysdba SQL> startup upgrade SQL> alter system set log_archive_dest_state_2=DEFER; SQL> spool catpatch_10.1.0.3.log SQL> @?/rdbms/admin/catpatch.sql SQL> spool off
11.	Review the catpatch spool log and check for errors. If needed, correct any errors and execute catpatch.sql again.
12.	Shut down the primary database instance and restart the listener and agent: SQL> shutdown immediate $ lsnrctl start $ emctl start
13.	Start up the primary instance in restricted session mode to restrict any user DML/DDL: SQL> startup mount SQL> alter system enable restricted session
14.	Open the primary database and build the LogMiner dictionary: SQL> alter database open SQL> exec dbms_logstdby.build;
15.	Disable restricted session mode on the primary instance and archive the current log file. Then capture the latest dictionary build log: [View full width] SQL> alter system disable restricted session; SQL> alter system archive log current; SQL> select name from v$archived_log where (sequence#=(select max(sequence#) from v$archived_log 2> where dictionary_begin='YES' and standby_dest='NO'));
16.	On the standby host, install the patch set on the standby database as outlined in the patch set instructions. Follow any post-install steps if required by Oracle support.
17.	After the patch set has been installed, start up the standby database in migrate mode, activate the standby database, disable remote archiving, and run catpatch.sql: $ sqlplus /nolog SQL> connect / as sysdba SQL> startup migrate SQL> alter database activate logical standby database; SQL> alter system set log_archive_dest_state_2=DEFER; SQL> spool catpatch_10.1.0.3.log SQL> @?/rdbms/admin/catpatch.sql SQL> spool off
18.	Review the catpatch spool log and check for errors. If needed, correct any errors and execute catpatch.sql again.
19.	Shut down the standby database instance and restart the listener and agent: SQL> shutdown immediate $ lsnrctl start $ emctl start
20.	Copy the latest dictionary build log, identified in step 15, from the primary host to the standby host.
21.	Start up the standby database instance and turn on database guard to restrict users from updating any objects: SQL> startup mount SQL> alter database guard all SQL> alter database open
22.	Register the copied log file from step 20 on the standby database: SQL> alter database register logical logfile '/ora1/drprod/standby/arc2_51.log';
23.	Begin SQL Apply operations on the standby database: SQL> alter database start logical standby apply initial; If you were using RAC on your standby host, start up the other standby instances.
24.	Begin remote archiving to the standby database: SQL> alter system set log_archive_dest_state_2=ENABLE; If you were using RAC on your primary host, start up the other primary instances.

To minimize the total downtime for the patch process, you can optionally perform steps 911 (install the patch on the primary) in parallel with steps 1618 (install the patch on the standby). If you choose to run these steps in parallel, however, you must complete steps 1215 on the primary host before you can run step 20 on the standby host.