DataGuard:Switch Protection Mode

DataGuard:Switch Protection Mode

 

演示從maximize performance 轉換到maximize protection 模式

 

 

1. 增加standby 的standby logfile,並檢查primary的 log_archive_dest_x 設定

   standby 庫增加standby logfile

        alter database recover managed standby database cancel;

         alter database add standby logfile group 4 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\STANDBY_REDO04.LOG') SIZE 50M

         alter database add standby logfile group 5 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\STANDBY_REDO05.LOG') SIZE 50M

         alter database add standby logfile group 6 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\STANDBY_REDO06.LOG') SIZE 50M

         alter database add standby logfile group 7 ('C:\ORACLE\PRODUCT\10.2.0\ORADATA\DB2\STANDBY_REDO07.LOG') SIZE 50M

         alter database recover managed standby database  disconnect from session;

 

2.primary 庫操作

 

          shutdown immediate;

          startup mount;

          alter database set standby to maximize protection;

          alter database open;

 

 

3.驗證及測試

 

primary 庫執行

   SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM PROTECTION   MAXIMUM PROTECTION

 

確認主庫為MAXIMUM PROTECTION 模式

繼續測試

SQL>  alter table test.a modify col1 varchar2(4000);

Table altered.

SQL>  insert into test.a values('Switch Protection Mode');

1 row created.

SQL> COMMIT;

注意,此時standby 並沒有實時反映出來，因為我們沒有啟用實時應用,他要等到primary做logfile switch ,觸發archived log傳輸到standby，standby的rfs程式把傳過來的歸檔檔案取下來，standby機器上的arch再把檔案歸檔到standby log的歸檔目的地,最後,mrp程式再基於該archived log檔案的基礎上做revover。如下

SQL>  alter database recover managed standby database cancel;

 

Database altered.

 

SQL> alter database open read only;

 

Database altered.

 

SQL>  select * from test.a;

 

COL1

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

My Data Guard!!!

看到primary insert的紀錄並沒有過來

在primary中觸發一次logfile switch

SQL> alter system switch logfile;

在standby 庫執行

SQL>  alter database recover managed standby database cancel;

Database altered.

SQL>  alter database open read only;

Database altered.

SQL>  select * from test.a;

COL1

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

My Data Guard!!!

Switch Protection Mode

 

可以看到應用在standby上體現出來了

 

(如果standby 用realtime apply redo方式，則primary commit後立刻可以在standby中應用，不需要做logfile switch，語句為

  alter database recover managed standby database using current logfile disconnect from session;)

 

附:

Switch protection mode 過程中可能遇到的故障

standby 發生故障後的ORA-01154錯誤：

在maximize protection 保護模式下，直接shutdown immediate會抱錯, ORA-01154: database busy. Open, close, mount, and dismount not allowed now,報這個錯誤很正常，因為我們處在maximize protection模式，並且只有一個standby 資料庫環境下，這個standby掛了，就無法實現maximize protection 保護了。

模擬ORA-01154錯誤

在standby庫 做shutdown abort操作

SQL> shutdown abort

模擬故障,

接著看看primary庫

SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM PROTECTION   RESYNCHRONIZATION

 

處於需要 RESYNCHRONIZATION的狀態，並且後臺的alert 日誌也會出現如下錯誤

LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)

LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'db2'

LNSb started with pid=21, OS id=3284

Error 12154 received logging on to the standby

Wed Jan 07 16:53:39 2009

LGWR: Error 12154 attaching to RFS for reconnect

LNSb started with pid=21, OS id=4788

Error 12154 received logging on to the standby

 

經過數分鐘後,primary庫因無法繼續執行在maximize protection而自動關閉.

 

可能出現的錯誤ORA-16072，ORA-03113

 

ORA-16072: a minimum of one standby database destination is required

error 16072 detected in background process

ORA-16072: a minimum of one standby database destination is required

 

shutdown immediate後重新startup，錯誤如下

 

 Database mounted.

ORA-03113: end-of-file on communication channel

 

此時察看後臺alert 日誌及跟蹤檔案，發現錯誤

 

Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O

Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR

Standby database verification failed:16072

ORA-16072: a minimum of one standby database destination is required

error 16072 detected in background process

ORA-16072: a minimum of one standby database destination is required

 

處理方法:

 startuo mount;

 alter sysetem set log_archive_dest_2='service=db2 lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=db2'

 alter database open;

  SQL> select protection_mode,protection_level from v$database;

 

PROTECTION_MODE      PROTECTION_LEVEL

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

MAXIMUM PROTECTION   MAXIMUM PROTECTION

修改log_archive_dest_2引數為lgwr sync affirm，其原因是因為處於maximize protection及maximize avaliabition 保護模式的時候，redo的傳送是通過lgwr程式,並以synchronous方式寫入standby,且需要確認(affirm ) 至少有一臺standby資料庫接收到redo資料,而預設情況的maximize performance 採用arch,synchronous方式傳送redo,且不需要收到確認(affirm ) 資訊即可完成primary的事務。

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