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

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

About The Author

Comments

Leave a Reply

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