How To Failover and Flashback a Physical Standby for testing without recreating Database

Failing over and using flashback on a physical standby database for testing without recreating the database involves a structured approach to ensure you can revert to the original state afterward.

This can help for Disaster Recovery testing and we was to have the original state back after the testing.

Pre-requisites:

1) Ensure there is enough space in the Fast Recovery Area (FRA) to hold the flashback logs.

On primary:

SQL> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- --------------------

db_recovery_file_dest string /u01/install/APPS/data

db_recovery_file_dest_size big integer 150G

df -h /u01/install/APPS/data

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/ocivolume-root 389G 270G 120G 70% /

On Standby:

SQL> show parameter db_recovery

NAME TYPE VALUE

------------------------------------ ----------- --------------------

db_recovery_file_dest string /u01/install/APPS/data

db_recovery_file_dest_size big integer 150G

df -h /u01/install/APPS/data

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/ocivolume-root 389G 261G 129G 68% /

2) Verify synchronization between the primary and standby databases.

On primary:

SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE

---------------- -------------------- --------------------

PRIMARY READ WRITE MAXIMUM PERFORMANCE

SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Primary Seq Generated

---------- --------------------------

1 207

On Standby:

SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE

---------------- -------------------- --------------------

PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE

SQL> select thread#, max(sequence#) "Last Standby Seq Applied" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;

THREAD# Last Standby Seq Applied

---------- ------------------------

1 207

Create a dummy data on Primary before failover.

-- Connect to your database as the appropriate user

sqlplus / as sysdba

-- Create a dummy table

CREATE TABLE dummy_table (

id NUMBER PRIMARY KEY,

name VARCHAR2(50),

description VARCHAR2(100)

);

-- Insert 5 records into the dummy table

INSERT INTO dummy_table (id, name, description) VALUES (1, 'Record1', 'This is the first record');

INSERT INTO dummy_table (id, name, description) VALUES (2, 'Record2', 'This is the second record');

INSERT INTO dummy_table (id, name, description) VALUES (3, 'Record3', 'This is the third record');

INSERT INTO dummy_table (id, name, description) VALUES (4, 'Record4', 'This is the fourth record');

INSERT INTO dummy_table (id, name, description) VALUES (5, 'Record5', 'This is the fifth record');

-- Commit the changes to make them permanent

COMMIT;

-- Verify the inserted records

SELECT * FROM dummy_table;

--Switch log file and make sure the standby is in sync.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

Check the synchronization now

Both sides it is in sync.

THREAD# Last Standby Seq Applied

---------- ------------------------

1 211

Now before failover lets create a GRP (Guaranteed restore point in Standby Server)

On Standby:

--Check current restore point

select * from v$restore_point;

Please note flashback is off in my environment. It will use FRA space for flashback.

--Stop redo apply process on the Standby database (stop MRP):

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

--Create Restore point

CREATE RESTORE POINT before_failover GUARANTEE FLASHBACK DATABASE;

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE

---------- --------------------- --- ------------

TIME

---------------------------------------------------------------------------

RESTORE_POINT_TIME PRE

--------------------------------------------------------------------------- ---

NAME

--------------------------------------------------------------------------------

PDB CLE PDB_INCARNATION# REP CON_ID

--- --- ---------------- --- ----------

5.9656E+12 7 YES 1048576000

02-NOV-24 05.27.00.000000000 AM

YES

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE

---------- --------------------- --- ------------

TIME

---------------------------------------------------------------------------

RESTORE_POINT_TIME PRE

--------------------------------------------------------------------------- ---

NAME

--------------------------------------------------------------------------------

PDB CLE PDB_INCARNATION# REP CON_ID

--- --- ---------------- --- ----------

BEFORE_FAILOVER

NO NO 0 NO 0

Making the database ready for failover.

On Primary Database

On the primary database, switch logs so the SCN of the restore point will be archived on the physical standby database.

When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> select NAME,DATABASE_ROLE,to_char(CURRENT_SCN),STANDBY_BECAME_PRIMARY_SCN,FLASHBACK_ON from v$database;

NAME DATABASE_ROLE TO_CHAR(CURRENT_SCN)

--------- ---------------- ----------------------------------------

STANDBY_BECAME_PRIMARY_SCN FLASHBACK_ON

-------------------------- ------------------

EBSCDB PRIMARY 5965595145672

0 NO

Primary now on higher sequence and SCN than standby

--Primary

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 213

Next log sequence to archive 215

Current log sequence 215

SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Primary Seq Generated

---------- --------------------------

1 214

--Standby

THREAD# Last Standby Seq Applied

---------- ------------------------

1 211

I will shut abort the primary to replicate a disaster

--on primary

sqlplus '/as sysdba'

SQL> shut abort

ORACLE instance shut down.

--on Standby- Activate Standby for Failover:

Activate the standby database to become the primary.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE

---------------- -------------------- --------------------

PRIMARY MOUNTED MAXIMUM PERFORMANCE

SQL> alter database open;

Database altered.

SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE

---------------- -------------------- --------------------

PRIMARY READ WRITE MAXIMUM PERFORMANCE

Lets check our dummy table and add few transaction on new primary site

SELECT * FROM dummy_table;

-- Insert 2 new records into the dummy table

INSERT INTO dummy_table (id, name, description) VALUES (6, 'Record6', 'This is the sixth record');

INSERT INTO dummy_table (id, name, description) VALUES (7, 'Record7', 'This is the seventh record');

commit;

SELECT * FROM dummy_table;

Now we have 7 records in table.

Lets failback and put the system back into original state

Shutdown and Start in Mount Mode:

--The new primary shutdown and start in mount mode

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

--Flashback the Database: Flashback the database to the appropriate SCN or a restore point created before the failover.

FLASHBACK DATABASE TO RESTORE POINT BEFORE_FAILOVER;

--Convert the Database Back to a Physical Standby:

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Start the original primary database

--On primary

sqlplus '/as sysdba'

startup

--on standby

Start MRP process

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Check sync on both

-- On primary

SQL> SELECT DATABASE_ROLE,OPEN_MODE, PROTECTION_MODE FROM V$DATABASE;

DATABASE_ROLE OPEN_MODE PROTECTION_MODE

---------------- -------------------- --------------------

PRIMARY READ WRITE MAXIMUM PERFORMANCE

SQL> select thread#, max(sequence#) "Last Primary Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

THREAD# Last Primary Seq Generated

---------- --------------------------

1 215

-- On Standby

THREAD# Last Standby Seq Applied

---------- ------------------------

1 211

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

ARCH CONNECTED 0 0 0 0

DGRD ALLOCATED 0 0 0 0

RFS IDLE 1 0 0 0

RFS IDLE 0 0 0 0

RFS RECEIVING 1 216 70410 1

RFS IDLE 0 0 0 0

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

MRP0 APPLYING_LOG 1 216 70409 2048000

12 rows selected.

THREAD# Last Standby Seq Applied

---------- ------------------------

1 211

SQL> /

THREAD# Last Standby Seq Applied

---------- ------------------------

1 211

SQL> SELECT PROCESS,STATUS, THREAD#,SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

ARCH CONNECTED 0 0 0 0

DGRD ALLOCATED 0 0 0 0

RFS IDLE 1 0 0 0

RFS IDLE 0 0 0 0

RFS RECEIVING 1 216 70464 1

RFS IDLE 0 0 0 0

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS

--------- ------------ ---------- ---------- ---------- ----------

MRP0 APPLYING_LOG 1 216 70463 2048000

12 rows selected.

THREAD# Last Standby Seq Applied

---------- ------------------------

1 211

SQL> /

THREAD# Last Standby Seq Applied

---------- ------------------------

1 215

No if you observe the standby will come back in sync after applying the log. Please wait for some time to make then synchronized.

Drop the restore point in Standby which was created before failover.

SQL> drop restore point BEFORE_FAILOVER;

Restore point dropped.

SQL> select * from v$restore_point;

no rows selected

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

How To Failover and Flashback a Physical Standby for testing without recreating Database