DBMS_ROLLING explained

DBMS_ROLLING explained

Active Data Guard is more than just the Read-Only Standby database. Together with your Active Data Guard license comes the “Rolling Upgrade using Active Data Guard” Feature, better known as DBMS_ROLLING.

If you search this blog for Transient logical standby, you can find it here. But DBMS_ROLLING is way easier. The principle remains the same:

  1. Create the guaranteed restore point
  2. Build the logminer dictionary
  3. Convert the physical standby to a logical standby
  4. Upgrade the logical standby
  5. Start the apply again and let it recover
  6. Switchover the primary database to the Logical standby, at that point, you are upgraded already!
  7. The old primary is now a logical standby, so it needs to be flashed back 
  8. Then converted to a physical standby.
  9. Due to that conversion, the redo (with the upgrade info in) is sent to the old primary, the new physical standby
  10. Finally, a switchover again to put the primary back in place 

These are a bunch of steps and when you do this manually it can be quite time consuming. For that, I will explain on a very simple example, on how this can be done using the PL/SQL Package DBMS_ROLLING. The more detailed explanation, also with 2 or more standby’s for protection during the process (what I do recommend) is outlined in this Oracle White Paper.

DBMS_ROLLING has different phases, which you can access using the PL/SQL API.

  1. Prerequisite Phase
  2. init_plan
  3. build_plan
  4. start_plan
  5. upgrade (or do what you want with the Transient logical standby)
  6. switchover
  7. Restart the standby
  8. finish_plan

Prerequisite Phase

First we check if the database is eligible for this way of upgrading. DBMS_ROLLING is, due to the nature of the transient logical standby, not supporting all data types. Before digging in all this, check the database for unsupported objects.
In you will find the DBA_ROLLING_UNSUPPORTED view which you can query for this purpose.

In my demo database, we’re good to go.


In this phase, we are telling DBMS_ROLLING which databases are there and we can set some extra parameters.

Check the log_archive_config parameter for this

For demo purposes, I keep this super simple, but when you upgrade your multi TB highly critical production database, you will find more than one standby database of course.

Tell DBMS_ROLLING that our standby database, will become the new primary

if necessary, we can set or change the parameters from DBMS_ROLLING with the set_parameter procedure.

You can check what DBMS_ROLLING came up with, via a simple query


When we have told DBMS_ROLLING who are the players in this configuration and we are happy with the required parameters ( I left them default), it is time to build the plan. There is one little thing to remember. We need to make sure the Standby is in MOUNT mode. If you use the Read only apply, make sure to restart the standby in mount before building the plan.

That sounds like a plan, right? We can see what has been generated in the plan using following query

At the end of the process, we will have run through 86 steps. Cool isn’t it?


This phase starts the rolling operation and executes all steps of the START phase. Upon successful completion of START_PLAN the future primary database will be a fully configured logical standby.

At this point, on my 2nd VM, I have my fully functional logical standby.

And still my primary on VM1


Or basically, do what you want to do on the logical standby.
For upgrade best practices, I would like to point you to Mike’s website (this is a good starting point  https://mikedietrichde.com/). It is full of good advice to perform a successful upgrade. Focus on upgrading itself will bring us a bit too far in this blogpost, so I will just create a table, but you got the point I hope.


When you are completely happy with the results on the logical standby, it is time to perform the switchover.

This has to be initiated on the primary.

After completing this step, the logical standby became the primary and the former primary is now a logical standby database.

This also means that my dgdemovm2 database is now the primary

As it was a logical standby, and we played around with it, we keep what we did, so in this example, our table is still here.


Restart the standby

The standby database on my first vm is still a logical standby, but we actually need to make sure that it gets flashed back and that it becomes a physical Standby again. So first thing is to shut it down and bring it up in mount mode.

Please pay attention, in case of an upgrade, start the database in mount, from the new Oracle Home.


Then on the primary (which is still on my VM2) we perform the finish_plan, which will convert the logical standby (the original primary) back into a physical standby database and will restart the media recovery.

When we then check our VM1

It is a physical standby again.


If you want to avoid long downtimes this method of upgrading your database can be a potential solution.

Imagine when your application is eligible for Application Continuity, which makes sure that your application does not notice a database switchover, this method can be used to upgrade your database with minimal brownout and there is no immediate need to perform the role switch back to the original nodes.

Leave a Reply

Your email address will not be published. Required fields are marked *

5 × four =

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

%d bloggers like this: