How to Failover Database with flashback using Dataguard

How to Failover Database with flashback using Dataguard

Scenario: This failover will not apply the changes on Standby back to primary

Step 1: On Primary – Stop Log Shipping

Defer the log shipping to standby.

SQL> Alter system set log_archive_dest_state_n=defer;

Check last log sequence

SQL> Select max(sequence#) from v$log_history;

Step 2: On Satndby

2.1 Enable flashback recovery.

Setup flashback destination and file size.

SQL> Alter system set db_recovery_file_dest=’/u01/oracle/flash’ scope=spfile;

SQL> Alter system set db_recovery_file_dest_size=10G scope=spfile;

SQL> Alter system set db_flashback_retention_target=1800;

Note : Make sure you have sufficient disk space and the size you set is capable for the retention period set.

Retention_Target, Recovery_file_Dest_Size should be based on the time and size of transaction you required for your Failover.

2.2 Cancel the recovery.

SQL> Alter database recover managed standby database cancel;

SQL> Shutdown immediate

Startup database in mount mode.

SQL> Startup mount

2.2 Change the database in the flashback mode

SQL> alter database flashback on;

2.3 Create the Restore Point on the standby database

SQL> Create restore point before_dr guarantee flashback database;

2.4 Activate Standby database

SQL> Alter database activate standby database;

Open the DR database

SQL> Alter database open;

Shutdown and restart the database

— Perform DR tests —

— Rollback and convert into Standby

3. On Satndby

3.1 Shutdown after DR test

SQL>Shutdown immediate

Startup database with mount and force option.

SQL>startup mount force

Restore database to the restored point.

SQL> Flashback database to restore point before_dr;

Drop restored point

SQL> Drop restore point before_dr;

Turn off flashback

alter database flashback off;

3.2 Convert database to physical standby

SQL> Alter database convert to physical standby;

shutdown and startup in standby mode

SQL> Shutdown immediate

SQL> startup nomount

SQL> Alter database mount standby database;

SQL> Alter database recover managed standby database disconnect from session;

4. On Production

Enable the log shipping to standby.

SQL> Alter system set log_archive_dest_state_n=’enable';

switch logg and verify the apply.

SQL> alter system switch logfile;