物理dataguard 正常切換 腳色轉換，switchover_status 狀態改變

正常切換

切換前：
主庫：

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY

SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;

COUNT(*)
----------
1
在切換前，殺掉所有的資料庫連線 觀察SWITCHOVER_STATUS，如果是 TO STANDBY，則可以直接切換
如果SESSIONS ACTIVE ，則用

備用庫 ：

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED

說明：

主庫需要注意事項
A 如果switchover_status為TO_STANDBY說明可以轉換
直接轉換
alter database commit to switchover to physical standby;
B 如果switchover_status為SESSIONS ACTIVE 則關閉會話
SQL>alter database commit to switchover to physical standby with session shutdown;

在備庫中操作，檢視備庫
SQL> select switchover_status from v$database;

A 如果switchover_status為TO_PRIMARY 說明標記恢復可以直接轉換為primary庫
SQL>alter database commit to switchover to primary

B 如果switchover_status為SESSION ACTIVE 就應該斷開活動會話
SQL>alter database commit to switchover to primary with session shutdown;

C 如果switchover_status為NOT ALLOWED 說明切換標記還沒收到，此時不能
執行轉換。

切換中 ：

備用庫
SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE SESSIONS ACTIVE

切換後備用的狀態，模式：

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY

2. 檢查環境：

確認主庫和從庫間網路連線通暢；

確認沒有活動的會話連線在資料庫中；

PRIMARY資料庫處於開啟的狀態，STANDBY資料庫處於MOUNT狀態；

確保STANDBY資料庫處於ARCHIVELOG模式；

如果設定了REDO應用的延遲，那麼將這個設定去掉；

確保配置了主庫和從庫的初始化引數，使得切換完成後，DATA GUARD機制可以順利的執行。

如果是最大保護模式，先變成最大效能模式：

3. 切換的順序： 先從主庫到備用，再從備庫到主庫

主切備：

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY

SQL>
SQL>
SQL> alter database commit to switchover to physical standby ;

Database altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount ;
ORACLE instance started.

Total System Global Area 3242987696 bytes
Fixed Size 733360 bytes
Variable Size 1174405120 bytes
Database Buffers 2063597568 bytes
Redo Buffers 4251648 bytes
SQL> alter database mount standby database;

Database altered.

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

Database altered.

SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY

備切主
SQL> select DATABASE_ROLE from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY

SQL> alter database commit to switchover to primary;

Database altered.

SQL> SQL> SQL>
SQL>
SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Total System Global Area 3242987696 bytes
Fixed Size 733360 bytes
Variable Size 1174405120 bytes
Database Buffers 2063597568 bytes
Redo Buffers 4251648 bytes
Database mounted.
Database opened.

修改主 備庫 的 tnsnames.ora
主庫SESSIONS ACTIVE是正常primary狀態下的結果。

DataGuard:Physical Standby Failover

接physical standby switchover

primary db :db2

standby db :db1

一般情況下failover都是表示primary資料庫癱瘓，最起碼也是起不來了，因此這種型別的切換基本上不需要primary資料庫做什麼操作。所以下列步驟中如果有提到primary和standby執行的，只是建議你如果primary還可以用，那就執行一下,即使不去執行，對failover來說也沒有關係

1.檢查歸檔檔案是否連續，是否有gap

      在standby庫執行

      SQL> select THREAD#,LOW_SEQUENCE#,HIGH_SEQUENCE# from v$archive_gap;

 2.如果步驟1查詢出來紀錄，則在primary庫上執行，否則跳過此步驟

    在主庫上執行語句，按步驟1查詢出來的紀錄找出歸檔檔案

　SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE THREAD#=1 AND DEST_ID=1 AND SEQUENCE# BETWEEN XX AND XX;

　　--如果primary存在，拷貝相應的歸檔到STANDBY資料庫，並註冊.

　　SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'xxx';

3.檢查歸檔檔案是否完整

  分別在primary/standby執行下列語句：

　　SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

　　把相差的歸檔複製到待轉換的standby伺服器,並手工register

4.開始做failover

  察看standby程式狀態

  SQL> select process,client_process,sequence#,status from v$managed_standby;

PROCESS  CLIENT_P SEQUENCE# STATUS

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

ARCH     ARCH            30 CLOSING

ARCH     ARCH             0 CONNECTED

RFS      LGWR            31 IDLE

RFS      ARCH             0 IDLE

RFS      N/A              0 IDLE

MRP0     N/A             31 APPLYING_LOG

   SQL> alter database recover managed standby database finish force ;

FORCE關鍵字將會停止當前活動的RFS程式，以便立刻執行failover。

　　或

　　SQL> alter database recover managed standby database finish skip standby logfile;

SQL> alter database commit to switchover to primary;

SQL> shutdown immediate

SQL> startup

SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

DATABASE_ROLE   PROTECTION_MODE     PROTECTION_LEVEL    OPEN_MODE

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

PRIMARY         MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE

Failover切換成功