1. Home
  2. Knowledge Base
  3. Database Administration
  4. Data Guard switchover checklist
  1. Home
  2. Knowledge Base
  3. High Availability
  4. Data Guard switchover checklist

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.

The following two tabs change content below.

Arjun Raja

Latest posts by Arjun Raja (see all)

Updated on June 2, 2021

Was this article helpful?

Related Articles

Leave a Comment