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:
- Create the guaranteed restore point
- Build the logminer dictionary
- Convert the physical standby to a logical standby
- Upgrade the logical standby
- Start the apply again and let it recover
- Switchover the primary database to the Logical standby, at that point, you are upgraded already!
- The old primary is now a logical standby, so it needs to be flashed back
- Then converted to a physical standby.
- Due to that conversion, the redo (with the upgrade info in) is sent to the old primary, the new physical standby
- 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.
- Prerequisite Phase
- init_plan
- build_plan
- start_plan
- upgrade (or do what you want with the Transient logical standby)
- switchover
- Restart the standby
- 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 12.1.0.2 you will find the DBA_ROLLING_UNSUPPORTED view which you can query for this purpose.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> select * from DBA_ROLLING_UNSUPPORTED; no rows selected SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 MYDGPDB READ WRITE NO SQL> alter session set container = mydgpdb; Session altered. SQL> select * from DBA_ROLLING_UNSUPPORTED; no rows selected SQL> |
In my demo database, we’re good to go.
INIT_PLAN
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
|
1 2 3 4 5 6 |
SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(dgdemovm1,dgdemovm2) SQL> |
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
|
1 2 3 4 5 |
SQL> exec dbms_rolling.init_plan('dgdemovm2'); PL/SQL procedure successfully completed. SQL> |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SQL> select scope, name, curval from dba_rolling_parameters order by scope, name; SCOPE NAME CURVAL ------------------------------ ------------------------------ ---------------------------------------- dgdemovm1 INVOLVEMENT FULL dgdemovm1 MEMBER NONE dgdemovm2 INVOLVEMENT FULL dgdemovm2 MEMBER TRAILING ACTIVE_SESSIONS_TIMEOUT 3600 ACTIVE_SESSIONS_WAIT 0 BACKUP_CONTROLFILE rolling_change_backup.f DGBROKER 1 DICTIONARY_LOAD_TIMEOUT 3600 DICTIONARY_LOAD_WAIT 1 DICTIONARY_PLS_WAIT_INIT 300 DICTIONARY_PLS_WAIT_TIMEOUT 3600 EVENT_RECORDS 10000 FAILOVER 0 GRP_PREFIX DBMSRU_ IGNORE_BUILD_WARNINGS 0 IGNORE_LAST_ERROR 0 LAD_ENABLED_TIMEOUT 600 LOG_LEVEL INFO READY_LGM_LAG_TIME 600 READY_LGM_LAG_TIMEOUT 86400 READY_LGM_LAG_WAIT 0 SWITCH_LGM_LAG_TIME 600 SWITCH_LGM_LAG_TIMEOUT 86400 SWITCH_LGM_LAG_WAIT 1 SWITCH_LGS_LAG_TIME 60 SWITCH_LGS_LAG_TIMEOUT 86400 SWITCH_LGS_LAG_WAIT 0 UPDATED_LGS_TIMEOUT 3600 UPDATED_LGS_WAIT 1 UPDATED_TGS_TIMEOUT 3600 UPDATED_TGS_WAIT 1 32 rows selected. SQL> |
BUILD_PLAN
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.
|
1 2 3 4 5 |
SQL> exec dbms_rolling.build_plan; PL/SQL procedure successfully completed. SQL> |
That sounds like a plan, right? We can see what has been generated in the plan using following query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
SQL> SELECT instid, target, phase, description FROM DBA_ROLLING_PLAN; INSTID TARGET PHASE DESCRIPTION ---------- ---------- ---------- ----------------------------------------------------------------- 1 dgdemovm1 START Notify Data Guard broker that DBMS_ROLLING has started 2 dgdemovm2 START Notify Data Guard broker that DBMS_ROLLING has started 3 dgdemovm1 START Verify database is a primary 4 dgdemovm1 START Verify MAXIMUM PROTECTION is disabled 5 dgdemovm2 START Verify database is a physical standby 6 dgdemovm2 START Verify physical standby is mounted 7 dgdemovm2 START Verify future primary is configured with standby redo logs 8 dgdemovm1 START Verify server parameter file exists and is modifiable 9 dgdemovm2 START Verify server parameter file exists and is modifiable 10 dgdemovm1 START Verify Data Guard broker configuartion is enabled 11 dgdemovm2 START Verify Data Guard broker configuartion is enabled 12 dgdemovm1 START Verify Fast-Start Failover is disabled 13 dgdemovm2 START Verify Fast-Start Failover is disabled 14 dgdemovm1 START Verify fast recovery area is configured 15 dgdemovm1 START Verify available flashback restore points 16 dgdemovm2 START Verify fast recovery area is configured 17 dgdemovm2 START Verify available flashback restore points 18 dgdemovm2 START Stop media recovery 19 dgdemovm2 START Drop guaranteed restore point DBMSRU_INITIAL 20 dgdemovm2 START Create guaranteed restore point DBMSRU_INITIAL 21 dgdemovm1 START Drop guaranteed restore point DBMSRU_INITIAL 22 dgdemovm1 START Create guaranteed restore point DBMSRU_INITIAL 23 dgdemovm2 START Start media recovery 24 dgdemovm2 START Verify media recovery is running 25 dgdemovm1 START Verify db_recovery_file_dest has been specified 26 dgdemovm1 START Backup control file to rolling_change_backup.f 27 dgdemovm2 START Verify db_recovery_file_dest has been specified 28 dgdemovm2 START Backup control file to rolling_change_backup.f 29 dgdemovm1 START Get current supplemental logging on the primary database 30 dgdemovm1 START Get current redo branch of the primary database 31 dgdemovm2 START Wait until recovery is active on the primary's redo branch 32 dgdemovm2 START Reduce to a single instance if database is a RAC 33 dgdemovm2 START Verify only a single instance is active if future primary is RAC 34 dgdemovm2 START Stop media recovery 35 dgdemovm1 START Execute dbms_logstdby.build 36 dgdemovm2 START Convert into a transient logical standby 37 dgdemovm2 START Open database including instance-peers if RAC 38 dgdemovm2 START Verify logical standby is open read/write 39 dgdemovm2 START Get redo branch of transient logical standby 40 dgdemovm2 START Get reset scn of transient logical redo branch 41 dgdemovm2 START Configure logical standby parameters 42 dgdemovm2 START Start logical standby apply 43 dgdemovm2 START Wait until logminer dictionary has been loaded 44 dgdemovm2 START Enable compatibility advance despite presence of GRPs 45 dgdemovm1 START Log pre-switchover instructions to events table 46 dgdemovm2 START Record start of user upgrade of dgdemovm2 47 dgdemovm2 SWITCH Verify database is in OPENRW mode 48 dgdemovm2 SWITCH Record completion of user upgrade of dgdemovm2 49 dgdemovm2 SWITCH Scan LADs for presence of dgdemovm1 destination 50 dgdemovm2 SWITCH Test if dgdemovm1 is reachable using configured TNS service 51 dgdemovm1 SWITCH Call Data Guard broker to enable redo transport to dgdemovm2 52 dgdemovm1 SWITCH Archive all current online redo logs 53 dgdemovm2 SWITCH Archive all current online redo logs 54 dgdemovm2 SWITCH Stop logical standby apply 55 dgdemovm2 SWITCH Start logical standby apply 56 dgdemovm2 SWITCH Wait until apply lag has fallen below 600 seconds 57 dgdemovm1 SWITCH Notify Data Guard broker that switchover to logical standby datab ase is starting 58 dgdemovm1 SWITCH Log post-switchover instructions to events table 59 dgdemovm1 SWITCH Switch database to a logical standby 60 dgdemovm1 SWITCH Notify Data Guard broker that switchover to logical standby datab ase has completed 61 dgdemovm2 SWITCH Wait until end-of-redo has been applied 62 dgdemovm1 SWITCH Archive all current online redo logs 63 dgdemovm1 SWITCH Notify Data Guard broker that switchover to primary is starting 64 dgdemovm2 SWITCH Switch database to a primary 65 dgdemovm2 SWITCH Notify Data Guard broker that switchover to primary has completed 66 dgdemovm1 SWITCH Enable compatibility advance despite presence of GRPs 67 dgdemovm1 SWITCH Synchronize plan with new primary 68 dgdemovm1 FINISH Verify only a single instance is active 69 dgdemovm1 FINISH Verify database is mounted 70 dgdemovm1 FINISH Flashback database 71 dgdemovm1 FINISH Convert into a physical standby 72 dgdemovm2 FINISH Verify database is open 73 dgdemovm2 FINISH Save the DBID of the new primary 74 dgdemovm2 FINISH Save the logminer session start scn 75 dgdemovm1 FINISH Wait until transient logical redo branch has been registered 76 dgdemovm1 FINISH Start media recovery 77 dgdemovm1 FINISH Wait until apply/recovery has started on the transient branch 78 dgdemovm1 FINISH Wait until upgrade redo has been fully recovered 79 dgdemovm1 FINISH Prevent compatibility advance if GRPs are present 80 dgdemovm2 FINISH Prevent compatibility advance if GRPs are present 81 dgdemovm1 FINISH Drop guaranteed restore point DBMSRU_INITIAL 82 dgdemovm2 FINISH Drop guaranteed restore point DBMSRU_INITIAL 83 dgdemovm2 FINISH Purge logical standby metadata from database if necessary 84 dgdemovm1 FINISH Notify Data Guard broker that DBMS_ROLLING has finished 85 dgdemovm2 FINISH Notify Data Guard broker that DBMS_ROLLING has finished 86 dgdemovm2 FINISH Restore Supplemental Logging 86 rows selected. SQL> |
At the end of the process, we will have run through 86 steps. Cool isn’t it?
START_PLAN
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.
|
1 2 3 4 5 |
SQL> exec dbms_rolling.start_plan PL/SQL procedure successfully completed. SQL> |
At this point, on my 2nd VM, I have my fully functional logical standby.
|
1 2 3 4 5 6 7 |
SQL> select host_name,instance_name, status,open_mode,database_role from v$instance, v$database; HOST_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE ---------------------------------------------------------------- ---------------- ------------ -------------------- ---------------- oelvm2.localdomain dgdemovm2 OPEN READ WRITE LOGICAL STANDBY SQL> |
And still my primary on VM1
|
1 2 3 4 5 6 7 8 |
SQL> select host_name,instance_name, status,open_mode,database_role from v$instance, v$database; HOST_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE ---------------------------------------------------------------- ---------------- ------------ -------------------- ---------------- oelvm1.localdomain dgdemovm1 OPEN READ WRITE PRIMARY SQL> |
UPGRADE
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
SQL> select host_name,instance_name, status,open_mode,database_role from v$instance, v$database; HOST_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE ---------------------------------------------------------------- ---------------- ------------ -------------------- ---------------- oelvm2.localdomain dgdemovm2 OPEN READ WRITE LOGICAL STANDBY SQL> create table vanpupi_was_here(c1 int); Table created. SQL> |
SWITCHOVER
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> select host_name,instance_name, status,open_mode,database_role from v$instance, v$database; HOST_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE ---------------------------------------------------------------- ---------------- ------------ -------------------- ---------------- oelvm1.localdomain dgdemovm1 OPEN READ WRITE PRIMARY SQL> SQL> exec dbms_rolling.switchover; PL/SQL procedure successfully completed. SQL> |
After completing this step, the logical standby became the primary and the former primary is now a logical standby database.
|
1 2 3 4 5 6 7 |
SQL> select host_name,instance_name, status,open_mode,database_role from v$instance, v$database; HOST_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE ---------------------------------------------------------------- ---------------- ------------ -------------------- ---------------- oelvm1.localdomain dgdemovm1 OPEN READ WRITE LOGICAL STANDBY SQL> |
This also means that my dgdemovm2 database is now the primary
|
1 2 3 4 5 6 7 |
SQL> select host_name,instance_name, status,open_mode,database_role from v$instance, v$database; HOST_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE ---------------------------------------------------------------- ---------------- ------------ -------------------- ---------------- oelvm2.localdomain dgdemovm2 OPEN READ WRITE PRIMARY SQL> |
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.
|
1 2 3 4 5 6 |
SQL> desc vanpupi_was_here Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- C1 NUMBER(38) SQL> |
Great!
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1073741304 bytes Fixed Size 8904184 bytes Variable Size 373293056 bytes Database Buffers 687865856 bytes Redo Buffers 3678208 bytes Database mounted. SQL> |
FINISH_PLAN
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> exec dbms_rolling.finish_plan; PL/SQL procedure successfully completed. SQL> select host_name,instance_name, status,open_mode,database_role from v$instance, v$database; HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE ---------------- ------------ -------------------- ---------------- oelvm2.localdomain dgdemovm2 OPEN READ WRITE PRIMARY SQL> |
When we then check our VM1
|
1 2 3 4 5 6 7 |
SQL> select host_name,instance_name, status,open_mode,database_role from v$instance, v$database; HOST_NAME INSTANCE_NAME STATUS OPEN_MODE DATABASE_ROLE ---------------------------------------------------------------- ---------------- ------------ -------------------- ---------------- oelvm1.localdomain dgdemovm1 MOUNTED MOUNTED PHYSICAL STANDBY SQL> |
It is a physical standby again.
Conclusion
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.