Oracle的DataGuard技術有三種實現模式，分別是max performance、max availability、max protection這三種模式。

以下是來自Oracle文件的摘要資訊：
In some situations, a business cannot afford to lose data. In other situations, the availability of the database may be more important than the loss of data. Some applications require maximum database performance and can tolerate some small amount of data loss. The following descriptions summarize the three distinct modes of data protection.

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits. To ensure data loss cannot occur, the primary database shuts down if a fault prevents it from writing its redo stream to the standby redo log of at least one transactionally consistent standby database.

This protection mode provides the highest level of data protection that is possible without compromising the availability of the primary database. Like maximum protection mode, a transaction will not commit until the redo needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one transactionally consistent standby database. Unlike maximum protection mode, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. Instead, the primary database operates in maximum performance mode until the fault is corrected, and all gaps in redo log files are resolved. When all gaps are resolved, the primary database automatically resumes operating in maximum availability mode.

This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum performance This protection mode (the default) provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by allowing a transaction to commit as soon as the redo data needed to recover that transaction is written to the local online redo log. The primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the transactions that create the redo data.

When network links with sufficient bandwidth are used, this mode provides a level of data protection that approaches that of maximum availability mode with minimal impact on primary database performance.

The maximum protection and maximum availability modes require that standby redo log files are configured on at least one standby database in the configuration. All three protection modes require that specific log transport attributes be specified on the LOG_ARCHIVE_DEST_n initialization parameter to send redo data to at least one standby database. See Section 5.6 for complete information about the data protection modes.

以下是對以上摘要資訊的翻譯資訊：

在一些情況下，業務不允許丟失資料。在另外一些情況下，資料庫的可用性比丟失資料更為重要。一些應用需要最強的資料庫效能並且能容忍丟失少量的資料。下面的描述概述了三種不同的資料保護模式。

最大保護模式 -- 這種保護模式確保如果主資料庫故障不會發生資料丟失。要提供這種級別的保護，恢復每個事務所需的重做資料必須在事務提交之前同時寫到本地聯機重做日誌和至少一個備資料庫上的備重做日誌。要確保不發生資料丟失，如果故障導致主資料庫無法寫重做流到至少一個事務一致性備資料庫的備重做日誌時，主資料庫會關閉。

最大可用性模式 -- 這種保護模式提供了可能的最高階別的資料保護，而不用與主資料庫的可用性相折衷。與最大保護模式相同，在恢復事務所需的重做寫到本地聯機重做日誌和至少一個事務一致性備資料庫上的備重做日誌之前，事務將不會提交。與最大保護模式不同的是，如果故障導致主資料庫無法寫重做流到異地備重做日誌時，主資料庫不會關閉。替代地，主資料庫以最大效能模式執行直到故障消除，並且解決所有重做日誌檔案中的中斷。當所有中斷解決之後，主資料庫自動繼續以最大可用性模式執行。

這種模式確保如果主資料庫故障，但是隻有當第二次故障沒有阻止完整的重做資料集從主資料庫傳送到至少一個備資料庫時，不發生資料丟失。

最大效能模式 -- 這種保護模式（預設）提供了可能的最高階別的資料保護，而不影響主資料庫的效能。這是透過允許事務在恢復該事務所需重做資料在寫到本地聯機重做日誌後立即提交而實現的。主資料庫的重做資料流也寫到至少一個備資料庫，但是那個重做流相對於建立重做資料的事務是非同步寫的。

當所用的網路連線有足夠的頻寬，這種模式提供了近似於最大可用性模式的資料保護級別，並且對主資料庫效能的影響最小。
最大保護和最大可用性模式需要備重做日誌檔案配置在配置中的至少一個備資料庫上。所有三種保護模式需要在LOG_ARCHIVE_DEST_n 初始化引數上指定特定的日誌傳輸屬性以傳送重做資料到至少一個備資料庫。檢視5.6 節以獲得資料保護模式的完整資訊。

實驗過程:

DG在最大效能模式MAXIMUM PERFORMANCE

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

SQL> create table emp001 as select * from emp; 建立了一張表emp001

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

SALGRADE TABLE

去備機上檢視，這張表並沒有出現：

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

SALGRADE TABLE

這個時候，我需要在主庫上執行一次日誌切換：

SQL> alter system switch logfile;

System altered.

再備庫上再次查詢，表已經出來：

SQL> /

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE ----在主庫新建立的表

SALGRADE TABLE

DG在最大可用性模式 Maximum availability

Primary 端操作

修改初始化引數

SQL> alter system set log_archive_dest_2 ='SERVICE=sty OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty';

System altered.

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=sty OPTIONAL LGWR SYNC

AFFIRM VALID_FOR=(ONLINE_LOGF

ILES,PRIMARY_ROLE) DB_UNIQUE_N

AME=sty

SQL> startup mount;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 549455864 bytes

Database Buffers 285212672 bytes

Redo Buffers 2396160 bytes

Database mounted.

SQL> alter database set standby database to maximize availability;

Database altered.

提示：maximize後可跟{PROTECTION | AVAILABILITY | PERFORMANCE}，分別對應最大保護，最高可用性及最高效能。
Down掉資料庫，重新啟動

SQL> alter database open;

Database altered.

看一下當前的保護模式 --primary資料庫操作

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL>

Standby端操作

SQL> alter system set log_archive_dest_2='SERVICE=pri OPTIONAL LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri';

System altered.

SQL> show parameter log_archive_dest_2

NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=pri OPTIONAL LGWR SYNC

AFFIRM VALID_FOR=(ONLINE_LOGF

ILES,PRIMARY_ROLE) DB_UNIQUE_N

AME=pri

SQL> select protection_mode,protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL>

這樣就已經配置完成了。

我們現在停掉standby資料庫，再看看primary資料庫狀態

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> select protection_mode,protection_level,name from v$database;

PROTECTION_MODE PROTECTION_LEVEL NAME

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

MAXIMUM AVAILABILITY RESYNCHRONIZATION WOO

Standby資料庫shutdown後，primary資料庫保護級別切換為待同步。

啟備機器後，一會兒就好了：

SQL> startup

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 507512824 bytes

Database Buffers 327155712 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.

SQL> select protection_mode,protection_level,name from v$database;

PROTECTION_MODE PROTECTION_LEVEL NAME

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

MAXIMUM AVAILABILITY RESYNCHRONIZATION WOO

SQL> select protection_mode,protection_level,name from v$database;

PROTECTION_MODE PROTECTION_LEVEL NAME

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY WOO

SQL>

再檢視下主庫狀態：

SQL> select protection_mode,protection_level,name from v$database;

PROTECTION_MODE PROTECTION_LEVEL NAME

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

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY WOO

測試:

在primary 執行建立表

檢視當前表的數量

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP003 TABLE

SALGRADE TABLE

6 rows selected.

刪除emp003表

SQL> drop table emp003 purge;

Table dropped.

建立emp004表

SQL> create table emp004 as select * from emp001;

Table created.

檢視emp003表已經被刪除，emp004表被建立成功。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP004 TABLE

SALGRADE TABLE

6 rows selected.

備庫上資料直接就可以過來了：

查詢到資料和第一次pry資料庫一致：

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP003 TABLE

SALGRADE TABLE

6 rows selected.

在pri端刪除emp003後，可以看到在sty端也隨即刪除。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

SALGRADE TABLE

在pri端建立emp004後，在sty端也隨即實時建立。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP004 TABLE

SALGRADE TABLE

6 rows selected.

SQL>

實驗二，在maximum availability 模式下關閉standby端，在主庫執行DML操作後起備庫，是否會有資料丟失:

1、關閉standby端:

[root@sty ~]# shutdown -h now

Broadcast message from root (pts/1) (Tue May 21 02:09:34 2013):

The system is going down for system halt NOW!

[root@sty ~]#

2、檢視主庫這個時候的狀態，已經檢測到了standby失敗

SQL> select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;

NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS

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

WOO READ WRITE PRIMARY MAXIMUM AVAILABILITY FAILED DESTINATION

SQL>

3、這個時候我們刪除emp004及建立emp005

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP004 TABLE

SALGRADE TABLE

6 rows selected.

SQL> drop table emp004 purge;

Table dropped.

SQL> create table emp005 as select * from emp001;

Table created.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP005 TABLE

SALGRADE TABLE

6 rows selected.

4、把sty伺服器啟起來，open資料庫：

開啟備庫後，我們可以看到，standby現在正在應用日誌：

SQL> select process, status from v$managed_standby;

PROCESS STATUS

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

ARCH CLOSING

ARCH CONNECTED

ARCH CLOSING

RFS IDLE

MRP0 APPLYING_LOG

過一會兒，我們再查詢，發現standby 端當機過程中primary端資料庫的改變也同樣透過日誌更新過來了。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP005 TABLE

SALGRADE TABLE

6 rows selected.

我們透過這個實驗可以看出，將DataGuard配置為Maximum availability模式後，pri資料是實時同步到sty端。

DG最大保護模式

1、將主庫修改為最大保護模式

先關閉主庫

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

啟動到mount狀態

SQL> startup mount;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 557844472 bytes

Database Buffers 276824064 bytes

Redo Buffers 2396160 bytes

Database mounted.

修改為最大保護模式

SQL> alter database set standby database to maximize protection;

Database altered.

Open資料庫

SQL> alter database open;

Database altered.

Primary端查詢狀態：

SQL> select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;

NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS

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

WOO READ WRITE PRIMARY MAXIMUM PROTECTION TO STANDBY

Standby端查詢狀態（切換隻需要操作主庫，備庫不需要動即可）

SQL> select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;

NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS

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

WOO READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PROTECTION NOT ALLOWED

我們現在可以看到，primary和standby端現在都為Maximum protection 最大保護模式。

實驗

先檢視當前的表

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP005 TABLE

SALGRADE TABLE

6 rows selected.

我們刪除emp005以及建立emp006表

SQL> drop table emp005 purge;

Table dropped.

SQL> create table emp006 as select * from emp001;

Table created.

我們可以看到emp005已經被刪除，並且emp006表也被建立好了。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP006 TABLE

SALGRADE TABLE

6 rows selected.

SQL>

現在看standby端，我們可以發現如下

兩邊的表是一樣的

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP005 TABLE

SALGRADE TABLE

6 rows selected.

在主庫端刪除的emp005及新建立的emp006也實時同步過來了

SQL> select * from tab;

TNAME TABTYPE CLUSTERID

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

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP001 TABLE

EMP006 TABLE

SALGRADE TABLE

6 rows selected.

SQL>

我現在關閉sty端伺服器：

[root@sty ~]# shutdown -h now

Broadcast message from root (pts/1) (Tue May 21 03:14:28 2013):

The system is going down for system halt NOW!

返回來看主庫的狀態:

日誌傳輸以及終止了:

Log Transport Services Error ORA-16198: LGWR received timedout error from KSR

Log Transport Services Error Error 12543 received logging on to the standby

Log Transport Services Error LGWR: Error 12543 attaching to RFS for reconnect

Log Transport Services Error Error 12543 received logging on to the standby

Log Transport Services Error LGWR: Error 12543 attaching to RFS for reconnect

一段時間之後，primary端也當機了：

SQL> select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;

NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE SWITCHOVER_STATUS

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

WOO READ WRITE PRIMARY MAXIMUM PROTECTION TO STANDBY

SQL> select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database;

select name,open_mode,database_role,protection_mode,SWITCHOVER_STATUS From v$database

*

ERROR at line 1:

ORA-03135: connection lost contact

Process ID: 8386

Session ID: 39 Serial number: 5

這個時候需要把standby端起來，primary端，資料庫才能起來。

SQL> startup mount;

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 507512824 bytes

Database Buffers 327155712 bytes

Redo Buffers 2396160 bytes

Database mounted.

停止redo應用

SQL>

Database altered.

Open資料庫

SQL> alter database open;

Database altered.

開啟redo應用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.再啟主庫

SQL> startup

ORACLE instance started.

Total System Global Area 839282688 bytes

Fixed Size 2217992 bytes

Variable Size 507512824 bytes

Database Buffers 327155712 bytes

Redo Buffers 2396160 bytes

Database mounted.

Database opened.

最後我們一塊來看一個表格，一目瞭然的清楚DG三種模式的區別：

	最大保護模式	最大可用	最大效能
程式	LGWR	LGWR	LGWR或ARCH
網路傳輸模式	SYNC	SYNC	LGWR時設定SYNC
磁碟寫操作	AFFIRM	AFFIRM	NOAFFIRM
備用日誌	Yes	Phycal 備庫需要	LGWR和物理需要
備用庫型別	Phycal mode	Phycal and logical	Phycal and logical

探索Oracle11gR2 之 DataGuard_03 三種保護模式

相關文章