先说说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 ASYNCSQL> alter database set standby database to maximize availability;Database altered.SQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string SERVICE=tgg LGWR ASYNC VALID_F OR=(ONLINE_LOGFILES,PRIMARY_RO LE) DB_UNIQUE_NAME=tggSQL> SELECT PROTECTION_MODE FROM V$DATABASE;PROTECTION_MODE--------------------MAXIMUM AVAILABILITYSQL> 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 channelProcess ID: 4876Session ID: 1 Serial number: 5此时主库实例直接DOWN掉，查看alert log 数据库已经改成Maximum Protection模式，但是因为LAG_ARCHIVE_DEST_2 参数中使用的是异步传输 ASYNC，这个在最大保护模式下是不允许的，所以ORACLE 为了保护数据不丢失，将实例直接关闭。tail -30f aler*.logalter database set standby database to maximize availabilityCompleted: alter database set standby database to maximize availabilityTue Oct 29 13:11:52 2013alter database set standby database to maximize PROTECTIONCompleted: alter database set standby database to maximize PROTECTIONalter database openTue Oct 29 13:11:57 2013LGWR: STARTING ARCH PROCESSESTue Oct 29 13:11:57 2013ARC0 started with pid=18, OS id=4918 ARC0: Archival startedLGWR: STARTING ARCH PROCESSES COMPLETELGWR: Primary database is in MAXIMUM PROTECTION modeLGWR: Destination LOG_ARCHIVE_DEST_2 is using asynchronous network I/OLGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWRLGWR: Minimum of 1 synchronous standby database requiredErrors in file /u01/oracle/diag/rdbms/ogg/ogg/trace/ogg_lgwr_4860.trc:ORA-16072: a minimum of one standby database destination is requiredLGWR (ospid: 4860): terminating the instance due to error 16072Tue Oct 29 13:11:58 2013System 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.trcDumping 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 bytesFixed Size 1365740 bytesVariable Size 314575124 bytesDatabase Buffers 201326592 bytesRedo Buffers 5840896 bytesDatabase mounted.SQL> SELECT PROTECTION_MODE FROM V$DATABASE;PROTECTION_MODE--------------------MAXIMUM PROTECTION此时是不能open database ，因为log_archive_dest_2 的参数还没更改过来，下面更改log_archive_dest_2 为SYNCSQL> 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 PERFORMANCESQL> show parameter log_archive_dest_2NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string SERVICE=tgg LGWR SYNC VALID_FO R=(ONLINE_LOGFILES,PRIMARY_ROL E) DB_UNIQUE_NAME=tggSQL> 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 anyinstanceSQL> 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 bytesFixed Size 1365740 bytesVariable Size 314575124 bytesDatabase Buffers 201326592 bytesRedo Buffers 5840896 bytesDatabase mounted.SQL> alter database set standby database to maximize PROTECTION;Database altered.SQL> SELECT PROTECTION_MODE FROM V$DATABASE;PROTECTION_MODE--------------------MAXIMUM PROTECTIONSQL> 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 eth1eth1 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 eth1eth1 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-f0840000SQL> SELECT PROTECTION_MODE FROM V$DATABASE;PROTECTION_MODE--------------------MAXIMUM AVAILABILITYSQL> 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.cNo standby database destinations have been configuredas being archived by the LGWR processThis instance will operate at a reduced protection mode untilnetwork connectivity to the standby databases is restored andall archivelog gaps have been resolved.既然保护模式已经降级 ，那么根据上面的测试，此时如果要在数据库OPEN状态下更改为Maximum Protection模式是不可能的，现在验证一把SQL> SELECT PROTECTION_MODE FROM V$DATABASE;PROTECTION_MODE--------------------MAXIMUM AVAILABILITYSQL> 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 anyinstance可见ORACLE 的Maximum Availability 模式的确在异常时刻保护模式降级了。总结：MAXIMUM AVAILABILITY MAXIMUM PERFORMANCE 两种模式可以从任意一种模式状态下转换过来，并且与LOG_ARCHIVE_DEST_N设置无关。MAXIMUM PROTECTION 模式有下面条件限制：1. LOG_ARCHIVE_DEST_N 日志传输参数必须有一个设置为SYNC2. 在数据库OPEN状态下，只能从MAXIMUM AVAILABILITY 模式直接转换3. 从MAXIMUM PERFORMANCE 模式转换，需先转换到MAXIMUM AVAILABILITY模式，否则数据库必须启动到MOUNT状态4. 当MAXIMUM AVALIABILITY 模式降级为MAXIMUM PERFORMANCE ，转换需要参照第3点，或者等异常处理完成恢复。
