Dataguard Physical Standy Switchover

dataguard switchover指的就是將dataguard 主庫和備庫進行角色互換，同時保證沒有資料丟失。

實驗具體步驟：

資料庫版本：

Primary Database :  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Physical Standby Database: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

1. 檢查是否有Log file gap(主庫和備庫操作)

SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

STATUS    GAP_STATUS
--------- ------------------------
VALID     NO GAP

2. 檢查備庫上有與主庫相匹配的臨時表空間檔案

主庫：

SQL> select file_name from dba_temp_files;

FILE_NAME
------------------------------------------------------------
/data/oracle/oradata/phyprimary/temp01.dbf

備庫：

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/data/oracle/oradata/phystandby/temp01.dbf

3. 確保主庫應用到歸檔日誌沒有延遲(主庫操作)

SQL> select delay_mins from v$archive_dest where dest_id = 2;

DELAY_MINS
----------
         0

如果存在，使用如下語句清除延遲

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

4. 確保主庫和備庫的切換狀態

主庫：

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS,PRIMARY_DB_UNIQUE_NAME,PROTECTION_MODE from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS    PRIMARY_DB_UNIQUE_NAME         PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA  phyprimary                     PRIMARY          TO STANDBY                                          MAXIMUM AVAILABILITY

備庫：

SQL> set linesize 200
SQL> /

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS    PRIMARY_DB_UNIQUE_NAME         PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA  phystandby                     PHYSICAL STANDBY NOT ALLOWED          phyprimary                     MAXIMUM AVAILABILITY

5. 把主庫切換成備庫，並重啟主庫到mount

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

Database altered.

SQL> shutdown abort;        
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             335546752 bytes
Database Buffers           79691776 bytes
Redo Buffers                6094848 bytes
Database mounted.

檢視日誌檔案，此時資料庫已經變成備庫狀態：

Successful mount of redo thread 1, with mount id 324279998
Physical Standby Database mounted

6. 檢視備庫的切換狀態

SQL> /

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS    PRIMARY_DB_UNIQUE_NAME         PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA  phystandby                     PHYSICAL STANDBY SESSIONS ACTIVE      phyprimary                     MAXIMUM AVAILABILITY

由於SWITCHOVER_STATUS  狀態是SESSIONS ACTIVE，所以切換時要加上WITH SESSION SHUTDOWN 

7. 切換備庫到主庫（備庫操作），並開啟新的主庫(原備庫)

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

Database altered.

SQL> alter database open;

Database altered.

8. 檢視當前主庫（原備庫）和備庫（原主庫）的狀態

當前主庫：

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS,PRIMARY_DB_UNIQUE_NAME,PROTECTION_MODE from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS    PRIMARY_DB_UNIQUE_NAME         PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA  phystandby                     PRIMARY          TO STANDBY           phyprimary                     MAXIMUM AVAILABILITY

當前備庫：

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS,PRIMARY_DB_UNIQUE_NAME,PROTECTION_MODE from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS    PRIMARY_DB_UNIQUE_NAME         PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA  phyprimary                     PHYSICAL STANDBY RECOVERY NEEDED      phystandby                     MAXIMUM AVAILABILITY

9. 最後一步，在新備庫應用Redo log

新備庫：

SQL> !date
Thu Aug  7 19:21:42 PDT 2014

SQL> SELECT SEQUENCE#, to_char(FIRST_TIME,'YYYY-MM-DD HH24:mi:ss'), to_char(NEXT_TIME,'YYYY-MM-DD HH24:mi:ss'),applied 
  2  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc;

SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' APPLIED
---------- ------------------- ------------------- ---------
        40 2014-08-07 19:19:41 2014-08-07 19:19:42 YES
        39 2014-08-07 19:12:01 2014-08-07 19:19:41 YES