Upgrading to Oracle 10g with a Logical Standby in Place

The procedures below contain information for upgrading your current release of Oracle to the new 10g release when a logical standby database is present in the configuration.

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 with 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.

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;

On the standby system:

6.      If you are using Real Application Clusters, shutdown (NORMAL or IMMEDIATE) all but one standby database instance.

7.      On the active standby instance, verify that each log file archived in Step 5 has been received by the standby database by querying the DBA_LOGSTDBY_LOG view. For example, to verify that the log file associated with thread number 1 and sequence number 12 was received by the logical standby database, you could repeatedly execute the following query on the standby database until it returned the name of the archived log file:

         SQL> SELECT FILE_NAME FROM DBA_LOGSTDBY_LOG WHERE THREAD#=1 AND SEQUENCE#=12;

8.      Verify that all remaining redo logs have been applied by querying the DBA_LOGSTDBY_PROGRESS view on the standby database. For example:

         SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

When the numbers in the APPLIED_SCN and NEWEST_SCN columns are equal, all available data in the redo logs received by the standby database has been applied.

9.      Stop SQL apply operations on the standby database.

         SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

10.  Cleanly shut down the active standby 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 primary system:

11.  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

12.  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.  After 10.1.0.2 has been installed, with your environment still set to the 9.2 installation, startup the primary database and disable remote archiving.

         SQL> STARTUP MIGRATE;
         SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;

14.  From the 10.1.0.2 ORACLE_HOME start the Database Upgrade Assistant and upgrade the 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.

15.  Once the database has been upgraded, change your environment to point to the new 10g installation, shutdown the primary database instance, and restart the agent and listener.

         SQL> SHUTDOWN IMMEDIATE;
         % agentctl start
         % lsnrctl start

16.  Startup the primary database instance and enable restricted session to reduce the likelihood of users or applications performing any DML or DDL operations.

         ______________________________________________________________
         CAUTION: Do not allow any DML or DDL operations to occur until
         after restricted session mode is disabled in step 18.
         ______________________________________________________________

         SQL> STARTUP MOUNT;
         SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

17.  Open the primary database and build the logminer dictionary.

         SQL> ALTER DATABASE OPEN;
         SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

1.      Disable restricted session mode on the primary database and archive the current logfile.

         SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
         SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

2.      On the primary database instance, run the following to determine the latest dictionary build log file:

         SQL> SELECT NAME FROM V$ARCHIVED_LOG
         2> WHERE (SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
         3> WHERE DICTIONARY_BEGIN = ?YES? AND STANDBY_DEST= ?NO?));

    Record the name of the log file returned by the query for later reference.

On the standby system:

3.      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.

4.      After 10.1.0.2 has been installed, with your environment still set to the 9.2 installation, startup the logical standby, activate it, and disable remote archiving.

         SQL> STARTUP MIGRATE;

SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;
         SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;

         _____________________________________________________________________
         CAUTION: Do not allow users to update the activated standby database
         because these changes will not be propagated to the primary database.
         _____________________________________________________________________

5.      From the 10.1.0.2 ORACLE_HOME start the Database Upgrade Assistant and upgrade the logical standby database.

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

% ./dbua

6.      Once the logical standby database has been upgraded, shutdown the instance and restart the agent and listener.

         SQL> SHUTDOWN IMMEDIATE;
         % agentctl start
         % lsnrctl start

7.      Copy the latest dictionary build log file identified in step 19 from the primary system to the standby system.

8.      Startup the logical standby database instance and turn on the database guard to prevent users from updating objects in the logical standby database:

         SQL> STARTUP MOUNT;
         SQL> ALTER DATABASE GUARD ALL;
         SQL> ALTER DATABASE OPEN;

9.      Register the copied logfile on the logical standby. For example,

         SQL> ALTER DATABASE REGISTER LOGICAL
         2> LOGFILE '/database/mtsmith/LGSTBY/arch/arc1_48.arc';
    

10.  Begin SQL apply operations on the standby:

         SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

Note: The above command will initially fail with the following error:

ORA-16101: a valid start SCN could not be found

To resolve the error simply register the logical logifile as done in step 26 and run the command in step 27 once again.

11.  If you are using Real Application Clusters, startup the other standby database instances.

On the primary system:

12.  Begin remote archiving to the standby database.

         SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

13.  If you are using Real Application Clusters, startup the other primary database instances.