Oracle 19C OGG基础运维-07减少复制表

Oracle 19C OGG基础运维-07减少复制表  场景一：源端extract进程和目标端replicat进程参数文件中通过*来匹配所有表。 场景二：源端extract进程和目标端replicat进程参数文件中没有通过*来匹配所有表，而是指定了固定的表。 场景一：源端extract进程和目标端replicat进程参数文件中通过*来匹配所有表。 例如： 源端： extract进程 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 87> edit param ext_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 88> view param ext_01 extract ext_01 userid c##ogg@cjcdb,password oracle GETUPDATEBEFORES GETTRUNCATES BR BRINTERVAL 2H CACHEMGR CACHESIZE 500MB WARNLONGTRANS 2H,CHECKINTERVAL 5M NUMFILES 4000 EOFDELAYCSECS 10 LOGALLSUPCOLS TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2) discardfile ./dirrpt/jcms.dsc,append, megabytes 200 exttrail ./dirdat/ex DDL INCLUDE ALL DDLOPTIONS ADDTRANDATA, REPORT TABLE cjcpdb.cjc.*; ---TABLE cjcpdb.cjc.emp; ---TABLE cjcpdb.cjc.dept; ---TABLE cjcpdb.cjc.bonus; ---TABLE cjcpdb.cjc.salgrade; ---TABLE cjcpdb.cjc.dummy; ---TABLE cjcpdb.cjc.t1; pump进程 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 89> edit param pump_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 90> view param pump_01 extract pump_01 dynamicresolution setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid c##ogg@cjcdb,password oracle rmthost 192.168.31.100,mgrport 7809,compress rmttrail ./dirdat/dp TABLE cjcpdb.cjc.*; ---TABLE cjcpdb.cjc.emp; ---TABLE cjcpdb.cjc.dept; ---TABLE cjcpdb.cjc.bonus; ---TABLE cjcpdb.cjc.salgrade; ---TABLE cjcpdb.cjc.dummy; ---TABLE cjcpdb.cjc.t1; 目标端： replicat进程 GGSCI (cjcos02) 48> edit param rep_01 GGSCI (cjcos02) 49> view param rep_01 replicat rep_01 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg@chenpdb,password oracle assumetargetdefs reperror default,discard discardfile ./dirrpt/replzl.dsc,append,megabytes 50 DDL INCLUDE MAPPED DDLOPTIONS REPORT DDLERROR 942 IGNORE MAP cjcpdb.cjc.*, TARGET chenpdb.chen.*; ---MAP cjcpdb.cjc.emp, TARGET chenpdb.chen.emp; ---MAP cjcpdb.cjc.dept, TARGET chenpdb.chen.dept; ---MAP cjcpdb.cjc.bonus, TARGET chenpdb.chen.bonus; ---MAP cjcpdb.cjc.salgrade, TARGET chenpdb.chen.salgrade; ---MAP cjcpdb.cjc.dummy, TARGET chenpdb.chen.dummy; ---MAP cjcpdb.cjc.t1, TARGET chenpdb.chen.t1; 减少复制表操作： 1 在源端修改extract进程的参数，排除不复制的表。 例如 不复制t1表 源端： extract参数 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 97> edit param ext_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 98> view param ext_01 extract ext_01 userid c##ogg@cjcdb,password oracle GETUPDATEBEFORES GETTRUNCATES BR BRINTERVAL 2H CACHEMGR CACHESIZE 500MB WARNLONGTRANS 2H,CHECKINTERVAL 5M NUMFILES 4000 EOFDELAYCSECS 10 LOGALLSUPCOLS TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2) discardfile ./dirrpt/jcms.dsc,append, megabytes 200 exttrail ./dirdat/ex DDL INCLUDE ALL DDLOPTIONS ADDTRANDATA, REPORT tableexclude cjcpdb.cjc.t1; TABLE cjcpdb.cjc.*; ---TABLE cjcpdb.cjc.emp; ---TABLE cjcpdb.cjc.dept; ---TABLE cjcpdb.cjc.bonus; ---TABLE cjcpdb.cjc.salgrade; ---TABLE cjcpdb.cjc.dummy; ---TABLE cjcpdb.cjc.t1; pump参数 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 99> edit param pump_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 100> view param pump_01 extract pump_01 dynamicresolution setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid c##ogg@cjcdb,password oracle rmthost 192.168.31.100,mgrport 7809,compress rmttrail ./dirdat/dp tableexclude cjcpdb.cjc.t1; TABLE cjcpdb.cjc.*; ---TABLE cjcpdb.cjc.emp; ---TABLE cjcpdb.cjc.dept; ---TABLE cjcpdb.cjc.bonus; ---TABLE cjcpdb.cjc.salgrade; ---TABLE cjcpdb.cjc.dummy; ---TABLE cjcpdb.cjc.t1; 2 在目标端修改extract进程的参数，排除不复制的表。 目标端： 在map语句上一行添加：mapexclude cjcpdb.cjc.t1; GGSCI (cjcos02) 53> edit param rep_01 GGSCI (cjcos02) 54> view param rep_01 replicat rep_01 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg@chenpdb,password oracle assumetargetdefs reperror default,discard discardfile ./dirrpt/replzl.dsc,append,megabytes 50 DDL INCLUDE MAPPED DDLOPTIONS REPORT DDLERROR 942 IGNORE mapexclude cjcpdb.cjc.t1; MAP cjcpdb.cjc.*, TARGET chenpdb.chen.*; ---MAP cjcpdb.cjc.emp, TARGET chenpdb.chen.emp; ---MAP cjcpdb.cjc.dept, TARGET chenpdb.chen.dept; ---MAP cjcpdb.cjc.bonus, TARGET chenpdb.chen.bonus; ---MAP cjcpdb.cjc.salgrade, TARGET chenpdb.chen.salgrade; ---MAP cjcpdb.cjc.dummy, TARGET chenpdb.chen.dummy; ---MAP cjcpdb.cjc.t1, TARGET chenpdb.chen.t1; 3 在源端系统上首先验证所需归档日志存在 查看指定进程详细信息 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 79> Info ext_01, detail 查看进程的检查点信息 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 80> Info ext_01, showch 4 重启extract和replicat进程 源端:停止 stop pump_01 stop ext_01  目标端:停止 stop rep_01  源端:启动 start pump_01  start ext_01  目标端:启动 start rep_01  5 更新t1表，查看数据库是否同步 源端：   SQL> conn cjc/cjc@cjcpdb  SQL> insert into t1 values(80,'CHEN','AAA'); SQL> insert into t1 values(90,'JCH','BBB'); SQL> commit;  SQL> select * from t1;     DEPTNO DNAME   LOC ---------- -------------- ------------- 10 ACCOUNTING   NEW YORK 20 RESEARCH   DALLAS 30 SALES   CHICAGO 40 OPERATIONS   BOSTON 50 DBA   DUNHUA 60 CJC   DAPUCHAI 70 CHEN    AAA 80 CHEN    AAA 90 JCH   BBB 9 rows selected. 目标端：t1表数据已不在同步 SQL> select * from t1;     DEPTNO DNAME   LOC ---------- -------------- ------------- 10 ACCOUNTING   NEW YORK 20 RESEARCH   DALLAS 30 SALES   CHICAGO 40 OPERATIONS   BOSTON 50 DBA   DUNHUA 60 CJC   DAPUCHAI 70 CHEN    AAA 7 rows selected. 场景二：源端extract进程和目标端replicat进程参数文件中没有通过*来匹配所有表，而是指定了固定的表。 例如： 源端： extract参数 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 73> edit param ext_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 74> view param ext_01 extract ext_01 userid c##ogg@cjcdb,password oracle GETUPDATEBEFORES GETTRUNCATES BR BRINTERVAL 2H CACHEMGR CACHESIZE 500MB WARNLONGTRANS 2H,CHECKINTERVAL 5M NUMFILES 4000 EOFDELAYCSECS 10 LOGALLSUPCOLS TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2) discardfile ./dirrpt/jcms.dsc,append, megabytes 200 exttrail ./dirdat/ex DDL INCLUDE ALL DDLOPTIONS ADDTRANDATA, REPORT ---TABLE cjcpdb.cjc.*; TABLE cjcpdb.cjc.emp; TABLE cjcpdb.cjc.dept; TABLE cjcpdb.cjc.bonus; TABLE cjcpdb.cjc.salgrade; TABLE cjcpdb.cjc.dummy; TABLE cjcpdb.cjc.t1; pump参数 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 75> edit param pump_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 76> view param pump_01 extract pump_01 dynamicresolution setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid c##ogg@cjcdb,password oracle rmthost 192.168.31.100,mgrport 7809,compress rmttrail ./dirdat/dp ---TABLE cjcpdb.cjc.*; TABLE cjcpdb.cjc.emp; TABLE cjcpdb.cjc.dept; TABLE cjcpdb.cjc.bonus; TABLE cjcpdb.cjc.salgrade; TABLE cjcpdb.cjc.dummy; TABLE cjcpdb.cjc.t1; 目标端： replicat参数 GGSCI (cjcos02) 43> edit param rep_01 GGSCI (cjcos02) 44> view param rep_01 replicat rep_01 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg@chenpdb,password oracle assumetargetdefs reperror default,discard discardfile ./dirrpt/replzl.dsc,append,megabytes 50 DDL INCLUDE MAPPED DDLOPTIONS REPORT DDLERROR 942 IGNORE ---MAP cjcpdb.cjc.*, TARGET chenpdb.chen.*; MAP cjcpdb.cjc.emp, TARGET chenpdb.chen.emp; MAP cjcpdb.cjc.dept, TARGET chenpdb.chen.dept; MAP cjcpdb.cjc.bonus, TARGET chenpdb.chen.bonus; MAP cjcpdb.cjc.salgrade, TARGET chenpdb.chen.salgrade; MAP cjcpdb.cjc.dummy, TARGET chenpdb.chen.dummy; MAP cjcpdb.cjc.t1, TARGET chenpdb.chen.t1; 减少复制表操作： 1 在源端系统上首先验证所需归档日志存在 查看 ext_01进程详细信息 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 79> Info ext_01, detail 查看 ext_01进程的检查点信息 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 80> Info ext_01, showch 2 在源端停止extract进程 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 81> stop ext_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 81> stop pump_01 3 在目标端停止replicat进程进程 GGSCI (cjcos02) 45> stop rep_01 4 源端：直接注释掉该表所在table行即可 例如 ： 源端： extract进程 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 73> edit param ext_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 74> view param ext_01 extract ext_01 userid c##ogg@cjcdb,password oracle GETUPDATEBEFORES GETTRUNCATES BR BRINTERVAL 2H CACHEMGR CACHESIZE 500MB WARNLONGTRANS 2H,CHECKINTERVAL 5M NUMFILES 4000 EOFDELAYCSECS 10 LOGALLSUPCOLS TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2) discardfile ./dirrpt/jcms.dsc,append, megabytes 200 exttrail ./dirdat/ex DDL INCLUDE ALL DDLOPTIONS ADDTRANDATA, REPORT ---TABLE cjcpdb.cjc.*; TABLE cjcpdb.cjc.emp; TABLE cjcpdb.cjc.dept; TABLE cjcpdb.cjc.bonus; TABLE cjcpdb.cjc.salgrade; TABLE cjcpdb.cjc.dummy; ---TABLE cjcpdb.cjc.t1; pump进程  GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 75> edit param pump_01 GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 76> view param pump_01 extract pump_01 dynamicresolution setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid c##ogg@cjcdb,password oracle rmthost 192.168.31.100,mgrport 7809,compress rmttrail ./dirdat/dp ---TABLE cjcpdb.cjc.*; TABLE cjcpdb.cjc.emp; TABLE cjcpdb.cjc.dept; TABLE cjcpdb.cjc.bonus; TABLE cjcpdb.cjc.salgrade; TABLE cjcpdb.cjc.dummy; ---TABLE cjcpdb.cjc.t1; 5 目标端：直接注释掉该表所在MAP行即可 例如：  目标端： replicat进程 GGSCI (cjcos02) 43> edit param rep_01 GGSCI (cjcos02) 44> view param rep_01 replicat rep_01 setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8) userid ogg@chenpdb,password oracle assumetargetdefs reperror default,discard discardfile ./dirrpt/replzl.dsc,append,megabytes 50 DDL INCLUDE MAPPED DDLOPTIONS REPORT DDLERROR 942 IGNORE ---MAP cjcpdb.cjc.*, TARGET chenpdb.chen.*; MAP cjcpdb.cjc.emp, TARGET chenpdb.chen.emp; MAP cjcpdb.cjc.dept, TARGET chenpdb.chen.dept; MAP cjcpdb.cjc.bonus, TARGET chenpdb.chen.bonus; MAP cjcpdb.cjc.salgrade, TARGET chenpdb.chen.salgrade; MAP cjcpdb.cjc.dummy, TARGET chenpdb.chen.dummy; ---MAP cjcpdb.cjc.t1, TARGET chenpdb.chen.t1; 6 目标端启动replicat进程  start rep_01  7 源端启动extract进程 start pump_01  start ext_01  8 更新t1表，查看数据库是否同步 源端：  SQL> conn cjc/cjc@cjcpdb  SQL> insert into t1 values(70,'CHEN','AAA'); SQL> insert into t1 values(80,'JCH','BBB'); SQL> commit;  SQL> select * from t1;      DEPTNO DNAME   LOC ---------- -------------- ------------- 10 ACCOUNTING   NEW YORK 20 RESEARCH   DALLAS 30 SALES   CHICAGO 40 OPERATIONS   BOSTON 50 DBA   DUNHUA 60 CJC   DAPUCHAI 70 CHEN    AAA 80 JCH   BBB 8 rows selected. 目标端：t1表不在同步 SQL> select * from t1;      DEPTNO DNAME   LOC ---------- -------------- ------------- 10 ACCOUNTING   NEW YORK 20 RESEARCH   DALLAS 30 SALES   CHICAGO 40 OPERATIONS   BOSTON 50 DBA   DUNHUA 60 CJC   DAPUCHAI 6 rows selected. 欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！