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

物理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.testtbSQLdesctest.testtbNameNull?Type---------------------------------------------------------------------------------------ANUMBER(38)切换前纪录：主库：SQLselect*fromtest.testtborderbya;A----------------------------4678910201002005001000A--------------------------1001100250003000015rowsselected.OGG查询库：SQLselect*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)34infoallProgramStatusGroupLagTimeSinceChkptMANAGERRUNNINGEXTRACTRUNNINGDP0100:00:0000:00:02EXTRACTRUNNINGEXT0100:00:0000:00:06GGSCI(loopback)35stop*SendingSTOPrequesttoEXTRACTDP01...Requestprocessed.SendingSTOPrequesttoEXTRACTEXT01...Requestprocessed.GGSCI(loopback)36infoallProgramStatusGroupLagTimeSinceChkptMANAGERRUNNINGEXTRACTSTOPPEDDP0100:00:0000:00:03EXTRACTSTOPPEDEXT0100:00:0000:00:013.查看主库状态并切换主-备主库操作：SqlselectNAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUSfromv$database;NAMEDB_UNIQUE_NAMEOPEN_MODEPROTECTION_MODEPROTECTION_LEVELSWITCHOVER#---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------DATABASE_ROLESWITCHOVER_STATUS------------------------------------------------------------------------ORATESToratestREADWRITEMAXIMUMPERFORMANCEMAXIMUMPERFORMANCE3369866885PRIMARYTOSTANDBYSqlalterdatabasecommittoswitchovertophysicalstandby;Databasealtered.主库后台日志：WedDec1813:48:012013alterdatabasecommittoswitchovertophysicalstandbyWedDec1813:48:012013ALTERDATABASECOMMITTOSWITCHOVERTOPHYSICALSTANDBY(oratest)WedDec1813:48:022013Thread1advancedtologsequence51(LGWRswitch)Currentlog#2seq#51mem#0:/oratest/oratest/redo02.logWedDec1813:48:022013ARC0:Standbyredologfileselectedforthread1sequence50fordestinationLOG_ARCHIVE_DEST_2WedDec1813:48:022013SMON:disablingtxrecoveryWedDec1813:48:022013StoppingbackgroundprocessCJQ0WedDec1813:48:022013StoppingbackgroundprocessQMNCWedDec1813:48:042013StoppingJobqueueslaveprocesses,flags=27WedDec1813:48:042013JobqueueslaveprocessesstoppedWaitingfordispatcher'D000'toshutdownAlldispatchersandsharedserversshutdownWedDec1813:48:092013SMON:disablingcacherecoveryWedDec1813:48:092013ShuttingdownarchiveprocessesArchivingisdisabledWedDec1813:48:142013ARCHshuttingdownARC1:ArchivalstoppedWedDec1813:48:192013ARCHshuttingdownARC0:ArchivalstoppedWedDec1813:48:202013Thread1closedatlogsequence51Successfulcloseofredothread1WedDec1813:48:202013ARCH:Noswitcharchivalofthread1,sequence51ARCH:End-Of-RedoBrancharchivalofthread1sequence51ARCH:ArchivingisdisabledduetocurrentlogfilearchivalClearingstandbyactivationID3369866885(0xc8dc1685)Theprimarydatabasecontrolfilewascreatedusingthe'MAXLOGFILES16'clause.Thereisspaceforupto13standbyredologfilesUsethefollowingSQLcommandsonthestandbydatabasetocreatestandbyredologfilesthatmatchtheprimarydatabase:ALTERDATABASEADDSTANDBYLOGFILE'srl1.f'SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILE'srl2.f'SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILE'srl3.f'SIZE52428800;ALTERDATABASEADDSTANDBYLOGFILE'sr