Pre-check For SWITCHOVER using DG Broker

1. Verify the primary database instance is open (READ/WRITE Mode) and the standby database instance is mounted.

SELECT NAME, OPEN_MODE FROM GV$DATABASE;

2. Verify there are no active users connected to the databases.

SET LINES 10000 pages 10000

SELECT SID, SCHEMANAME, OSUSER, MACHINE, STATUS FROM GV$SESSION WHERE USERNAME IS NOT NULL;

3. Check for any active jobs running

SELECT * FROM DBA_JOBS_RUNNING;

SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED

FROM DBA_SCHEDULER_JOBS WHERE ENABLED='TRUE' AND OWNER <> 'SYS';

4) Check the primary db has standby redolog files

SELECT GROUP#, BYTES/1024, STATUS FROM GV$STANDBY_LOG;

5) Check standby database has tempfile and it should match the size of the temp file from primary db.

SELECT NAME, BYTES FROM V$TEMPFILE;

6) Switch the backup process to the new primary database, if the backup policy is from the primary db.

7) Check whether the following settings are available for redo transport services such as LogXptMode, NetTimeout, LogShipping, StandbyFileManagement, FastStartFailoverTarget, StandbyArchiveLocation & AlternateLocation., DelayMins in the standby & primary database.

DGMGRL> SHOW DATABASE VERBOSE 'PRIMARY_DB_UNIQUE_NAME';

DGMGRL> SHOW DATABASE VERBOSE 'STANDBY_DB_UNIQUE_NAME';

Check whether StaticConnectIdentifier is configured on ALL NODES, so you have run this command 4 times if you have 2 node RAC primary & standby database.

SHOW INSTANCE VERBOSE 'INSTANCE_NAME' ON DATABASE 'DB_NAME'

Check start options for the primary & standby database

 srvctl config database -d ‘PRIMARY_DB_NAME’ –a

 srvctl config database -d ‘STANDBY_DB_NAME’ –a

Check the DELAYMINS, if it has set for value. Reduce the DELAYMINS to ZERO and apply all the logs before starting switchover.

DGMGRL> SHOW DATABASE 'PRIMARY_DB_UNIQUE_NAME' DELAYMINS;

DGMGRL> EDIT DATABASE 'PRIMARY_DB_UNIQUE_NAME' SET PROPERTY 'DELAYMINS'='0';

8)  Check the datafile status in the standby database.

SELECT DISTINCT STATUS FROM V$DATAFILE;

Note: The status should be in “ONLINE” & “SYSTEM” mode. If any file/files are in RECOVER status, then identify the reason.

Check the offline datafiles in the primary & standby database

SELECT DISTINCT STATUS FROM V$DATAFILE_HEADER WHERE STATUS <> 'ONLINE';

9) Perform a log switch from the primary DB and verify logs are applied on the standby database.

IF RAC

ALTER SYSTEM ARCHIVE LOG CURRENT;

NON – RAC

ALTER SYSTEM SWITCH LOGFILE;

10) Make sure things are fine with the database on both primary (All instances) and Standby database (All instances). Check alert log, trace files from all nodes to make sure the database running without any issues.

11) check the status of the DG Broker LISTENER status, it should be up and running and check the status of the REMOTE_LISTENER, LISTENER_NETWORKS, LOCAL_LISTENER

lsnrctl status <LSNR_NAME>

12) Check FAST START FAILOVER is enabled and check the preferred STANDBY database.

13) Once the switch over process completes, Oracle DG Broker will have the same DG protection mode & Network Transmission role for the NEW PRIMARY DB.

14) Check the following

The primary database is enabled and is in the TRANSPORT-ON state.

DGMGRL> show database 'PRIMARY_DB_UNIQUE_NAME';

Note: Check for “Intended State” and it should be “TRANSPORT-ON”

 The target standby database is enabled and is in the APPLY-ON state.

DGMGRL> show database 'STANDBY_DB_UNIQUE_NAME';

Note: Check for “Intended State” and it should be “APPLY-ON”

15) Check the flashback database is enabled in the primary/standby database. If it is not enabled then enable the FLASHBACK database

SELECT FLASHBACK_ON FROM GV$DATABASE;

TIPS :

“ORA-12514 during the switchover” - Check whether the StaticConnectIdentifier is set correctly

TO CHECK (NEED TO CHECK ON ALL THE INSTANCES)

SHOW INSTANCE VERBOSE 'INSTANCE_NAME' ON DATABASE 'DB_NAME'

TO CHANGE

DGMGRL> edit instance dg112i1 on database dg112i_prm set PROPERTY StaticConnectIdentifier='';

Ex

DGMGRL> edit instance dg112i1 on database dg112i_prm set PROPERTY StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.11.225)(PORT=1555))(CONNECT_DATA=(SERVICE_NAME=DG112I_PRM_DGMGRL.au.oracle.com)(INSTANCE_NAME=dg112i1)(SERVER=DEDICATED)))';