Patching and Upgrading Databases in Oracle Data Guard Configuration

Data Guard Standby-First Patch Apply

A typical database patch consists of two main steps:
step 1) patch installation consisting of patching the Oracle Database Home or Grid Infrastructure Home or both.
step 2) patch post-installation consisting of running SQL scripts or SQL commands. A Standby-First Patch only executes step 1 to a physical standby environment.

To finalize the patch, both step 1 and step 2 need to be executed on the primary database, because step 2 requires a read-write database. All changes from step 2 are replicated via the redo stream to the physical standby database.

Oracle Data Guard Standby-First Patch Apply provides a supported method to apply a patch initially to a physical standby database while the primary database remains at the previous software release.

Oracle Data Guard Standby-First Patch Apply is supported between database patch releases between the primary and standby that are a maximum of one year apart from the patch release dates or the patches are within 6 versions of each other. For example (using 11.2.0.2 versions, because there are no valid Oracle Database 12c examples currently), the primary database at 11.2.0.2 Bundle Patch 8 (updated 25-Apr-2011) and standby at 11.2.0.2 Bundle Patch 16 (update 17-Apr-2012) barely qualifies. The Data Guard software combination is a viable candidate because the bundle patches are less than 1 year apart and also within “n-6″and 11.2.0.2 BP16 patch readme states “This patch is Data Guard Standby-First Installable.

The following types of patches are supported:

• Oracle Exadata Database Machine bundled patch or Quarter Database Patch for Exadata
• Oracle Exadata Storage Server Software Update
• Oracle Exadata Database Machine hardware or network changes
• Oracle Grid Infrastructure Patches or Software Updates
• Patch Set Update (PSU)
• Critical Patch Update (CPU)
• Patch Set Exception (PSE)
• Operating System software changes that do not have any dependencies on Oracle database software

Oracle patch sets and major release upgrades do not apply. For example, upgrades from 11.2.0.2 to 11.2.0.3 or 11.2 to 12.1 do not qualify for standby-first patch apply. Use the Data Guard transient logical standby method for patch sets and major releases.

Data Guard Standby-First Patch Apply

Steps to apply the patch to the standby database first:

1. Shut down all standby instances on the standby database (if patch is not RAC Rolling).
2. On the standby site, apply the patch as described in the patch README.
3. Restart the standby instances.
4. Restart media recovery on the physical standby database.
5. Evaluate the patch on the standby by monitoring alert and log files, using snapshot standby, or Active Data Guard.
6. Apply the patch to the primary database site.
   • Standby can be read-only if the patch is RAC Rolling capable.
   • Standby must be mounted if the patch is not RAC Rolling capable.
   • Alternatively, switch over to standby and apply the patch to the former primary

The Data Guard can be managed by SQL*Plus or the broker when using the Standby-First Patch Apply technique. The patch evaluation can be performed by any or all of the following methods:

• Leave the standby database in managed recovery mode at the mount state, and monitor for any issues in the standby alert log and trace files.
• If using the Active Data Guard option, open the standby database in read-only mode and stress the standby database by running your read-only workload.
• Convert the standby database into a snapshot standby, as described in the Oracle Data Guard Concepts and Administration Guide, execute patch post-installation step 2, and test the application against the snapshot standby. Optionally, use Oracle Real Application Testing to perform a full application evaluation. This evaluation method is the most comprehensive and is the Oracle recommended best practice. Once testing is complete, convert the snapshot standby back to a physical standby.

Patches that are listed as RAC Rolling Installable in the patch README can be applied on the primary with the standby performing recovery in read-only mode. However, for patches that are not RAC Rolling Installable, you must stop read that are not RAC Rolling Installable, you must stop read-only recovery on the standby bring only recovery on the standby, bring the standby database to the mount state, and restart recovery prior to applying the patch to the primary database.

Upgrading an Oracle Data Guard Broker Configuration

Oracle patch sets and major releases require an upgrade method. To upgrade the databases in your Data Guard configuration:

1. Disable broker management of the databases in the Data Guard configuration by executing the following DGMGRL command:

DGMGRL> DISABLE CONFIGURATION;

2. Execute the following SQL*Plus statement to stop the broker:

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

After completing the upgrade:
1. Start the Data Guard broker by executing the following command in SQL Plus: *

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;

2. Enable broker management of the configuration by connecting to the primary database and executing the following DGMGRL command:

DGMGRL> ENABLE CONFIGURATION

Upgrading Oracle Database in a Data Guard Configuration with a Physical Standby Database

Perform the steps outlined below to upgrade to Oracle Database 12c Release 1 (12.1) when your Data Guard configuration contains one or more physical standby databases.

1. Perform the preparation steps for an upgrade.
2. Install the Oracle Database software on physical standby database systems and the primary database system database systems and the primary database system.
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.
6. If Oracle Automatic Storage Management (ASM) is in use, it should be upgraded before the databases.
7. In the new Oracle home, restart all listeners, agents, and other processes stopped in step 5.
8. Mount the physical standby database(s) on the new Oracle home.
9. Start Redo Apply on the physical standby database(s).
10. Upgrade the primary database as described in the Upgrade the primary database as described in the Oracle Database Upgrade Guide. Redo Transport will propagate this to the standby database(s).
11. Open the upgraded primary database.
12. If Active Data Guard was being used prior to the upgrade, it will need to be reenabled it will need to be reenabled.
13. Optionally modify the COMPATIBLE initialization parameter.

Upgrading Oracle Database in a Data Guard Configuration with a Logical Standby Database

Perform the steps outlined below to upgrade to Oracle Database 12c Release 1 (12.1) when your Data Guard configuration contains a logical standby database.

1. Perform the preparation steps for an upgrade.
2. Set the data protection mode to MAXIMUM PERFORMANCE.
3. Stop all user activity on the primary database and defer the Stop all user activity on the primary database and defer the logical standby database remote archival destination.
4. Stop SQL Apply on the standby database.
5. Install Oracle Database software on the primary database system and upgrade the primary database.
6. Install Oracle Database software on the logical standby Install Oracle Database software on the logical standby database system and upgrade the standby database.
7. Restart SQL Apply on the standby database.
8. Open the upgraded primary database.
9. Reset the data protection mode, if necessary.

Using SQL Apply to Upgrade the Oracle Database

You can use a logical standby database to perform a rolling upgrade of Oracle Database 11g software to Oracle Database 12c. During a rolling upgrade, different releases of Oracle Database can be on the primary database and logical standby databases while you upgrade them one at a time, incurring minimal down time on the primary database.

Using Data Guard SQL Apply, you can perform a rolling upgrade of the Oracle Database software from patch set release n to any higher-versioned patch set or major version release.

Requirements for Using SQL Apply to Perform a Rolling Upgrade

Prior to performing the rolling upgrade, complete the following requirements:

• Disable the broker configuration.
• Set the Data Guard protection mode to either maximum availability or maximum performance.
• The LOG_ARCHIVE_DEST_n initialization parameter for the logical standby destination must not be set to MANDATORY.
• Set the COMPATIBLE initialization parameter so that it matches the software release prior to the upgrade. A rolling upgrade from release x to release y requires that the COMPATIBLE initialization parameter be set to release x on the primary database and the standby database

Performing a Rolling Upgrade by Using SQL Apply

You can use SQL Apply to perform a rolling upgrade in several different configurations. Each of the configurations is outlined in detail in next sections of the post.

• Use an existing logical standby database to perform the rolling upgrade.
• Create a new logical standby database to perform the rolling upgrade.
• Use an existing physical standby database to perform the rolling upgrade.

Identifying Unsupported Data Types

You identify data types and storage attributes on the primary database that are not supported in a logical standby database by querying the DBA_LOGSTDBY_UNSUPPORTED and DBA_LOGSTDBY_SKIP views on the primary database. If your primary database contains unsupported objects, you may be able to perform the upgrade by temporarily suspending changes to the unsupported tables for the period of time it takes to perform the upgrade procedure.

If you cannot prevent changes to unsupported tables during the upgrade, you may be able to use Oracle Data Pump or the Import utility to import the changed tables to the upgraded databases. Unsupported transactions that occur are recorded in the DBA_LOGSTDBY_EVENTS table on the logical standby database table on the logical standby database.

Logical Standby: New Data Type Support for Oracle Database 12 Oracle Database 12c

The rolling upgrade process converts a physical standby database to a transient logical standby database. While physical standby databases support all Oracle Database data types, a logical standby database has restrictions on supported data types. If the primary database uses the restricted data types, the Data Guard rolling upgrade process may not be feasible.

To help eliminate this barrier for using Data Guard to perform rolling upgrades with minimal down time, Data Guard supports the following data types in a logical standby database:

• Abstract data types (ADTs) and ADT tables
• Database file system (DBFS)
• LOBs stored as SecureFiles
• Objects stored as VARRAYs (except for Collections)
• Oracle SecureFiles (deduplication)
• Oracle Text
• Spatial (except MDSYS.SDO_GEORASTER and MDSYS.SDO_TOPO_GEOMETRY) and multimedia (Opaque type restrictions and REFs are not supported)
• User-defined types
• XDB

Performing a Rolling Upgrade by Using an Existing Logical Standby Database

To perform a rolling upgrade by using a logical standby database in your Data Guard configuration:
1. Prepare for the rolling upgrade as follows:

a. Stop SQL Apply by issuing the following statement on the logical standby database:

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

b. Set the COMPATIBLE initialization parameter to the highest value. Ensure that the COMPATIBLE initialization parameter specifies the release number for the Oracle Database software running on the primary database prior to the upgrade.

2. Upgrade the Oracle Database software on the logical standby database to release y. While the logical standby database is being upgraded, it does not accept redo data from the primary database. See the Oracle Database Upgrade Guide for detailed information.

3. Restart SQL Apply by executing the following statement on the standby database:

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

The redo data that was accumulating on the primary database system is automatically transmitted and applied on the logical standby database. To monitor how quickly the logical standby database is catching up to the primary database, query the V$LOGSTDBY_PROGRESS view on the logical standby database. For example:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY HH24:MI:SS';
Session altered.

SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;

SYSDATE             APPLIED_TIME
------------------  ------------------
27-FEB-10 17:07:06  27-FEB-10 17:06:50

4. Query DBA_LOGSTDBY_EVENTS to determine whether there are any DDL and DML statements that were not applied on the logical standby database:

SELECT EVENT TIMESTAMP, EVENT, STATUS
FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIMESTAMP;

5. Begin a switchover to the upgraded logical standby database by executing the following statement on the primary database:

ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

This statement waits for existing transactions to complete To minimize the time it takes This statement waits for existing transactions to complete. To minimize the time it takes to complete the switchover, users connected to the primary database should log off immediately and reconnect to the standby database.

6. Import any tables that were modified during the upgrade from the primary database that were unsupported in the logical standby database.

7. Complete the switchover by executing the following statement on the logical standby database:

ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL PRIMARY;

After the switchover, you cannot send redo data from the new primary database (using the new Oracle Database software release) to the new standby database (using the older Oracle Database software release).

8. Upgrade the Oracle Database software on the original primary database to release y.

9. Start SQL Apply on the original primary database. You may also need to create a database link to the new primary database:

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE NEW PRIMARY db_link;

10. Monitor events on the new logical standby database by querying the DBA_LOGSTDBY_EVENTS view

11.Optional: Perform a switchover to return to the original configuration.

12.Optional: Raise the compatibility level on both databases by setting the COMPATIBLE initialization parameter on the standby database before you set it on the primary database.

Performing a Rolling Upgrade by Using a Physical Standby Database

To perform a rolling upgrade when your configuration contains a physical standby database:

1. Prepare the primary database for a rolling upgrade:

a. Enable Flashback Database.

b. Create a guaranteed restore point:

CREATE RESTORE POINT pre_upgrade GUARANTEE FLASHBACK DATABASE;

2. Convert the physical standby database to a logical standby database. This is only temporarily done for the duration of the rolling upgrade.
a. Create a logical standby database and execute the following command:

ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;

b. Disable automatic deletion of foreign archived logs at the logical standby database:

execute DBMS_LOGSTDBY.APPLY SET('LOG_AUTO_DELETE', 'FALSE');

c. Start SQL Apply:

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

3. Upgrade the logical standby database (steps 1–7 of “Performing a Rolling Upgrade by Using a Logical Standby Database”).

After completing these steps, your original primary database will be the logical standby database and your original physical standby database will be your primary database with an upgraded version of the Oracle Database software.

4. Flash back the original primary database to the guaranteed restore point that you created in step 1.

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> FLASHBACK DATABASE TO RESTORE POINT pre_upgrade;
SQL> SHUTDOWN IMMEDIATE

5. Mount the original primary database using the new version of the software. You will not run the upgrade scripts, because this database will be turned into a physical standby, and will be upgraded automatically as it applies redo data generated from the new primary database.

SQL> startup mount

6. Convert the original primary database to a physical standby database:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE;

7. Start the managed recovery process on the original primary database:

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

8. To perform a switchover to return to your original configuration, execute the following commands on the new primary database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

On the original primary database, execute the following commands:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL> SHUTDOWN IMMEDIATE; SHUTDOWN IMMEDIATE;
SQL> STARTUP

9. Clean up the guaranteed restore point created in step 1.

SQL> DROP RESTORE POINT PRE_UPGRADE;

Rolling Upgrades Using DBMS_ROLLING and Active Data Guard

The Rolling Upgrade Using Active Data Guard feature, new as of Oracle Database 12c Release 1 (12.1), provides a streamlined method of performing rolling upgrades. It is implemented using the new DBMS_ROLLING PL/SQL package, which allows you to upgrade the database software in a Data Guard configuration in a rolling fashion. The Rolling Upgrade Using Active Data Guard feature requires the Oracle Active Data Guard option.

You can use this feature for database version upgrades starting with the first patchset of Oracle Database 12c. This means that the manual Transient Logical Standby upgrade procedure must still be used when upgrading from Oracle Database 11g to Oracle Database12c, or when upgrading from the initial Oracle Database 12c release to the first patchset of Oracle Database 12 patchset of Oracle Database 12c.

Additionally, you can use this feature immediately for other database maintenance tasks beginning with Oracle Database 12c Release 1 (12.1). The database where maintenance is performed must be operating at a minimum of Oracle 12.1. Such maintenance tasks include adding partitioning to nonpartitioned tables, changing BasicFiles LOBs to SecureFiles LOBs, changing XMLType stored as CLOB to XMLType stored as binary XML, and altering tables to be OLTP-compressed.

DBMS_ROLLING: Concepts

Rolling changes can be applied to the whole Data Guard configuration using all standby databases identified by the LOG_ARCHIVE_CONFIG parameter. There are three stages to the rolling upgrade process using the DBMS_ROLLING PL/SQL package: Specification Compilation, and Execution. In the Specification stage, you identify how you want to implement the rolling upgrade process and designate which standby will become the future primary database. The Compilation stage builds an upgrade plan by performing validation checks and reports any errors that require corrective actions before the Execution stage begins. The Execution stage performs the actual upgrade and has three phases to it: Start, Switchover, and Finish.

Conceptually the rolling upgrade process splits the Data Guard configuration into two groups: Data Guard configuration into two groups: the leading group (LG) and the trailing group (TG). The leading group contains a master database that is designated to be the future primary database. The leading group can contain additional standby databases designed to protect the master database during the upgrade process. Databases in the leading group are upgraded first before switchover. The trailing group contains the original primary database along with any additional databases designed to protect it during the upgrade process. Databases in the trailing group are upgraded last after the switchover is performed.

The leading group contains the designated future primary database, known as the Leading Group Master database (LGM), and the physical standbys that you can configure to protect the designated future primary. All other standbys in the leading group can only be physical standbys. The LGM is first converted into a logical standby database and then the new database software is installed on it and the upgrade process is run. Other standby databases in the leading group also must have their software upgraded at this point. In the event of a failure during the upgrade process, you can failover to any of the additional standby databases in the leading group and then designate the failover target database to take over the role of the LGM.

The trailing group contains the original prim The trailing group contains the original primary database known as the Trailing Group Master database (TGM) and standby databases that will protect the original primary during the rolling upgrade process. While the databases in the leading group are going through the upgrade process, user applications can still be connected to the original primary and making changes. The trailing group databases continue running the old database software until all the databases in the leading group are upgraded and the future primary has caught up with the original primary by applying the changes that were generated at the original primary database during the upgrade window New software is then installed on the databases that are part of the trailing group after switchover, and they are reinstated into the configuration as standbys to the new primary database. The role of TGM can be transferred to other standby databases in the trailing group in the event a failure occurs during the upgrade.

DBMS_ROLLING: Key Features

The DBMS_ROLLING package provides for a Specify – Compile – Execute protocol, all driven from a centralized interface using procedures. Configuration errors that would prevent execution of the upgrade plan can be identified during the Compilation stage and resolved before beginning the actual upgrade. DBA_ROLLING_* views provide diagnostic information should any runtime errors occur during the Execution stage.

Plan parameters are persisted in the database and remain even after completion of a rolling upgrade. After the rolling upgrade has been successfully executed, you can remove your rolling upgrade specification by calling the DBMS_ROLLING.DESTROY_PLAN procedure.

In the previous versions of Oracle Data Guard, a rolling upgrade using a transient logical standby database required at least 42 steps for a simple two-database configuration. The number of steps would significantly increase for each additional standby database contained in the Data Guard configuration. Using the DBMS_ROLLING package, the runtime steps of the Execution stage are constant, regardless of how many standby databases exist.

The concept of leading group standbys and trailing group standbys allow for multiple databases in each group to provide fault tolerance for the group. In addition, the additional databases, if present, can provide protection for the upgraded primary immediately following the upgrade.

Database Rolling Upgrade: Specification and Compilation Stages

The Specification and Compilation stages involve the following six steps to create a centralized upgrade plan that drives the rolling upgrade process:

1. Initialize the upgrade parameters.
2. View the current upgrade parameters.
3. Modify the upgrade parameter values as necessary.
4. Build the upgrade plan.
5. View the current plan.
6. Revise the upgrade plan as necessary.

The DBMS_ROLLING.INIT_PLAN procedure generates system default parameters for all databases specified in the DG_CONFIG parameter. You can adjust parameters with the DBMS_ROLLING.SET_PARAMETER procedure. All parameters for the rolling upgrade are visible with the DBA_ROLLING_PARAMETERS view.

When you finalize the parameters, use the DBMS_ROLLING.BUILD_PLAN procedure to generate the actual plan and perform validation against the plan. If the validation identifies any errors with the plan, the DBMS ROLLING EVENTS view displays the errors that need attention.

Specification Stage Examples

Plan parameters must be initialized to system-generated default values before they can be customized. To initialize plan parameters, call the DBMS_ROLLING.INIT_PLAN procedure. This procedure identifies the DB_UNIQUE_NAME of the future primary database (that is, the Leading Group Master or LGM). The INIT_PLAN procedure returns an initial set of system-generated plan parameters. Once the database-related parameters have been defined, the INIT_PLAN procedure defines operational parameters with system-supplied defaults. In most cases, the plan parameters will be ready for plan validation; however, to ensure that they meet your needs, you should review each parameter. Plan parameters are persisted in the database until you call the DESTROY_PLAN procedure to remove all states related to the rolling upgrade.

You can query the DBA_ROLLING_PARAMETERS view to see the plan parameters and their current values. Plan parameters are either global or local in scope. Global parameters are attributes of the rolling upgrade as a whole and are independent of the database participants. Global parameters have a NULL value in the SCOPE column. Local parameters are associated with a specific database name in the SCOPE column.

To modify any existing rolling upgrade parameter, use the DBMS_ROLLING.SET_PARAMETER PL/SQL procedure.

Summary

1. Initialize the upgrade parameters:

SQL> exec DBMS_ROLLING.INIT_PLAN(future_primary=>'london');

2. View the current upgrade parameter values:

SQL> select scope, name, curval from dba_rolling_parameters order by scope, name;

SCOPE          NAME                CURVAL
------------ --------------        -------------
boston       INVOLVEMENT           FULL
             SWITCH_LGM_LAG_WAIT   60
...

3. Configuring the plan to wait for the apply lag to fall below 60 seconds before switching over to the future primary:

SQL> exec DBMS_ROLLING.SET_PARAMETER('SWITCH_LGM_LAG_WAIT','1');
SQL> exec DBMS_ROLLING.SET_PARAMETER('SWITCH_LGM_LAG_TIME','60');

Compilation Stage Examples

After all the necessary parameters are specified, you build an upgrade plan. An upgrade plan is a custom-generated set of instructions that guides your Data Guard configuration through a rolling upgrade. To build an upgrade plan, use the DBA_ROLLING.BUILD_PLAN PL/SQL procedure. This procedure requires the configuration to be exactly as described by the plan parameters with all of the instances started and reachable through the network. There are no arguments to specify because the procedure gets all its input from the DBA_ROLLING_PARAMETERS view. The procedure validates plan parameters and performs site-specific validations of resources such as log transport and flash recovery area settings. In general, configuration settings that do not meet best-practice criteria generate a warning message.

After the BUILD_PLAN procedure successfully returns, the complete upgrade plan is viewable in the DBA_ROLLING_PLAN view. Each record in the view identifies a specific instruction that is scheduled for execution and recorded in the DBA_ROLLING_EVENTS view.

Summary

1. Build the upgrade plan:

SQL> exec DBMS_ROLLING.BUILD_PLAN;

2. View the current upgrade plan:

SQL> select instid, target, phase, description from dba_rolling_plan;

INSTID  TARGET        PHASE    DESCRIPTION
------  ------------ ------- ------------------------------------------
     1 boston         START    Verify database is a primary
     2 boston         START    Verify MAXIMUM PROTECTION is disabled
     3 london         START    Verify database is a physical standby
     4 london         START    Verify physical standby is mounted
     5 boston         START    Verify server parameter file exists and is 
                               modifiable
...

Database Rolling Upgrade: Execution Stage

The Execution stage of the rolling upgrade process involves the following five steps:

1. Call the DBMS_ROLLING.START_PLAN procedure to configure the future primary and physical standbys designated to protect the future primary.
2. Manually upgrade the Oracle Database software at the future primary database and standbys that protect it.
3. Call the DBMS_ROLLING.SWITCHOVER procedure to switch roles between the current primary database and future primary database.
4. Manually restart the former primary and remaining standby databases on the higher version of the Oracle Database software.
5. Call the DBMS_ROLLING.FINISH_PLAN procedure to convert the former primary to a physical standby and to configure the remaining standby databases for recovery of the upgrade redo

No arguments are required for the START_PLAN, SWITCHOVER, and FINISH_PLAN procedures.