Oracle Data Guard test of switchover and failover

Active DataGuard failover:

https://docs.oracle.com/cd/E11882_01/server.112/e41134/role_management.htm#SBYDB4769

check the status before switchover or failover:

SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

Switchover steps:

Old Primary:

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

SHUTDOWN ABORT;

STARTUP MOUNT;

New primary:

SELECT SWITCHOVER_STATUS FROM V$DATABASE;  -- value should be TO PRIMARY or SESSIONS ACTIVE

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

ALTER DATABASE OPEN;

Bounce the database – this is important, without this step, the v$archive_dest_status will show “bad parameter” and no redo apply on standby.

New standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Select * from v$dataguard_stats;

SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

Failover steps:

Old primary:

Mount database

ALTER SYSTEM FLUSH REDO TO target_db_name;

alter system set log_archive_dest_2=''  scope=both;

Old standby:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

SELECT SWITCHOVER_STATUS FROM V$DATABASE;

alter system set log_archive_dest_2=''  scope=both;

ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

or

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

ALTER DATABASE OPEN;

Now test the switchover from test to test_stby:

On the old primary:

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

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

RECOVERY NEEDED

SQL>

On the old standby:

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 SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

NOT ALLOWED

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

NOT ALLOWED

SQL>

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

RECOVERY NEEDED

SQL> shutdown abort;

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size                  2235208 bytes

Variable Size             729810104 bytes

Database Buffers          331350016 bytes

Redo Buffers                5541888 bytes

Database mounted.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS

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

TO PRIMARY

SQL>

On new primary:

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

STATUS    GAP_STATUS

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

BAD PARAM

SQL>

Bounce the new primary database solved the problem.

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

VALID     NO GAP

Now test the switchover from test_stby to test:

Works as expected.

Now test the failover of test_stby:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

TO PRIMARY

SQL> alter system set log_archive_dest_2=''  scope=both;

System altered.

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

SQL> alter database open;

Database altered.

SQL> exec print_table('select * from v$database')

DBID                          : 2210233584

NAME                          : TEST

CREATED                       : 17-may-2016 13:32:32

RESETLOGS_CHANGE#             : 1897922

RESETLOGS_TIME                : 30-may-2016 15:37:41

PRIOR_RESETLOGS_CHANGE#       : 995548

PRIOR_RESETLOGS_TIME          : 17-may-2016 13:32:34

LOG_MODE                      : ARCHIVELOG

CHECKPOINT_CHANGE#            : 1897925

ARCHIVE_CHANGE#               : 0

CONTROLFILE_TYPE              : CURRENT

CONTROLFILE_CREATED           : 17-may-2016 13:32:32

CONTROLFILE_SEQUENCE#         : 913217881

CONTROLFILE_CHANGE#           : 1898008

CONTROLFILE_TIME              : 30-may-2016 15:39:36

OPEN_RESETLOGS                : NOT ALLOWED

VERSION_TIME                  : 27-may-2016 13:40:34

OPEN_MODE                     : READ WRITE

PROTECTION_MODE               : MAXIMUM PERFORMANCE

PROTECTION_LEVEL              : MAXIMUM PERFORMANCE

REMOTE_ARCHIVE                : ENABLED

ACTIVATION#                   : 2211353098

SWITCHOVER#                   : 2211353098

DATABASE_ROLE                 : PRIMARY

ARCHIVELOG_CHANGE#            : 0

ARCHIVELOG_COMPRESSION        : DISABLED

SWITCHOVER_STATUS             : NOT ALLOWED

DATAGUARD_BROKER              : DISABLED

GUARD_STATUS                  : NONE

SUPPLEMENTAL_LOG_DATA_MIN     : NO

SUPPLEMENTAL_LOG_DATA_PK      : NO

SUPPLEMENTAL_LOG_DATA_UI      : NO

FORCE_LOGGING                 : NO

PLATFORM_ID                   : 13

PLATFORM_NAME                 : Linux x86 64-bit

RECOVERY_TARGET_INCARNATION#  : 3

LAST_OPEN_INCARNATION#        : 3

CURRENT_SCN                   : 1898135

FLASHBACK_ON                  : NO

SUPPLEMENTAL_LOG_DATA_FK      : NO

SUPPLEMENTAL_LOG_DATA_ALL     : NO

DB_UNIQUE_NAME                : test_stby

STANDBY_BECAME_PRIMARY_SCN    : 1897910

FS_FAILOVER_STATUS            : DISABLED

FS_FAILOVER_CURRENT_TARGET    :

FS_FAILOVER_THRESHOLD         : 0

FS_FAILOVER_OBSERVER_PRESENT  :

FS_FAILOVER_OBSERVER_HOST     :

CONTROLFILE_CONVERTED         : NO

PRIMARY_DB_UNIQUE_NAME        : test

SUPPLEMENTAL_LOG_DATA_PL      : NO

MIN_REQUIRED_CAPTURE_CHANGE#  :

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

PL/SQL procedure successfully completed.