Oracle Standby - Rolling Upgrade using Transient Logical Standby database

Oracle Standby - Rolling Upgrade using Transient Logical Standby database

In Database world, Getting downtime for mission critical database is very difficult. Specially for activity like upgrading database where downtime can be easily vary from 4 Hours to 8 Hours. We can reduce the downtime significantly , using Rolling Upgrade using Transient Logical Standby database. 

In this blog ,We will  illustrates how we can perform a rolling upgrade from Oracle 12c Release 1 to Oracle 12c Release 2 using a Transient Logical Standby database.
This approach will miminise the downtime required for an upgrade which can potentially run into several hours down to just the time required to perform a switchover which could be a few minutes in most cases.

A rolling upgrade using a Transient Logical Standby database at a very high level will involve three main stages or steps:
1) Temporarily convert a physical standby database to a logical standby database using the new KEEP IDENTITY clause
2) Perform a database upgrade of the logical standby database
3) Return the logical standby database back to its identity or original status as a physical standby database once the upgrade is complete

The assumption here is that ….
1)We already have configured a Physical Standby Database using Data Guard best practices and both Primary and Standby databases are in sync and Redo Transport and Redo Apply are working properly.
2)Data Guard Broker if configured is disabled
3) Standby database is operating in Maximum Availability or Maximum Protection mode
4) Flashback Database is enabled

Below are the steps:
1) Create a Guaranteed Restore Point on original Primary

SQL> create restore point Primary_pre_upgrade_1 guarantee flashback database;

Restore point created.

2) Create a guaranteed restore point on original Standby database.We also create a restore point on the standby database in case we encounter any errors while upgrading the database and we would like to have a fallback in place.

SQL> recover managed standby database cancel;
Media recovery complete.
SQL>  create restore point Standby_pre_upgrade_1 guarantee flashback database;

Restore point created.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

3) Create the Log Miner dictionary on the original primary database

SQL> exec dbms_logstdby.build;

PL/SQL procedure successfully completed.

4) Convert the original physical standby database to logical standby database.

We will use the new 12c KEEP IDENTITY clause which ensures that the logical standby database keeps its DBID which is identical to the DBID of the original primary database.

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  526131200 bytes
Fixed Size                  2139784 bytes
Variable Size             209717624 bytes
Database Buffers          306184192 bytes
Redo Buffers                8089600 bytes
Database mounted.

SQL> alter database recover to logical standby keep identity;

Database altered.

SQL> alter database open;

Database altered.

5) Start SQL Apply on the new logical standby database and monitor the dictionary build.At this point now, the logical standby database will be receiving the necessary redo information from the original primary database to populate its own Log Miner dictionary. We can monitor the dictionary build by querying the view V$LOGSTDBY_STATE.

SQL> alter database start logical standby apply immediate;

Database altered.

SQL> select state from v$logstdby_state;

STATE
----------------------------------------------------------------
LOADING DICTIONARY

SQL> /

STATE
----------------------------------------------------------------
APPLYING

SQL> /

STATE
----------------------------------------------------------------
IDLE

6) Prepare the Logical Standby database for upgrade
We need to stop the SQL Apply and also create another restore point on the logical standby as a fallback measure.

On Original Primary

SQL> alter system set log_archive_dest_state_2=DEFER scope=memory;

System altered.

On Logical Standby

SQL> alter database stop logical standby apply;

Database altered.

SQL> create restore point Standby_pre_upgrade_2 guarantee flashback database;

Restore point created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

7) Peform the upgrade of the Logical Standby database to 12c Release 2 using DBUA or by using the manual steps.While the upgrade is in progress, users are still connected to the original 12c Release 1 primary database. Let us make some DDL as well as DML changes on this database.
SQL> conn scoott/scoott
Connected.
SQL> update employee set emp_state='SG';

55500 rows updated.

SQL> commit;

Commit complete.

SQL> create table myemployee as select * from employee;

Table created.

SQL> update myemployee set emp_state='IND';

55500 rows updated.

SQL> commit;

Commit complete.

8) After the upgrade is complete, we will now start SQL Apply on the upgraded Logical Standby database

On Origial Primary

SQL> alter system set log_archive_dest_state_2=enable scope=memory;

System altered.

On Upgraded Logical Standby database

SQL> alter database start logical standby apply immediate;

9)Perform a Switchover to the Upgraded 12c Release 2 standby database

On Original Primary

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> alter database commit to switchover to logical standby;

Database altered.

On upgraded Logical Standby database

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL>  alter database commit to switchover to logical primary;

Database altered.

10) Perform some checks
While the logical standby database was being upgraded to 12c Release 2, users were connected to the original 12c Release 1 primary database. Let us see if those changes we made have been propagated to the standby site – which now after the switchover has become the new Primary database.

SQL> conn scoott/scoott
Connected.
SQL> select distinct emp_state from employee;

emp_state
------------------------------
SG

SQL> select distinct emp_state from myemployee;

emp_state
------------------------------
IND

Make some changes on the upgraded 12c Release 2 database
SQL> conn scoott/scoott
Connected.
SQL> update myemployee set emp_state='PK';

55500 rows updated.

SQL> update employee set emp_state='USA';

55500 rows updated.

SQL> commit;

Commit complete.

11) Retransformation into Physical Standby database
The former primary database is running at a lower Oracle version (12c Release 1) as a transient logical standby database. It cannot receive and apply any redo from the new primary database until it has been converted back into a physical standby database.
On New Primary (12c Release 2)

SQL> conn / as sysdba
Connected.
SQL> alter system set log_archive_dest_state_2=defer scope=memory;

On New Logical Standy database  (12c Release 1)

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
LOGICAL STANDBY

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE    12.1.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  526131200 bytes
Fixed Size                  2139784 bytes
Variable Size             260049272 bytes
Database Buffers          255852544 bytes
Redo Buffers                8089600 bytes
Database mounted.

SQL> flashback database to restore point pre_upgrade_1;

Flashback complete.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

12) Start the new logical standby database in the 12c Release 2 Oracle Home and convert it to a physical standby database.We need to do some prep work before we can start the instance in the 12c Release 2 Oracle home. Copy the password file and the init.ora file from the 12c R1 locations to the 12c R2 locations.

oracle>/u01/oracle/product/12.2.0.2/dbhome_1/dbs> cp /u01/oracle/product/12.1.0.2/dbhome_1/dbsorapwdba1 .
oracle>/u01/oracle/product/12.2.0.2/dbhome_1/dbs> cp /u01/oracle/product/12.1.0.2/dbhome_1/dbsspfiledba1.ora .

SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2208368 bytes
Variable Size             159387024 bytes
Database Buffers          352321536 bytes
Redo Buffers                8019968 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2208368 bytes
Variable Size             159387024 bytes
Database Buffers          352321536 bytes
Redo Buffers                8019968 bytes
Database mounted.

At this stage we will get an error if we try and open the database as the database is of a lower version and has not been upgraded as yet.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 393392
Session ID: 191 Serial number: 3

13) Start Redo Apply on the converted physical database
On New Primary

SQL> alter system set log_archive_dest_state_2=enable scope=memory;

System altered.

On New Physical Standby database

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

14) Monitor Redo Apply
When the redo apply starts, it will register a new incarnation received from the primary database.
Until that happens, we will see from the alert log that the Redo Apply loops every ten seconds or so waiting for the incarnation to be registered.

Fri Jan 07 10:38:51 2022
Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery waiting for new incarnation during transient logical upgrade procedure
Errors in file /u01/app/oracle/diag/rdbms/primary/PRIMARY/trace/dba1_pr00_368854.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Slave exiting with ORA-19906 exception
Errors in file /u01/app/oracle/diag/rdbms/primary/PRIMARY/trace/dba1_pr00_368854.trc:
ORA-19906: recovery target incarnation changed during recovery
Then we will see …..
RFS[1]: Identified database type as 'physical standby': Client is LGWR SYNC pid 1134740
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
Standby controlfile consistent with primary
RFS[1]: Selected log 4 for thread 1 sequence 111 dbid 2023850869 branch 713959355
Fri Jan 07 10:39:12 2022
RFS[2]: Assigned to RFS process 1736934
RFS[2]: Identified database type as 'physical standby': Client is ARCH pid 1990894
RFS[2]: Selected log 5 for thread 1 sequence 110 dbid 2023850869 branch 713959355
RFS[2]: New Archival REDO Branch(resetlogs_id): 713959355  Prior: 713439030
RFS[2]: Archival Activation ID: 0x78aa01e2 Current: 0x0
RFS[2]: Effect of primary database OPEN RESETLOGS
RFS[2]: Managed Standby Recovery process is active
RFS[2]: Incarnation entry added for Branch(resetlogs_id): 713959355 (dba1)
Fri Jan 07 10:39:13 2022
Setting recovery target incarnation to 3
Then we will see the the media recovery in progress. This will apply all the redo which contains the changes related to the upgrade of the database to 12c Release 2, so that once the media recovery is complete, we will see that the physical standby database has indeed been upgraded to 12c Release 2.
Fri Jan 07 11:01:06 2022
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves

RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 1990894
Fri Jan 07 11:01:22 2022
RFS[4]: Assigned to RFS process 1835206
RFS[4]: Identified database type as 'physical standby': Client is ARCH pid 794694
RFS[3]: Opened log for thread 1 sequence 7 dbid 2023850869 branch 713959355
RFS[4]: Opened log for thread 1 sequence 8 dbid 2023850869 branch 713959355
Archived Log entry 79 added for thread 1 sequence 7 rlc 713959355 ID 0x78aa01e2 dest 2:
Archived Log entry 80 added for thread 1 sequence 8 rlc 713959355 ID 0x78aa01e2 dest 2:
......
......
RFS[44]: Assigned to RFS process 561242
RFS[44]: Identified database type as 'physical standby': Client is ARCH pid 794694
RFS[44]: Opened log for thread 1 sequence 99 dbid 2023850869 branch 713959355
Archived Log entry 119 added for thread 1 sequence 99 rlc 713959355 ID 0x78aa01e2 dest 2:
Archived Log entry 120 added for thread 1 sequence 97 rlc 713959355 ID 0x78aa01e2 dest 2:
RFS[44]: Opened log for thread 1 sequence 100 dbid 2023850869 branch 713959355
Archived Log entry 121 added for thread 1 sequence 100 rlc 713959355 ID 0x78aa01e2 dest 2:
......
......
Media Recovery Log /oraarchive/primary/arch/arch1_10_713959355.log
Fri Jan 07 11:42:53 2022
Media Recovery Log /oraarchive/primary/arch/arch1_11_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_12_713959355.log
Fri Jan 07 11:43:09 2022
Media Recovery Log /oraarchive/primary/arch/arch1_13_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_14_713959355.log
......
......
Media Recovery Log /oraarchive/primary/arch/arch1_24_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_25_713959355.log
Fri Jan 07 11:45:02 2022
Media Recovery Log /oraarchive/primary/arch/arch1_26_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_27_713959355.log
Fri Jan 07 11:45:19 2022
Media Recovery Log /oraarchive/primary/arch/arch1_28_713959355.log
.....
.....
Media Recovery Log /oraarchive/primary/arch/arch1_104_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_105_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_106_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_107_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_108_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_109_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_110_713959355.log
Fri Jan 07 13:19:50 2022
Media Recovery Log /oraarchive/primary/arch/arch1_111_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_112_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_113_713959355.log
Media Recovery Log /oraarchive/primary/arch/arch1_114_713959355.log
Media Recovery Waiting for thread 1 sequence 115 (in transit)
Recovery of Online Redo Log: Thread 1 Group 6 Seq 115 Reading mem 0
  Mem# 0: /u02/oradata/dba1/flash_recovery_area/DBA1/onlinelog/o1_mf_6_5sm961z3_.log

15) Switchback Steps

We will now perform a second switchover (note, this step is optional if we wish to continue running the database from the original standby site). This will return the new primary database back to its original role as the physical standby database and the new standby database back to its original role of Primary database.
On new Standby

SQL>  select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2208368 bytes
Variable Size             159387024 bytes
Database Buffers          352321536 bytes
Redo Buffers                8019968 bytes
Database mounted.
Database opened.

On current Primary

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> alter database commit to switchover to standby with session shutdown;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  521936896 bytes
Fixed Size                  2208368 bytes
Variable Size             159387024 bytes
Database Buffers          352321536 bytes
Redo Buffers                8019968 bytes
Database mounted.
Database opened.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.

16) Test all changes made from 12c Release 2 database  before switchback have been propagated

SQL> conn scoott/scoott
Connected.
SQL> select distinct emp_state from employee;

emp_state
------------------------------
USA

SQL> select distinct emp_state from myemployee;

emp_state
------------------------------
PK

You can learn more about oracle database administration: 

Pro Oracle GoldenGate for the DBA

Oracle Security Tools Installation and Configuration

Expert Oracle Exadata 

Oracle Database 12c Release 2 Real Application Clusters Handbook: Concepts, Administration, Tuning & Troubleshooting

Expert Oracle RAC 12c

==========================================================

Please check our other blogs for  Dataguard ,Standby  & DR.