How to perform a Disaster Recovery Test in Oracle Dataguard Standby and rollback changes

Leave a reply

Steps to test the Standby Database on Dataguard Environment for Disaster Recovery

Performed a disaster recovery test on the Oracle Dataguard environment without impacting production. Verified the standby database by creating a flashback point and conducted application testing to ensure proper functionality in the standby Dataguard environment.

On the DR System:

1. Login with SQLPLUS
SET ORACLE_SID=ORCLDR
SQLPLUS / as sysdba
2. Set the DB_RECOVERY_FILE_DEST size 
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=30G;
3. Create a folder on the any drive for restore point configure i.e. flashback
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='E:\FLASHBACK';
4. Cancel the recovery on the DR server
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
5. Create a restore point 
CREATE RESTORE POINT DRPOINT GUARANTEE FLASHBACK DATABASE;
6. Now activate the standby database:
ALTER DATABASE ACTIVATE STANDBY DATABASE;
7. Restart the DB in mount state:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT ;
OR 
IF NOT WORK USE: STARTUP MOUNT FORCE;
8. Convert the Standby database to maximize peformance;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
9. Open the database:
ALTER DATABASE OPEN;
10. Check the select query by running the server
select * from dba_tables;

On the Primary Server:

1. Login with SQLPLUS
SET ORACLE_SID=ORCL
SQLPLUS / as sysdba
2.Check the Primary Database 
Select name,open_mode,database_role from v$database;
NAME        OPEN_MODE        DATABASE_ROLE
------      ----------       --------------------
ORCL        READ WRITE       PRIMARY
3. Defer the log archive service
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

Perform the Application Test

After application testing completed, we have to rollback all the changes to make standby database sync with Primary database.

Steps to Rollback all the Changes to Activate the Standby Server of Data Guard

On the DR Server:

1. Restart the database to mount state
Shutdown immediate
Startup mount
OR 
If not work then use: STARTUP MOUNT FORCE
2. Restore the database to created restored point DRPOINT
FLASHBACK DATABASE TO RESTORE POINT DRPOINT;
3. Convert the database to standby again:
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
4. Again restart the database.
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
5. Start the Standby database recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the Primary Server:

1. Login to Server
SET ORACLE_SID=ORCL
SQLPLUS / as sysdba
2. Enable the archive destination
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Check the both Primary and Standby databases comes in Sync state:

How to check Oracle Dataguard is in sync status
Primary Server:

select status,gap_Status from v$archive_Dest_Status where dest_id=2;
STATUS    GAP_STATUS
--------- ------------------------
ERROR     RESOLVABLE GAP
select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
        193254

Standby Server:

Select name,open_mode,database_role from v$database;
NAME           DATABASE_ROLE
------        --------------------
ORCLDR           PHYSICAL STANDBY
select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
       193130
Verify the archive log status applied or any gap with above commands:
Select status,gap_status from v$archive_dest_status where dest_id=2;
STATUS            GAP_STATUS
---------         ----------------------
VALID             NO_GAP

On the DR Server

When both are in Sync State, We are good to go to delete the Restore point which was early created on DR server:

1. Login to DR Server
SET ORACLE_SID=ORCLDR
Sqlplus / as sysdba
2. Check the database is standby:
Select name,open_mode,database_role from v$database;
NAME           DATABASE_ROLE
------        --------------------
ORCLDR           PHYSICAL STANDBY
3. Drop the restore point created:
DROP RESTORE POINT DRPOINT;
4. Drop the Recovery folder:
E:\flashback

Leave a Reply