先说说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_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 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 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点,或者等异常处理完成恢复。