ogg 配置复习(dmlddlsequnce)

dml 配置过程与测试概要在源端创建专用的表空间、schema、并授权。create tablespace goldengate datafile 'goldengate01.dbf' size 100m autoextend on;create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp;grant connect to goldengate;grant alter any table to goldengate;grant alter session to goldengate;grant create session to goldengate;grant flashback any table to goldengate;grant select any dictionary to goldengate;grant select any table to goldengate;grant resource to goldengate;grant select any transaction to goldengate;检查源端数据库是否在归档模式，强烈建议在归档模式。SQL> archive log listDatabase log modeArchive ModeAutomatic archivalEnabledArchive destination/archOldest online log sequence1Next log sequence to archive 3Current log sequence3端：添加附加日志来唯一标识一行记录，要在数据库级别打开最小开关。为了减少整个数据库添加附加日志，以及减少归档量，goldengate建议复制哪些对象，就添加哪些表的附加日志（我们到时候是拿hr用户下的表来实验）。检查：SQL> select supplemental_log_data_min from v$database;SUPPLEME——–NO打开：SQL> alter database add supplemental log data;Database altered.再次查看：SQL> select supplemental_log_data_min from v$database;SUPPLEME——–YES切换日志组，使附加日志开关生效。SQL> alter system switch logfile;System altered.解释：在正常情况下，oracle是用rowid来唯一标示一行记录的，但goldengate这里不够，需要打开附加日志。安装与配置GGSCI (gg2) 2> create subdirsGGSCI (oratest) 80> view params mgrport 7839 DYNAMICPORTLIST 7840-7850AUTOSTART EXTRACT *AUTORESTART EXTRACT *PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45启动配置的管理进程GGSCI (gg2) 4> start mgrManager started.select owner||'.'||table_name table_name,loggingfrom dba_tableswhere owner='SENDER';SQL> alter table SENDER.A1 logging;源端：配置hr下面表的表级附加日志。配置登陆用户。并检查日志是否添加成功。以goldengate这个schema登陆数据库。GGSCI (gg1) 10> dblogin userid goldengate, password goldengateSuccessfully logged into database.查看：GGSCI (gg1) 11> info trandata hr.*add extract ext_demo, tranlog, begin now, threads 1参数分析：tranlog：表示数据抓取的来源是数据库的redo数据。begin now：表示我们在启动这个抓取进程的就去抓取数据。threads 1：表示我们数据库有多少个redo threads，单实例基本上是1或者不设，rac就自己设置了，，配置过RAC的基本都了解添加源端的队列文件。GGSCI (gg1) 15> add extract ext_demo,tranlog, begin now, threads 1GGSCI (gg1) 15> add EXTTRAIL ./dirdat/r1, extract ext_demo,megabytes 100EXTTRAIL added./dirdat：表示trail文件的目录r1：trail文件的前缀extract ext_demo：值指定给那个进程用的（ext_demo）。megabytes 100：文件大小是100m编辑我们刚刚在源端配置的抓取进程的参数：GGSCI (gg1) 16> edit param ext_demoEXTRACT EXT_DEMOuserid goldengate,password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000DISCARDFILE ./dirrpt/ext_demo.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 3:00exttrail ./dirdat/r1,megabytes 100dynamicresolutionTRANLOGOPTIONS EXCLUDEUSER goldengateTRANLOGOPTIONS convertucs2clobs TRANLOGOPTIONS DBLOGREADERTABLE SENDER.*;setenv (ORACLE_SID=ogg)：指定oracle的sid，单机环境下可以不设或者bashprofile文件中设置了默认的，也可以不设，但是集群环境就要指定了，这里我们强烈建议指定。setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)：源端数据库的字符集，一定要跟数据库中匹配。userid goldengate,password goldengate：告诉抓取进程是通过这个用户登陆数据库的。exttrail ./dirdat/r1,megabytes 100：与我们上面配置抓取进程的设置要一致，路径和大小嘛。TABLE HR.*：最重要的，告诉抓取进程抓取的是哪些用户哪些表的数据配置datapump进程，将抓取数据传到目标主机。负责TCPIP通讯。GGSCI (gg1) 31> add extract dpe_demo, exttrailsource ./dirdat/r1EXTRACT added.输出：目标主机怎么写，也是定义datapumo进程的输出。GGSCI (gg1) 33> add rmttrail ./dirdat/t1,EXTRACT dpe_demo,MEGABYTES 100RMTTRAIL added.分析：exttrailsource：抓取进程的来源，因为这个进程不否则到数据库中取抓取，所以抓取的来源是通过抓取进程已经生成好的trail文件中的内容。17、配置datapump进程参数：GGSCI (oratest) 83> view params dpe_demoextract dpe_demodynamicresolutionpassthrurmthost 192.168.175.139, mgrport 7840, compressrmttrail ./dirdat/t1numfiles 5000TABLE SENDER.*;GGSCI (oratest) 84> info allProgramStatusGroupLag at Chkpt Time Since ChkptMANAGERRUNNINGEXTRACTRUNNINGDPE_DEMO 00:00:0000:00:00EXTRACTRUNNINGEXT_DEMO 00:00:0000:00:06GGSCI (oratest) 85> passthru：上面已经说过不需要连接数据库，所以用这个指令。rmthost：目标主机的IP地址，就是说我们的rmttrail进程发送到的主机。mgrport：目标主机管理进程的端口号，我们上面已经配置了。compress：在传输的过程中启用压缩。rmttrail：路径和文件与上面配置的一致。TABLE SENDER.*：上面说过，哪些表的redo信息传输。目标端：创建golengate软件使用的schema，并授权一些必要的特殊权限，注意与源端有所区别，保证我们能DML。create tablespace goldengate datafile 'goldengate01.dbf' size 100m autoextend on;create user goldengate identified by goldengate default tablespace goldengate temporary tablespace temp;grant connect to goldengate;grant alter any table to goldengate;grant alter session to goldengate;grant create session to goldengate;grant flashback any table to goldengate;grant select any dictionary to goldengate;grant select any table to goldengate;grant resource to goldengate;grant insert any table to goldengate;grant update any table to goldengate;grant delete any table to goldengate;grant create any index to goldengate;grant select any transaction to goldengate;目标端：为replicat进程创建checkpoint表：GGSCI (gg2) 1> dblogin userid goldengate,password goldengateSuccessfully logged into database.GGSCI (gg2) 2> add checkpointtable goldengate.rep_demo_ckptSuccessfully created checkpoint table goldengate.rep_demo_ckpt.21、配置目标端replicate进程：GGSCI (gg2) 3> add replicat rep_demo,exttrail ./dirdat/t1,checkpointtable goldengate.rep_demo_ckptREPLICAT added.分析：exttrail：表示这个进程获取数据的来源是什么地方，是我们源端rmttrail所设置过的。22、配置目标端replicate参数：GGSCI (oratest) 13> view params rep_demoREPLICAT rep_demoUSERID goldengate,PASSWORD goldengateREPORTCOUNT EVERY 30 MINUTES, RATEREPERROR DEFAULT, ABENDnumfiles 5000–HANDLECOLLISIONSassumetargetdefsDISCARDFILE ./dirrpt/rep_demo.dsc, APPEND, MEGABYTES 1000ALLOWNOOPUPDATESMAP SENDER.*,TARGET SENDER.*;分析：重要的地方与上面基本一致，有兴趣可以查查其他参数的信息。MAP：源端和目标端的对应。添加表格的时候先在目标添加，再元端，数据库可以自动同步！1.4 启用DDLSQL>@marker_setup.sql –提示输入目标schemaSQL>@ddl_setup.sql–提示输入目标schema,输入initialsetup最后输入yesSQL>@role_setup.sqlSQL>grant GGS_GGSUSER_ROLE to ddw; –不进行该步赋权后面起进程会报错SQL>@ddl_enable.sql–使触发器生效1.5 清除DDL 同步设置如果因为DDL 同步出现很多问题，最简单的方法就是卸载之后重建。安装目录下只提供了清除对象的脚本，可以如下操作：首先要求把所有的GG进程停掉，包括mgr进程SQL>@ddl_disable.sql –首先使DDL触发器失效SQL>@ddl_remove.sqlSQL>@marker_remove.sqlalter table OGG1.A1 logging;info trandata OGG1.*add extract ext2,tranlog, begin now, threads 1add EXTTRAIL ./dirdat/r3, extract ext2,megabytes 100EXTRACT ext2userid goldengate,password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000DISCARDFILE ./dirrpt/ext_demo1.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 3:00exttrail ./dirdat/r2,megabytes 100ddl include allTABLE OGG1.*;add extract dpe_ext2, exttrailsource ./dirdat/r3add rmttrail ./dirdat/t3,EXTRACT dpe_ext2,MEGABYTES 100extract dpe_ext2dynamicresolutionpassthrurmthost 192.168.175.139, mgrport 7840, compressrmttrail ./dirdat/t3numfiles 5000TABLE OGG1.*;add replicat rep_ext2,exttrail ./dirdat/t3,checkpointtable goldengate.rep_demo_ckptREPLICAT rep_ext2USERID goldengate,PASSWORD goldengateREPORTCOUNT EVERY 30 MINUTES, RATEREPERROR DEFAULT, ABENDnumfiles 5000–HANDLECOLLISIONSassumetargetdefsDISCARDFILE ./dirrpt/rep_rep2.dsc, APPEND, MEGABYTES 1000ALLOWNOOPUPDATESMAP OGG1.*,TARGET OGG1.*;启用序列：目标端：SQL> @sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:goldengateSetting schema name to GOLDENGATEUPDATE_SEQUENCE STATUS:Line/pos Error———- —————————————————————–No errors No errorsGETSEQFLUSHLine/pos Error———- —————————————————————–No errors No errorsSEQTRACELine/pos Error———- —————————————————————–No errors No errorsREPLICATE_SEQUENCE STATUS:Line/pos Error———- —————————————————————–No errors No errorsSTATUS OF SEQUENCE SUPPORT————————————————————–SUCCESSFUL installation of Oracle Sequence Replication supportSQL> GRANT EXECUTE on goldengate.replicateSequence to ogg1;Grant succeeded.SQL> 目标端：SQL> @sequence.sqlPlease enter the name of a schema for the GoldenGate database objects:goldengateSetting schema name to GOLDENGATEUPDATE_SEQUENCE STATUS:Line/pos Error———- —————————————————————–No errors No errorsGETSEQFLUSHLine/pos Error———- —————————————————————–No errors No errorsSEQTRACELine/pos Error———- —————————————————————–No errors No errorsREPLICATE_SEQUENCE STATUS:Line/pos Error———- —————————————————————–No errors No errorsSTATUS OF SEQUENCE SUPPORT————————————————————–SUCCESSFUL installation of Oracle Sequence Replication supportSQL> GRANT EXECUTE on goldengate.replicateSequence to ogg1;Grant succeeded.SQL> 复制进程：传输进程：GGSCI (oratest) 41> edit params DPE_EXT2extract dpe_ext2dynamicresolutionpassthrurmthost 192.168.175.139, mgrport 7840, compressrmttrail ./dirdat/t3numfiles 5000TABLE OGG1.*;SEQUENCE OGG1.*;~"dirprm/dpe_ext2.prm" 8L, 158C writtenGGSCI (oratest) 42> info allProgramStatusGroupLag at Chkpt Time Since ChkptMANAGERRUNNINGEXTRACTRUNNINGDPE_DEMO 00:00:0000:00:00EXTRACTRUNNINGDPE_EXT2 00:00:0000:00:04EXTRACTRUNNINGEXT100:00:0000:00:09EXTRACTRUNNINGEXT200:00:0000:00:06EXTRACTRUNNINGEXT_DEMO 00:00:0000:00:09GGSCI (oratest) 43> stop DPE_EXT2Sending STOP request to EXTRACT DPE_EXT2 …Request processed.GGSCI (oratest) 44> start DPE_EXT2Sending START request to MANAGER …EXTRACT DPE_EXT2 startingGGSCI (oratest) 45> info allProgramStatusGroupLag at Chkpt Time Since ChkptMANAGERRUNNINGEXTRACTRUNNINGDPE_DEMO 00:00:0000:00:00EXTRACTRUNNINGDPE_EXT2 00:00:0000:00:05EXTRACTRUNNINGEXT100:00:0000:00:00EXTRACTRUNNINGEXT200:00:0000:00:03EXTRACTRUNNINGEXT_DEMO 00:00:0000:00:09GGSCI (oratest) 46> 抽取进程：GGSCI (oratest) 48> edit params EXT2EXTRACT ext2userid goldengate,password goldengateREPORTCOUNT EVERY 1 MINUTES, RATEnumfiles 5000DISCARDFILE ./dirrpt/ext2.dsc,APPEND,MEGABYTES 1000DISCARDROLLOVER AT 3:00exttrail ./dirdat/r3,megabytes 100ddl include allTRANLOGOPTIONS DBLOGREADERTABLE OGG1.*;SEQUENCE OGG1.*;~"dirprm/ext2.prm" 11L, 284C writtenGGSCI (oratest) 49> stop ext2Sending STOP request to EXTRACT EXT2 …Request processed.GGSCI (oratest) 50> start ext2Sending START request to MANAGER …EXTRACT EXT2 startingGGSCI (oratest) 51> info allProgramStatusGroupLag at Chkpt Time Since ChkptMANAGERRUNNINGEXTRACTRUNNINGDPE_DEMO 00:00:0000:00:07EXTRACTRUNNINGDPE_EXT2 00:00:0000:00:02EXTRACTRUNNINGEXT100:00:0000:00:06EXTRACTRUNNINGEXT200:00:0300:00:07EXTRACTRUNNINGEXT_DEMO 00:00:0000:00:05GGSCI (oratest) 52> 提前修改该文件：修改文件：GGSCI (oratest) 79> EDIT PARAMS ./GLOBALSGGSCHEMA goldengate~FLUSH SEQUENCE ogg1.*重启更加有效果！

如你想要拥有完美无暇的友谊，可能一辈子找不到朋友

相关文章：

你感兴趣的文章：

标签云：