Flashback through role transition for physical standby

In this post, I will demonstrate that database role does not change when a physical standby is flashed back through a switchover.

Overview:
– Note current SCN# (pre switchover)
– Check current configuration
pc00sby1 – Primary database
pc00prmy – Physical standby database
– Perform switchover
– check post switchover configuration
pc00prmy – Primary database
pc00sby1 – Physical standby database
– Flashback new primary (pc00prmy) to SCN# before switchover
– check that there is error in configuration and current standby(pc00sby1) has diverged from the primary as SCN# of standby is ahead of primary
– To bring back the standby database into configuration, flashback it to a scn# before switchover
— check that both the databases have retain their current roles after flashback
pc00prmy – Primary database
pc00sby1 – Physical standby database

– Implementation –

- Check current configuration

pc00sby1 – Primary database
pc00prmy – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00sby1 - Primary database
pc00prmy - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

– Find out current SCN# (before switchover)

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1100354

– Perform switchover

DGMGRL> switchover to pc00prmy;
Performing switchover NOW, please wait...
New primary database "pc00prmy" is opening...
Operation requires shutdown of instance "pc00sby1" on database "pc00sby1"
Shutting down instance "pc00sby1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "pc00sby1" on database "pc00sby1"
Starting instance "pc00sby1"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "pc00prmy"

– check that switchover has taken place successfully
pc00prmy – Primary database
pc00sby1 – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

PC00SBY1>select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

PC00PRMY>select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

– Flashback new primary (pc00prmy) to SCN# before switchover (1100354)

PC00PRMY>shu immediate;
         startup mount;
         flashback database to scn 1100354;
         alter database open resetlogs;

         alter system switch logfile;

– check that there is error in configuration and current standby(pc00sby1) has diverged from the primary

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database
Error: ORA-16810: multiple errors or warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
 ERROR

DGMGRL> show database pc00sby1;

Database - pc00sby1

Enterprise Manager Name: pc00sby1.us.oracle.com
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
pc00sby1

Database Error(s):
 ORA-16700: the standby database has diverged from the primary database
 ORA-16766: Redo Apply is stopped

Database Status:
 ERROR

— The reason for diverging is that SCN# of standby( 1101143) is ahead of primary (1100582)

PC00PRMY>select current_scn from v$database;

CURRENT_SCN
-----------
1100582

PC00SBY1>select current_scn from v$database;

CURRENT_SCN
-----------
1101143

— To bring back the standby database into configuration, we need to flashback it to an scn# behind primary.

– Let’s flashback standby database  to a scn# before switchover (1100354) since we want to verify that databases maintain their current role after flashback.

PC00SBY1>shu immediate;
startup mount;
flashback database to scn 1100354;

alter database open;

PC00SBY1>select current_scn from v$database;

CURRENT_SCN
-----------
1100354

– switch log on primary –

PC00PRMY>alter system switch logfile;

System altered.

– check configuration –
— Both the databasese retain their current roles –
pc00prmy – Primary database
pc00sby1 – Physical standby database

DGMGRL> show configuration;

Configuration - dgconfig1

Protection Mode: MaxPerformance
Databases:
pc00prmy - Primary database
pc00sby1 - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

I hope this post was useful.

Enjoy!

———————————————————————————–

Related links:

————————————————————————–

2 thoughts on “Flashback through role transition for physical standby

  1. Very good article. Very clear to follow. I do have one question though :
    Why do your open the standby database after resetlogs? Can I keep it in Mount mode since I don’t have a license?

Leave a Reply to Anju Garg Cancel reply