Dataguard Administration: switchover and failover

From Robs_Wiki
Jump to: navigation, search

Switchover a dataguard database

Step 1 On the Primary: Run Pre-Checks at the Primary Database

-- check the role and switchover status
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE            SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
PRDDB     PRDDB_DB1                      PRIMARY          READ WRITE           SESSIONS ACTIVE (1)

-- check the tempfile status
This is a necessary check to make sure the tempfiles are the same on the Primary and the Standby. (2)

SQL> select name, round(bytes/(1024*1024)) from v$tempfile;

NAME                                                         ROUND(BYTES/(1024*1024))
------------------------------------------------------------ ------------------------
/u02/app/oracle/oradata/PRDDB/dbf01/PRDDB/temp01.dbf                             1024

-- check the status of the applied logs on the Primary
SQL> select SEQUENCE#, ARCHIVED, APPLIED, to_char(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') NEXT_TIME from V$ARCHIVED_LOG 
where next_time > sysdate-1 order by sequence#;

 SEQUENCE# ARC APPLIED   NEXT_TIME
---------- --- --------- -----------------------------
        30 YES NO        02-APR-2019 14:52:25
        30 YES YES       02-APR-2019 14:52:25
        . . .
        31 YES YES       02-APR-2019 15:00:48
        31 YES NO        02-APR-2019 15:00:48
        38 YES NO        03-APR-2019 09:51:01
        39 YES NO        03-APR-2019 09:55:40
        39 YES NO        03-APR-2019 09:55:40

-- generate a log file switch and recheck
SQL> alter system switch logfile;

SQL> select SEQUENCE#, ARCHIVED, APPLIED, to_char(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') NEXT_TIME from V$ARCHIVED_LOG 
where next_time > sysdate-1 order by sequence#;
 
 SEQUENCE# ARC APPLIED   NEXT_TIME
---------- --- --------- -----------------------------
        30 YES NO        02-APR-2019 14:52:25
        30 YES YES       02-APR-2019 14:52:25
        . . .
        38 YES YES       03-APR-2019 09:51:01
        39 YES NO        03-APR-2019 09:55:40
        39 YES NO        03-APR-2019 09:55:40
        40 YES NO        03-APR-2019 10:55:07
        40 YES NO        03-APR-2019 10:55:07

22 rows selected.

-- check the view v$dataguard_status
SQL> select message from v$dataguard_status;

. . .
LNS: Beginning to archive log 1 thread 1 sequence 40
ARC1: Beginning to archive thread 1 sequence 40 (2175427-2183135)
LNS: Completed archiving log 1 thread 1 sequence 40
ARC1: Completed archiving thread 1 sequence 40 (2175427-2183135)
TT00: Standby redo logfile selected for thread 1 sequence 41 for destination LOG_ARCHIVE_DEST_2
LNS: Beginning to archive log 2 thread 1 sequence 41

256 rows selected.
  • (1) A switchover status with SESSIONS ACTIVE means that you have to close the active # connections upon switchover. This will be done as part of the switchover.
  • (2) Despite the fact that standby_file_management is set to auto (which replicates new datafiles), this setting does not replicate new tempfiles, because new tempfiles are not added automatically in the Physical Standby database because no redo is generated. (Oracle documentation Doc ID 834174.1). Therefore, it is always good to compare the tempfile status prior to any switchover operation.

Step 2 On the Standby: Run Pre-Checks at the Standby Database

-- check the current state
SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE            SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
PRDDB     PRDDB_DB2                      PHYSICAL STANDBY MOUNTED              NOT ALLOWED (1)

-- check the applied logs

SQL> select SEQUENCE#, ARCHIVED, APPLIED, to_char(NEXT_TIME,'DD-MON-YYYY HH24:MI:SS') NEXT_TIME from V$ARCHIVED_LOG 
where next_time > sysdate-1 order by sequence#;
 
 SEQUENCE# ARC APPLIED   NEXT_TIME
---------- --- --------- -----------------------------
        30 YES YES       02-APR-2019 14:52:25
        31 YES YES       02-APR-2019 15:00:48
        . . .
        38 YES YES       03-APR-2019 09:51:01
        39 YES YES       03-APR-2019 09:55:40
        40 YES IN-MEMORY 03-APR-2019 10:55:07

11 rows selected.

-- check the view v$dataguard_status

SQL> select message from v$dataguard_status;
. . .
Primary database is in MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process (PID:10973)
ARC1: Beginning to archive thread 1 sequence 40 (2175427-2183135)
ARC1: Completed archiving thread 1 sequence 40 (0-0)
Media Recovery Waiting for thread 1 sequence 41
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Assigned to RFS process (PID:26672)

256 rows selected.
  • (1): The SWITCHOVER_STATUS is NOT ALLOWED because the Primary is still on that role currently

Step 3 On the Primary: Switch the Primary to Standby -- part 1

-- Perform the switchover
SQL> alter database commit to switchover to physical standby with session shutdown;

-- Now shutdown the Primary database
SQL> shutdcown immediate;

Step 4 On the Standby: Switch the Standby to Primary

SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE            SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
PRDDB     PRDDB_DB2                      PHYSICAL STANDBY MOUNTED              TO PRIMARY


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

SQL> shutdown immediate;

SQL> startup;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE          SWITCHOVER_STATUS
--------- ------------------------------ ---------------- ------------------ -----------------
PRDDB     PRDDB_DB2                      PRIMARY          READ WRITE         FAILED DESTINATION (1)

-- enable the log_archive_dest_2 when ready:
SQL> alter database set log_archive_dest_state_2=enable scope=both;
  • (1) The SWITCHOVER_STATUS is set to FAILED DESTINATION because the Standby is currenlty down. We will start it up in the next step.

Step 5 On the former Primary: Switch the Primary to Standby -- part 2

SQL> startup nomount;

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session;

SQL> select name, db_unique_name, database_role, open_mode, switchover_status from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    OPEN_MODE            SWITCHOVER_STATUS
--------- ------------------------------ ---------------- -------------------- --------------------
PRDDB     PRDDB_DB1                      PHYSICAL STANDBY MOUNTED              NOT ALLOWED

Additional remarks about CLient Failover

The application clients should fail over automatically, as the TNSNAMES.ora string has both database servers defined. However, this needs to be checked, and also the performance impact needs to be checked. In the client string(see below) the Production server (now servering as the Standby Database) is tried first, and then, when unsuccesfull, the Standby server is probed. However, this may add additional latency to the client operation and we may have to reverse that situation, by making the Standby Server the first host that is probed in the connect string.

PRDDB =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (FAILOVER=ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver1.example.com)(PORT = 1525))
      (ADDRESS = (PROTOCOL = TCP)(HOST = prodserver2.example.com)(PORT = 1525))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRDDB.example.com)
    )
  )

In a RAC Environment you can also create role dependant services. For example a service to the Primary that is only up when the database_role is PRIMARY. But that is explained in another post on this website.