OGG11g同构(GoldenGate-ORACLE)、异构(GoldenGate-MYSQL)同步配置及错误解析.docx

OGG同构(ORACLE-ORACLE)、异构(ORACLE-MYSQL)同步配置及错误解析环境：(已安装数据库实例)+OEL5.75zlmsid:zlm11g0zlm2sid:zlm11g一、安装软件，配置环境，创建相关用户1.1下载介质并安装OGG软件从官方网址下载最新版OGGFORORACLE11gONLINUXX86-64软件：/technetwork/middleware/goldengate/downloads/index.html?ssSourceSiteId=ocomenOracleGoldenGateV.1forOracle11gonLinuxx86-64(86MB)介质名称：ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip1.2把OGG软件包复制到源端oracle主目录，创建安装目录gg11后2次解压到gg11oraclezlm$lsogg112101_fbo_ggs_Linux_x64_ora11g_64bit.ziporaclezlm$cd$OACLE_BASEoraclezlmoracle$mkdirgg11oraclezlmoracle$cdgg11oraclezlmgg11$unzip/home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.ziporaclezlmgg11$lltotal223764-rw-rw-r-1oracleoinstall228556800Apr232012fbo_ggs_Linux_x64_ora11g_64bit.tar-rwxrwxrwx1oracleoinstall220546May22012OGG_WinUnix_Rel_Notes_.1.pdf-rwxrwxrwx1oracleoinstall93696May22012OracleGoldenGate.1README.doc-rwxrwxrwx1oracleoinstall24390May22012OracleGoldenGate.1README.txtoraclezlmgg11$tarxvoffbo_ggs_Linux_x64_ora11g_64bit.tar1.3修改环境变量文件.bash_profile，加入OGG的环境变量exportGGHOME=$ORACLE_BASE/gg11exportPATH=$ORACLE_BASE/gg11:$ORACLE_HOME/bin:/usr/bin/:$PATHoraclezlmgg11$./.bash_profileoraclezlmgg11$ggsciggsci:errorwhileloadingsharedlibraries:libnnz11.so:cannotopensharedobjectfile:Nosuchfileordirectory由于没有设置LD_LIBRARY_PATH环境变量，所以无法执行ggsci，添加exportLD_LIBLARY_PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/lib:$ORACLE_HOME/bin到环境变量.bash_profile并source，或者创建一个link文件：oraclezlmgg11$ln-s/u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so-/u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so1.4创建OGG专用目录subdirsGGSCI(zlm)1createsubdirsCreatingsubdirectoriesundercurrentdirectory/u01/app/oracle/gg11Parameterfiles/u01/app/oracle/gg11/dirprm:alreadyexistsReportfiles/u01/app/oracle/gg11/dirrpt:createdCheckpointfiles/u01/app/oracle/gg11/dirchk:createdProcessstatusfiles/u01/app/oracle/gg11/dirpcs:createdSQLscriptfiles/u01/app/oracle/gg11/dirsql:createdDatabasedefinitionsfiles/u01/app/oracle/gg11/dirdef:createdExtractdatafiles/u01/app/oracle/gg11/dirdat:createdTemporaryfiles/u01/app/oracle/gg11/dirtmp:createdStdoutfiles/u01/app/oracle/gg11/dirout:created1.5开启归档模式、强制日志、附加日志查看v$database看这3个参数是否已开启，使用OGG必须是开启状态SQLselectLOG_MODE,SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGINGfromv$database;LOG_MODESUPPLEMEFOR-ARCHIVELOGYESYES各参数开启方法：-archivelogSQLshutdownimmediateSQLstartupmountSQLalterdatabasearchivelog;SQLalterdatabaseopen;-forceloggingSQLalterdatabaseforcelogging;-supplementallogdataSQLalterdatabaseaddsupplementallogdata;1.6关闭数据库的recyclebin(10gDDL必须，11gDDL可选)SQLaltersystemsetrecyclebin=offscope=spfile;-同步DDL要求关闭10g中的回收站特性1.7创建复制用户ogg并授予权限SQLcreateuseroggidentifiedbyoggdefaulttablespaceuserstemporarytablespacetemp;SQLgrantconnect,resource,unlimitedtablespacetoogg;1.8创建测试用户sender并授予权限SQLcreateusersenderidentifiedbysenderdefaulttablespaceuserstemporarytablespacetemp;SQLgrantconnect,resource,unlimitedtablespacetosender;1.9配置复制的DDL支持(必须SYSDBA登录执行)SQLgrantexecuteonutl_filetoogg;SQL$GGHOME/marker_setup.sql;-建立一个DDL标记表SQL$GGHOME/ddl_setup.sql;-INITIALSETUP选项运行ddl_setup.sql将在数据库中创建捕获DDL语句的Trigger等必要组件(注意，执行时必须断开GGSCI连接，否则报错)SQL$GGHOME/role_setup.sql;-建立GGS_GGSUSER_ROLE角色SQLgrantGGS_GGSUSER_ROLEtoogg;-授予给extractgroup参数中定义的userid用户SQL$GGHOME/ddl_enable.sql;-enableddl捕获触发器注意：下面2个SQL脚本只是为了提高DDL复制性能，不是必须的SQL?/rdbms/admin/dbmspool-创建DBMS_SHARED_POOL包SQLddl_pin-通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中，以保证这些对象不要reload，提升性能1.10目标端重复配置以上1.1-1.9全部内容，至此，ORACLE-ORACLE环境搭建完毕二、用EXPDP/IMPDP初始化测试数据(仅限ORACLE-ORACLE)2.1创建EXPDP/IMPDP使用的directory及其对应的本地目录SQLsetlin200pages999SQLcolownerfora5SQLcoldirectory_namefora25SQLcoldirectory_pathfora75SQLselect*fromdba_directories;OWNERDIRECTORY_NAMEDIRECTORY_PATH-SYSGGS_DDL_TRACE/u01/app/oracle/diag/rdbms/zlm11g/zlm11g/traceSYSSUBDIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/2002/SepSYSSS_OE_XMLDIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/SYSBACKUP/u01/backupSYSLOG_FILE_DIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/log/SYSMEDIA_DIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/SYSXMLDIR/u01/app/oracle/product/11.2.0/db_1/rdbms/xmlSYSDATA_FILE_DIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/SYSDATA_PUMP_DIR/u01/app/oracle/admin/zlm11g/dpdump/SYSORACLE_OCM_CONFIG_DIR/u01/app/oracle/product/11.2.0/db_1/ccr/stateSQLcreatedirectoryexpdumpas/u01/expdp;SQLselect*fromdba_directories;OWNERDIRECTORY_NAMEDIRECTORY_PATH-SYSGGS_DDL_TRACE/u01/app/oracle/diag/rdbms/zlm11g/zlm11g/traceSYSEXPDUMP/u01/expdpSYSSUBDIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/2002/SepSYSSS_OE_XMLDIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/SYSBACKUP/u01/backupSYSLOG_FILE_DIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/log/SYSMEDIA_DIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/SYSXMLDIR/u01/app/oracle/product/11.2.0/db_1/rdbms/xmlSYSDATA_FILE_DIR/u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/SYSDATA_PUMP_DIR/u01/app/oracle/admin/zlm11g/dpdump/SYSORACLE_OCM_CONFIG_DIR/u01/app/oracle/product/11.2.0/db_1/ccr/stateoraclezlmgg11$cd/u01/oraclezlmu01$lsappbackuporaclezlmu01$mkdir/u01/expdporaclezlmu01$lltotal16drwxr-xr-x4oracleoinstall4096Jul1303:12appdrwxrwxr-x2oracleoinstall4096Jul2120:26backupdrwxr-xr-x2oracleoinstall4096Aug2822:11expdp2.2登录sender用户并创建测试表testSQLconnsender/senderConnected.SQLbegin2foriin1.3loop3insertintotestvalues(i);4endloop;5end;6/PL/SQLproceduresuccessfullycompleted.SQLselect*fromtest1;ID-1232.3赋予sender读写directory的权限，执行expdp导出测试表testSQLgrantread,writeondirectoryexpdumptosender;SQL!oraclezlm$expdpsender/senderdirectory=expdumpdumpfile=test.dmplogfile=test.logtables=testoraclezlm$cd/u01/expdporaclezlmexpdp$lstest.dmptest.log2.4在目标端创建directory及相应的本地路径，复制dump文件到目标端SQLcreatedirectoryimpdumpas/u01/impdp;-targetoraclezlm2gg11$mkdir/u01/impdporaclezlm2gg11$ls/u01appbackupimpdp-sourceoraclezlmexpdp$scptest.*zlm2:/u01/impdporaclezlm2spassword:test.dmp100%92KB92.0KB/s00:00test.log100%10111.0KB/s00:002.5目标端赋予recerver用户权限，执行impdp导入测试表testSQLgrantread,writeondirectoryimpdumptosender;SQL!oraclezlm2$impdpsender/senderdirectory=impdumpdumpfile=test.dmptables=test注意：expdp/impdp必须是在同一个schema下的object，否则不能执行成功，logfile可以无SQLconnsender/senderConnected.SQLselect*fromtest;ID-123此时完成test表的初始化同步，注意：如果非ORACLE-ORACLE方式同步，则只能使用OGG推荐的方式，即配置initialextract来初始化数据，具体可以参考我之前的一篇blog：OGG配置DML单向复制一例及错误分析/aaron8219/article/details/10275431三、不使用PUMP抽取进程的DML同步参数配置(ORACLE-ORACLE)3.1单向复制3.1.1创建并配置manageroggzlmgg11$./ggsciGGSCI(zlm)1infoallGGSCI(zlm)2editparamsmgrPORT7809ggate(zlm)3startmanagerManagerstarted.3.1.2配置源端抽取组ext1GGSCI(zlm)1addextractext1,tranlog,beginnowGGSCI(zlm)2addrmttrail./dirdat/rt,extractext1GGSCI(zlm)3editparamsext1extractext1useridogg,passwordoggrmthostzlm2,mgrport7809rmttrail./dirdat/rtddlincludemappedobjnamesender.*;tablesender.*;GGSCI(zlm)4infoall3.1.3配置目标端同步组在目标端添加checkpoint表oggzlmgg11$./ggsciGGSCI(zlm2)1editparams./GLOBALGGSCHEMAoggCHECKPOINTTABLEogg.ckptGGSCI(zlm2)2dbloginuseridoggpasswordoggGGSCI(zlm2)3addcheckpointtableogg.ckpt创建同步组rep1GGSCI(zlm2)4addreplicatrep1,exttrail./dirdat/rt,checkpointtableogg.ckptGGSCI(zlm2)5editparamsrep1replicatrep1ASSUMETARGETDEFSuseridogg,passwordoggreperrordefault,discarddiscardfile./dirrpt/rep1.dsc,append,megabytes5DDLmapsender.*,targetsender.*;3.1.4源端开启抽取组ext1，目标端开启同步组rep1-sourceGGSCI(zlm)1startmgrGGSCI(zlm)2startextractext1GGSCI(zlm)3infoall-targetGGSCI(zlm2)1startmgrGGSCI(zlm2)2startreplicatrep1GGSCI(zlm2)3infoall3.1.5测试DML同步insert-sourceSQLselect*fromtest2/ID-123SQLinsertintotestvalues(4);1rowcreated.SQLcommit;Commitcomplete.-targetSQLselect*fromtest;ID-1234注意：可以顺利同步，但是查看ext1和rep1的runtimemessages，都报了如下的错误：WARNINGOGG-00869NouniquekeyisdefinedfortableTEST.Allviablecolumnswillbeusedtorepresentthekey,butmaynotguaranteeuniqueness.KEYCOLSmaybeusedtodefinethekey.UsingthefollowingkeycolumnsforsourcetableSENDER.TEST:ID.该表没有主键，使用KEYCOLS代替，查询官方说明如下：OGG-00869:0Cause:Thespecifieddatabaseerroroccurred,butcanbeignored.Action:ContactOracleSupportonlyifaproblempersists.由于这只是个警告，况且对实际的应用也没什么影响，是可以忽略掉的。update(略).delete(略).3.1.6测试DDL同步(可选)测试DDL需要之前做过1.8步骤的全部配置(绿色部分)，并在相应参数中添加过相应DDL需要使用的参数(绿色部分)createtable-sourceSQLcreatetabletest2asselect*fromtest;Tablecreated.-targetSQLselect*fromtest2;ID-1235rowsselected.altertable-sourceSQLaltertabletest2add(namevarchar(10);Tablealtered.-targetSQLdesctest2;NameNull?Type-IDNUMBERNAMEVARCHAR2(10)tuncatetable-sourceSQLtruncatetabletest2;Tabletruncated.-targetSQLselect*fromtest2;norowsselecteddroptable-sourceSQLdroptabletest2;Tabledropped.-targetSQLdesctest2ERROR:ORA-04043:objecttest2doesnotexist至此，不设置本地trail和pump抽取进程的DML,DDL都可以顺利复制到目标端3.2双向复制双向复制，其实就是再配置一条目标端到源端的复制通道，分别在源端和目的端各配置一个extract和replicat进程就可以了3.2.1配置源端抽取组ext1GGSCI(zlm2)1addextractext1,tranlog,beginnowGGSCI(zlm2)2addrmttrail./dirdat/rt,extractext1GGSCI(zlm2)3editparamsext1extractext1useridogg,passwordoggrmthostzlm1,mgrport7809-注意主机名要修改一下，别的参数和之前的一致就可以了rmttrail./dirdat/rtddlincludemappedobjnamesender.*;tablesender.*;GGSCI(zlm2)4infoall3.2.2配置目标端同步组在目标端添加checkpoint表oggzlmgg11$./ggsciGGSCI(zlm)1editparams./GLOBALGGSCHEMAogg-DDL同步必须指定，DML同步不需要CHECKPOINTTABLEogg.ckptGGSCI(zlm)2dbloginuseridoggpasswordoggGGSCI(zlm)3addcheckpointtableogg.ckpt创建同步组rep1GGSCI(zlm)4addreplicatrep1,exttrail./dirdat/rt,checkpointtableogg.ckptGGSCI(zlm)5editparamsrep1replicatrep1ASSUMETARGETDEFSuseridogg,passwordoggreperrordefault,discarddiscardfile./dirrpt/rep1.dsc,append,megabytes5DDLmapsender.*,targetsender.*;3.2.3源端(原目标端)开启抽取组ext1，目标端(原源端)开启同步组rep1-sourceGGSCI(zlm2)1startmgrGGSCI(zlm2)2startextractext1GGSCI(zlm2)3infoall-targetGGSCI(zlm)1startmgrGGSCI(zlm)2startreplicatrep1GGSCI(zlm)3infoall具体测试内容和之前配置的单向复制一样，步骤略，DML，DDL都顺利复制到目标端，和之前一样，第一次同步的时候会比较慢，之后就快了，可能是第一次同步需要创建文件的缘故。另外，这里要特别注意，此时因为配置了双向复制，当两边各自的ext1和rep1进程都是running状态，如果此时源端和目标端对同一个表进行DML操作，如insert一条记录，那么这条记录会不断地在两端来回复制，永不停歇，如下所示：SQLinsertintotestvalues(6);1rowcreated.SQLselect*fromtest;ID-1234566666这条插入id=6的数据就会不断地插入test表，要解决这个问题，就需要在两端的任意一个extract进程ext1里添加一条TRANLOGOPTIONSEXCLUDEUSERogg就可以了四、使用PUMP抽取进程的单向复制(ORACLE-ORACLE)4.1添加本地trail路径，修改源端extract抽取组ext1参数GGSCI(zlm)1addextractext1,tranlog,beginnowGGSCI(zlm)2addexttrail./dirdat/lt,extractext1GGSCI(zlm)3editparamsext1extractext1useridogg,passwordogg-rmthostzlm2,mgrport7809-rmttrail./dirdat/rtexttrail./dirdat/ltddlincludemappedobjnamesender.*;tablesender.*;注释掉2行和远程连接有关的参数，再添加一行本地trail路径即可4.2添加pump抽取组epp1GGSCI(zlm)1addextractepp1,exttrailsource./dirdat/ltGGSCI(zlm)2addrmttrail./dirdata/rt,extractepp1GGSCI(zlm)3editparamsepp1extractepp1useridogg,passwordoggrmthostzlm2,mgrport7809rmttrail./dirdat/rtddlincludemappedobjnamesender.*;tablesender.*;相当于把原来配置在ext1里的远程配置参数搬过来了，因为现在是通过pump与远端连接，replicat复制组rep1参数不用修改，直接使用就可以4.2DML和DDL测试(参考3.1.5和3.1.6的步骤，方法一致)(略)注意：测试同步的时候要多开启一个pump进程，即源端运行ext1，epp1五、异构数据库之间的单向复制(ORACLE-MYSQL)5.1下载并安装介质OGGFORMYSQL:官网链接：/EPD/Download/get_form介质名称：OracleGoldenGateV.1forMySQL5.xonLinuxx86-64MYSQLDATABASE:官网链接：/EPD/Search/handle_go介质名称：MySQLDatabase5.6.13RPMforOracleLinux/RHEL5x86(64bit)5.1.1目标端安装OGG软件并配置环境因为MYSQL默认是以root用户登录并使用的，所以只要配置root的环境变量就可以了在root用户的.bash_profile文件中添加以下内容：exportGGHOME=/ggmysqlexportPATH=$PATH:$GGHOME拷贝OGGFORMYSQL介质到/rpm目录下并解压缩，然后再一次解压缩到/ggmysqlrootzlm2#cd/rpmrootzlm2rpm#lsV27808-01.ziprootzlm2rpm#unzipV27808-01.zipArchive:V27808-01.zipinflating:ggs_Linux_x64_MySQL_64bit.tarinflating:OGG_WinUnix_Rel_Notes_.1.pdfinflating:Oracle_GoldenGate__README.txtrootzlm2rpm#lsggs_Linux_x64_MySQL_64bit.tarOracle_GoldenGate__README.txtOGG_WinUnix_Rel_Notes_.1.pdfV27808-01.ziprootzlm2rpm#mkdir/ggmysqlrootzlm2rpm#cd/ggmysqlrootzlm2ggmysql#tar-xvf/rpm/ggs_Linux_x86_MySQL_32bit.tar(略).进入OGG界面，添加OGG目录rootzlm2ggmysql#./ggsciGGSCI(zlm2)1createsubdirs5.1.2目标端安装配置MYSQL数据库软件把从官网下载的MYSQL数据库软件也拷贝到/rpm下并解压rootzlm2rpm#lsrootzlm2rpm#lsggs_Linux_x64_MySQL_64bit.tarV27808-01.zipOGG_WinUnix_Rel_Notes_.1.pdfV39097-01.zipOracle_GoldenGate__README.txtrootzlm2rpm#unzipV39097-01.zipArchive:V39097-01.zipextracting:MySQL-shared-compat-advanced-5.6.13-1.rhel5.x86_64.rpmextracting:MySQL-devel-advanced-5.6.13-1.rhel5.x86_64.rpmextracting:MySQL-embedded-advanced-5.6.13-1.rhel5.x86_64.rpmextracting:MySQL-shared-advanced-5.6.13-1.rhel5.x86_64.rpmextracting:MySQL-server-advanced-5.6.13-1.rhel5.x86_64.rpmextracting:MySQL-test-advanced-5.6.13-1.rhel5.x86_64.rpmextracting:MySQL-client-advanced-5.6.13-1.rhel5.x86_64.rpmextracting:README.txtMYSQL数据库软件是一个rpm包，直接以root执行rpm命令进行安装如果提示libaio.so.1依赖包没有安装的错误，就要先安装依赖包libaio配置本地yum并执行：yuminstalllibaio，或者直接用rpm包安装rootzlm2rpm#rpm-ivhMySQL-server-advanced-5.6.13-1.rhel5.x86_64.rpmPreparing.#100%1:MySQL-server-advanced#100%2013-08-3022:46:210WarningTIMESTAMPwithimplicitDEFAULTvalueisdeprecated.Pleaseuse-explicit_defaults_for_timestampserveroption(seedocumentationformoredetails).2013-08-3022:46:215633NoteInnoDB:TheInnoDBmemoryheapisdisabled2013-08-3022:46:215633NoteInnoDB:Mutexesandrw_locksuseGCCatomicbuiltins2013-08-3022:46:215633NoteInnoDB:Compressedtablesusezlib1.2.32013-08-3022:46:215633NoteInnoDB:UsingLinuxnativeAIO2013-08-3022:46:215633NoteInnoDB:NotusingCPUcrc32instructions/usr/sbin/mysqld:Cantcreate/writetofile/tmp/ibgnuRhJ(Errcode:13-Permissiondenied)2013-08-3022:46:217fa1cc42c6f0InnoDB:Error:unabletocreatetemporaryfile;errno:132013-08-3022:46:215633ERRORPluginInnoDBinitfunctionreturnederror.2013-08-3022:46:215633ERRORPluginInnoDBregistrationasaSTORAGEENGINEfailed.2013-08-3022:46:215633ERRORUnknown/unsupportedstorageengine:InnoDB2013-08-3022:46:215633ERRORAborting2013-08-3022:46:215633NoteBinlogend2013-08-3022:46:215633Note/usr/sbin/mysqld:Shutdowncomplete显示完以上信息后就停住了，跳回了root的shell界面，查看了上面提示的内容，出现几个报错，第一次装MYSQL，不太明白是什么意思，网上查了一下说是需要配置f参数，直接在f里的mysqld下加一行explicit_defaults_for_timestamp/topics/390369836?page=1#post-393710642但是我觉得应该不是这个问题，现在软件肯定是还没有装好，查看了一下，相关的目录和配置文件都没有创建，可能是官网这个最新版server-advanced-5.6.13包兼容性有点问题，网上查了一下，也说最好别下官网最新版的MYSQL数据库安装包于是去MYSQL官网下载MySQLCommunityServer最新版官方链接：/downloads/mirr