国际影视中心云平台利用golden gate进行数据库迁移实施步骤

TOC\o"1-2"\h\z\u配置 系统环 GoldenGate配置步 初始化数 将源库恢复至青龙服务 将数据迁入 目标端启动同 验证数据准确 应用切 失败回 系统环两个节点OracleRAC(源端)通过GoldenGate向两个节点OracleRAC(目标端RACRACRAC$oslevel-$lslpp-l|$oslevel-$lslpp-l|grep-iGoldenGate配置步$mkdir$cpggs_AIX_ppc_ora10.2_64bit.zip$cd$unzip$tar–xvf源端节$mkdir$cpggs_AIX_ppc_ora10.2_64bit.zip$cd$unzip$tar–xvf源端节1,目标端节点1，修oracle用户exportexportLIBPATH=/goldengate:$ORACLE_HOME/lib:$LIBPATHexportPATH=/goldengate:$PATHSQL>showparameter检查源RAC节点SQL>showparameterSQL>SQL>SELECT*FROMNLS_DATABASE_PARAMETERSWHEREPARAMETERIN('NLS_CHARACTERSET',SQL>archiveloglog_archive_formatSQL>SQL>archiveloglog_archive_formatSQL>showparameterLOG_ARCHIVE_FORMATSQL>selectSUPPLEMENTAL_LOG_DATA_MINfromv$database;SQL>ALTERDATABASESQL>selectSUPPLEMENTAL_LOG_DATA_MINfromv$database;SQL>ALTERDATABASEADDSUPPLEMENTALLOGDATA;SQL>ALTERSYSTEMSWITCHLOGFILE;SQL>selectSUPPLEMENTAL_LOG_DATA_MINfromv$database;源端创建GoldenGate连接用SQL>CREATEUSERgoldengateIDENTIFIEDBYgoldengateDEFAULTTABLESPACEUSERSTEMPORARYSQL>CREATEUSERgoldengateIDENTIFIEDBYgoldengateDEFAULTTABLESPACEUSERSTEMPORARYTABLESPACETEMPQUOTAUNLIMITEDONGRANTCONNECTTOGRANTCREATESESSIONTOgoldengate;GRANTALTERSESSIONTOgoldengate;GRANTRESOURCETOgoldengate;GRANTSELECTANYDICTIONARYTOGRANTSELECTANYTABLETOgoldengate;GRANTFLASHBACKANYTABLETOgoldengate;GRANTALTERANYTABLETO检查目标端字符SQL>SQL>SELECT*FROMNLS_DATABASE_PARAMETERSWHEREPARAMETERIN('NLS_CHARACTERSET',目标端创建GoldenGate连接用SQL>CREATEUSERgoldengateIDENTIFIEDBYgoldengateDEFAULTTABLESPACEUSERSTEMPORARYSQL>CREATEUSERgoldengateIDENTIFIEDBYgoldengateDEFAULTTABLESPACEUSERSTEMPORARYTABLESPACETEMPQUOTAUNLIMITEDONGRANTALTERSESSIONTOgoldengate;GRANTCREATESESSIONTOgoldengate;GRANTCONNECTTOgoldengate;GRANTRESOURCETOGRANTSELECTANYDICTIONARYTOGRANTSELECTANYTABLETOgoldengate;GRANTFLASHBACKANYTABLETOgoldengate;GRANTINSERTANYTABLETOgoldengate;GRANTUPDATEANYTABLETOgoldengate;GRANTDELETEANYTABLETO源端节点1,目标端节点1建系 和设置全局变$cd$cd$GGSCI>createGGSCI>EditParams./GLOBALSGGSchemagoldengateCheckpointTableGGSCI>$GGSCI>DbLoginUserIdgoldengate,PasswordgoldengateGGSCI>AddCheckpointTable源端打开测试表附加日GGSCIGGSCI>DbLoginUserIdgoldengate,PasswordgoldengateGGSCI>AddTranDatauser_pro.*GGSCI>AddTranDataorder_status.*GGSCI>AddTranDatapopcard.*GGSCI>AddTranDataconfig_center.*GGSCI>AddTranDatapopmonitor.*GGSCI>AddTranDatapopbi.*GGSCI>AddTranDatapopfin.*GGSCI>AddTranDatapoporder.*GGSCIGGSCI>AddTranDatapopware.*GGSCI>AddTranDatapopdir.*GGSCI>AddTranDatapopuser.*源端配Manager进GGSCI>GGSCI>EditParamsmgrPort7809UserIdgoldengate,PasswordgoldengateAutoRestartER*,WaitMinutes5,Retries3LagInfoMinutes0LagReportMinutesPortAutoRestartER*,WaitMinutes5,Retries自动重启LagInfoMinutesLagReportMinutes在中记录延迟的目标端配置Manager进GGSCI>GGSCI>EditParamsmgrPort7809UserIdgoldengate,PasswordgoldengateCheckMinutes20PurgeOldExtracts./dirdat/*,UseCheckpoints,MinKeepHours1AutoRestartER*,WaitMinutes5,Retries3LagInfoMinutesLagReportMinutes源端配TNSNAMES文件添ASM连接ASMASM=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db10)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ASM)(INSTANCE_NAME=+ASM1)(UR=A)10gASM))源端配DBLog抽取进GGSCI>EditParamsextAExtractextAsetenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBKUserIdgoldengate,PasswordgoldengateRmtHost42,MgrPort7809RmtTrail./dirdat/rATRANLOGOPTIONSASMUSERsys@ASM, TranLogOptionsExcludeUserTranLogOptionsAltArchiveLogDestPrimaryInstancepopdb1+backdatagroup,AltArchiveLogDestInstancepopdb2+backdatagroup mitPropagationDelay10000IOLatency3000sequenceuser_pro.*;sequenceorder_status.*;sequencepopcard.*;sequencejdlottery.*;sequencepopchongzhi.*;sequenceair sequenceconfig_center.*;sequencepopmonitor.*;sequencepopbi.*;sequencepopfin.*;sequencepoporder.*;sequencepopware.*;sequencepopdir.*;sequencepopuser.*;tableuser_pro.*;tableorder_status.*;tablepopcard.*;tablejdlottery.*;tablepopchongzhi.*;tableair tableconfig_center.*;tablepopmonitor.*;tablepopbi.*;tablepopfin.*;tablepoporder.*;tablepopware.*;tablepopdir.*;tablepopuser.*;SQL>selectSTART_TIMEfromGGSCIGGSCIAddExtractextA,TranLogBeginNow,Threads2RACGGSCI>AddRmtTrail./dirdat/rA,ExtractextA,MegaBytes20UserIdgoldengate,Password登录源端数据库 51980MB,CacheDirectory缓存大事务的临时RmtHost42,MgrPort目标端连接参数RmtTrailTranLogOptionsExcludeUser不用 NETDB1%t_%s_%r.dbf NETDB2归档日志文件TranLogOptionsAltArchiveLogDestPrimaryInstanceNETDB1/arch1,AltArchiveLogDestInstance mitPropagationDelayIOLatencyRACSequenceTableExcludeBNYL.SYS_EXPORT_SCHEMA*TableExcludeBNYL.N_TABLETableExcludeTableGGSCI>EditParamsrepAReplicatrepASetEnv(NLS_LANGGGSCI>EditParamsrepAReplicatrepASetEnv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"UserIdgoldengate,PasswordgoldengateDiscardFile./dirrpt/repA.dsc,Purgesequenceuser_pro.*,targetuser_pro.*sequenceorder_status.*,targetorder_status.*sequencepopcard.*,targetpopcard.*sequencejdlottery.*,targetjdlottery.*sequencepopchongzhi.*,targetpopchongzhi.*sequenceair ne.*,targetair sequenceconfig_center.*,targetsequencesequencepopmonitor.*,targetpopmonitor.*sequencepopbi.*,targetpopbi.*sequencepopfin.*,targetpopfin.*sequencepoporder.*,targetpoporder.*sequencepopware.*,targetpopware.*sequencepopdir.*,targetpopdir.*sequencepopuser.*,targetpopuser.*mapuser_pro.*,Targetuser_pro.*,maporder_status.*,Targetorder_status.*,InsertAppend;mappopcard.*,Targetpopcard.*,InsertAppend;mapjdlottery.*,Targetjdlottery.*,mappopchongzhi.*,Targetpopchongzhi.*,InsertAppend;mapair ne.*,Targetair ne.*,InsertAppend;mapconfig_center.*,Targetconfig_center.*,InsertAppend;mappopmonitor.*,Targetpopmonitor.*,InsertAppend;mappopbi.*,Targetpopbi.*,InsertAppend;mappopfin.*,Targetpopfin.*,InsertAppend;mappoporder.*,Targetpoporder.*,InsertAppend;mappopware.*,Targetpopware.*,InsertAppend;mappopdir.*,Targetpopdir.*,InsertAppend;mappopuser.*,Targetpopuser.*,InsertAppend;GGSCI>AddReplicatrepA,ExtTrail "AMERICAN_AMERICA.ZHS16GBK")设置环境变量，因为s环境中NLS_LANGUserIdgoldengate,Password登录目标数据库的从目标端获取表结构DiscardFile./dirrpt/repA.dsc,保存出错数据的文件允许不修改任何数确保目标Sequence和源端SequenceBNYL.*,TargetSequenceMapExcludeexpdp表MapBNYL.*,TargetBNYL.*,表映射，插入时用Append源端节1,目标端节点1，配sequencecd/back_rac/goldengateSqlplus“/assysdba”SQL>grantcd/back_rac/goldengateSqlplus“/assysdba”SQL>grantdbatogoldengate;GRANTEXECUTEongoldengate.updateSequenceTO目标端节点1，配sequenceSqlplusSqlplus“/asSQL>GRANTEXECUTEongoldengate.replicateSequenceTOGGSCI>startGGSCI>startGGSCI>startGGSCI>startGGSCI>startGGSCI>start查询数据库中当前最早的开始时间,直到该时间超过OGG抽取启动时间点(extract启动后开始的，否则可能前面部分抓取不到*）SCNSCN，rmanscn必须源端系统变更汇建立GoldenGate安 可用空间为每天数据库归档日志量*2oracle用户.profile所有被的表都必须是LOGGING，不能是NOLOGGING。update语句和表是否有主键影响。根据以往经验，如果执行update5%左右，如果执行update20%左右。另外，3天归档日志。所以变更后归档日志空间=目前每天归档日志量*110%*3goldengate初始化数将源库恢复至青龙服务源库执行检将11g$ORACLE_HOME/rdbms/admin下的utlu112i.sqlcopy到10g$ORACLE_HOME/rdbms/admin下，并在10g上执行。若不执行，在恢复时将RmantargetBackupdatabaseformat‘/full%d%t.bak’includeRmantargetBackupdatabaseformat‘/full%d%t.bak’includecurrentcontrolfileplus将备份以及所需归档传输到青龙服务补充修改完毕后的参数文RMAN>startupRMAN>restoreRMAN>startupRMAN>restorecontrolfilefromRMAN>alterdatabaseRMAN>alterdatabaseRMAN>catalogstartwithRMAN>setnewnamefordataf