Blog

SQL> alter database switchover to STESTDB;

OR

DGMGRL> switchover to STESTDB; 

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

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.

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)