Step by step procedure for Disaster Recovery exercise (Data Guard)

This is all about recovering the databases at Disaster Recovery (DR) site that support the business.Every company that
maintains Disaster recovery site wants to test the databases and applications at least once in a year to confirm
that the recovery plans and strategies you have put in place will actually work .

For the below scenario, we have two sites.
In Oracle terminolgy , 1)Primary(read write) 2) Standby sites(mount). Data is in sync by using Dataguard replication.
i))Here we DIFFER the log shipping on Primary and convert standby(DR) site as primary role.
ii)Application team connects to DR site and inserts data and verify certain use cases and validates.
iii)Once every thing is verified ,apps team approves to convert the DB at DR to put into mount mode back.

At Stand by

1) Verify flash recovery area in DR

show parameter DB_RECOVERY_FILE_DEST_SIZE show parameter DB_RECOVERY_FILE_DEST

To check to see if apply is running (on the standby db):

select process, status , sequence# from v$managed_standby;

2)To see log sequence Gaps (on the standby db):

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

3)Cancel the recovery on the standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

4) Select Flashback on

SELECT FLASHBACK_ON FROM V$DATABASE;

5) Turn on Flashback for standby database :

ALTER DATABASE FLASHBACK ON; SELECT FLASHBACK_ON FROM V$DATABASE;

6) Restore point creation on Stand by Database

create restore point APRIL032023 GUARANTEE FLASHBACK DATABASE; Select name,time from v$restore_point; <- confirm restore point

At Primary

7) login to primary and run below command on each respective instance:

Set environment

. oraenv ORACLE_SID = [oracle] ? ? The Oracle base has been set to /u01/app/oracle

Connect using sqlplus

[oracle@Primary ~]$ sqlplus “/as sysdba” Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options

At Standby

9) Now login to Standby DB check archive gap and perform all below steps only from DR databases.

select to_char(sysdate,’DD.MM.RR HH24:MI: SS’) time, a.thread#, (select max (sequence#) from v$archived_log where archived=’YES’ and thread#=a.thread#) archived, max (a.sequence#) applied, (select max (sequence#) from v$archived_log where archived=’YES’ and thread#=a.thread#)-max (a.sequence#) gap from v$archived_log a where a.applied=’YES’ group by a.thread#;

10) ALTER DATABASE ACTIVATE STANDBY DATABASE;

Shutdown immediate. Startup mount; Select open_mode from v$database;

11) Application team inserts data and validates data.

Creating user for inserting data.(For testing purpose) Now create a table in user drtest and insert data

12) Shutdown and Start at standby

Shutdown immediate; Startup mount;

13)Flash back to restorepoint

FLASHBACK DATABASE TO RESTORE POINT APRIL032023;

14) Covert to Physical Standby

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

 

Shutdown immediate; Startup mount;

15) 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session;

16)

select name, open_mode, database_role, switchover_status, guard_status from v$database;

17) Verify log sequence gap

SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

 

See Also: