Oracle Database Dataguard Switchover

 

############## Switchover Preparation   ##############

1)  Take  full backup of  Database 

2) Take backup  of  Controlfile 

3)  srvctl config database -d primary -a 

##############    Switchover Without Datagurad Broker   ##############

1.1 Start Oem  Blackout 

emctl start blackout  blackout_name  -nodelevel 

emctl  status blackout 

1.2  Perform some  log switch 

Alter system archive log current  ; 

Alter system archive log current  ; 

Alter system archive log current  ; 

2.1 On Primary Database, Verify That it Can Be Switched to the Standby Role

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

---------------------------------

TO STANDBY

2.2 Initiate the Switchover

On Standby  Database,

SQL> alter database recover managed standby database cancel;

SQL> alter database recover managed standby database finish;

On Primary Database, Initiate the Switchover

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

SQL> SHUTDOWN ABORT;

SQL> STARTUP MOUNT;

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

2.3 On Standby Database, Verify That it Can Be Switched to the Primary Role

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

---------------------------------

TO PRIMARY

2.4 On Standby Database, Switch Standby Database Role to the Primary Role

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

SQL> ALTER DATABASE OPEN;

SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

Below  is  one of good features we never used but good to be included 

ALTER DATABASE SWITCHOVER TO CHICAGO VERIFY;   ( pre   switchover ) 
ALTER DATABASE SWITCHOVER TO BOSTON FORCE;

##############  Switchover  With Datagurad Broker   ##############

2.1   Connect to Primary 

DMGRL >   show configuration

DMGRL >   show database  verbose primary  

DGMGRL> validate database verbose primary  

DMGRL >   show database   primary  inconsistantproperties 

DMGRL >    show database  verbose  standby 

DGMGRL> validate database verbose standby 

DMGRL >    show database   standby inconsistantproperties 

DMGRL >    switchover to standby 

##############  Switchover Post task ##############

We need to update right role of database in Crs 

1)  srvctl config database -d primary -a    

Verify Service is started on New primary 

2)  srvctl status  service -d primary

##############  Tracing for switchover  ##############

We had one of  case where switchover was not complete  and oracle suggested to  enable below tracing .

 

SQL> alter system set log_archive_trace=8191;  -- enabling trace
SQL> alter system set log_archive_trace=0;      -- disabling trace

##############   ORA-12514 during switchover using Data Guard Broker  ##############

SID_LIST_lsnrDBNMAE =
        (SID_LIST =
         (SID_DESC =  (GLOBAL_DBNAME = DBNMAE) (ORACLE_HOME = /path) (SID_NAME = DBNMAE))
         (SID_DESC =  (GLOBAL_DBNAME = DBNMAE_DGMGRL) (ORACLE_HOME = /pth) (SID_NAME = DBNMAE))
        )

Switch over fails – 2 standby :   ORA-16816:

Problem Description

Should you find yourself in a situation where a Data Guard Broker switchover to Standby has failed and left your environment with 2 Physical Standby Databases, follow this simple procedure to switch the failed switchover Standby Database back to Primary.

You may also see the following error from a DGMGRL "show configuration" command:

ORA-16816: incorrect database role

Solution

 

1.       Logon (as sysdba) to the instance that was your Primary database instance before the switchover.

2.       Confirm the database role.

SQL> select database_role from v$database;

DATABASE_ROLE

---------------

PHYSICAL STANDBY

 

3.       Shutdown the instance.

SQL> shutdown immediate;

4.       Mount the database.

SQL> startup mount;

5. Cancel the MRP process. You will receive “ORA-16136: Managed Standby Recovery not active” if it is           not running, but you can ignore.

SQL> alter database recover managed standby database cancel;

6.       Terminate the current switchover to Standby that never completed fully.  

SQL> alter database recover managed standby database finish;

7.       Now switchover to Primary.

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

8.       Open the database.

SQL> alter database open;

9.       Confirm the database role.

SQL> select database_role from v$database;

 

DATABASE_ROLE

----------------

PRIMARY

Additional Steps

When attempting to open the Primary Database you may suffer the following error:

 

SQL> alter database open

*

ERROR at line 1:

ORA-16649: possible failover to another database prevents this database being opened

 

In this case, before you can open the database, you must disable Data Guard Broker as follows:

SQL> alter system set dg_broker_start=false scope=both sid=’*’;

System altered.

 

SQL> alter database open;

 

Database altered.

Now re-install Data Guard Broker.

##############  Known Issues  ##############

Primary Instance Fails To Mount W/ Ora-19970 After Switchover Interrupted (document ID 2256447.1)

DGMGRL>switchover to <standby> Fails with ORA-12514 (Doc ID 1582927.1)

If  we  have  broker ,  use dgmgrl  fir switchover or else we will end  up with 

ORA-16649 

##############  References   ##############

https://docs.oracle.com/en/database/oracle/oracle-database/19/spmss/switchover-to-a-physical-db.html#GUID-AAD70601-D248-4309-B8DD-F461EE31A5FF

https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/examples-using-data-guard-broker-DGMGRL-utility.html

SRDC - Collect Data Guard Diagnostic Information (Doc ID 2219763.1)