物理dataguard 正常切換 腳色轉(zhuǎn)換，switchover_status 狀態(tài)改變：

正常切換

切換前： 
主庫：

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


在切換前，殺掉所有的數(shù)據(jù)庫連接 觀察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

說明：

主庫需要注意事項(xiàng)
A 如果switchover_status為TO_STANDBY說明可以轉(zhuǎn)換
直接轉(zhuǎn)換
alter database commit to switchover to physical standby;
B 如果switchover_status為SESSIONS ACTIVE 則關(guān)閉會(huì)話
SQL>alter database commit to switchover to physical standby with session shutdown;

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

A 如果switchover_status為TO_PRIMARY 說明標(biāo)記恢復(fù)可以直接轉(zhuǎn)換為primary庫
SQL>alter database commit to switchover to primary

B 如果switchover_status為SESSION ACTIVE 就應(yīng)該斷開活動(dòng)會(huì)話
SQL>alter database commit to switchover to primary with session shutdown;

C 如果switchover_status為NOT ALLOWED 說明切換標(biāo)記還沒收到，此時(shí)不能
執(zhí)行轉(zhuǎn)換。

切換中 ：

備用庫
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

切換后備用的狀態(tài)，模式：

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. 檢查環(huán)境：

確認(rèn)主庫和從庫間網(wǎng)絡(luò)連接通暢；

確認(rèn)沒有活動(dòng)的會(huì)話連接在數(shù)據(jù)庫中；

PRIMARY數(shù)據(jù)庫處于打開的狀態(tài)，STANDBY數(shù)據(jù)庫處于MOUNT狀態(tài)；

確保STANDBY數(shù)據(jù)庫處于ARCHIVELOG模式；

如果設(shè)置了REDO應(yīng)用的延遲，那么將這個(gè)設(shè)置去掉；

確保配置了主庫和從庫的初始化參數(shù)，使得切換完成后，DATA GUARD機(jī)制可以順利的運(yùn)行。

如果是最大保護(hù)模式，先變成最大性能模式：

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狀態(tài)下的結(jié)果。

 

DataGuard:Physical Standby Failover

 

接physical standby switchover

 

primary db :db2

standby db :db1

 

一般情況下failover都是表示primary數(shù)據(jù)庫癱瘓，最起碼也是起不來了，因此這種類型的切換基本上不需要primary數(shù)據(jù)庫做什么操作。所以下列步驟中如果有提到primary和standby執(zhí)行的，只是建議你如果primary還可以用，那就執(zhí)行一下,即使不去執(zhí)行，對(duì)failover來說也沒有關(guān)系

 

 

1.檢查歸檔文件是否連續(xù)，是否有gap

      在standby庫執(zhí)行

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

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

    在主庫上執(zhí)行語句，按步驟1查詢出來的紀(jì)錄找出歸檔文件

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

　　--如果primary存在，拷貝相應(yīng)的歸檔到STANDBY數(shù)據(jù)庫，并注冊(cè).

　　SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'xxx';

3.檢查歸檔文件是否完整

  分別在primary/standby執(zhí)行下列語句：

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

　　把相差的歸檔復(fù)制到待轉(zhuǎn)換的standby服務(wù)器,并手工register

 

4.開始做failover

  察看standby進(jìn)程狀態(tài)

  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關(guān)鍵字將會(huì)停止當(dāng)前活動(dòng)的RFS進(jìn)程，以便立刻執(zhí)行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切換成功