As a DBA, there are many situations required to switchover database role in Data Guard environment. From 12c onwards , the easy way is to execute one line command to switchover database:
SQL> alter database switchover to STESTDB;
OR
DGMGRL> switchover to STESTDB;
This post demonstrates how to implement role transitions step by step by using SQL.
Primary DB/Instances: TESTDB/TESTDB1,TESTDB2
Standby DB/Instances: STESTDB/STESTDB1, STESTDB2
On Primary Database
From node1:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TESTDB 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.
Still on node1:
SQL> alter database switchover to STESTDB verify;
alter database switchover to STESTDB verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details
Go to alert log, and find the following messages:
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to a primary database, the new primary database will not be protected.
ORA-16475 signalled during: alter database switchover to STESTDB verify...
Standby database ‘ LOG_ARCHIVE_DEST_2’ has not been defined, so we define them properly.
SQL> show parameter LOG_ARCHIVE_DEST_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
SQL> alter system set log_archive_dest_2='SERVICE=TESTDB VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TESTDB';
System altered.
Now we go to primary node 1 and run verify again. Check alert log, and fix the problem until getting the following result.
SQL> alter database switchover to STESTDB verify;
Database altered.
Finally primary role transition to standby, and check alert log for details.
Still on primary node 1:
SQL> alter database commit to switchover to standby with session shutdown;
Database altered.
After the previous step, the new standby database ( old primary database ) is down. so we have to manually startup and start recovery.
$ srvctl status database -d testdb
Instance TESTDB1 is not running on node node1
Instance TESTDB2 is not running on node node2
-- on old primary node 1 still
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.
-- start up second new standby instance through SQL or srvctl ( you need change database role and startup option first).
$ srvctl start instance -d TESTDB -i TESTDB2 -o mount
$ srvctl status database -d testdb
Instance TESTDB1 is running on node node 1
Instance TESTDB2 is running on node node 2
Now we move onto standby database.
On Standby Database
On old standby node 1:
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.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TESTDB READ WRITE PRIMARY
Check and make sure second instance OPEN READ WRITE.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
TESTDB MOUNTED PRIMARY
SQL> alter database open;
Database altered.
SQL> select INST_ID,NAME,OPEN_MODE,RESTRICTED
from gv$pdbs order by 1,2;
INST_ID NAME OPEN_MODE RES
-------- ------------ ---------- ---
1 RACTESTPDB READ WRITE NO
1 PDB$SEED READ ONLY NO
2 RACTESTPDB READ WRITE NO
2 PDB$SEED READ ONLY NO
SQL> alter pluggable database RACTESTPDB save state instances=all;
Pluggable database altered.
SQL> select CON_ID,CON_NAME,INSTANCE_NAME,state, restricted from DBA_PDB_SAVED_STATES;
-- make sure PDB' restricted=NO
Enable Change Blocking
SQL>SELECT status, filename FROM V$BLOCK_CHANGE_TRACKING;
SQL> show parameter DB_CREATE_FILE_DEST;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Check instances both are running :
$ srvctl status database -d STESTDB
Instance TESTDB1 is running on node snode1
Instance TESTDB2 is running on node snode2
Reconfigure Data Guard Broker because databases role changed.
On new Primary node 1 ( snode 1):
DGMGRL> show configuration;
Configuration - dg_testdb
Protection Mode: MaxPerformance
Members:
testdb - Primary database
Error: ORA-16810: multiple errors or warnings detected for the member
stestdb - Physical standby database
Error: ORA-16816: incorrect database role
Fast-Start Failover: Disabled
Configuration Status:
ERROR (status updated 27 seconds ago)
DGMGRL > disable configuration;
GMGRL > enable configuration;
DGMGRL> show configuration;
Configuration - dg_testdb
Protection Mode: MaxPerformance
Members:
stestdb - Primary database
testdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 12 seconds ago)
Finally if it is RAC environment, modify database configurations like “Start options” and “Database role”, etc.
$srvctl config database -d TESTDB
$ srvctl modify database -d TESTDB -role PHYSICAL_STANDBY -startoption "READ ONLY"
$ srvctl stop database -d TESTDB
$srvctl start database -d TESTDB
$srvctl config database -d STESTDB
$srvctl modify database -d STESTDB -role PRIMARY -startoption OPEN
$ srvctl stop database -d STESTDB
$srvctl start database -d STESTDB
DGMGRL> show configuration;
Configuration - dg_testdb
Protection Mode: MaxPerformance
Members:
stestdb - Primary database
testdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 42 seconds ago)
On primary, log on to RMAN and change the archive log deletion policy by running:
RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
On standby, log on to RMAN and change the archive log deletion policy by running:
RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
Make DBA Life Easy 