ORACLE 11G DATAGUARD保护模式设置详解

先说说DG的三种保护模式
Maximum Protection:
最大保护模式，不允许数据丢失，所有事务必须完成主备库的日志写后才能提交，如果主备库之间网络异常，将会严重影响主库性能。
Maximum Performance：
此为DG的默认模式，此模式保证主库以最大性能运行，允许事务在完成本地日志写成功后主库立即提交，而不需等待redo数据写到备库，事务产生的redo 数据异步的传输到备库，此模式在主库发生故障时，备库可能有少量数据丢失，对主库性能影响最小。
Maximum Availability：
在正常情况下，此保护模式同Maximum Protection,需要恢复此事务的所有redo 数据都同步写到主库online redo file 和备库的standby redo log中，事务才能成功提交，当主库不能将redo数据同步写到主库和备库，此保护模式自动变成同Maximum Performance模式运行，不会导致主库不可用，而当异常处理完成，其又恢复到同Maximum Protection模式
大家可以根据实际情况和需求采用不同的模式，各种保护模式的设置非常简单，但是有些地方还是需要了解清楚。 
各保护模式与log_archive_dest_n 参数日志传输属性对应表
 
 
Maximum Availability
Maximum Performance
Maximum Protection
AFFIRM
NOAFFIRM
AFFIRM
SYNC
ASYNC
SYNC
DB_UNIQUE_NAME
DB_UNIQUE_NAME
DB_UNIQUE_NAME
如何确认DG处于何种保护模式
SELECT PROTECTION_MODE FROM V$DATABASE;
设置Maximum Avalilability 模式：
SQL> alter database set standby database to maximize availability;

Database altered.

SQL> SELECT NAME,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE ,DB_UNIQUE_NAME FROM v$database;

NAME      PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    DB_UNIQUE_NAME
--------- -------------------- -------------------- ---------------- ------------------------------
OGG      MAXIMUM AVAILABILITY RESYNCHRONIZATION    PRIMARY         ogg

设置Maximum Performance 模式：
SQL> alter database set standby database to maximize PERFORMANCE;

Database altered.

SQL> SELECT NAME,PROTECTION_MODE,PROTECTION_LEVEL,DATABASE_ROLE ,DB_UNIQUE_NAME FROM v$database;

NAME      PROTECTION_MODE      PROTECTION_LEVEL     DATABASE_ROLE    DB_UNIQUE_NAME
--------- -------------------- -------------------- ---------------- ------------------------------
OGG      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  PRIMARY         ogg

以上两种模式在设置没有特别条件限制，可以自由从一种模式转换过来，也不需关注log_archive_dest_n的参数设置。
Maximum Protection 有限制，下面进行测试：
由Maximum Avalilability模式设置为Maximum Protection
条件Maximum Avalilability + log_archive_dest_n ASYNC
SQL> alter database set standby database to maximize availability;

Database altered.

SQL> show parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2      string     SERVICE=tgg LGWR ASYNC VALID_F
                                   OR=(ONLINE_LOGFILES,PRIMARY_RO
                                   LE) DB_UNIQUE_NAME=tgg

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

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
Process ID: 4876
Session ID: 1 Serial number: 5

此时主库实例直接DOWN掉，查看alert log 数据库已经改成Maximum Protection模式，但是因为LAG_ARCHIVE_DEST_2 参数中使用的是异步传输 ASYNC，这个在最大保护模式下是不允许的，所以ORACLE 为了保护数据不丢失，将实例直接关闭。

tail -30f aler*.log
alter database set standby database to maximize availability
Completed: alter database set standby database to maximize availability
Tue Oct 29 13:11:52 2013
alter database set standby database to maximize PROTECTION
Completed: alter database set standby database to maximize PROTECTION
alter database open
Tue Oct 29 13:11:57 2013
LGWR: STARTING ARCH PROCESSES
Tue Oct 29 13:11:57 2013
ARC0 started with pid=18, OS id=4918 
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
LGWR: Primary database is in MAXIMUM PROTECTION mode
LGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/O
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
LGWR: Minimum of 1 synchronous standby database required
Errors in file /u01/oracle/diag/rdbms/ogg/ogg/trace/ogg_lgwr_4860.trc:
ORA-16072: a minimum of one standby database destination is required
LGWR (ospid: 4860): terminating the instance due to error 16072
Tue Oct 29 13:11:58 2013
System state dump requested by (instance=1, osid=4860 (LGWR)), summary=[abnormal instance termination].
System State dumped to trace file /u01/oracle/diag/rdbms/ogg/ogg/trace/ogg_diag_4850_20131029131158.trc
Dumping diagnostic data in directory=[cdmp_20131029131158], requested by (instance=1, osid=4860 (LGWR)), summary=[abnormal instance termination].
Instance terminated by LGWR, pid = 4860

现在将实例mount起来，确认模式设置是否成功
SQL> startup mount;
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size            1365740 bytes
Variable Size          314575124 bytes
Database Buffers      201326592 bytes
Redo Buffers            5840896 bytes
Database mounted.
SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM PROTECTION
此时是不能open database ，因为log_archive_dest_2 的参数还没更改过来，下面更改log_archive_dest_2 为SYNC
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=tgg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tgg';

System altered.

SQL> alter database open;

Database altered.

数据库成功open.
由Maximum Performance模式设置为Maximum Protection
条件Maximum Performance + log_archive_dest_n SYNC ，
log_archive_dest_n 设置为ASYNC 在任何时候都不能启用Maximum Protection模式，这个就不再在这里测试
SQL> alter database set standby database to maximize PERFORMANCE;

Database altered.

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL> show parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2       string     SERVICE=tgg LGWR SYNC VALID_FO
                                    R=(ONLINE_LOGFILES,PRIMARY_ROL
                                    E) DB_UNIQUE_NAME=tgg

SQL> alter database set standby database to maximize PROTECTION;
alter database set standby database to maximize PROTECTION
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

从Maximum Performance 模式不能直接设置为Maximum Protection模式，ORACLE提示需要在mount模式下进行

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size            1365740 bytes
Variable Size          314575124 bytes
Database Buffers      201326592 bytes
Redo Buffers            5840896 bytes
Database mounted.
SQL> alter database set standby database to maximize PROTECTION;

Database altered.

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM PROTECTION

SQL> alter database open;

Database altered.

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM PROTECTION

在mount 状态从Maximum Performance 设置为Maximum Protection 成功

另外一种特殊情况，当主库在Maximum Availability模式的情况，ORACLE说在异常情况下，Maximum Availability模式
会自动变为Maximum Performance 模式，那么是否在这个情况下，能直接设置为Maximum Protection模式呢
在备库将网卡DOWN掉，模拟主备之间网络故障
[root@rac2 ~]# ifconfig eth1
eth1      Link encap:Ethernet  HWaddr 08:00:27:09:6F:27  
          inet addr:192.168.2.201  Bcast:192.168.2.255  Mask:255.255.255.0
          inet6 addr: fe80::a00:27ff:fe09:6f27/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:11747 errors:0 dropped:0 overruns:0 frame:0
          TX packets:9657 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:11586163 (11.0 MiB)  TX bytes:991538 (968.2 KiB)
          Base address:0xd240 Memory:f0820000-f0840000
[root@rac2 ~]# ifdown eth1
[root@rac2 ~]# ifconfig eth1
eth1      Link encap:Ethernet  HWaddr 08:00:27:09:6F:27  
          BROADCAST MULTICAST  MTU:1500  Metric:1
          RX packets:11547 errors:0 dropped:0 overruns:0 frame:0
          TX packets:9626 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000 
          RX bytes:11573787 (11.0 MiB)  TX bytes:984117 (961.0 KiB)
          Base address:0xd240 Memory:f0820000-f0840000

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY
SQL> insert into xhl select * from dba_users where rownum=1;

1 row created.

SQL> commit;

Commit complete.

查看V$DATAGUARD_STATUS,可以看到此时主库连接备库错误
SQL> select * from (SELECT message FROM V$DATAGUARD_STATUS order by MESSAGE_NUM desc ) where rownum =1;

MESSAGE
------------------------------------------------------------------------------------------------------
PING[ARC3]: Heartbeat failed to connect to standby 'tgg'. Error is 12543.

这个时候Maximum Availability 模式应该降为Maximum Performance 模式

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

从数据库中查询结果显示没有改变，但是在LGWR TRACE file 里面发现这样一段日志，模式已经发生转变

*** 2013-10-29 14:58:04.161 2689 krsl.c
No standby database destinations have been configured
as being archived by the LGWR process
This instance will operate at a reduced protection mode until
network connectivity to the standby databases is restored and
all archivelog gaps have been resolved.

既然保护模式已经降级 ，那么根据上面的测试，此时如果要在数据库OPEN状态下更改为Maximum Protection模式
是不可能的，现在验证一把

SQL> SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM AVAILABILITY

SQL> alter database set standby database to maximize PROTECTION;
alter database set standby database to maximize PROTECTION
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance

可见ORACLE 的Maximum Availability 模式的确在异常时刻保护模式降级了。

总结：
MAXIMUM AVAILABILITY MAXIMUM PERFORMANCE 两种模式可以从任意一种模式状态下转换过来，并且与LOG_ARCHIVE_DEST_N设置无关。
MAXIMUM PROTECTION 模式有下面条件限制：

1. LOG_ARCHIVE_DEST_N 日志传输参数必须有一个设置为SYNC
2. 在数据库OPEN状态下，只能从MAXIMUM AVAILABILITY 模式直接转换
3. 从MAXIMUM PERFORMANCE 模式转换，需先转换到MAXIMUM AVAILABILITY模式，否则数据库必须启动到MOUNT状态
4. 当MAXIMUM AVALIABILITY 模式降级为MAXIMUM PERFORMANCE ，转换需要参照第3点，或者等异常处理完成恢复。