Upgrading to Oracle Database 12cR2 Using Transient Logical Standby Method

Rolling upgrade with Transient Logical Standby is known as a MAA (Maximum Availability Architecture) technique, to minimize downtime during upgrade of Oracle database. 

This note will document the steps for performing the Advanced Data Guard method of Transient Logical Rolling Upgrade.

The following picture provides the general flow for an example migration of on-premise database to Oracle Cloud.  This document will focus on steps 2 and 4 from the image.

Migration and upgrade of Production database with a minimum downtime by using physru.sh script.

Use the physru.sh script

From Oracle Support document  Oracle11g Data Guard: Database Rolling Upgrade Shell Script (Doc ID 949322.1) you can download the physru.sh scriptThe assumptions are below:

1. Create a Physical Standby database with “Duplicate target database for standby from active database”
2. Recover managed standby database disconnect.
3. Standby database must be in sync and put the database into Maximum Availability or Maximum Protection mode
4. Convert physical standby to logical standby.

Before running the physru.sh script, we have to do some checks

At Primary

1. Flashback must be ON

   alter database flashback on;

2. DG Broker set to False

   alter system set dg_broker_start=false;

3. Standby file management=auto

   alter system set standby_file_management=auto scope=both sid='*';

4. Production database in read write mode.

At Standby

1. Flashback must be ON

   alter database flashback on;

2. DG Broker set to False

   alter system set dg_broker_start=false;

3. Standby file management=auto

   alter system set standby_file_management=auto scope=both sid='*';

4. Active standby database recovery must be stopped

   Alter database recover managed standby database cancel;

5. Standby database must be on Mount Stage

   Startup mount;

Arguments

physru accepts the following arguments:

<username> = sys should be used
<primary_tns> = tns service name to primary
<standby_tns> = tns service name to physical standby
<primary_name> = db_unique_name of primary (or any identifying string)
<standby_name> = db_unique_name of standby (or any identifying string)
<upgrade_version> = target rdbms version

Note: Click on images to read the content. Apologies for improper resolution

On Primary Side

we can run the script on primary side.

[oracle@oracle Desktop]$ ./physru.sh sys finance finan finance finan 12.2.0.1.0

Please enter the sysdba password:

At Standby database

Check the database role and name.

Now Upgrade the logical database by dbua or Manually , we will upgraded the database with DBUA.

Finally logical database upgrade successfully.

Next step is to OPEN the standby database in read write mode as suggested by the Physru.sh script

Now at the Primary Site Run again  Physru script

In this step, the primary database will become the logical standby database and logical standby database become primary database but it has db_unique_name=finan which is running on 12.2.0.1.0.

It has applied all the update which are made to the database while upgrading the standby logical database into 12c and then it will switch-over the roles.

Now I will mount my primary database, which is currently has Physical Standby database role with new binary.

[oracle@oracle Desktop]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1
[oracle@oracle Desktop]$ export PATH=$PATH:$ORACLE_HOME/bin
[oracle@oracle Desktop]$ export ORACLE_SID=finance
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Mar 13 00:41:45 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> startup mount
ORACLE instance started.
Total System Global Area 1660944384 bytes
Fixed Size 8621376 bytes
Variable Size 1056965312 bytes
Database Buffers 587202560 bytes
Redo Buffers 8155136 bytes
Database mounted.

SQL> select name,dbid,database_role from v$database;

NAME       DBID      DATABASE_ROLE
--------- ---------- ----------------
FINANCE   1324657    PHYSICAL STANDBY

Third call of the script will flashback the Primary to the guaranteed restore point, created at the first call of the script.

[oracle@oracle Desktop]$ ./physru sys finance finan finance finan 12.2.0.1.0
Please enter the sysdba password:

### Initialize script to either start over or resume execution
Mar 12 10:08:02 2011 [0-1] Identifying rdbms software version
Mar 12 10:08:02 2011 [0-1] database finance is at version 12.2.0.1.0
Mar 12 10:08:02 2011 [0-1] database finan is at version 12.2.0.1.0
Mar 12 10:08:04 2011 [0-1] verifying flashback database is enabled at finance and finan
Mar 12 10:08:04 2011 [0-1] verifying available flashback restore points
Mar 12 10:08:05 2011 [0-1] verifying DG Broker is disabled
Mar 12 10:08:05 2011 [0-1] looking up prior execution history
Mar 12 10:08:06 2011 [0-1] last completed stage [5-4] using script version 0001
Mar 12 10:08:06 2011 [0-1] resuming execution of script

### Stage 6: Run media recovery through upgrade redo
Mar 12 10:08:08 2011 [6-1] upgrade redo region identified as scn range [199999, 8361261]
Mar 12 10:08:08 2011 [6-1] starting media recovery on finance
Mar 12 10:08:15 2011 [6-1] confirming media recovery is running
Mar 12 10:09:04 2011 [6-1] waiting for media recovery to initialize v$recovery_progress
Mar 12 10:09:35 2011 [6-1] monitoring media recovery's progress
Mar 12 10:09:38 2011 [6-2] last applied scn 199813 is approaching upgrade redo start scn 199999
Mar 12 10:10:14 2011 [6-3] recovery of upgrade redo at 01% - estimated complete at Mar 13 12:46:31
Mar 12 10:10:30 2011 [6-3] recovery of upgrade redo at 16% - estimated complete at Mar 13 12:15:57
Mar 12 10:10:46 2011 [6-3] recovery of upgrade redo at 25% - estimated complete at Mar 13 12:14:39
Mar 12 10:11:02 2011 [6-3] recovery of upgrade redo at 33% - estimated complete at Mar 13 12:14:16
Mar 12 10:11:18 2011 [6-3] recovery of upgrade redo at 45% - estimated complete at Mar 13 12:13:31
Mar 12 10:11:34 2011 [6-3] recovery of upgrade redo at 48% - estimated complete at Mar 13 12:13:50
Mar 12 10:11:51 2011 [6-3] recovery of upgrade redo at 56% - estimated complete at Mar 13 12:13:44
Mar 12 10:12:07 2011 [6-3] recovery of upgrade redo at 60% - estimated complete at Mar 13 12:13:51
Mar 12 10:12:24 2011 [6-3] recovery of upgrade redo at 68% - estimated complete at Mar 13 12:13:44
Mar 12 10:12:40 2011 [6-3] recovery of upgrade redo at 70% - estimated complete at Mar 13 12:14:00
Mar 12 10:12:56 2011 [6-3] recovery of upgrade redo at 74% - estimated complete at Mar 13 12:14:07
Mar 12 10:13:13 2011 [6-3] recovery of upgrade redo at 82% - estimated complete at Mar 13 12:14:00
Mar 12 10:13:29 2011 [6-3] recovery of upgrade redo at 87% - estimated complete at Mar 13 12:14:02
Mar 12 10:13:46 2011 [6-3] recovery of upgrade redo at 96% - estimated complete at Mar 13 12:13:56
Mar 12 10:14:04 2011 [6-4] media recovery has finished recovering through upgrade

### Stage 7: Switch back to the original roles prior to the rolling upgrade
NOTE: At this point, you have the option to perform a switchover
which will restore finance back to a primary database and
finan back to a physical standby database. If you answer 'n'
to the question below, finance will remain a physical standby
database and finan will remain a primary database.

Do you want to perform a switchover? (y/n):n

SUCCESS: The physical rolling upgrade is complete

Finally , Upgrade completed successfully.

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:

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

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp