ORACLE 11G DATAGUARD保护模式设置详解
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 alter
查看更多关于ORACLE 11G DATAGUARD保护模式设置详解的详细内容...