我的虛擬機器一直開著DG庫，一直想做完這個failover切換並把原來的主庫恢復為新備庫的測試，但由於事情太多，
擱了10天才把這個DG的failover切換做了一遍，往後還會有一兩篇文章是講DG切換的，但是後面的使用DGbroker做的切換。

failover切換：

---檢視是否有斷檔：

--主備庫都沒有：

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

#檢視主備庫是否有斷檔的情況，主要看備庫。

----主庫在mount狀態下清空未傳輸的日誌：

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 696257656 bytes

Database Buffers 130023424 bytes

Redo Buffers 2392064 bytes

Database mounted.

#也當做是主庫掛庫的狀態。

SQL> ALTER SYSTEM FLUSH REDO TO 'ORA11GR2';----ora11gr2指的是備庫

System altered.

#沒有任何報錯，則說明沒有斷檔的狀況。

----備庫停止應用主庫傳輸的日誌：

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

----此時檢視主備庫的狀態：

--主庫的狀態：

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

MOUNTED MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY

----備庫的狀態：

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ ONLY MAXIMUM PERFORMANCE TO PRIMARY PHYSICAL STANDBY

----備庫側作切換成主庫的操作：

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

----備庫（新主庫）例項此時的狀態：

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

ORA11GR2 MOUNTED

#此時是mount狀態。

----開啟新的主庫：

SQL> alter database open;

Database altered.

#新的主庫沒有發生報錯，正常開啟。

----此時檢視新主庫的角色與狀態：

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ WRITE MAXIMUM PERFORMANCE FAILED DESTINATION PRIMARY

#此時只是作為原來DG的主庫作用，不允許做切換。

----檢視原來主庫此時的狀態：

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

MOUNTED MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY

#原來的主庫依然顯示為主庫。

----新主庫檢視切換時的scn號：

SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)

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

3214388

----在原來的主庫重啟並開啟閃回：

SQL> SHUTDOWN IMMEDIATE;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 696257656 bytes

Database Buffers 130023424 bytes

Redo Buffers 2392064 bytes

Database mounted.

----原來的主庫嘗試閃回到SCN 3214388：

SQL> FLASHBACK DATABASE TO SCN 3214388;

FLASHBACK DATABASE TO SCN 3214388

*

ERROR at line 1:

ORA-38726: Flashback database logging is not on.

----檢視原來主庫的FLASHBACK_ON：

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

NO

---設定為開啟模式：

SQL> alter database FLASHBACK on;

Database altered.

---確認閃回是夠開啟成功：

SQL> select FLASHBACK_ON from v$database;

FLASHBACK_ON

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

YES

----閃回原來主庫切換前那一刻的狀態：

SQL> FLASHBACK DATABASE TO SCN 3214388;

Flashback complete.

#閃回成功。

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

MOUNTED MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY

----把原來主庫切換成物理備庫角色：

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Database altered.

#切換成功。

----此時檢視切換後的狀態與角色：

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

select open_mode,protection_mode,switchover_status,database_role from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

SBDB STARTED

SQL> shutdown immediate;

ORA-01507: database not mounted

----重啟到mount狀態：

ORACLE instance shut down.

SQL>

SQL> startup mount;

ORACLE instance started.

Total System Global Area 830930944 bytes

Fixed Size 2257800 bytes

Variable Size 696257656 bytes

Database Buffers 130023424 bytes

Redo Buffers 2392064 bytes

Database mounted.

SQL>

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

MOUNTED MAXIMUM PERFORMANCE SWITCHOVER LATENT PHYSICAL STANDBY

----檢視新主庫的（這部分可以先前設定好）：

SQL> col DESTINATION for a35

SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;

DEST_ID DEST_NAME STATUS PROTECTION_MODE DESTINATION ERROR SRL

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

1 LOG_ARCHIVE_DEST_1 VALID MAXIMUM PERFORMANCE /u01/app/arch/ORA11GR2/ NO

2 LOG_ARCHIVE_DEST_2 VALID MAXIMUM PERFORMANCE SBDB YES

3 LOG_ARCHIVE_DEST_3 INACTIVE MAXIMUM PERFORMANCE NO

4 LOG_ARCHIVE_DEST_4 INACTIVE MAXIMUM PERFORMANCE NO

---新備主庫設定歸檔傳輸目錄（這部分可以先前設定好）：

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_5=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_6=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_7=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_8=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_9=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_10=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_11=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_12=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_13=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_14=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_16=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_17=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_18=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_19=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_20=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_21=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_22=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_23=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_24=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_26=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_27=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_28=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_29=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_30=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_31=ENABLE;

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

----開啟新備庫：

SQL> alter database open;

Database altered.

----檢視新主備庫的角色與狀態：

--新主庫：

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ WRITE MAXIMUM PERFORMANCE TO STANDBY PRIMARY

--新備庫：

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ ONLY MAXIMUM PERFORMANCE SWITCHOVER LATENT PHYSICAL STANDBY

----新備庫引用主庫傳輸的日誌：

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

Database altered.

----檢視新主備庫的最大日誌號：

SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1

2 and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);

MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T

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

182 2017-03-18 23:53:23

#主備庫的最大日誌號一樣。

---切換完成。

----再次檢視主備庫的狀態與角色：

---主庫：

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ WRITE MAXIMUM PERFORMANCE SESSIONS ACTIVE PRIMARY

---備庫：

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

OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE

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

READ ONLY WITH APPLY MAXIMUM PERFORMANCE SWITCHOVER PENDING PHYSICAL STANDBY

--這種切換一般是應急時採用的，比如主庫主機當機了或者資料庫掛了，因為這種failover切換備庫為新主庫，
快速地把被庫投入生產當中，但多多少少會丟失部分資料（未傳輸被備庫應用的的資料）。

failover切換後恢復原來主庫為新備庫

相關文章