RAC Database upgrade using Transient Logical Standby

Database Upgrade using Transient Logical Standby

Objective

The Typical Oracle DB upgrades take about 1.5 - 2 hrs (Application downtime) depending upon how much time is required to keep the DB in upgrade mode. The Transient Logical Standby technique empowers DBA to upgrade Database with minimum downtime (5mins in this case switchover and switchback). In this case I am upgrading from 11.2.0.3 to 11.2.0.4

Infrastructure Assumptions

1. Two Node RAC hosting Primary DB

PRIM : PRIM1 and PRIM2 are instances

2. Two Node RAC hosting Standby DB

STDB : STDB1 and STDB2 are instances

3. Clusterware of higher/same version(11.2.0.4 in this case)
4. Role based RAC service configuredfor Application

At PRIM : srvctl add service -d PRIM -s prim_app -r PRIM1,PRIM2 -m BASIC -l PRIMARY -y AUTOMATIC -q TRUE -z 180 -w 5

 At STDB : srvctl add service -d STDB -s prim_app -r STDB1,STDB2 -m BASIC -l PRIMARY -y AUTOMATIC -q TRUE -z 180 -w 5

5. Application is DR enabled in TNS

(DESCRIPTION=(ADDRESS_LIST=(FAILOVER=on)(ADDRESS=(PROTOCOL=TCP)(HOST=PRIM-scan)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=STDB-scan)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=prim_app)))  

6. Standby is in Sync
7. DGMGRL configured (optional).
DGMGRL> show configuration 
Configuration - dg_PRIDR 
 Protection Mode: MaxPerformance
 Databases:
PRIM - Primary database
STDB - Physical standby database 
Fast-Start Failover: DISABLED 
Configuration Status: SUCCESS

Steps for Upgrade

Without going into techincal explanation I am going straight into the Steps and Commands. Ensure that the chronology of the Steps are quite critical and any miss may result in failure of the Upgrade process. Few steps like Dataguard setup, Software installation etc. are skipped.

 1 Enable Flashback at both PRIM and STDB.

             Alter database flashback enable;

 2 Ensure Standby Database is in Sync.

 3 Create guarantee restore point.

At PRIM

create restore point upgrade_1 guarantee flashback database;

alter database backup controlfile to'/tmp/PRU_PRIM_f.f';

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

At STDB

recover managed standby database cancel;

create restore point upgrade_2 guarantee flashback database;

alter database backup controlfile to '/tmp/PRU_STDB_f.f';

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

 4 Disable dgmgrl Broker.

At PRIM and STDB

alter system set dg_broker_start=FALSE scope=both;

5 Ensure MRP is stopped at Standby(STDB)

     ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Note : The step is important. If not done then Step7 will hang infinitely.

6 Build Logminer process.

exec dbms_logstdby.build;

NOTE : If encounter “ORA-01354: Supplemental log data must be added to run this command” execute

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

7 Convert to Logical Standby using Keep Identity clause.

At STDB

alter system set cluster_database=false scope=spfile;

shutdown immediate;

startup mount;

alter database recover to logical standby keep identity;

alter database open;

alter database start logical standby apply immediate;

select state from v$logstdby_state;

At PRIM

alter system set log_archive_dest_state_2=DEFER scope=memory;

At STDB

alter database stop logical standby apply;

create restore point upgrade_3 guarantee flashback database;

 8 Upgrade Logical standby to 11.2.0.4

shutdown immediate;

change .bash_profile to reflect 11.2.0.4

change /etc/oratab to reflect 11.2.0.4

logout and login to reflect new Oracle home settings

startup upgrade

@catupgrade.sql 

Apply DST patch. Refer Oracle documentation.

 9 Sync Logical Database with Primary Data using SQL Apply.

At PRIM

alter system set log_archive_dest_state_2=enable scope=both;

At STDB

alter database start logical standby apply immediate;

Check SQL Apply and Sync Status

set lines 200

set pages 200

col STATUS for a70

SELECT   PID,   TYPE,   STATUS,   to_char(HIGH_SCN)  FROM   V$LOGSTDBY;

select * from v$logstdby_process;

10 Change DB config at clusterware

srvctl upgrade database -d STDB -o <new Oracle home>

11 Switchover Database to Logical Standby (Downtime for Application)

At PRIM

     SELECT SWITCHOVER_STATUS FROM V$DATABASE; (TO STANDBY)

     ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

select switchover_status from v$database;

At STDB

select switchover_status from v$database; (TO PRIMARY)

alter database commit to switchover to primary;

At PRIM

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

12 Flashback DB to restore point

At STDB

     alter system set log_archive_dest_state_2=defer scope=memory;

At PRIM

select database_role from v$database;

shutdown immediate;

startup mount

flashback database to restore point upgrade_1;

shutdown immediate;

13 Start Standby DB from new home

At PRIM

     startup mount from new home <copy pwd,tns to new home. Update the ORACLE_HOME in listener.ora>

     alter database convert to physical standby;

     shutdown immediate;

     startup mount;

14 Let Standby sync with Primary

At STDB

     alter system set log_archive_dest_state_2=enable scope=memory;

At PRIM

     startup mount;

     recover managed standby database using current logfile disconnect;

15 Change DB config at clusterware

     srvctl upgrade database -d PRIM -o <new Oracle home>

     Bounce the DB using srvctl

16 Enable dgmgrl broker at Primary and Standby

17 Recreate Broker configurations.

18 Check standby is in Sync.

19 Switchover to Physical Standby  (Downtime for Application)
           Usual switchover process for Physical Standby.

20 Perform necessary validations

21 Remove restore points.