How To Manually Failover to Standby Database When Primary Database Got Issues

In normal situation, DBA can use Data Guard utility to switchover or failover database roles. DBA can also use SQL command to switchover or failver database roles.

This post demonstrate how to failover to standby database in 12c when Primary Databases is lost due to various reasons.

Check Database Role

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

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY

Stop Standby Database Recovery

SQL> alter database recover managed standby database cancel;
Database altered.

Complete Recovery and Transit Current Standby as Primary

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Database altered.

After this command, this standby database will NOT be able to go back to standby database any more.

Activate the Standby Database

SQL>alter database activate standby database;
Database altered.

Open the Database

SQL>alter database open;


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

OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE
---------- -------------------- ----------------
MOUNTED    MAXIMUM PERFORMANCE   PRIMARY

The Last and Not the Least

  • Backup new primary database ASAP.
  • Reinstate the old primary as new standby database, if flashback on. Otherwise rebuild the standby database as per normal processes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.