Data Guard switchover checklist

In some establishments, Dataguard switchovers are manual -

Please perform these pre-requisite checks before undertaking a switchover to primary.

1. ON STANDBY SITE:

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY…………….PROCEED.

If you receive a reply like the one below, then do not proceed- you are most probably firing the sql command in the primary site:

SQL> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY…………….do not proceed if this message is received …in this case you are most probably on the primary site..

Again on STANDBY SITE:

SQL> select name,value from v$parameter where name in (‘log_archive_dest_1′,’log_archive_dest_state_1′, ‘log_archive_dest_2′,’log_archive_dest_state_2′);

NAME
—————————————————————-
VALUE
——————————————————————————–
log_archive_dest_1
LOCATION=/opt/oracle/opsdb9i/arch

log_archive_dest_2
SERVICE=opsdb9i_blade07 lgwr sync affirm nodelay……….Make sure lgwr and not arch is mentioned here, otherwise new primary database will not open after switchover ( if the protection_mode is Maximum availability).

log_archive_dest_state_1
ENABLE

log_archive_dest_state_2
DEFER …………..DO NOT PROCEED.

While functioning as a STANDBY, it is better to set log_archive_dest_state_2 to DEFER to avoid errors appearing in the alert_log , but make sure it is set to ENABLE before starting a switchover.

This command can be issued to convert it to ENABLE

sql> alter system set log_archive_dest_state_2=’ENABLE’ scope=both;

Now check again…

SQL>NAME
—————————————————————-
VALUE
——————————————————————————–
log_archive_dest_state_2
ENABLE…………………………………PROCEED .

SQL> select distinct status from v$datafile;

STATUS
——-
ONLINE
SYSTEM

If any file/files are in RECOVER status, DO NOT PROCEED with the switchover…

SQL> select distinct open_mode from v$database;

OPEN_MODE
———-
MOUNTED………This is the correct response ..PROCEED with switchover.

If the STANDBY database has been opened in READ ONLY mode, you will receive the following message…

SQL> select distinct open_mode from v$database;

OPEN_MODE
———-
READ ONLY……………….DO NOT PROCEED with switchover.

To confirm that logs are being shipped and more importantly the LAST LOG archived has been applied…

On PRIMARY site
SQL> select max(SEQUENCE#) “LAST_LOG_GENERATED” FROM V$ARCHIVED_LOG WHERE THREAD# =1 and ARCHIVED=’YES’;

LAST_LOG_GENERATED
——————
14

Now on STANDBY site.
SQL> select max(SEQUENCE#) “LAST_LOG_APPLIED” FROM V$ARCHIVED_LOG WHERE THREAD# = 1 and APPLIED=’YES’;

LAST_LOG_APPLIED
—————-
14

SAFE TO PROCEED.