To upgrade the database software in an Oracle Data Guard configuration in a rolling fashion, you first designate a physical standby as the future primary database.

Conceptually, the rolling upgrade process splits the Oracle Data Guard configuration into two groups: the leading group (LG) and the trailing group (TG).

Databases in the leading group are upgraded first; hence the name leading group. The leading group contains the designated future primary database, and the physical standbys that you can configure to protect the designated future primary. The future primary 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.

The trailing group contains the original primary database and standby databases that 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. At this point a switchover is done to transfer the primary role to the designated future primary database, and the user applications are switched over to the new primary database. New software is then installed on the databases that are part of the trailing group, and they are reinstated into the configuration as standbys to the new primary database.

The standbys in the respective groups are called the Leading Group Standbys (LGS) and Trailing Group Standbys (TGS). Other than the designated future primary, all other standbys in the leading group can only be physical standbys. The trailing group can contain both physical and logical standbys; they are called Trailing Group Physical (TGP) and Trailing Group Logical (TGL) in cases where it is necessary to make a distinction between the standby types. The designated future primary is also called the Leading Group Master (LGM) and the original primary database is called the Trailing Group Master (TGM).

The DBMS_ROLLING package increases the robustness of the rolling upgrade process as follows:

• It can handle failures during the rolling upgrade process. The original primary or the TGM database can fail. You can initiate a regular failover operation to any other physical standby in the trailing group, and then designate the new primary database as the TGM.

• It allows data protection of the LGM (the designated future primary) during the rolling upgrade process. You can set up physical standbys for the LGM database, and thus protect it during the upgrade process and also achieve Zero Data Loss after the upgrade. After the LGM has been successfully upgraded, a failure in the LGM can be accommodated by failing over to any of its physical standby databases. You can then designate the failover target database to take over the role of the LGM.

Table 14-1 compares the characteristics of TGP standbys versus LGP standbys before and after a switchover operation.

There are three stages to the rolling upgrade process using the DBMS_ROLLING PL/SQL Package: specification, compilation, and execution.

1. Specification: You first specify how you want to implement the rolling upgrade process. It is mandatory that you designate a future primary database. This act conceptually creates the leading and the trailing groups. At this point, the leading group only contains the LGM. You can optionally specify other standbys to protect the LGM.

   You use the following procedures during the specification phase:

   • DBMS_ROLLING.INIT_PLAN

   • DBMS_ROLLING.SET_PARAMETER

2. Compilation: This is initiated by calling the DBMS_ROLLING.BUILD_PLAN procedure. The BUILD_PLAN procedure communicates with all databases participating in the rolling upgrade and assembles a rolling upgrade plan. The BUILD_PLAN procedure is also called to alter an existing rolling upgrade plan. Alterations are necessary after changes to DBMS_ROLLING parameters and after changes to the topology as a result of failover. All participating databases must be reachable during execution of the BUILD_PLAN procedure because numerous validations are required to ensure a successful rolling upgrade.

3. Execution: Execution of the rolling upgrade has five stages.

   Stage 1: The DBMS_ROLLING.START_PLAN procedure starts the execution of the rolling upgrade. This converts the LGM database to a logical standby and starts the SQL Apply process at the LGM.

   Stage 2: You upgrade the database software at the databases that are part of the leading group. You also run the upgrade scripts at the LGM. After this is done, you must restart SQL Apply processes at the LGM database. (See Oracle Database Upgrade Guide for information about upgrade scripts.) Leading group physical standbys are also addressed during this stage by re-mounting them using the higher version binaries. These databases are upgraded via recovery of the redo from the LGM.

   Stage 3: After the apply lag reaches a given threshold (set to 10 minutes by default, but can be configured during the specification stage), the DBMS_ROLLING.SWITCHOVER procedure proceeds with the switchover operation. When the switchover is complete, the LGM becomes the primary database.

   Stage 4: The LGM is now the primary database running the new database software and the databases in the leading group are protecting it. The TGM is mounted and the databases in the trailing group are still running the older version of the database software. You must prepare the TGM and TGS databases for upgrade by upgrading the database software and re-mounting the databases on the higher version binaries. (See Oracle Database Upgrade Guide for information about upgrade scripts.)

   Stage 5: Execute the DBMS_ROLLING.FINISH_PLAN procedure at the current primary database (originally the LGM). It reinstates all the databases in the trailing group to become the standbys of the current primary database, and restarts the apply processes. The FINISH_PLAN procedure waits for all databases in the trailing group to be upgraded to the new release (although the database software for the trailing group databases was changed in Stage 4, the data dictionary of the trailing group databases, except for any logical standbys in the trailing group, are updated based on media recovery of the redo generated during the upgrade at the LGM database).

After the rolling upgrade has been successfully executed, you can remove your rolling upgrade specification by calling the DBMS_ROLLING.DESTROY_PLAN procedure.

Planning your rolling upgrade is essential to a successful upgrade experience. In the planning phase you specify various upgrade parameters and build an upgrade plan.

1. 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 (the leading group master or LGM). The LGM is converted into a logical standby database as part of the START_PLAN procedure call. The following is a sample call to the INIT_PLAN procedure in which boston is identified as the future primary database:

   DBMS_ROLLING.INIT_PLAN(future_primary=>'boston');
   

   The INIT_PLAN procedure returns an initial set of system-generated plan parameters. It adds each physical and logical standby database specified in the DG_CONFIG init.ora parameter as a participant in the rolling upgrade. Other databases (such as downstream databases serving GoldenGate downstream deployment or snapshot standbys) are excluded automatically.

   By default, standby databases other than the future primary are configured to protect the primary database, and are configured as mandatory participants in the rolling upgrade.

   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 are ready for plan validation, but to ensure they meet your needs, be sure to 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.

2. After the INIT_PLAN procedure has completed, 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 have a specific database name in the SCOPE column, with which they are associated. The following is a sample query:

   SQL> select scope, name, curval from dba_rolling_parameters order by scope, name;
   
   SCOPE          NAME                        CURVAL
   -------------- ------------------------    ------------------------------
   seattle        INVOLVEMENT                 FULL
   seattle        MEMBER                      NONE
   boston         INVOLVEMENT                 FULL
   boston         MEMBER                      TRAILING
   oakland        INVOLVEMENT                 FULL
   oakland        MEMBER                      TRAILING
   atlanta        INVOLVEMENT                 FULL
   atlanta        MEMBER                      LEADING
                  ACTIVE_SESSIONS_TIMEOUT     3600
                  ACTIVE_SESSIONS_WAIT        0
                  BACKUP_CONTROLFILE          rolling_change_backup.f
                  DGBROKER                    0
                  DICTIONARY_LOAD_TIMEOUT     3600
                  DICTIONARY_LOAD_WAIT        0
                  DICTIONARY_PLS_WAIT_INIT    300
                  DICTIONARY_PLS_WAIT_TIMEOUT 3600
                  EVENT_RECORDS               10000
                  FAILOVER                    0
                  GRP_PREFIX                  DBMSRU_
                  IGNORE_BUILD_WARNINGS       0
                  IGNORE_LAST_ERROR           0
                  LAD_ENABLED_TIMEOUT         600
                  LOG_LEVEL                   INFO
                  READY_LGM_LAG_TIME          600
                  READY_LGM_LAG_TIMEOUT       60
                  READY_LGM_LAG_WAIT          0
                  SWITCH_LGM_LAG_TIME         600
                  SWITCH_LGM_LAG_TIMEOUT      60
                  SWITCH_LGM_LAG_WAIT         1
                  SWITCH_LGS_LAG_TIME         60
                  SWITCH_LGS_LAG_TIMEOUT      60
                  SWITCH_LGS_LAG_WAIT         0
                  UPDATED_LGS_TIMEOUT         10800
                  UPDATED_LGS_WAIT            1
                  UPDATED_TGS_TIMEOUT         10800
                  UPDATED_TGS_WAIT            1
   35 rows selected.

   In the sample output, the databases atlanta, boston, oakland, and seattle were all discovered through the DG_CONFIG, and assigned parameters in the current plan.

   See Also:

   • Oracle Database Reference for more information about the DBA_ROLLING_PARAMETERS view

3. To modify any existing rolling upgrade parameter, use the DBMS_ROLLING.SET_PARAMETER PL/SQL procedure. The following is an example of using the SET_PARAMETER procedure:

   DBMS_ROLLING.SET_PARAMETER(
     scope IN VARCHAR2,
     name IN VARCHAR2,
     value IN VARCHAR2);
   

   The scope identifies either a DB_UNIQUE_NAME value for local parameters or NULL for global parameters. It is not necessary to provide a scope of NULL for parameters that are not specific to a database.

   The name is the name of the parameter to modify.

   The value identifies the value for the specified parameter. A value of NULL reverts the parameter back to its system-supplied default if one exists.

   See Also:

   • Oracle Database PL/SQL Packages and Types Reference for a complete list of all available rolling upgrade parameters

   The following examples illustrate sample usage of some rolling upgrade parameters.

4. After all the necessary parameters are specified, you build an upgrade plan. An upgrade plan is a custom generated set of instructions which guides your Oracle Data Guard configuration through a rolling upgrade. (The plan that is generated differs based on whether or not the configuration is managed by Data Guard broker.)

   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.

   The procedure is called as follows:

   DBMS_ROLLING.BUILD_PLAN;
   

   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 the criteria of best-practice values are treated as warnings and recorded in the DBA_ROLLING_EVENTS view. By default, the IGNORE_BUILD_WARNINGS parameter is set to 1, meaning warnings do not prevent an upgrade plan from reaching a usable state. You can set this parameter to 0 for stricter rule enforcement when plans are built.

   Note:

   The validations performed during plan generation are specific to rolling upgrades. They are not a substitute for the recommended practice of running the Pre-Upgrade Information Tool to evaluate upgrade readiness.

   After generating the plan, move on to the following steps to view it, diagnose any problems with it, and revise it if necessary.

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

   The following output is an example of how a rolling upgrade plan would appear:

   SQL> SELECT instid, target, phase, description FROM DBA_ROLLING_PLAN;
   
   INSTID TARGET       PHASE   DESCRIPTION
   ------ ------------ ------- -----------------------------------------------------
        1 seattle      START   Verify database is a primary
        2 seattle      START   Verify MAXIMUM PROTECTION is disabled
        3 boston       START   Verify database is a physical standby
        4 boston       START   Verify physical standby is mounted
        5 oakland      START   Verify database is a physical standby
        6 oakland      START   Verify physical standby is mounted
        7 atlanta      START   Verify database is a physical standby
        8 atlanta      START   Verify physical standby is mounted
        9 seattle      START   Verify server parameter file exists and is modifiable
       10 boston       START   Verify server parameter file exists and is modifiable
       11 oakland      START   Verify server parameter file exists and is modifiable
       12 atlanta      START   Verify server parameter file exists and is modifiable
       13 seattle      START   Verify Data Guard Broker configuration is disabled
       14 boston       START   Verify Data Guard Broker configuration is disabled
       15 oakland      START   Verify Data Guard Broker configuration is disabled
       16 atlanta      START   Verify Data Guard Broker configuration is disabled
       17 seattle      START   Verify flashback database is enabled
       18 seattle      START   Verify available flashback restore points
       19 boston       START   Verify flashback database is enabled
       20 boston       START   Verify available flashback restore points
       21 oakland      START   Verify flashback database is enabled
       22 oakland      START   Verify available flashback restore points
       23 atlanta      START   Verify flashback database is enabled
       24 atlanta      START   Verify available flashback restore points
       25 boston       START   Scan LADs for presence of atlanta destination
       26 boston       START   Test if atlanta is reachable using configured TNS service
       27 boston       START   Stop media recovery
       28 oakland      START   Stop media recovery
       29 atlanta      START   Stop media recovery
       30 boston       START   Drop guaranteed restore point DBMSRU_INITIAL
       31 boston       START   Create guaranteed restore point DBMSRU_INITIAL
       32 oakland      START   Drop guaranteed restore point DBMSRU_INITIAL
       33 oakland      START   Create guaranteed restore point DBMSRU_INITIAL
       34 atlanta      START   Drop guaranteed restore point DBMSRU_INITIAL
       35 atlanta      START   Create guaranteed restore point DBMSRU_INITIAL
       36 seattle      START   Drop guaranteed restore point DBMSRU_INITIAL
       37 seattle      START   Create guaranteed restore point DBMSRU_INITIAL
   
   INSTID TARGET       PHASE   DESCRIPTION
   ------ ------------ ------- ----------------------------------------------------------
       38 boston       START   Start media recovery
       39 boston       START   Verify media recovery is running
       40 oakland      START   Start media recovery
       41 oakland      START   Verify media recovery is running
       42 atlanta      START   Start media recovery
       43 atlanta      START   Verify media recovery is running
       44 seattle      START   Verify user_dump_dest has been specified
       45 seattle      START   Backup control file to rolling_change_backup.f
       46 boston       START   Verify user_dump_dest has been specified
       47 boston       START   Backup control file to rolling_change_backup.f
       48 oakland      START   Verify user_dump_dest has been specified
       49 oakland      START   Backup control file to rolling_change_backup.f
       50 atlanta      START   Verify user_dump_dest has been specified
       51 atlanta      START   Backup control file to rolling_change_backup.f
       52 seattle      START   Get current redo branch of the primary database
       53 boston       START   Wait until recovery is active on the primary's redo branch
       54 boston       START   Stop media recovery
       55 seattle      START   Execute dbms_logstdby.build
       56 boston       START   Convert into a transient logical standby
       57 boston       START   Open database
       58 boston       START   Configure logical standby parameters
       59 boston       START   Start logical standby apply
       60 boston       START   Get redo branch of transient logical standby
       61 boston       START   Get reset scn of transient logical redo branch
       62 atlanta      START   Stop media recovery
       63 atlanta      START   Flashback database
       64 seattle      START   Disable log file archival to atlanta
       65 boston       START   Enable log file archival to atlanta
       66 boston       START   Wait for log archive destination to atlanta to reach a valid state
       67 atlanta      START   Wait until transient logical redo branch has been registered
       68 atlanta      START   Start media recovery
       69 atlanta      START   Wait until v$dataguard_stats has been initialized
       70 atlanta      START   Wait until recovery has started on the transient redo branch
       71 seattle      START   Log pre-switchover instructions to events table
       72 boston       START   Record start of user upgrade of boston
       73 boston       SWITCH  Verify database is in OPENRW mode
       74 boston       SWITCH  Record completion of user upgrade of boston
   
   INSTID TARGET       PHASE   DESCRIPTION
   ------ ------------ ------- ---------------------------------------------------------
       75 boston       SWITCH  Scan LADs for presence of seattle destination
       76 boston       SWITCH  Scan LADs for presence of oakland destination
       77 boston       SWITCH  Scan LADs for presence of atlanta destination
       78 boston       SWITCH  Test if seattle is reachable using configured TNS service
       79 boston       SWITCH  Test if oakland is reachable using configured TNS service
       80 boston       SWITCH  Test if atlanta is reachable using configured TNS service
       81 seattle      SWITCH  Enable log file archival to boston
       82 boston       SWITCH  Enable log file archival to atlanta
       83 boston       SWITCH  Start logical standby apply
       84 atlanta      SWITCH  Start media recovery
       85 atlanta      SWITCH  Wait until upgrade redo has been fully recovered
       86 boston       SWITCH  Wait until apply lag has fallen below 600 seconds
       87 seattle      SWITCH  Log post-switchover instructions to events table
       88 seattle      SWITCH  Switch database to a logical standby
       89 boston       SWITCH  Wait until end-of-redo has been applied
       90 oakland      SWITCH  Wait until end-of-redo has been applied
       91 seattle      SWITCH  Disable log file archival to oakland
       92 boston       SWITCH  Switch database to a primary
       93 oakland      SWITCH  Stop media recovery
       94 seattle      SWITCH  Synchronize plan with new primary
       95 seattle      FINISH  Verify only a single instance is active
       96 seattle      FINISH  Verify database is mounted
       97 seattle      FINISH  Flashback database
       98 seattle      FINISH  Convert into a physical standby
       99 oakland      FINISH  Verify database is mounted
      100 oakland      FINISH  Flashback database
      101 boston       FINISH  Verify database is open
      102 boston       FINISH  Save the DBID of the new primary
      103 boston       FINISH  Save the logminer session start scn
      104 seattle      FINISH  Wait until transient logical redo branch has been registered
      105 oakland      FINISH  Wait until transient logical redo branch has been registered
      106 seattle      FINISH  Start media recovery
      107 oakland      FINISH  Start media recovery
      108 seattle      FINISH  Wait until apply/recovery has started on the transient branch
      109 oakland      FINISH  Wait until apply/recovery has started on the transient branch
      110 seattle      FINISH  Wait until upgrade redo has been fully recovered
   
   INSTID TARGET       PHASE   DESCRIPTION
   ------ ------------ ------- ------------------------------------------------
      111 oakland      FINISH  Wait until upgrade redo has been fully recovered
      112 seattle      FINISH  Drop guaranteed restore point DBMSRU_INITIAL
      113 boston       FINISH  Drop guaranteed restore point DBMSRU_INITIAL
      114 oakland      FINISH  Drop guaranteed restore point DBMSRU_INITIAL
      115 atlanta      FINISH  Drop guaranteed restore point DBMSRU_INITIAL
   
   115 rows selected.
   
   SQL> 
   

   The columns in this view display the following information:

   • INSTID - The Instruction ID, which is the order in which the instruction is to be performed. Instructions are typically performed in groups.

   • PHASE - Every instruction in the upgrade plan is associated with a particular phase. A phase is a logical grouping of instructions which is performed by a procedure in the DBMS_ROLLING PL/SQL package. When a DBMS_ROLLING procedure is invoked, all of the associated instructions in the upgrade plan for that phase are executed. Possible phases are as follows:

     • START: Consists of activities related to setup such as taking restore points, instantiation of the transient logical standby database, and configuration of LGS databases. Activities in this phase are initiated when you call the DBMS_ROLLING.START_PLAN procedure. See Step 1 in "Performing a Rolling Upgrade".

     • SWITCH: Consists of activities related to the switchover of the transient logical standby into the new primary database. Activities in this phase are initiated when you call the DBMS_ROLLING.SWITCHOVER procedure. See Step 3 in"Performing a Rolling Upgrade"

     • FINISH: Consists of activities related to configuring standby databases for recovery of the upgrade redo. Activities in this phase are initiated when you call the DBMS_ROLLING.FINISH_PLAN procedure. See Step 5 in "Performing a Rolling Upgrade".

   • EXEC_STATUS - The overall status of the instruction.

   • PROGRESS - The progress of an instruction's execution. A value of REQUESTING indicates an instruction is being transmitted to a target database for execution. A value of EXECUTING indicates the instruction is actively being executed. A value of REPLYING indicates completion information is being returned.

   • DESCRIPTION - The specific operation that is scheduled to be performed.

   • TARGET - The site at which a given instruction is to be performed.

   • EXEC_INFO - Additional contextual information related to the instruction.

   See Also:

   • Oracle Database Reference for more information about the DBA_ROLLING_PLAN view

6. Upgrade plans need to be revised after any change to the rolling upgrade or database configuration. A configuration change could include any of the following:

   • init.ora parameter file changes at any of the databases participating in the rolling upgrade

   • database role changes as a result of failover events

   • rolling upgrade parameter changes

   To revise an active upgrade plan, call the BUILD_PLAN procedure again. In some cases, the BUILD_PLAN procedure may raise an error if a given change cannot be accepted. For example, setting the ACTIVE_SESSIONS_WAIT parameter has no effect if the switchover has already occurred.

   It is recommended that you call the BUILD_PLAN procedure to process a group of parameter changes rather than processing parameters individually.

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

DBMS_ROLLING.SET_PARAMETER (
  name=>'LOG_LEVEL', 
  value=>NULL);

DBMS_ROLLING.SET_PARAMETER (
  scope=>'atlanta', 
  name=>'involvement', 
  value=>'optional');

DBMS_ROLLING.SET_PARAMETER (
  scope=>'atlanta', 
  name=>'member', 
  value=>'leading');

Follow these steps to perform a rolling upgrade using the DBMS_ROLLING PL/SQL package.

There are several views you can query for information about the databases involved in a rolling upgrade.

• DBA_ROLLING_STATUS

  Provides information about the overall status of the upgrade.

• DBA_ROLLING_DATABASES

  Provides information about the role, protection, and recovery state of each database involved in the rolling upgrade.

• DBA_ROLLING_STATISTICS

  Provides statistics such as start and finish times, how long services were offline, and so on.

To roll back a rolling upgrade procedure, you can call the DBMS_ROLLING.ROLLBACK_PLAN procedure.

DBMS_ROLLING.ROLLBACK_PLAN;

If a situation arises in which a rolling upgrade is underway and you need to perform a failover in your Oracle Data Guard configuration before the rollover completes, you can do so only in these circumstances.

• The failover was not performed while a DBMS_ROLLING procedure was in progress.

• The failover was between a primary database and a physical standby database, and was a no-data-loss failover.

• The failover was between a transient logical standby database and a physical standby of the transient logical standby database.

A role change is a significant event that inevitably invalidates instructions in the upgrade plan, which was tailored for a different configuration. To resume the rolling upgrade, a new plan must be created. You must set the FAILOVER parameter to indicate that the configuration has changed. This parameter is detected on the next invocation of the BUILD_PLAN procedure, and the existing plan is amended accordingly.

After the revised plan is built, you can resume the rolling upgrade.

These examples show a variety of rolling upgrade scenarios.

At some point in all of the scenarios, the same basic rolling upgrade steps are used. These steps are shown in Example 14-5. The rest of the examples refer back to this example where appropriate rather than reiterating the same steps.

Some of the examples in this section instruct you to resume the rolling upgrade, which means that you should continue where you left off. Resuming a rolling upgrade involves identifying the current phase of the rolling upgrade and reperforming either the PL/SQL procedure associated with the phase or the activities relevant to the phase. The current phase of the rolling upgrade is shown in the PHASE column of the DBA_ROLLING_STATUS view.