We know the pain of Upgrading Database, taking hours of downtime. But, Oracle comes with a new feature called DBMS_ROLLING from 12C Release 1. If you are having a license for Active Data Guard this method will minimize your downtime for upgrade or patching.

This method is well automated. You just need 90 seconds 120 seconds of downtime for your primary database as this will work in Rolling Fashion with your standby database.

Now let us see how DBMS_ROLLING works,

In DBMS_ROLLING, the first step is to assign a future primary database. As that database is upgraded first and then Primary Role will be transferred to Future Primary Database [Physical Standby] and then that will be upgraded.

Let us understand this through below diagram :

As we can see in the above diagram DBMS_ROLLING is divided into two groups.

  1. Leading Group [Leading Group Master]: Which contain Future Primary Database and physical standby database that we designate to protect our future primary database.
  2. Trailing Group [Trailing Group Master]: It contains original primary database and any logical standbys or Physical standby database which we designate to protect our original primary database.

Databases in the Leading group will be upgraded first and databases in trailing group will continue running in old database software version until the role is switch over to Future Primary Database and original database is upgraded.

We can use DBMS_ROLLING to upgrade 12.1 databases to any higher version but it is not usable in upgrading from any version lower than 12.1 so to upgrade from 11g we can still use the Transient Logical Standby method.

Prerequisites for Performing Upgrade using DBMS_ROLLING

For Primary Database and Standby Database: 

  1. Flashback must be enabled.
  2. Log_archive_config must be set properly.
  3. Log_Archive_dest – destinations should be configured properly with a valid VALID_FOR attribute.
  4. List unsupported data types by logical standby [Note: You have to maintain it manually.]
  5. Disable the Broker configuration
  6. The compatible parameter must be set 12.1 or higher.

Details of Up-gradation databases :

Primary Database Physical Standby Database Old version New Version
UPGDB STD_UPGDB 12.1.0.2 12.2.0.2

Note: 12.2.0.2 binaries are already installed in primary and standby databases before starting this procedure.

Let us first complete prerequisites listed above.

Step 1: To enable Flashback database

Step 2: Disable dgbroker configuration

Step 3: Set dg_broker_start to false.

Step 4: Set the VALID_FOR attributes

Check other prerequisites in a list if it’s not set first we need to work on it.

To begin with DBMS_ROLLING, it works in 3 stages

Stage 1 INIT_PLAN

Stage 2 BUILD_PLAN

Stage 3 START_PLAN

Stage 4 FINISH_PLAN

Plan the rolling upgrade  using INIT_PLAN : 

Stage 1: In this step, we will designate a future primary database from one of our physical standby database.

Check status from DBA_ROLLING_STATUS

Status should be READY.

We can View the current upgrade parameter values with DBA_ROLLING_PARAMETERS.

Stage 2: After setting INIT_PLAN to let us now build_plan 

Note: Be aware if any of your prerequisites are not met build_plan will give you the error.

After successful execution of build_plan check DBA_ROLLING_PLAN that will show us set of instructions which are to be performed when we will run start plan and finish_plan procedure.

Check status 

Stage 3: START_PLAN: START_PLAN procedure

Configure the LGM database. This basically takes actions required to convert the standby database into the transient logical standby database. A GRP will also be created. It takes a few minutes to finish.

Check Status

Step 4: After successful completion of start_plan we can see the Physical Standby Database is converted into the logical standby database.

Step 5: Shut down a logical standby database.

Upgrade LGM [Logical Standby] database :

Step 6: Start DBUA from 12.2 home :

Step 7: Select the LGM database, give SYSDBA username and password and click on next.

Step 8: Here, you will see warnings and errors. Click on next.

Step 9: Check check-boxes according to your requirement. Specify if you want to run any pre-upgrade scripts and click on next.

Step 10: In case our upgrade gets fail, we need the backup policy. I am using RMAN backup policy of Database Upgrade Assistant, you can use your own backup policy.

Step 11: Select listener which you want to migrate to new Oracle home and click on next.

Step 12: Specify here if you want to configure EM and register it with a cloud. Then click on next.

Step 13: Check summary and click on finish.

Check progress . . . . . . . . .

Step 14: Once it is completed 100% you can see upgrade results.

We can see here the UPGDB database is successfully upgraded from 12.1.0.2 to 12.2.0.1.0.

Step 15: After upgrading the LGM database run dbms_rolling.switchover procedure from a Primary database.

This is an actual downtime for your Production. This switchover process takes 90 to 120 seconds.

Step 16: Check the primary database role it is now converted into the logical standby database.

Step 17: Shut down TGM database.

Step 18: Upgrade TGM [original primary] database

Follow Step 6 to Step 14.

After successful completion of TGM database upgrade. Shut down TGM database and start TGM database in mount stage.

Step 19: Call FINISH_PLAN procedure

After successful completion of finish_plan, you can check your TGM database is converted back to Physical Standby Database from Logical Standby Database.

Optionally you can switch over the role of a Physical standby database to a Primary database if you want your original Primary Database back in action..!!!!

Stay tuned for More articles on Oracle DataGuard

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Telegram Channel: https://t.me/helporacle

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.