DG學習筆記(7)_保護模式

DG學習筆記(7)_保護模式

目錄

1 Set the Protection Mode

2 MAXimum Protection

3 Maximum Availability

4 Maximum Performance

 

1 Set the Protection Mode

 

Each level of protection requires certain attributes of the LOG_ARCHIVE_DEST_n be set properly. For each level of protection you must have at least one standby database with the following:

• Maximum Protection: LGWR, SYNC, AFFIRM, and standby redo logs files

 

• Maximum Availability: LGWR, SYNC, AFFIRM, and standby redo logs files for physical standby databases

 

• Maximum Performance, any combination of: LGWR or ARCH

 

alter system set log_archive_dest_2='service=standby lgwr SYNC NOAFFIRM';

 

Using the following SQL SET STANDBY DATABASE statement on the primary database, you can configure the Data Guard environment to maximize data protection, availability, or performance:

 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE};

 

2 MAXimum Protection

 

優勢:

Guarantees zero data loss

Highest level of data protection

Must use SYNC transport method

Must use standby redo logs

If no physical standby available, primary database is aborted

 

A transaction does not commit until all data needed to recover it has been written to at least one physical standby database that is configured to use the SYNC log transport mode.(提交之前必須將資料寫到了備端,這是不是有問題, 不提交,資料不能保證被寫到了Online redo log,不會有問題, 意思即是在Primary提交完成之前,Standby redo log 也必須已記錄日誌變化.)

 

 

最大保護: 按理解,應是寫online日誌的過程中也同步變化到備端,不用透過切換日誌來觸發.

即在Primary DML資料,Commit. Standby也會更改資料. 不用透過切換日誌來觸發. 是這樣嗎? 不是的, 如不透過切換來觸發不會應用日誌,

那這樣的話有什麼意義呢, 還是說,對同步的理解有誤,只是說將變化傳到了備端,但不應用. 這才是正確的,只是保證你資料傳過去了,不會丟失資料而已.

注意只是保證資料不會丟失，而不是保證資料已應用到Standby. 實際的情況是會保證資料傳輸到Standby，但不一定應用．

 

You must define at least one standby site destination with these attributes of the LOG_ARCHIVE_DEST_n parameter: MANDATORY, LGWR, SYNC, and AFFIRM. If the primary database is unable to write the redo records to at least one such physical standby database, the primary database will be shut down.

(這四個屬性中LGWR, SYNC是必須的)

 

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  320308744 bytes

Fixed Size                   742920 bytes

Variable Size             285212672 bytes

Database Buffers           33554432 bytes

Redo Buffers                 798720 bytes

Database mounted.

 

嘗試用NOAFFIRM

SQL> alter system set log_archive_dest_2='service=standby lgwr SYNC NOAFFIRM';

 

System altered.

 

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

成功

 

 

嘗試用ASYNC

SQL> alter system set log_archive_dest_2='service=standby lgwr ASYNC NOAFFIRM';

 

 

System altered.

 

SQL> SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

 

Database altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

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

 

失敗

 

在實際測試中:

如果Shutdown abort Standby,則Primary也隨之被Shutdown.

 

在嘗試正常關閉Shutdown immediate Standby Database時, 發現關閉不了，在Standby alert_xxx.log中,發現

alter database recover managed standby database cancel

Mon Mar  8 17:16:30 2010

MRP0: Background Media Recovery user canceled with status 16037

Recovery interrupted.

MRP0: Background Media Recovery process shutdown

Mon Mar  8 17:16:31 2010

Managed Standby Recovery Cancelled

Completed:      alter database recover managed standby database c

Attempt to shut down Standby Database

Standby Database operating in NO DATA LOSS mode

Detected primary database alive, shutdown primary first, shutdown aborted

 

表明在關閉Primary之前,不能正常關閉Standby.

 

在關閉Primary,Standby後,嘗試啟動Primary,檢視Primary Alert_xxx.log

 

Alert_xxx.log

LGWR: Primary database is in CLUSTER CONSISTENT mode

LGWR: Primary database is in MAXIMUM PROTECTION mode

LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR

LNS0 started with pid=15

Mon Mar  8 22:10:39 2010

LGWR: Error 12505 verifying archivelog destination LOG_ARCHIVE_DEST_2

LGWR: Continuing...

Mon Mar  8 22:10:39 2010

Errors in file /opt/oracle/admin/mydb/bdump/mydb_lgwr_13090.trc:

ORA-12505: TNS:listener could not resolve SID given in connect descriptor

LGWR: Minimum of 1 applicable standby database required

Mon Mar  8 22:10:39 2010

Errors in file /opt/oracle/admin/mydb/bdump/mydb_lgwr_13090.trc:

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

LGWR: terminating instance due to error 16072

Instance terminated by LGWR, pid = 13090

 

表明在最大保護模式,至少必須有一個Standby是啟動的,才能啟動Primary.

 

Note: Oracle Corporation recommends that you use multiple physical standby databases when your business requires maximum data protection. With multiple physical standby databases, if one becomes unavailable, the primary database can continue operations as long as at least one physical standby database is participating in the configuration.

 

所以Oracle建議如果是配置MAXIMUN Protection Mode, 則Standby database 最好有兩個以上.

 

 

3 Maximum Availability

 

優勢:

Zero data loss

Next highest level of data protection

Must use SYNC transport method

Must use standby redo logs for physical standby database

If no standby available, primary database is not shut down

 

Unlike Maximum Protection mode, the primary database will not be shut down if it is unable to write the redo records to at least one such standby database. Instead, the primary database will run in Maximum Performance mode until the fault has been corrected and the standby database has caught up with the primary database. Then it will return to Maximum Availability.

(不像最大保護模式,在最大可用性模式中，備庫的崩潰不會導致主庫也隨之崩潰,只會自動切換到最大效能模式)

 

切換到Maximum Availability

Primay:

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  320308744 bytes

Fixed Size                   742920 bytes

Variable Size             285212672 bytes

Database Buffers           33554432 bytes

Redo Buffers                 798720 bytes

Database mounted.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE availability;

 

Database altered.

 

2.1 Shutdown Abort Standby database 觀察Primary的情況

 

Standby: SQL> shutdown abort;

ORACLE instance shut down.

 

主庫要多久才能檢測到呢?

 

Primary:  SQL> select protection_mode from v$database;

 

PROTECTION_MODE

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

MAXIMUM AVAILABILITY

 

仍是最大可用.

因為你設定的就是最大可用性模式，雖然正常情況下其表現出來的與最大保護模式一致，只有在備庫崩潰時才會切換到最大效能模式的工作方式．

 

過一段時間在alert_xxx.log中發現,檢測到了對端的錯誤

 

Network asynch I/O wait error 3114 log 2 service 'standby'

Mon Mar  8 22:35:55 2010

Errors in file /opt/oracle/admin/mydb/bdump/mydb_lgwr_13820.trc:

ORA-03114: not connected to ORACLE

 

Primary:  SQL> select protection_mode from v$database;

 

PROTECTION_MODE

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

MAXIMUM AVAILABILITY

 

仍是最大可用,

 

2.2 恢復Standby database 到正常狀態

 

 

4 Maximum Performance

 

優勢:

Default level of data protection

Can use SYNC, ASYNC, or ARCH transport method

Can use standby redo logs

No write required to any physical or logical standby database before commit

If no standby available, primary database is not shutdown

Data loss is possible

 

A transaction commits before the data needed to recover it has been written to a (physical or logical) standby database. (事務提交後再傳輸到Standby)