Oracle DBMS_ROLLING
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Provides procedures to perform rolling upgrade-related tasks. All the procedures are executed at the current primary database, which eliminates the potential confusion of moving between remote databases to perform various upgrade operations. If necessary, all the procedures can be re-called to resume the rolling upgrade after an error or interruption.

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 database software itself and the on-disk setup of the new Oracle binaries). The number of steps remains the same regardless of the size of the Data Guard configuration.
Needless to say it will not be easy to produce demos with this package prior to being in a position to perform an upgrade ... which will require that one be released.
AUTHID CURRENT_USER
Dependencies
DBMS_ROLLING_LIB ROLLING$DIRECTIVES ROLLING$PLAN
ROLLING$CONNECTIONS ROLLING$EVENTS ROLLING$STATISTICS
ROLLING$CONNECTIONS ROLLING$PARAMETERS ROLLING$STATUS
ROLLING$DATABASES    
Documented Yes
Exceptions
Error Code Reason
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
ORA-67004 Future release is not supported
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
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
ORA-67015 Transient logical must be mounted in order to restore configuration
ORA-67016 Configuration cannot be restored due to switchover
First Available 12.1.0
Security Model Owned by SYS with EXECUTE granted to the DBA role.
Source {ORACLE_HOME}/rdbms/admin/dbmsrupg.sql
Subprograms
 
BUILD_PLAN
Either builds a complete upgrade plan or modifies the remaining unprocessed portion of an existing plan. The build procedure interprets the configured rolling upgrade parameters to produce a customized upgrade plan dbms_rolling.build;
exec dbms_rolling.build;
 
DESTROY_PLAN
Purges all rolling upgrade state from the database. It is called on completion of a rolling upgrade dbms_rolling.destroy_plan;
exec dbms_rolling.destroy_plan;
 
FINISH_PLAN
Executes the FINISH phase instructions in the upgrade plan. --  called after the START_UPGRADE and SWITCHOVER phase instructions have completed, and the user has restarted the original primary and physical standbys of the primary on their higher version binaries. Upon completion of this procedure, the original primary and its physical standbys will have completed recovery of the ugprade redo.

dbms_rolling.finish_plan;
exec dbms_rolling.finish_plan;
 
INIT_PLAN
The first procedure that must be called to prepare for a DBMS_ROLLING administered rolling upgrade. Communicates with the complete set of databases in the DG_CONFIG, and creates a default set of rolling upgrade parameters for building a rolling upgrade plans. dbms_rolling.init_plan(future_primary IN VARCHAR2);
BEGIN
  -- specification phase
  dbms_rolling.init_plan('ORABASE');
  dbms_rolling.set_parameter('orabase', 'LOG_LEVEL', 'FULL');
  -- compilation phase
  dbms_rolling.build;
  -- execution phase
  dbms_rolling.start_upgrade;
END;
/

-- upgrade the database
exec dbms_rolling.switchover;

-- perform the second database upgrade
exec dbms_rolling.finish;
exec dbms_rolling.destroy_plan;
 
ROLLBACK_PLAN
Rolls back the group of administered PDBs to their initial state. The package creates an initial set of guaranteed restore points for all participating PDBs. This proc will flashback all PDBs in the leading change group to their respective restore points if the switchover has not been performed. dbms_rolling.rollback_plan;
exec dbms_rolling.rollback_plan;
 
SET_PARAMETER
Called to set and unset rolling upgrade parameters. Changes to the plan parameters do not take effect until the user re-invokes the BUILD procedure to reconstruct the upgrade plan.

Overload 1
dbms_rolling.set_parameter(
scope IN VARCHAR2,
name  IN VARCHAR2,
value IN VARCHAR2);

Parameter Name Global Description Default
AFFINITY No Affinity of the standby database. Valid values are PRIMARY, TRANSIENT, or NONE. A value of PRIMARY indicates that a standby will protect the primary database. A value of TRANSIENT indicates that a standby will protect the transient logical standby. A value of NONE indicates that a standby will protect its current database but not be configured after the switchover. PRIMARY
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
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
IGNORE_BUILD_WARN Yes Ignore warnings which would otherwise raise exceptions during execution of the BUILD_PLAN procedure. Valid values are TRUE or FALSE. TRUE
IGNORE_LAST_ERROR Yes Ignore last encountered error upon startup of next rolling upgrade operation. Valid values are TRUE or FALSE. FALSE
INVOLVEMENT No Involvement level of the associated database. Valid values are MANDATORY, OPTIONAL, and NONE. A value of MANDATORY indicates the database must participate in the rolling upgrade. A value of OPTIONAL indicates failures on a database will be tolerated. A value of NONE indicates a database will not be managed during the rolling upgrade. MANDATORY
LOG_LEVEL Yes Logging level for the DBMS_ROLLING PL/SQL package. Valid values are NONE, MINIMAL, and FULL. MINIMAL
ROLE No Database role associated with a given DB_UNIQUE_NAME. This parameter is derived during the INIT_PLAN procedure, and must be manually set after a role change. Valid values are: PRIMARY, PHYSICAL, TRANSIENT, or LOGICAL. NULL
SESSION_SHUTDOWN Yes Whether sessions should be shut down during switchover at the original primary. If TRUE, then active sessions are immediately killed to expedite the switchover. Valid values are TRUE or FALSE. TRUE
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_PRIMARY_LAG Yes The apply lag limit, in seconds, at 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. 600
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. Must be non-zero. 3600
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_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
show parameterdb_unique_name

exec dbms_rolling.set_parameter('orabeta', 'LOG_LEVEL', 'FULL');
Overload 2 dbms_rolling.set_parameter(
name  IN VARCHAR2,
value IN VARCHAR2);
TBD
 
START_PLAN
executes the START phase instructions in the upgrade plan. It is the first procedure that is called to initiate the rolling upgrade. Upon completion of this phase, the future primary will be ready to be upgraded. dbms_rolling.start_upgrade;
exec dbms_rolling.start_upgrade;
 
SWITCHOVER
executes the SWITCHOVER phase instructions in the upgrade plan. It is called once the START procedure has completed execution of all START phase instructions. dbms_rolling.switchover;
exec dbms_rolling.switchover;
 
Related Queries
Check for DBMS_ROLLING usage SELECT COUNT(status)
FROM dba_rolling_status;
Get the total number of DBMS_ROLLING participants SELECT COUNT(dbun) ROLLING_PARTICIPANTS
FROM dba_rolling_databases
WHERE participant = 'YES';
Get the number of physicals of the original primary SELECT COUNT(scope) ORIG_PRIMARIES
FROM dba_rolling_parameters
WHERE name = 'PROTECTS'
AND curval = 'PRIMARY'
AND scope IN (
  SELECT dbun
  FROM dba_rolling_databases
  WHERE participant = 'YES'
  AND role = 'PHYSICAL');
Get the number of physicals of the future primary SELECT COUNT(scope) FUTURE_PRIMARIES
FROM dba_rolling_parameters
WHERE name = 'PROTECTS'
AND curval = 'TRANSIENT'
AND scope IN (
  SELECT dbun
  FROM dba_rolling_databases
  WHERE participant = 'YES'
  AND role = 'PHYSICAL');
Get the number of logical standbys SELECT COUNT(dbun) LOGICAL_STANDBYS
FROM dba_rolling_databases
WHERE participant = 'YES'
AND role = 'LOGICAL'
AND dbun != (
  SELECT future_primary
  FROM dba_rolling_status);
Get number of Far Sync Standbys SELECT COUNT(*) FAR_SYNC_STANDBYS
FROM v$archive_dest_status
WHERE status = 'VALID'
AND type = 'FAR SYNC';

Related Topics
Data Guard
Packages