关于DataGuard的三种保护模式实验

Data Guard是Oracle高可用性HA的重要解决方案。针对不同的系统保护需求，DG提供了三种不同类型的保护模式（Protection Mode），分别为：最大保护（Maximum Protection）、最大可用（Maximum Availability）和最大性能（Maximum performance）。在实际应用场景下，我们需要根据不同的业务场景和数据可用性需求，来设置DG环境的保护类型。1、三种保护模式Protection Mode三种保护模式是DG的核心概念。DG本质上是一种基于Redo Log的数据同步机制。Undo和Redo是Oracle早期奠定行业地位的核心技术。Undo负责记录事务操作的前镜像，而Redo负责记录事务操作的后镜像。在Oracle事务commit的动作中，写入日志文件是一个一定需要完成的动作。写入日志文件之后，即使立刻出现严重的实例终止事件，在重新启动实例的时候也会进行实例恢复动作，将事务落实。在DG环境中，无论采用何种初始化方法，都是确保一个Primary和Standby的初始化数据一致，之后Primary一端接收的事务类型操作，均会以归档日志串列的方式传递到Standby端的standby redo log和归档日志列表中，最后重复应用这些日志，实现Primary和Standby端一致。Primary和Standby是相互为备份的冗余结构，Standby跟随Primary的情况，反映了HA结构的可用性级别。理论上，最保险的策略是一个事务要保证在Primary和Standby上都提交了，才返回给用户说已经完成。这样是可以保证主备库完全一致的最保险做法。另一个极端情况，就是主库“自顾自”进行事务处理，独立将日志进行传输，也不用管日志是否传输到或者应用到。针对不同的传输情况，DG区分为三种保护类型：ü 最大可用模式Maximum Availability在官方文档中，对这种模式的描述如下：“This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to the standby redo log on at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.”Maximum Availability模式下，事务只有在所有相关日志都被传输到至少一个Standby端日志的时候，才可以正式提交。但是，如果Primary在传输日志的过程中，发现所有standby端都不能进行传输，模式会退化到最大性能模式（Maximum Performance）工作方式。应该说，Maximum Availability是一种自适应的保护模式，当出现问题的时候，DG会退而求其次，确保Primary主库事务进行。ü 最大性能模式（Maximum Performance）官方文档中介绍如下：“This protection mode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database（s）.This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.This is the default protection mode.”最大性能模式是在不影响主库工作情况下，可以提供的最高数据保护级别。当事务进行提交的时候，主库不会去确认日志是否写入到备库中，更不会确认是否被apply。这种方式下，主库的工作性能是不会收到备库提交应用的影响的。当然，这种保护模式会有一定的事务数据丢失，但是绝对不会出现数据误提交的情况。对DG而言，最大性能模式是默认的保护模式。当我们完成了DG安装之后，就自动进入了Maximum Performance模式。ü 最大保护模式（Maximum Protection）最大保护模式在官方中的描述为：“This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to the standby redo log on at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, if it cannot write its redo stream to at least one synchronized standby database.Transactions on the primary are considered protected as soon as Data Guard has written the redo data to persistent storage in a standby redo log file. Once that is done, acknowledgment is quickly made back to the primary database so that it can proceed to the next transaction. This minimizes the impact of synchronous transport on primary database throughput and response time. To fully benefit from complete Data Guard validation at the standby database, be sure to operate in real-time apply mode so that redo changes are applied to the standby database as fast as they are received. Data Guard signals any corruptions that are detected so that immediate corrective action can be taken.”最大保护模式是完全HA架构理想中的事务模式。如果Primary数据库进行一个事务，连带Standby数据库也要同步进行操作。如果由于网络、执行模式等原因，Standby不能够跟上主库的操作，那么主库会放弃事务，并且强制停库。保护模式的三种和数据库之间传输日志的机制是密切相关的。主要体现是否同步传输Redo日志和对日志进行确认两个方面。我们配置三种日志模式，一定要以Log_Archive_Config参数配置为基础。Oracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htmOracle Data Guard 重要配置参数 http://www.linuxidc.com/Linux/2013-08/88784.htm基于同一主机配置 Oracle 11g Data Guard http://www.linuxidc.com/Linux/2013-08/88848.htm探索Oracle之11g DataGuard http://www.linuxidc.com/Linux/2013-08/88692.htmOracle Data Guard （RAC+DG）归档删除策略及脚本 http://www.linuxidc.com/Linux/2013-07/87782.htmOracle Data Guard 的角色转换 http://www.linuxidc.com/Linux/2013-06/86190.htmOracle Data Guard的日志FAL gap问题 http://www.linuxidc.com/Linux/2013-04/82561.htmOracle 11g Data Guard Error 16143 Heartbeat failed to connect to standby 处理方法 http://www.linuxidc.com/Linux/2013-03/82009.htm

Maximum Availability	Maximum Performance	Maximum Protection
AFFIRM	NOAFFIRM	AFFIRM
SYNC	ASYNC	SYNC
DB_UNIQUE_NAME	DB_UNIQUE_NAME	DB_UNIQUE_NAME

下面通过一系列的测试，来分析三种保护模式的工作行为方式。2、环境介绍笔者使用环境为Oracle 11gR2，具体版本为11.2.0.4。主备库环境已经搭建完成，同步保护模式是采用默认方式。主库信息：SQL> select name, open_mode, database_role, protection_mode from v$database;NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE--------- -------------------- ---------------- --------------------VLIFE READ WRITE PRIMARY MAXIMUM PERFORMANCESQL> select instance_name from v$instance;INSTANCE_NAME----------------vlife主库与备库连接方式，采用默认的异步非确认方式。SQL> select dest_id, dest_name, TRANSMIT_MODE, ASYNC_BLOCKS, AFFIRM TYPE, VALID_NOW, VALID_TYPE, VALID_ROLE, DB_UNIQUE_NAME from v$archive_dest where status<>"INACTIVE"; DEST_ID DEST_NAME TRANSMIT_MODE ASYNC_BLOCKS TYPE VALID_NOW VALID_TYPE VALID_ROLE DB_UNIQUE_NAME---------- -------------------- ------------- ------------ ---- ---------------- --------------- ------------ --------------- 1 LOG_ARCHIVE_DEST_1 SYNCHRONOUS 0 NO YES ALL_LOGFILES ALL_ROLES NONE 2 LOG_ARCHIVE_DEST_2 ASYNCHRONOUS 61440 NO YES ONLINE_LOGFILE PRIMARY_ROLE vlifesb此时，传输通道配置。SQL> show parameter LOG_ARCHIVE_DEST_2;NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string SERVICE=vlifesb valid_for=（online_logfiles,primary_role） db_unique_name=vlifesblog_archive_dest_20 string log_archive_dest_21 string 备库信息如下：SQL> select name, open_mode, database_role, protection_mode from v$database;NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE--------- -------------------- ---------------- --------------------VLIFE READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCESQL> select instance_name from v$instance;INSTANCE_NAME----------------vlifesbSQL> col dest_name for a20;SQL> select dest_id, dest_name, TRANSMIT_MODE, ASYNC_BLOCKS, AFFIRM TYPE, VALID_NOW, VALID_TYPE, VALID_ROLE, DB_UNIQUE_NAME from v$archive_dest where status<>"INACTIVE"; DEST_ID DEST_NAME TRANSMIT_MODE ASYNC_BLOCKS TYPE VALID_NOW VALID_TYPE VALID_ROLE DB_UNIQUE_NAME---------- -------------------- ------------- ------------ ---- ---------------- --------------- ------------ ------------------------------ 1 LOG_ARCHIVE_DEST_1 SYNCHRONOUS 0 NO YES ALL_LOGFILES ALL_ROLES NONE 2 LOG_ARCHIVE_DEST_2 ASYNCHRONOUS 61440 NO WRONG VALID_TYPE ONLINE_LOGFILE PRIMARY_ROLE vlife 32 STANDBY_ARCHIVE_DEST SYNCHRONOUS 0 NO YES ALL_LOGFILES ALL_ROLES NONESQL> show parameter LOG_ARCHIVE_DEST_2;NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_2 string SERVICE=vlife valid_for=（online_logfiles,primary_role） db_unique_name=vlifelog_archive_dest_20 string log_archive_dest_21 string 3、最大可用模式Maximum Availability Mode测试当前从默认的最大性能切换到最大可用模式，首先需要满足将日志传输模式进行修改。SQL> alter system set log_archive_dest_2="SERVICE=vlifesb sync affirm net_timeout=30 valid_for=（online_logfiles,primary_role） db_unique_name=vlifesb";System alteredSQL> select dest_id, dest_name, TRANSMIT_MODE, ASYNC_BLOCKS, AFFIRM TYPE, VALID_NOW, VALID_TYPE, VALID_ROLE, DB_UNIQUE_NAME, NET_TIMEOUT from v$archive_dest where status<>"INACTIVE"; DEST_ID DEST_NAME TRANSMIT_MODE ASYNC_BLOCKS TYPE VALID_NOW VALID_TYPE VALID_ROLE DB_UNIQUE_NAME NET_TIMEOUT---------- -------------------- ------------- ------------ ---- ---------------- --------------- ------------ ------------------------------ ----------- 1 LOG_ARCHIVE_DEST_1 SYNCHRONOUS 0 NO YES ALL_LOGFILES ALL_ROLES NONE 0 2 LOG_ARCHIVE_DEST_2 PARALLELSYNC 0 YES YES ONLINE_LOGFILE PRIMARY_ROLE vlifesb 30此时，将保护模式使用alter database进行设置。SQL> alter database set standby database to maximize availability;Database alteredSQL> select name, open_mode, database_role, protection_mode from v$database;NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE--------- -------------------- ---------------- --------------------VLIFE READ WRITE PRIMARY MAXIMUM AVAILABILITY在切换动作的时候，主库日志情况如下：Wed Oct 21 15:13:48 2015alter database set standby database to maximize availabilityCompleted: alter database set standby database to maximize availabilityWed Oct 21 15:13:49 2015Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED –发现没有同步，需要补充。******************************************************************LGWR: Setting "active" archival for destination LOG_ARCHIVE_DEST_2******************************************************************Wed Oct 21 15:13:49 2015NSS2 started with pid=34, OS id=9186 LGWR: Standby redo logfile selected to archive thread 1 sequence 82LGWR: Standby redo logfile selected for thread 1 sequence 82 for destination LOG_ARCHIVE_DEST_2Thread 1 advanced to log sequence 82 （LGWR switch） Current log# 3 seq# 82 mem# 0: /u01/app/oracle/oradata/VLIFE/onlinelog/o1_mf_3_c1kb1c24_.log Current log# 3 seq# 82 mem# 1: /u01/app/oracle/fast_recovery_area/VLIFE/onlinelog/o1_mf_3_c1kb1c43_.logWed Oct 21 15:13:53 2015Archived Log entry 104 added for thread 1 sequence 81 ID 0xfad4f44b dest 1:Wed Oct 21 15:13:54 2015ARC3: Archive log rejected （thread 1 sequence 81） at host "vlifesb"FAL[server, ARC3]: FAL archive failed, see trace file.ARCH: FAL archive failed. Archiver continuingORACLE Instance vlife - Archival Error. Archiver continuing.Wed Oct 21 15:14:42 2015Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZEDLGWR: Standby redo logfile selected to archive thread 1 sequence 83LGWR: Standby redo logfile selected for thread 1 sequence 83 for destination LOG_ARCHIVE_DEST_2Thread 1 advanced to log sequence 83 （LGWR switch） Current log# 1 seq# 83 mem# 0: /u01/app/oracle/oradata/VLIFE/onlinelog/o1_mf_1_c1kb19q4_.log Current log# 1 seq# 83 mem# 1: /u01/app/oracle/fast_recovery_area/VLIFE/onlinelog/o1_mf_1_c1kb19sb_.logWed Oct 21 15:14:42 2015Archived Log entry 107 added for thread 1 sequence 82 ID 0xfad4f44b dest 1:在Primary端，在进行切换之后，Oracle发现传输状态不是同步情况。于是自动加快进行日志传输和同步动作。在Standby端，也可以看到后续追赶动作。SQL> select name, open_mode, database_role, protection_mode from v$database;NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE--------- -------------------- ---------------- --------------------VLIFE READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITYStandby端上的日志追赶动作。Wed Oct 21 08:27:05 2015Primary database is in MAXIMUM PERFORMANCE modeRe-archiving standby log 4 thread 1 sequence 80Wed Oct 21 08:27:05 2015Media Recovery Waiting for thread 1 sequence 81RFS[14]: Assigned to RFS process 31500RFS[14]: Selected log 5 for thread 1 sequence 81 dbid -87496857 branch 892734889Wed Oct 21 08:27:05 2015Archived Log entry 76 added for thread 1 sequence 80 ID 0xfad4f44b dest 1:Recovery of Online Redo Log: Thread 1 Group 5 Seq 81 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_5_c265gqd8_.log Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_5_c265gqj0_.logWed Oct 21 15:13:52 2015Primary database is in MAXIMUM AVAILABILITY modeChanging standby controlfile to MAXIMUM AVAILABILITY modeChanging standby controlfile to RESYNCHRONIZATION levelStandby controlfile consistent with primaryRFS[15]: Assigned to RFS process 969RFS[15]: Selected log 4 for thread 1 sequence 82 dbid -87496857 branch 892734889Wed Oct 21 15:13:53 2015Archived Log entry 77 added for thread 1 sequence 81 ID 0xfad4f44b dest 1:Wed Oct 21 15:13:53 2015Media Recovery Waiting for thread 1 sequence 82 （in transit）Recovery of Online Redo Log: Thread 1 Group 4 Seq 82 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_4_c265gc9q_.log Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_4_c265gcfk_.logWed Oct 21 15:14:41 2015Archived Log entry 78 added for thread 1 sequence 82 ID 0xfad4f44b dest 1:Wed Oct 21 15:14:41 2015Media Recovery Waiting for thread 1 sequence 83Wed Oct 21 15:14:42 2015Primary database is in MAXIMUM AVAILABILITY modeChanging standby controlfile to MAXIMUM AVAILABILITY levelStandby controlfile consistent with primaryRFS[16]: Assigned to RFS process 976RFS[16]: Selected log 4 for thread 1 sequence 83 dbid -87496857 branch 892734889Recovery of Online Redo Log: Thread 1 Group 4 Seq 83 Reading mem 0 Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_4_c265gc9q_.log Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_4_c265gcfk_.log此时，两个库由于网络通畅，同步状态正常，同步测试正常。（Maximium Availiablity模式下使用）--主库下SQL> create table t_m as select * from dba_objects where rownum<10;Table created--Standby下SQL> select count（*） from t_m; COUNT（*）---------- 9如果此时中断应用日志，Standby情况如下：SQL> alter database recover managed standby database cancel;Database alteredSQL> select name, open_mode, database_role, protection_mode from v$database;NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE--------- -------------------- ---------------- --------------------VLIFE READ ONLY PHYSICAL STANDBY MAXIMUM AVAILABILITY日志情况如下：Wed Oct 21 15:20:49 2015alter database recover managed standby database cancelWed Oct 21 15:20:49 2015MRP0: Background Media Recovery cancelled with status 16037Errors in file /u01/app/oracle/diag/rdbms/vlifesb/vlifesb/trace/vlifesb_pr00_17539.trc:ORA-16037: user requested cancel of managed recovery operationManaged Standby Recovery not using Real Time ApplyRecovery interrupted！Recovered data files to a consistent state at change 1692263Wed Oct 21 15:20:49 2015MRP0: Background Media Recovery process shutdown （vlifesb）Managed Standby Recovery Canceled （vlifesb）Completed: alter database recover managed standby database cancel如果此时再中断监听器，中断连接。此时数据库不能做到实时同步。[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl stopLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-OCT-2015 15:24:17Copyright （c） 1991, 2013, Oracle. All rights reserved.Connecting to （DESCRIPTION=（ADDRESS=（PROTOCOL=TCP）（HOST=localhost）（PORT=1521）））The command completed successfully--主库***********************************************************************Fatal NI connect error 12541, connecting to: （DESCRIPTION=（ADDRESS_LIST=（ADDRESS=（PROTOCOL=TCP）（HOST=172.16.19.90）（PORT=1521）））（CONNECT_DATA=（SERVICE_NAME=vlifesb）（CID=（PROGRAM=oracle）（HOST=vLIFE-URE-OT-DB-PRIMARY）（USER=oracle）））） VERSION INFORMATION: TNS for Linux: Version 11.2.0.4.0 - Production TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production Time: 21-OCT-2015 15:24:38 Tracing not turned on. Tns error struct: ns main err code: 12541 TNS-12541: TNS:no listener ns secondary err code: 12560 nt main err code: 511 TNS-00511: No listener nt secondary err code: 111 nt OS err code: 0Error 12541 received logging on to the standbyCheck whether the listener is up and running.PING[ARC2]: Heartbeat failed to connect to standby "vlifesb". Error is 12541.强制日志切换。SQL> alter system switch logfile;System alteredSQL> alter system switch logfile;System alteredSQL> alter system switch logfile;System alteredSQL> select * from v$archive_dest_status; DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION STANDBY_LOGFILE_COUNT STANDBY_LOGFILE_ACTIVE ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR SRL DB_UNIQUE_NAME SYNCHRONIZATION_STATUS SYNCHRONIZED GAP_STATUS---------- -------------------- --------- -------------- --------------- ----------------------- -------------------- -------------------------------------------------------------------------------- --------------------- ---------------------- ---------------- ------------- --------------- ------------ -------------------------------------------------------------------------------- --- ------------------------------ ---------------------- ------------ ------------------------ 1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE MAXIMUM PERFORMANCE /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch 0 0 1 85 0 0 NO NONE CHECK CONFIGURATION NO 2 LOG_ARCHIVE_DEST_2 ERROR PHYSICAL OPEN_READ-ONLY IDLE RESYNCHRONIZATION vlifesb 3 0 1 82 1 82 ORA-12541: TNS: ？？？à？？？？？ò YES vlifesb CHECK CONNECTIVITY NO RESOLVABLE GAPSQL> select recid, name, sequence# from v$archived_log where sequence#>82; RECID NAME SEQUENCE#---------- -------------------------------------------------------------------------------- ---------- 108 vlifesb 83 109 /u01/app/oracle/fast_recovery_area/VLIFE/archivelog/2015_10_21/o1_mf_1_83_c2ghkz 83 110 /u01/app/oracle/fast_recovery_area/VLIFE/archivelog/2015_10_21/o1_mf_1_84_c2ghl0 84 111 /u01/app/oracle/fast_recovery_area/VLIFE/archivelog/2015_10_21/o1_mf_1_85_c2ghl4 85而standby端，归档日志就没有传输到。SQL> select recid, name, sequence# from v$archived_log where sequence#>82; RECID NAME SEQUENCE#---------- -------------------------------------------------------------------------------- ---------- 79 /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_21/o1_mf_1_83_c2gh 83SQL> select group#, dbid, archived from v$standby_log; GROUP# DBID ARCHIVED---------- ---------------------------------------- -------- 4 UNASSIGNED NO 5 UNASSIGNED NO 6 UNASSIGNED YES此时，Primary和Standby的连接明显被中断，日志不能传送，也就达不到同步确认的设置要求。但是此时，Primary还是可以进行事务操作。（事务可以进行）SQL> insert into t_m select * from dba_objects where rownum<10;9 rows insertedSQL> commit;Commit complete此时，如果恢复两者连接，启动监听器和日志应用。[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-OCT-2015 15:51:46Copyright （c） 1991, 2013, Oracle. All rights reserved.Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.4.0 - ProductionSystem parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.oraLog messages written to （篇幅原因，有省略…….）SQL> alter database recover managed standby database using current logfile disconnect from session;Database altered之后主库和从库日志上进行归档日志传输和后续同步动作，篇幅原因，日志信息省略。从上面实验中，我们可以看到最大可用性模式的核心即使“可用”。所谓可用，即使保证Primary和Standby整体的可用。如果在日志传输通路顺畅，两者之间会维持严格的同步关系，行为类似于最大保护模式。但是，如果连接或者同步动作不能满足要求，DG是不会终止实例运行，而是退而求其次，进行一种类似最大性能模式的工作方式。更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2015-11/125053p2.htm

Oracle 12c In-Memory in TablespacesMySQL中order by 结果不准确的问题及解决相关资讯 DataGuard DataGuard保护模式

DataGuard如何传送Redo到Standby （05月04日）
RAC环境下DataGuard到单机（04月08日）
Oracle 11G R2 DataGuard日常维护（02月20日）

MRP0- Background Media Recovery （04月16日）
DataGuard环境中的密码维护（03月17日）
Oracle 11G R2利用RMAN搭建（02月20日）

本文评论查看全部评论（0）