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.
- Leading Group [Leading Group Master]: Which contain Future Primary Database and physical standby database that we designate to protect our future primary database.
- 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:
- Flashback must be enabled.
- Log_archive_config must be set properly.
- Log_Archive_dest – destinations should be configured properly with a valid VALID_FOR attribute.
- List unsupported data types by logical standby [Note: You have to maintain it manually.]
- Disable the Broker configuration
- 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
SQL> ALTER DATABASE FLASHBACK ON; Database altered.
Step 2: Disable dgbroker configuration
DGMGRL> disable configuration; Disabled.
Step 3: Set dg_broker_start to false.
SQL> alter system set dg_broker_start=false; System altered.
Step 4: Set the VALID_FOR attributes
SQL> alter system set log_archive_dest_1='location=/u01/arc/upgdb valid_for=(online_logfiles,all_roles)'; System altered.
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.
SQL> exec DBMS_ROLLING.INIT_PLAN(FUTURE_PRIMARY=>'STD_UPGDB'); PL/SQL procedure successfully completed. SQL>
Check status from DBA_ROLLING_STATUS
SQL> SELECT REVISION, STATUS, PHASE FROM DBA_ROLLING_STATUS; REVISION STATUS PHASE ---------- ------------ -------------- 0 READY BUILD PENDING
Status should be READY.
We can View the current upgrade parameter values with DBA_ROLLING_PARAMETERS.
SQL> SELECT SCOPE, NAME, CURVAL FROM DBA_ROLLING_PARAMETERS ORDER BY SCOPE, NAME; SCOPE NAME CURVAL ---------- ----------------------------------- ----------------------------------- STD_UPGDB INVOLVEMENT FULL STD_UPGDB MEMBER TRAILING UPGDB INVOLVEMENT FULL UPGDB MEMBER NONE ACTIVE_SESSIONS_TIMEOUT 3600 ACTIVE_SESSIONS_WAIT 0 BACKUP_CONTROLFILE rolling_change_backup.f DICTIONARY_LOAD_TIMEOUT 3600 DICTIONARY_LOAD_WAIT 0 DICTIONARY_PLS_WAIT_INIT 300 DICTIONARY_PLS_WAIT_TIMEOUT 3600 SCOPE NAME CURVAL ---------- ----------------------------------- ----------------------------------- EVENT_RECORDS 10000 FAILOVER 0 GRP_PREFIX DBMSRU_ IGNORE_BUILD_WARNINGS 1 IGNORE_LAST_ERROR 0 LAD_ENABLED_TIMEOUT 600 LOG_LEVEL INFO READY_LGM_LAG_TIME 600 READY_LGM_LAG_TIMEOUT 60 READY_LGM_LAG_WAIT 0 SWITCH_LGM_LAG_TIME 600 SCOPE NAME CURVAL ---------- ----------------------------------- ----------------------------------- SWITCH_LGM_LAG_TIMEOUT 60 SWITCH_LGM_LAG_WAIT 1 SWITCH_LGS_LAG_TIME 60 SWITCH_LGS_LAG_TIMEOUT 60 SWITCH_LGS_LAG_WAIT 0 UPDATED_LGS_TIMEOUT 10800 UPDATED_LGS_WAIT 1 UPDATED_TGS_TIMEOUT 10800 UPDATED_TGS_WAIT 1 31 rows selected. SQL>
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.
SQL> exec dbms_rolling.build_plan; PL/SQL procedure successfully completed.
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.
SQL> SELECT INSTID, TARGET, PHASE, DESCRIPTION FROM DBA_ROLLING_PLAN ORDER BY 1;
INSTID TARGET PHASE DESCRIPTION
---------- ---------- ---------- -----------------------------------------------------------------
1 UPGDB START Verify database is a primary
2 UPGDB START Verify MAXIMUM PROTECTION is disabled
3 STD_UPGDB START Verify database is a physical standby
4 STD_UPGDB START Verify physical standby is mounted
5 UPGDB START Verify server parameter file exists and is modifiable
6 STD_UPGDB START Verify server parameter file exists and is modifiable
7 UPGDB START Verify Data Guard Broker configuration is disabled
8 STD_UPGDB START Verify Data Guard Broker configuration is disabled
9 UPGDB START Verify flashback database is enabled
10 UPGDB START Verify available flashback restore points
11 STD_UPGDB START Verify flashback database is enabled
12 STD_UPGDB START Verify available flashback restore points
13 STD_UPGDB START Stop media recovery
14 STD_UPGDB START Drop guaranteed restore point DBMSRU_INITIAL
15 STD_UPGDB START Create guaranteed restore point DBMSRU_INITIAL
16 UPGDB START Drop guaranteed restore point DBMSRU_INITIAL
17 UPGDB START Create guaranteed restore point DBMSRU_INITIAL
18 STD_UPGDB START Start media recovery
19 STD_UPGDB START Verify media recovery is running
20 UPGDB START Verify user_dump_dest has been specified
21 UPGDB START Backup control file to rolling_change_backup.f
22 STD_UPGDB START Verify user_dump_dest has been specified
23 STD_UPGDB START Backup control file to rolling_change_backup.f
24 UPGDB START Get current redo branch of the primary database
25 STD_UPGDB START Wait until recovery is active on the primary's redo branch
26 STD_UPGDB START Stop media recovery
27 UPGDB START Execute dbms_logstdby.build
28 STD_UPGDB START Convert into a transient logical standby
29 STD_UPGDB START Open database
30 STD_UPGDB START Get redo branch of transient logical standby
31 STD_UPGDB START Get reset scn of transient logical redo branch
32 STD_UPGDB START Configure logical standby parameters
33 STD_UPGDB START Start logical standby apply
34 STD_UPGDB START Enable compatibility advance despite presence of GRPs
35 UPGDB START Log pre-switchover instructions to events table
36 STD_UPGDB START Record start of user upgrade of STD_UPGDB
37 STD_UPGDB SWITCH Verify database is in OPENRW mode
38 STD_UPGDB SWITCH Record completion of user upgrade of STD_UPGDB
39 STD_UPGDB SWITCH Scan LADs for presence of UPGDB destination
40 STD_UPGDB SWITCH Test if UPGDB is reachable using configured TNS service
41 UPGDB SWITCH Enable log file archival to STD_UPGDB
42 STD_UPGDB SWITCH Start logical standby apply
43 STD_UPGDB SWITCH Archive all current online redo logs
44 STD_UPGDB SWITCH Wait until apply lag has fallen below 600 seconds
45 UPGDB SWITCH Log post-switchover instructions to events table
46 UPGDB SWITCH Switch database to a logical standby
47 STD_UPGDB SWITCH Wait until end-of-redo has been applied
INSTID TARGET PHASE DESCRIPTION
---------- ---------- ---------- -----------------------------------------------------------------
48 UPGDB SWITCH Archive all current online redo logs
49 STD_UPGDB SWITCH Switch database to a primary
50 UPGDB SWITCH Enable compatibility advance despite presence of GRPs
51 UPGDB SWITCH Synchronize plan with new primary
52 UPGDB FINISH Verify only a single instance is active
53 UPGDB FINISH Verify database is mounted
54 UPGDB FINISH Flashback database
55 UPGDB FINISH Convert into a physical standby
56 STD_UPGDB FINISH Verify database is open
57 STD_UPGDB FINISH Save the DBID of the new primary
58 STD_UPGDB FINISH Save the logminer session start scn
59 UPGDB FINISH Wait until transient logical redo branch has been registered
60 UPGDB FINISH Start media recovery
61 UPGDB FINISH Wait until apply/recovery has started on the transient branch
62 UPGDB FINISH Wait until upgrade redo has been fully recovered
63 UPGDB FINISH Prevent compatibility advance if GRPs are present
64 STD_UPGDB FINISH Prevent compatibility advance if GRPs are present
65 UPGDB FINISH Drop guaranteed restore point DBMSRU_INITIAL
66 STD_UPGDB FINISH Drop guaranteed restore point DBMSRU_INITIAL
66 rows selected.
SQL>
Check status
SQL> SELECT REVISION, STATUS, PHASE FROM DBA_ROLLING_STATUS;SQL> REVISION STATUS PHASE ---------- ------------ --------------- 1 READY START PENDING
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.
SQL> exec DBMS_ROLLING.START_PLAN; PL/SQL procedure successfully completed.
Check Status
SQL> SELECT REVISION, STATUS, PHASE FROM DBA_ROLLING_STATUS; REVISION STATUS PHASE ---------- ------------ --------------- 2 READY SWITCH PENDING LOGICAL RUNNING NOT STARTED
Step 4: After successful completion of start_plan we can see the Physical Standby Database is converted into the logical standby database.
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- UPGDB READ WRITE LOGICAL STANDBY
Step 5: Shut down a logical standby database.
SQL> SQL> SHUT IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down.
Upgrade LGM [Logical Standby] database :
Step 6: Start DBUA from 12.2 home :
[oracle@localhost ~]$ export ORACLE_HOME=/u01/oracle/product/12.2.0/db_1 [oracle@localhost ~]$ export PATH=$ORACLE_HOME/bin:$PATH [oracle@localhost ~]$ dbua
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.
SQL> exec dbms_rolling.switchover; PL/SQL procedure successfully completed.
Step 16: Check the primary database role it is now converted into the logical standby database.
SQL> SELECT NAME,OPEN_MODE,DATABASE_ROLE,DB_UNIQUE_NAME FROM V$DATABASE; NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME --------- -------------------- ---------------- ------------------------------ UPGDB READ WRITE LOGICAL STANDBY UPGDB
Step 17: Shut down TGM database.
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
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.
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 1342177280 bytes Fixed Size 8620464 bytes Variable Size 469763664 bytes Database Buffers 855638016 bytes Redo Buffers 8155136 bytes Database mounted.
Step 19: Call FINISH_PLAN procedure
SQL> execute DBMS_ROLLING.FINISH_PLAN; PL/SQL procedure successfully completed.
After successful completion of finish_plan, you can check your TGM database is converted back to Physical Standby Database from Logical Standby Database.
[oracle@localhost ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Jul 12 05:51:52 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select name,open_mode,database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- DBUPGRAD MOUNTED PHYSICAL STANDBY
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
well-defined ..thanks