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

skyin_1603發表於2017-03-20
AI

我的虛擬機器一直開著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_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切換備庫為新主庫，
快速地把被庫投入生產當中，但多多少少會丟失部分資料（未傳輸被備庫應用的的資料）。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31392094/viewspace-2135698/，如需轉載，請註明出處，否則將追究法律責任。

相關文章