物理DATAGUARD主备切换对OGG(DML)同步影响测试VIP免费

物理DATAGUARD主备切换对OGG（DML）同步影响测试一、环境介绍数据库角色DG主库DG备库OGG同步库（查询库）IP10.15.59.10210.14.156.13710.15.34.36操作系统版本AIX5.3AIX5.3AIX5.2Oraclerdbms版本10.2.0.4.010.2.0.4.010.2.0.4.0OGG版本10.2.0.19build002None10.2.0.19build002xlC.rte版本9.0.0.110.1.0.09.0.0.1OraclesidoratestoratestoratestDb_unique_nameoratestphyoratestoratest注：如何搭建环境在此文档中不做描述。测试表：test.testtbSQL>desctest.testtbNameNull?Type---------------------------------------------------------------------------------------ANUMBER(38)切换前纪录：主库：SQL>select*fromtest.testtborderbya;A----------------------------4678910201002005001000A--------------------------1001100250003000015rowsselected.OGG查询库：SQL>select*fromtest.testtborderbya;A-------------------------4678910201002005001000A-----------------------1001100250003000015rowsselected.通过表中的数据比较可以看到主库与OGG查询库为同步状态。主库后台日志：Thread1advancedtologsequence48(LGWRswitch)Currentlog#2seq#48mem#0:/oratest/oratest/redo02.logWedDec1813:28:542013ARC1:Standbyredologfileselectedforthread1sequence47fordestinationLOG_ARCHIVE_DEST_2WedDec1813:40:322013Thread1advancedtologsequence49(LGWRswitch)Currentlog#3seq#49mem#0:/oratest/oratest/redo03.logWedDec1813:40:322013ARC1:Standbyredologfileselectedforthread1sequence48fordestinationLOG_ARCHIVE_DEST_2可以看到当前主库归档日志为48号，在线日志为49号（未归档）DG灾备库后台日志：RFS[1]:Identifieddatabasetypeas'physicalstandby'TueDec1723:27:202013RFSLogMiner:ClientdisabledfromfurthernotificationTueDec1723:29:512013RFS[1]:Successfullyopenedstandbylog4:'/oratest/oratest/stand01.dbf'TueDec1723:29:522013MediaRecoveryLog/oratest/archive/1_47_834059738.dbfMediaRecoveryWaitingforthread1sequence48TueDec1723:41:302013RFS[1]:Successfullyopenedstandbylog4:'/oratest/oratest/stand01.dbf'TueDec1723:41:332013MediaRecoveryLog/oratest/archive/1_48_834059738.dbfMediaRecoveryWaitingforthread1sequence49可以看到当前灾备库已经MediaRecovery的归档日志为48号，等待的下个日志为49号综上，可以判断出主库与灾备库当前为同步状态。二、DG主备切换：主库操作：1.Altersystemswitchlogfile;主库后台日志：WedDec1813:45:032013Thread1advancedtologsequence50(LGWRswitch)Currentlog#1seq#50mem#0:/oratest/oratest/redo01.logWedDec1813:45:032013ARC1:Standbyredologfileselectedforthread1sequence49fordestinationLOG_ARCHIVE_DEST_2备库后台日志：TueDec1723:46:012013RFS[1]:Successfullyopenedstandbylog4:'/oratest/oratest/stand01.dbf'TueDec1723:46:032013MediaRecoveryLog/oratest/archive/1_49_834059738.dbfMediaRecoveryWaitingforthread1sequence502.在主库关闭OGG查询库同步进程GGSCI(loopback)34>infoallProgramStatusGroupLagTimeSinceChkptMANAGERRUNNINGEXTRACTRUNNINGDP0100:00:0000:00:02EXTRACTRUNNINGEXT0100:00:0000:00:06GGSCI(loopback)35>stop*SendingSTOPrequesttoEXTRACTDP01...Requestprocessed.SendingSTOPrequesttoEXTRACTEXT01...Requestprocessed.GGSCI(loopback)36>infoallProgramStatusGroupLagTimeSinceChkptMANAGERRUNNINGEXTRACTSTOPPEDDP0100:00:0000:00:03EXTRACTSTOPPEDEXT0100:00:0000:00:013.查看主库状态并切换主->备主库操作：Sql>selectNAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUSfromv$database;NAMEDB_UNIQUE_NAMEOPEN_MODEPROTECTION_MODEPROTECTION_LEVELSWITCHOVER#------------------------------------------------------------------------------------------------------------------------...