B Upgrading and Downgrading Databases in an Oracle 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 Oracle Data Guard broker to manage your configuration, follow the instructions in the 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.

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

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

• In an Oracle Data Guard configuration, all physical and snapshot standby databases must use a copy of the password file from the primary database, and that copy must be refreshed whenever an administrative privilege (SYSDG, SYSOPER, SYSDBA, and so on) is granted or revoked, and after the password of any user with administrative privileges is changed.

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

Perform the following steps to upgrade to Oracle Database 12c Release 1 (12.1) when a physical standby database is present in the configuration:

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

2. Install the new release of the Oracle software into a new Oracle home on the physical standby database and primary database systems, as described in the Oracle Database Upgrade Guide.

3. Shut down the primary database.

4. Shut down the physical standby database(s).

5. Stop all listeners, agents, and other processes running in the Oracle homes that are to be upgraded. Perform this step on all nodes in an Oracle Real Application Clusters (Oracle RAC) environment.

6. If Oracle Automatic Storage Management (Oracle ASM) is in use, shut down all databases that use Oracle ASM, and then shut down all Oracle ASM instance(s).

7. In the new Oracle home, restart all listeners, agents, and other processes that were stopped in step 5.

8. Mount the physical standby database(s) on the new Oracle home (upgraded version). See Section 3.2.6 for information on how to start a physical standby database.

   Note:

   The standby database(s) should not be opened until the primary database upgrade is completed.

9. Start Redo Apply on the physical standby database(s). See Section 3.2.6 for information on how to start Redo Apply.

10. Upgrade the primary database as described in the Oracle Database Upgrade Guide. Note that the physical standby database(s) will be upgraded when the redo generated by the primary database as it is upgraded is applied.

11. Open the upgraded primary database.

12. If Oracle Active Data Guard was being used prior to the upgrade, then refer to Section 10.2.1 for information about how to reenable it after upgrading.

13. Optionally, modify the COMPATIBLE initialization parameter, following the procedure described in Section B.4.

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

Perform the following steps to upgrade to Oracle Database 12c Release 1 (12.1) when a logical standby database is present in the configuration:

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

2. Set the data protection mode to MAXIMUM PERFORMANCE at the primary database, if needed:

   SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
   

3. On the primary database, stop all user activity and defer the remote archival destination associated with the logical standby database (for this procedure, it is assumed that LOG_ARCHIVE_DEST_2 is associated with the logical standby database):

   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER SCOPE=BOTH;
   SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
   

4. Stop SQL Apply on the logical standby database:

   SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
   

5. On the primary database install the newer release of the Oracle software as described in the Oracle Database Upgrade Guide.

6. On the logical standby database, install the newer release of the Oracle software as described in Oracle Database Upgrade Guide.

   Note:

   Steps 5 and 6 can be performed concurrently (in other words, the primary and the standby databases can be upgraded concurrently) to reduce downtime during the upgrade procedure.

7. On the upgraded logical standby database, restart SQL Apply. If you are using Oracle RAC, start up the other standby database instances:

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
   

8. Open the upgraded primary database and allow users to connect. If you are using Oracle RAC, start up the other primary database instances.

   Also, enable archiving to the upgraded logical standby database, as follows:

   SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
   

9. Optionally, reset to the original data protection mode if you changed it in Step 2.

10. Optionally, modify the COMPATIBLE initialization parameter, following the procedure described in Section B.4.

B.4 Modifying the COMPATIBLE Initialization Parameter After Upgrading

When you upgrade to a new release of Oracle Database, certain new features might make your database incompatible with your previous release. Oracle Database enables you to control the compatibility of your database with the COMPATIBLE initialization parameter.

After the upgrade is complete, you can increase the setting of the COMPATIBLE initialization parameter to the maximum level for the new Oracle Database release. When you are certain that you no longer need the ability to downgrade your database back to its original version, set the COMPATIBLE initialization parameter based on the compatibility level you want for your new database.

In an Oracle Data Guard configuration, if you decide to increase the setting of the COMPATIBLE initialization parameter after upgrading, then it is important that you perform the following steps in the order shown (note that the standby database should have a COMPATIBLE setting equal to, or higher than, the primary):

1. Increase the value of the COMPATIBLE initialization parameter on all standby databases in the configuration first, as follows:

   1. Ensure that apply is current on the standby database(s).

   2. On one instance of each standby database, execute the following SQL statement:

      ALTER SYSTEM SET COMPATIBLE=<value> SCOPE=SPFILE;
      

   3. If Redo Apply or SQL Apply is running, then stop them.

   4. Restart all instances of the standby database(s).

   5. If you previously stopped Redo Apply or SQL Apply, then restart them.

2. Increase the value of the COMPATIBLE initialization parameter on the primary database, as follows:

   1. On one instance of the primary database, execute the following SQL statement:

      ALTER SYSTEM SET COMPATIBLE=<value> SCOPE=SPFILE;
      

   2. Restart all instances of the primary database.

B.5 Downgrading Oracle Database with No Logical Standby in Place

Perform the following steps to downgrade Oracle Database in an Oracle Data Guard configuration that does not contain a logical standby database:

1. Ensure that all physical standby databases are mounted, but not open.

   Note:

   The standby database(s) should not be opened until all redo generated by the downgrade of the primary database has been applied.

2. Start Redo Apply, in real-time apply mode, on the physical standby database(s).

3. Downgrade the primary database using the procedure described in Oracle Database Upgrade Guide, keeping the following in mind:

   • At each step of the downgrade procedure where a script is executed, execute the script only at the primary database. Do not perform the next downgrade step until all redo generated by the execution of the script at the primary database has been applied to each physical standby database.

   • At each step of the downgrade procedure where an action other than running a script is performed, perform the step at the primary database first and then at each physical standby database. Do not perform the next downgrade step at the primary database until the action has been performed at each physical standby database.

4. If it becomes necessary to perform a failover during a downgrade, perform the failover and then continue with the downgrade procedure at the new primary database.

B.6 Downgrading Oracle Database with a Logical Standby in Place

Perform the following steps to downgrade Oracle Database in an Oracle Data Guard configuration that contains a logical standby database or a mixture of logical and physical standby databases.

1. Issue the following command at the primary database (database P, for the sake of this discussion) before you downgrade it:

   SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
   

   Database P is no longer in the primary database role.

2. Wait for all standby databases in the configuration to finish applying all available redo. To determine whether each standby database has finished applying all available redo, run the following query at each standby database:

   SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
    
   SWITCHOVER_STATUS
   -----------------
   TO PRIMARY
   

   Do not continue on to step 3 until the query returns a value of TO PRIMARY for all standby databases in the configuration.

3. Downgrade the logical standby databases using the procedures described in Oracle Database Upgrade Guide, keeping the following in mind:

   • At each step of the downgrade procedure where a script is executed, execute the script only at the logical standby databases. Do not perform the next downgrade step until all redo generated by executing the script at the logical standby database that was most recently in the primary role (database P) has been applied to each physical standby database.

   • At each step of the downgrade procedure where an action other than running a script is performed, first perform the step at the logical standby database that was most recently in the primary role (database P), and then perform the step at each physical standby database. Do not perform the next downgrade step at the logical standby database that was most recently in the primary role (database P) until the action has been performed at each physical standby database.

4. After the logical standby that was most recently in the primary role (database P) has been successfully downgraded, open it, and issue the following command:

   SQL> ALTER DATABASE ACTIVATE LOGICAL STANDBY DATABASE;
   

   Database P is now back in the primary role.

5. At each of the logical standby databases in the configuration, issue the following command (note that the command requires that a database link back to the primary exist in all of the logical standby databases):

   SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE NEW PRIMARY
   prim_db_link;