Upgrading to Oracle 10g with a Physical Standby in place

The procedures below contain information about upgrading your current release of Oracle to the new 10g release for a configuration that includes a physical standby database. This document on only covers the upgrade path of 9i Release 2 to 10g.

Considerations:

• Any Data Guard broker configurations, created by Data Guard Manager or the CLI, must be removed prior to performing the upgrade procedure below. Once the upgrade is complete the broker configuration can be recreated at the new version. Please refer to the Oracle? Data Guard Broker 10g Release 1 (10.1) guide for complete steps.
• The following procedure uses the Database Upgrade Assistant to perform. the actual upgrade. For instructions on performing the upgrade manually please refer to the Oracle? Database Upgrade Guide. The manual upgrade steps described should be performed whenever usage of dbua is mentioned.
• These procedures are to be used in conjunction with the ones contained within the Oracle? Database Upgrade Guide10g Release 1 (10.1) guide. This document complements and supplements the existing 10g upgrade documentation; it does not replace the current documentation.
• Check for the existence of nologging operations. If nologging operations have been performed then the standby will need to be updated. Please refer to Chapter 6, scenario 4 of the Oracle9i Data Guard Concepts and Administration guide for further details.
• Make note of any tablespaces or datafiles that need recovery due to offline immediate. Tablespaces or datafiles should be recovered and either online or offline prior to upgrading.

Upgrade Procedures

1.                         Review and perform. steps listed in Chapter 2 Preparing to Upgrade from the Oracle? Database Upgrade Guide.

On the primary and standby systems:

2.      Login to each system as the owner of the Oracle software directory and set the environment to the existing 9.2.0 installation.

On the primary system:

3.      Stop all user activity on the primary database.

4.      If you are using Real Application Clusters, shutdown (NORMAL or IMMEDIATE) all but one primary database instance. Then, on the remaining active database instance, archive the current log file:

         SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
 
This will ensure that all available archived redo data from the Real Application Clusters instances is transported to the standby database.

5.      On the active primary database instance, identify and record the current log thread and sequence number. Then, archive the current log.

         SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';
         SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

6.      Cleanly shut down the active primary database instance with normal or immediate priority. Stop all listeners, agents and other processes running against the ORACLE_HOME.

         SQL> SHUTDOWN IMMEDIATE;
         % agentctl stop
         % lsnrctl stop

On the standby system:

7.      If you are using Real Application Clusters, shutdown (NORMAL or IMMEDIATE) all but one standby database instance. Place the remaining standby database instance is currently in managed recovery.

8.      On the active standby instance that is running managed recovery, verify that each log file archived in Step 5 has been received by the standby database and applied by querying the V$LOG_HISTORY view.

         SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;

9.      Once the last log has been applied stop managed recovery, shutdown the standby database, and stop all listeners and agents running against the 9.2 installation.

               SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

               SQL> SHUTDOWN IMMEDIATE;

               % agentctl stop

               % lsnrctl stop

10.  On the standby host install 10.1.0.2 into its own Oracle home using the Oracle Universal Installer as outlined in the upgrade documentation.  It is recommended you install the Companion CD as well to insure an error-free upgrade.

11.  Copy the initialization parameter file / spfile, password file, and any necessary networking files from the 9i ORACLE_HOME/dbs directory into the new 10g ORACLE_HOME/dbs directory. Please note that in order for the standby to receive archived redo logs from the primary the standby database must have remote_login_exclusive set to shared or exclusive, a password file must exist, and the sys password must be the same as the sys password on the primary.

      On the primary system:

12.  On the primary host install 10.1.0.2 into its own Oracle home using the Oracle Universal Installer as outlined in the upgrade documentation.  It is recommended you install the Companion CD as well to insure an error-free upgrade.

13.  Ensure that the OracleNet sevice name that is used in the remote archive destination to connect to the standby will be resolvable when the primary database is mounted in the new 10g home by the Database Upgrade assistant. The OracleNet service name should be included in the tnsnames.ora that resides in the 10g Oracle home.

14.  After 10.1.0.2 has been installed, with your environment still set to the 9.2 installation, startup the primary database.

         SQL> STARTUP MIGRATE;

15.  In order to optimize performance the 10g Release 1 and lower versions of the Database Upgrade Assistant will disable archive log mode during the upgrade process. As this will invalidate the standby we must disable this option.

                 a) Within the new 10g home open the following file:      ?/rdbms/admin/utlu101x.sql 

                 b) Ssearch for a line with the text DisableArchiveLogMode and change the value to false.  For instance:

                                    Modify the line 

                                   '<DisableArchiveLogMode value="true"/>'); 

                                   to 

                                  '<DisableArchiveLogMode value="false"/>'); 

                 c) Save the file.

               RESULT: This will cause the database to be upgraded in Archive log mode.

16.  From the 10.1.0.2 ORACLE_HOME start the Database Upgrade Assistant and upgrade the primary database.

% cd /u01/app/oracle/product/10.1/bin

% ./dbua

Note: The 9.2 database must be included in the oratab file in order to be seen by the upgrade assistant. For complete information on using the Database Upgrade Assistant please see the Oracle? Database Upgrade Guide.

Note: You may receive errors in the primary alert log stating that the primary database is unable to contact the standby. This is normal as we have not restarted the standby up to this point.

On the standby system:

17.  After the upgrade process has begun on the primary database, start the standby listener on the new 10.1.0.2 software.

18.  With the environment set to the new 10.1.0.2 software installation bring the standby to the nomount state. Ensure that the STANDBY_FILE_MANAGEMENT parameter on the standby is set to AUTO and that FAL_SERVER and FAL_CLIENT is properly configured. FAL_SERVER should be set to an OracleNet service name that exist in the standby tnsnames.ora FAL_CLIENT should be set to an OracleNet service name that exist in the primary tnsnames.ora file that points to the standby database listener. The OracleNet service names must be able to be resolved within the new 10g Oracle home.

SQL> STARTUP NOMOUNT

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_SERVER=MTS SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_CLIENT=MTS_PHY SCOPE=BOTH;

19.  Mount the standby and start managed recovery.

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;

On the primary system:

20.  Once the Database Upgrade Assistant has completed, configure your environment to the new 10g home and connect to the primary database. Identify and record the current log thread and sequence number. Then, archive the current log.

         SQL> SELECT THREAD#, SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';
         SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

21.  On the standby instance, verify that each log file archived in Step 20 has been received by the standby database and applied by querying the V$LOG_HISTORY view.

         SQL> SELECT MAX(SEQUENCE#) FROM V$LOG_HISTORY;