Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
12c Release 1 (12.1)

E17602-14
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

134 DBMS_ROLLING

The DBMS_ROLLING PL/SQL package is used to implement the Rolling Upgrade Using Active Data Guard feature, which streamlines the process of upgrading Oracle Database software in a Data Guard configuration in a rolling fashion. The Rolling Upgrade Using Active Data Guard feature requires a license for the Oracle Active Data Guard option, and can be used for database version upgrades starting with the first patchset of Oracle Database 12c.

Additionally, you can use this feature immediately for other database maintenance tasks. The database where maintenance is performed must be operating at a minimum of Oracle Database 12c Release 1 (12.1). Such maintenance tasks include:

See Also:

This chapter contains the following topics:


Using DBMS_ROLLING


Overview

The DBMS_ROLLING PL/SQL package provides procedures that you can use to perform any change throughout a Data Guard configuration in a rolling fashion, including a rolling upgrade of the Oracle Database software. Although the focus of this document is rolling upgrade operations, the content is applicable to the deployment of any rolling changes.

All the procedures are executed at the current primary database, which eliminates the potential confusion of moving between remote databases to perform various operations related to the rolling upgrade. If necessary, all the procedures can be called again to resume the rolling upgrade after an error or interruption. (The upgrade script must still be run at the standby.)

The package also provides a procedure that allows you to return a Data Guard configuration back to its original, pre-upgrade state in the event users wish to abandon the rolling upgrade.

The actual execution of a rolling upgrade has been reduced to three steps (excluding the upgrade of the Oracle Database software itself and the on-disk setup of the new Oracle Database software). The number of steps remains the same regardless of the size of the Data Guard configuration.

Conceptually, for the purposes of the DBMS_ROLLING package, you divide your Data Guard configuration into two groups: the leading group and the trailing group. The databases in the leading group undergo the upgrade operation (or any other change that you are deploying) first. The databases in the trailing group undergo the upgrade of the Oracle Database software (or any other change that you are deploying) only after the switchover operation. This insulates them from the upgrade and gives you time to evaluate the effect of the change in the leading group databases.

Each group has a master database: the future primary database as specified in the DBMS_ROLLING.INIT_PLAN procedure is the master of the leading group, called Leading Group Master (LGM), while the original primary database is the master of the trailing group called Trailing Group Master (TGM). These terms are used throughout this documentation. You can configure databases to protect the LGM and the TGM. DBMS_ROLLING allows you to transfer the LGM or TGM role to other physical standbys in the respective group, as long as you have not invoked the DBMS_ROLLING.SWITCHOVER procedure.


Security Model

The DBMS_ROLLING package is available to users who have been granted the DBA role.


Summary of DBMS_ROLLING Subprograms

Table 134-1 DBMS_ROLLING Package Subprograms

Subprogram Description

INIT_PLAN Procedure

Initializes a rolling operation plan with system-generated default values.

DESTROY_PLAN Procedure

Destroys any existing rolling operation plan, its parameters, and all resources associated with the rolling operation.

BUILD_PLAN Procedure

Validates plan parameters and creates or modifies a rolling operation plan.

SET_PARAMETER Procedure

Modifies a rolling operation parameter.

START_PLAN Procedure

Starts the rolling operation.

SWITCHOVER Procedure

Performs a switchover between the current primary database and the transient logical standby database.

FINISH_PLAN Procedure

Finalizes the rolling operation.

ROLLBACK_PLAN Procedure

Completely rolls back the rolling operation.



INIT_PLAN Procedure

This procedure initializes a rolling operation plan with system-generated default values.

Syntax

DBMS_ROLLING.INIT_PLAN (
   future_primary   IN VARCHAR2);
           

Parameters

Table 134-2 INIT_PLAN Procedure Parameters

Parameter Description

future_primary

DB_UNIQUE_NAME of the future primary (also known as the Leading Group Master (LGM))


Exceptions

  • ORA-67000: Procedure must be invoked on database %s.

  • ORA-67001: Upgrade plan is already active.

  • ORA-67002: DG_CONFIG parameter must be specified.

  • ORA-67003: Future primary not specified in DG_CONFIG.

Usage Notes

  • A plan must be prepared before any parameters can be customized.


DESTROY_PLAN Procedure

This procedure destroys any existing upgrade plan, its parameters, and all resources associated with a rolling operation.

Syntax

DBMS_ROLLING.DESTROY_PLAN ();   

Parameters

This procedure has no parameters.

Exceptions

  • ORA-67000: Procedure must be invoked on database %s.

  • ORA-67005: Not permitted while rolling upgrade operation in progress.

  • ORA-67006: Upgrade plan has not been prepared.

Usage Notes

  • When a rolling operation is complete, this procedure can be called to completely purge all states related to a rolling operation.


BUILD_PLAN Procedure

This procedure validates plan parameters and creates or modifies a rolling operation plan. A successfully constructed plan is required in order to perform a rolling operation. This procedure must return successfully before the START_PLAN procedure can be called to start the rolling operation. Parameter changes made subsequent to a prior call to this procedure will not be reflected in a rolling operation plan until this procedure is called again.

Syntax

DBMS_ROLLING.BUILD_PLAN ();

Parameters

This procedure has no parameters.

Exceptions

  • ORA-67000: Procedure must be invoked on database %s.

  • ORA-67005: Not permitted while rolling upgrade operation in progress.

  • ORA-67006: Upgrade plan has not been prepared.

  • ORA-67008: Operation completed with warnings. Consult DBA_ROLLING_EVENTS for more information.

  • ORA-67009: Operation failed to complete. Consult DBA_ROLLING_EVENTS for more information.

Usage Notes

  • This procedure connects to databases specified as plan parameters. These instances must be mounted or open, and must be reachable via the network.


SET_PARAMETER Procedure

This procedure modifies a rolling operation parameter.

Syntax

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

Parameters

Table 134-3 SET_PARAMETER Procedure Parameters

Parameter Description

scope

Parameter scope. It can either be NULL for global parameters, or the DB_UNIQUE_NAME of a specific database for local parameters.

name

The DBMS_ROLLING constant for a given parameter.

value

New value for the parameter or NULL to revert to a default value.


Exceptions

  • ORA-67000: Procedure must be invoked on database %s.

  • ORA-67006: Upgrade plan has not been prepared.

  • ORA-67010: Parameter name is unknown.

  • ORA-67011: Parameter value is out of bounds.

  • ORA-67012: Parameter is no longer applicable.

  • ORA-67013: Parameter change not permitted due to dependency with %s.

  • ORA-67014: Parameter has no default value.

Usage Notes

  • Changes to any parameter value require a call to the DBMS_ROLLING.BUILD_PLAN procedure so that the upgrade plan will be refreshed with the parameter change.

  • Table 134-4 lists all the available parameters and their descriptions. Each parameter name in the table has an associated public constant in the DBMS_ROLLING PL/SQL package in the form of DBMS_ROLLING.Name.

Table 134-4 Valid Values for DBMS_ROLLING.SET_PARAMETER Procedure

Parameter Name Global? Description Default

ACTIVE_SESSIONS_TIMEOUT

Yes

This parameter is only valid if ACTIVE_SESSIONS_WAIT is set to TRUE. This specifies the number of seconds the switchover operation should wait for active sessions to complete before timing out.

300 seconds

ACTIVE_SESSIONS_WAIT

Yes

Whether the switchover operation will wait for active sessions to finish. If TRUE, then the switchover operation waits for active sessions to complete, although no new sessions are allowed to connect to the database. If FALSE, then active sessions are immediately killed to expedite the switchover. Valid values are TRUE or FALSE.

Default is FALSE

TRUE

DBNAME

No

The DB_UNIQUE_NAME of a database in the configuration. This parameter is derived during execution of the INIT_PLAN procedure when the DG_CONFIG is parsed, and is not modifiable.

NULL

DICTIONARY_LOAD_TIMEOUT

Yes

Relevant only if DICTIONARY_LOAD_WAIT is set to TRUE. Specifies how many seconds to wait for THE data dictionary load at the LGM before timing out the START_PLAN all.

3600 seconds

DICTIONARY_LOAD_WAIT

Yes

Whether or not the START_PLAN call will wait for data dictionary snapshot seen in redo to be loaded by the LGM database (The LGM becomes a logical standby as a result of the START_PLAN invocation). Valid values are TRUE or FALSE.

FALSE

DICTIONARY_PLS_WAIT_INIT

Yes

Initial wait time on supplemental logging enqueue related to procedural replication as part of START_PLAN.

300 seconds

DICTIONARY_PLS_WAIT_TIMEOUT

Yes

Total time to wait for the supplemental logging enqueue related to procedural replication during START_PLAN.

3600 seconds

FAILOVER_EVENT

Yes

Automatically attempt to resolve parameters as a result of a failover event. Valid values are TRUE or FALSE.

FALSE

FUTURE_PRIMARY

Yes

The DB_UNIQUE_NAME of the future primary (transient logical standby).

NULL

FUTURE_VERSION

Yes

Target database version of the future primary.

NULL

GRP_PREFIX

 

Execution of procedures in DBMS_ROLLING results in a number of Guaranteed Restore Points (GRP) taken in various databases participating in the Data Guard configuration. All such GRPs have the same prefix in their names. You can use this parameter to override the default prefix.

DBMSRU

IGNORE_BUILD_WARNING

Yes

Ignore warnings which would otherwise raise exceptions during execution of the BUILD_PLAN procedure. Valid values are TRUE or FALSE.

FALSE

IGNORE_LAST_ERROR

Yes

Ignore last encountered error upon startup of next rolling operation. Valid values are TRUE or FALSE.

FALSE

LOG_LEVEL

Yes

Logging level for the DBMS_ROLLING PL/SQL package. Valid values are ERRORS, INFO, FULL, and DEBUG.

ERRORS means that only Errors are logged.

INFO means that errors and relevant non-fatal warnings are logged.

FULL means that in addition to errors and warnings, all high level actions are logged.

DEBUG means that in addition to messages logged for FULL, finer grained actions taken by the database are also logged.

ERRORS

PROTECTION_GROUP

No

Valid values are LEADING, TRAILING and NONE. A value of LEADING indicates that the standby will protect the Leading Group Master (LGM) i.e., the future primary.

A value of TRAILING indicates that the standby will protect the Trailing Group Master (TGM) i.e., the original primary database.

A value of NONE indicates that a standby will protect its current database but will not be configured after the switchover operation.The default value is TRAILING.

PRIMARY

STATPOLL_INTERVAL

Yes

Seconds which must elapse before a new request is issued to gather statistics from a remote database. Value must be non-zero and is expressed in seconds.

5

SWITCH_IMMEDIATE

Yes

Switchover should not wait for apply lag to drop below SWITCH_PRIMARY_LAG or SWITCH_TRANSIENT_LAG during switchover. Valid values are TRUE or FALSE.

TRUE

SWITCH_LGM_LAG_TIME

Yes

The apply lag limit, in seconds, at the Leading Group Master (LGM) i.e, the future primary database. This is valid only if SWITCH_LGM_LAG_WAIT is set to TRUE. If the apply lag goes below the specified value, then the DBMS_ROLLING.SWITCHOVER procedure is permitted to initiate a switchover. Value must be non-zero and expressed in seconds.

600

SWITCH_LGM_LAG_WAIT

Yes

Whether to wait for the apply lag on the leading group master (LGM) to fall below SWITCH_LGM_LAG_TIME seconds in order to switchover.

TRUE

SWITCH_LAG_TIMEOUT

Yes

The number of minutes to wait before raising an error due to SWITCH_PRIMARY_LAG or SWITCH_TRANSIENT_LAG not being reached. Value must be non-zero and expressed in seconds.

1800

SWITCH_TRANSIENT_LAG

Yes

The apply lag limit, in seconds, between the transient logical standby and physicals of the transient logical standby. If the apply lag goes below the specified value, then the DBMS_ROLLING.SWITCHOVER procedure is permitted to initiate a switchover. Value must be non-zero and expressed in seconds.

3600



START_PLAN Procedure

This procedure starts the rolling operation. This procedure must be executed on the primary database to formally start the rolling operation. When the START_PLAN procedure is complete, the LGM (identified with the future_primary parameter in the INIT_PLAN procedure) will be converted into a fully configured transient logical standby database.

Syntax

DBMS_ROLLING.START_PLAN ();

Parameters

This procedure has no parameters.

Exceptions

  • ORA-67000: Procedure must be invoked on database %s.

  • ORA-67006: Upgrade plan has not been prepared.

  • ORA-67008: Operation completed with warnings. Consult DBA_ROLLING_EVENTS for more information.

  • ORA-67009: Operation failed to complete. Consult DBA_ROLLING_EVENTS for more information.

Usage Notes

  • A rolling operation plan must have previously been generated through the BUILD_PLAN procedure.


SWITCHOVER Procedure

This procedure performs a switchover between the current primary database (also known as the TGM) and the transient logical standby database (also known as the LGM). At the successful completion of the procedure, the LGM assumes the primary role for the Data Guard configuration.

Syntax

DBMS_ROLLING.SWITCHOVER (); 

Parameters

This procedure has no parameters.

Exceptions

  • ORA-67000: Procedure must be invoked on database %s.

  • ORA-67006: Upgrade plan has not been prepared.

  • ORA-67008: Operation completed with warnings. Consult DBA_ROLLING_EVENTS for more information.

  • ORA-67009: Operation failed to complete. Consult DBA_ROLLING_EVENTS for more information.

Usage Notes

  • This procedure can only be called after you have manually upgraded the transient logical standby and opened it on the higher Oracle Database version.


FINISH_PLAN Procedure

This procedure finalizes the rolling operation. It configures the former primary (also known as the TGM) as a physical standby, and configures remaining physical standbys to recover the upgrade redo from the future primary.

Syntax

DBMS_ROLLING.FINISH_PLAN ();

Parameters

This procedure has no parameters.

Exceptions

  • ORA-67000: Procedure must be invoked on database %s.

  • ORA-67006: Upgrade plan has not been prepared.

  • ORA-67008: Operation completed with warnings. Consult DBA_ROLLING_EVENTS for more information.

  • ORA-67009: Operation failed to complete. Consult DBA_ROLLING_EVENTS for more information.

Usage Notes

  • This procedure can only be called after you have remounted the former primary and remaining physical standbys on the higher Oracle Database version.


ROLLBACK_PLAN Procedure

This procedure rolls back the configuration-wide rolling operation. Once completed, all of the databases in the leading group become physical standbys of the original primary database. This procedure can only be called if the configuration has not yet gone through a switchover operation since the START_PLAN procedure was invoked.

Syntax

DBMS_ROLLING.ROLLBACK_PLAN;

Parameters

This procedure has no parameters.

Exceptions

  • ORA-67000: Procedure must be invoked on database %s.

  • ORA-67006: Upgrade plan has not been prepared.

  • ORA-67015: Transient logical must be mounted in order to restore configuration.

  • ORA-67016: Configuration cannot be restored due to switchover.

Usage Notes

  • You must manually restart media recovery on the lower Oracle Database version if the upgrade of the transient logical standby has already been performed.