Using DBMS_ROLLING to perform Database Rolling Upgrade in Oracle Database 12c DataGuard Physical Standby Environment

Oracle 12c introduced streamlined and simplified method to perform database rolling upgrades. It can be implemented using DBMS_ROLLING PL/SQL package which allows DBA to upgrade or patch databases in DataGuard environment in rolling fashion.This feature require Oracle Active DataGuard license.

The pre-12c (&12c) rolling upgrade method using SQL Apply (Logical Standby) is still an alternate option and there is no licensing cost using SQL Apply to perform rolling upgrades.

It's a 3-stage process to perform rolling upgrades using DBMS_ROLLING PL/SQL package.

• Specify how the rolling upgrade needs to be implemented. Conceptually assigning trailing groups and leading groups. i.e. using DBMS_ROLLING.INIT_PLAN and DBMS_ROLLING.SET_PARAMETER.
• Compile the plan using DBMS_ROLLING.BUILD_PLAN.
• Executing Rolling Upgrade.

Following example outlines the steps for performing PSU patch apply in rolling fashion in DataGuard Physical Standby environment:

1. Make sure that there are no UNSUNPPORTED DATATYPES. Check if the unsupported objects are supported by EXTENDED DATATYPE SUPPORT. And, enable EDS accordingly. 
   1. SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
   2. SELECT * FROM DBA_LOGSTDBY_EDS_SUPPORTED;
   3. EXECUTE DBMS_LOGSTDBY.EDS_ADD_TABLE(schema, table);
2. Disable Broker Configuration from DGMGRL Cli.
3. Initiaize the rolling upgrade plan.
   1. exec DBMS_ROLLING.INIT_PLAN(future_primary=>'<physical standby node>');
   2. select scope, name, curval from dba_rolling_parameters order by scope, name;
   3. exec DBMS_ROLLING.SET_PARAMETER('SWITCH_LGM_LAG_WAIT', '1');
   4. exec DBMS_ROLLING.SET_PARAMETER('SWITCH_LGM_LAG_TIME', '60');
4. Compile the Plan.
   1. exec DBMS_ROLLING.BUILD_PLAN;
   2. SELECT instid, target, phase, description FROM DBA_ROLLING_PLAN;
5. Execute the plan.
   1. Convert Physical Standby to Logical. This step executes the rolling upgrade plan and converts physical standby to logical.
      1. EXECUTE DBMS_ROLLING.START_PLAN;
   2. Make sure the logical standby is in sync with primary and shutdown the logical standby and the corresponding listener process.
   3. Apply PSU on Logical Standby Oracle Home.
   4. Restart the listener and logical standby database.
   5. Make sure the logical standby is in sync with primary.
   6. Perform Switchover. This will perform role-transition, Logical Standby will become PRIMARY.
      1. EXECUTE DBMS_ROLLING.SWITCHOVER;
   7. Shutdown former Primary, listener and Apply PSU.
   8. Startup the listener, former Primary in MOUNT mode.
   9. Call the DBMS_ROLLING.FINISH_PLAN to convert former primary to physical standby.
      1. EXECUTE DBMS_ROLLING.FINISH_PLAN;

I have recorded a sample demo video performing Patch rollout in rolling fashion using dbms_rolling to provide database service continuity.

demo part 1

demo part 2

Please see Oracle Documentation for complete details.