ORACLE 11G DATAGUARD保护模式设立详解
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点,或者等异常处理完成恢复。