B Upgrading Databases in a Data Guard Configuration

B.1 Before You Upgrade the Oracle Database Software

Consider the following points before beginning to upgrade your Oracle Database software:

• If you are using the Data Guard broker to manage your configuration, follow the instructions in Oracle Data Guard Broker manual for information about removing or disabling the broker configuration.

• The procedures in this appendix are to be used in conjunction with the ones contained in the Oracle Database Upgrade Guide for 10g release 2 (10.2).

• The procedures in this appendix use the Database Upgrade Assistant (DBUA) to perform the upgrade. For instructions on performing the upgrade manually, refer to the Oracle Database Upgrade Guide. The manual upgrade steps described should be performed whenever use of DBUA is mentioned.

• Check for nologging operations. If nologging operations have been performed then you must update the standby database. See Section 12.10, "Recovering After the NOLOGGING Clause Is Specified" for details.

• Make note of any tablespaces or datafiles that need recovery due to OFFLINE IMMEDIATE. Tablespaces or data files should be recovered and either online or offline prior to upgrading.

B.2 Upgrading Oracle Database with a Physical Standby Database In Place

Perform the following steps to upgrade to Oracle Database 10g Release 2 (10.2) when a physical standby database is present in the configuration:

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

2. On the primary and standby systems, log in to each system as the owner of the Oracle software directory, and set the environment to the existing (9.2 or 10.1) installation.

3. On the primary database, stop all user activity on the primary database.

4. If you are using Real Application Clusters, issue a SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE statement on all but one primary database instance.

   SQL> SHUTDOWN IMMEDIATE;
   
   

   Then, on the remaining active database instance, archive the current log file. For example:

   SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
   
   

   This ensures all available archived redo data from the Real Application Clusters instances is transmitted 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. 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
   

7. On the standby system, 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 Redo Apply, query the V$LOG_HISTORY view to verify that each log file archived in Step 5 has been received and applied to the standby database. For example:

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

9. Once the last log has been applied stop Redo Apply, shut down the standby database, and stop all listeners and agents running against the current (9.2 or 10.1) database.

   SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
   SQL> SHUTDOWN IMMEDIATE;
   % agentctl stop
   % lsnrctl stop
   
   

10. On the standby system, install Oracle Database 10g Release 2 (10.2) into its own Oracle home using the Oracle Universal Installer, as described in the Oracle Database Upgrade Guide. To ensure an error-free upgrade, it is recommended that you also install the Companion CD. Do not perform any other upgrade steps.

11. Copy the server parameter file (SPFILE), password file, and any necessary networking files from the old (9.2 or 10.1) ORACLE_HOME directory into the new 10.2 ORACLE_HOME directory. Note that for the standby database to receive redo from the primary database, the standby database must have the REMOTE_LOGIN_EXCLUSIVE initialization parameter set to SHARED or EXCLUSIVE, a password file must exist, and the SYS password on the standby database must be the same as the SYS password on the primary database.

12. On the primary system, install Oracle Database 10g Release 2 (10.2) into its own Oracle home using the Oracle Universal Installer as described in the Oracle Database Upgrade Guide. To ensure an error-free upgrade, it is recommended that you also install the Companion CD.

13. Include the Oracle Net service name in the TNSNAMES.ORA file that resides in the release 10.2 Oracle_Home.

14. After 10.2 has been installed, with your environment still set to the old (9.2 or 10.1) installation, startup the primary database in UPGRADE mode:

    SQL> STARTUP UPGRADE;
    
    

15. From the 10.2 Oracle_Home, start the Database Upgrade Assistant and upgrade the primary database.

    %  cd /u01/app/oracle/product/10.2/bin
    %  ./dbua
    
    

    
    

    Note: The old (9.2 or 10.1) database must be included in the oratab file to be seen by the Database Upgrade Assistant. For complete information on using DBUA, see the Oracle Database Upgrade Guide.

    
    
    
    

    Note: You may receive errors in the alert log on the primary database stating that the primary database is unable to contact the standby database. You can ignore this error; This error is expected because the standby database has not been restarted up to this point.

    
    

16. After the upgrade process has begun on the primary database, start the standby listener on the standby database running the new Oracle Database 10g Release 2 (10.2) software.

17. With the environment set to the new 10.2 software installation, issue the STARTUP NOMOUNT statement on the standby database. Ensure that the STANDBY_FILE_MANAGEMENT initialization parameter on the standby database is set to AUTO and that FAL_SERVER and FAL_CLIENT are properly configured:

    • The FAL_SERVER should be set to an Oracle Net service name that exists in the standby database's TNSNAMES.ORA file.

    • The FAL_CLIENT should be set to an Oracle Net service name that exists in the primary database's TNSNAMES.ORA file that points to the standby database listener.

    The Oracle Net service names must be able to be resolved in the new 10.2 Oracle_Home. For example:

    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;
    
    

18. Mount the standby database and start Redo Apply:

    SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
    
    

19. Once the Database Upgrade Assistant has completed, on the primary system, configure your environment to the new 10.2 Oracle_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;
    
    

20. On the standby instance, query the V$LOG_HISTORY view to verify that each log file archived in Step 19 has been received and applied on the standby database:

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

B.3 Upgrading Oracle Database with a Logical Standby Database In Place

Perform the following steps to upgrade Oracle Database 10g Release 2 (10.2) when a logical standby database is present in the configuration:

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

2. On the primary and standby systems, log in to each system as the owner of the Oracle software directory, and set the environment to the existing (9.2 or 10.1) installation.

3. On the primary system, stop all user activity on the primary database.

4. If you are using Real Application Clusters, issue a SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE statement on all but one primary database instance.

   SQL> SHUTDOWN IMMEDIATE;
   
   

   Then, on the remaining active database instance, archive the current log file:

   SQL> SHUTDOWN IMMEDIATE;
   SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
   
   

   This ensures all available archived redo data from the Real Application Clusters instances is transmitted 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. If you are using Real Application Clusters on the standby system, issue the SHUTDOWN NORMAL or SHUTDOWN IMMEDIATE statement on all but one standby 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 run the following query on the standby database until it returns the name of the archived redo log file:

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

8. Verify that all remaining redo log files 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 log files received by the standby database has been applied.

9. Stop SQL Apply on the standby database:

   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
   
   

10. Shut down all active standby database instances with either the NORMAL or the IMMEDIATE priority. Stop all listeners, agents, and other processes running against the Oracle home:

    SQL> SHUTDOWN IMMEDIATE;
    % agentctl stop
    % lsnrctl stop
    
    

11. Shut down the primary database instance with either the NORMAL or the IMMEDIATE priority. Stop all listeners, agents, and other processes running against the Oracle home:

    SQL> SHUTDOWN IMMEDIATE;
    % agentctl stop
    % lsnrctl stop
    
    

12. On the primary system, install Oracle Database 10g Release 2 (10.2) into its own Oracle home using the Oracle Universal Installer, as described in the Oracle Database Upgrade Guide. To ensure an error-free upgrade, it is recommended that you also install the Companion CD. Do not perform any other upgrade steps.

13. After Oracle Database 10g Release 2 (10.2) has been installed, and with your environment still set to the old (9.2 or 10.1) installation, startup the primary database in UPGRADE mode:

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

14. From the release 10.2 ORACLE_HOME, start the Database Upgrade Assistant and upgrade the primary database. For example:

    %  cd /u01/app/oracle/product/10.2/bin
    %  ./dbua
    

    
    

    Note: The old (9.2 or 10.1) database must be included in the oratab file to be seen by the Database Upgrade Assistant. For complete information on using DBUA, see the Oracle Database Upgrade Guide.

    
    

15. Once the database has been upgraded, change your environment to point to the new Oracle Database 10g Release 2 (10.2) installation, shut down 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:

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

    
    

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

    
    

17. Open the primary database and build the LogMiner dictionary:

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

18. Disable restricted session mode on the primary database and archive the current log file.

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

19. On the primary database instance, issue the following query to determine the latest LogMiner 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.

20. On the standby system, install Oracle Database 10g Release 2 (10.2) in its own Oracle home using the Oracle Universal Installer, as described in the Oracle Database Upgrade Guide. To ensure an error-free upgrade, it is recommended that you also install the Companion CD. Do not perform any other upgrade steps.

21. After Oracle Database 10g Release 2 (10.2) has been installed, with your environment still set to the old (9.2 or 10.1) installation, startup the logical standby database in UPGRADE mode, activate it, and defer remote archiving:

    SQL> STARTUP UPGRADE;
    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 the changes will not be propagated to the primary database.

    
    

22. From the 10.2 Oracle_Home directory, start the Database Upgrade Assistant (DBUA) and upgrade the logical standby database.

    %  cd /u01/app/oracle/product/10.2/bin
    %  ./dbua
    
    

23. Once the logical standby database has been upgraded, shut down the instance and restart the agent and listener:

    SQL> SHUTDOWN IMMEDIATE;
    % agentctl start
    % lsnrctl start
    
    

24. Copy the latest LogMiner dictionary build log file (identified in step 19) from the primary system to the standby system.

25. 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;
    
    

26. Register the copied log file on the logical standby database. For example:

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

27. Start SQL Apply on the logical standby database:

    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, register the logical log file as described in step 26 and reissue the statement to start SQL Apply.

    
    

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

29. On the primary system, enable archiving to the upgraded logical standby database.

    SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
    
    

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