Role Transitions with Oracle Data Guard by using SQL

As a DBA, there are many situations required to switchover database role in Data Guard environment. For post 12c, the easy way is to execute one line command to switchover database:

SQL> alter database switchover to SBYTEST;

DGMGRL> switchover to SBYTEST;

This post demonstrates how to implement role transitions step by step by using SQL.

On Primary Database

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      READ WRITE           PRIMARY

SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO STANDBY

The switchover_status column of v$database can have the following values:

Not Allowed:-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases
Session Active:- Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted
Switchover Pending:- This is a standby database and the primary database switchover request has been received but not processed.
Switchover Latent:- The switchover was in pending mode, but did not complete and went back to the primary database
To Primary:- This is a standby database, with no active sessions, that is allowed to switch over to a primary database
To Standby:- This is a primary database, with no active sessions, that is allowed to switch over to a standby database
Recovery Needed:- This is a standby database that has not received the switchover request

SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

Verify switchover is OK or not.

SQL>  alter database switchover to SBYTEST verify;
 alter database switchover to SBYTETS verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

Check alert log, and fix the problem until getting the following result.

SQL> alter database switchover to SBYTEST verify;


Database altered.

Primary role transition to standby, and check alert log for details.

SQL> alter database commit to switchover to standby with session shutdown;

Database altered.

After the previous step, the new standby database is down. so we have to manually startup and start recovery.

SQL> startup nomount;

SQL> alter database mount standby database;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST      MOUNTED              PHYSICAL STANDBY

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

Database altered.

Now we move onto standby database.

On Standby Database

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TESTDB    MOUNTED              PHYSICAL STANDBY

SQL> select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

This is the initial switchover status before primary switchover to standby yet.

After primary switchover to standby already, then continue the following steps.

SQL>  select switchover_status from v$database ;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL>  alter database commit to switchover to PRIMARY with session shutdown;

Database altered.


SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
SBYTEST  MOUNTED              PRIMARY


SQL> alter database open;

Database altered.

Reconfigure Data Guard Broker because databases role changed.

DGMGRL > show configuration;

DGMGRL > disable configuration;

DGMGRL > enable configuration;

Finally if it is RAC environment, modify database configurations like “Start options” and “Database role”, etc.

$srvctl config database -d TEST

$ srvctl modify  database -d TEST -role PHYSICAL_STANDBY -startoption "READ ONLY"

$ srvctl stop database -d TEST

$srvctl start database -d TEST

$srvctl config database -d SBYTEST

$srvctl modify  database -d SBYTEST -role PRIMARY -startoption OPEN 

$ srvctl stop database -d SBYTEST

$srvctl start database -d SBYTEST

DGMGRL> show configuration;

Configuration - TESTDB

  Protection Mode: MaxPerformance
  Members:
  SBYTEST - Primary database
     TEST  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 31 seconds ago)