Oracle 19C OGG基础运维-05DDL操作同步

Oracle 19C OGG基础运维-05DDL操作同步 一 源端和目标端：停止同步进程 二 源端：修改抽取进程ext_01 三 目标端：修改应用进程rep_0l 四 启动进程  五 DDL操作同步测试 六 查看日志 七 常见问题 一 源端和目标端：停止同步进程 源端： stop pump_01  stop ext_01 stop mgr  目标端： stop rep_01 stop mgr 二 源端：修改抽取进程ext_01 红色部分为新添加DDL相关部分 GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 2> edit 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 DDL INCLUDE ALL DDLOPTIONS ADDTRANDATA, REPORT ---添加DDL  TABLE cjcpdb.cjc.emp; TABLE cjcpdb.cjc.dept; TABLE cjcpdb.cjc.bonus; TABLE cjcpdb.cjc.salgrade; TABLE cjcpdb.cjc.dummy; 三 目标端：修改应用进程rep_0l 红色部分为新添加DDL相关部分 GGSCI (cjcos02 as chen@chendb/CHENPDB) 44> view param rep_0l 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  DDL INCLUDE MAPPED DDLOPTIONS REPORT ---添加DDL  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; 四 启动进程  源端：  start mgr  start extract ext_01  start extract pump_01  目标端：  start mgr  start replicat rep_01  五 DDL操作同步测试 源端： DDL操作1：创建索引 SQL> conn cjc/cjc@cjcpdb Connected. SQL> select index_name from user_indexes; no rows selected SQL> create index i_dept_01 on dept(LOC); Index created. 目标端： SQL> conn chen/chen@chenpdb Connected. SQL> select index_name from user_indexes; INDEX_NAME -------------------------------------------------------------------------------- I_DEPT_01 DDL操作2：增加列  源端：  SQL> conn cjc/cjc@cjcpdb SQL> alter table dept add col01 varchar2(200); SQL> desc dept  Name    Null?    Type  ----------------------------------------- -------- ----------------------------  DEPTNO      NUMBER(2)  DNAME     VARCHAR2(14)  LOC     VARCHAR2(13)  COL01     VARCHAR2(200) 目标端： SQL> conn chen/chen@chenpdb  SQL> desc dept  Name    Null?    Type  ----------------------------------------- -------- ----------------------------  DEPTNO      NUMBER(2)  DNAME     VARCHAR2(14)  LOC     VARCHAR2(13)  COL01     VARCHAR2(200) DDL操作3：更改字段长度 源端： SQL> conn cjc/cjc@cjcpdb  SQL> alter table dept modify (col01 varchar2(300)); 目标端： SQL> conn chen/chen@chenpdb  SQL> desc dept  Name    Null?    Type  ----------------------------------------- -------- ----------------------------  DEPTNO      NUMBER(2)  DNAME     VARCHAR2(14)  LOC     VARCHAR2(13)  COL01     VARCHAR2(300) DDL操作4：删除字段  源端： SQL> conn cjc/cjc@cjcpdb  SQL> alter table dept drop column col01; 目标端： SQL> conn chen/chen@chenpdb  SQL> desc dept  Name    Null?    Type  ----------------------------------------- -------- ----------------------------  DEPTNO      NUMBER(2)  DNAME     VARCHAR2(14)  LOC     VARCHAR2(13) DDL操作5：清空表 源端： SQL> conn cjc/cjc@cjcpdb  ---create table dept_bak as select * from dept;  SQL> truncate table dept;  SQL> select * from dept;  no rows selected 目标端： SQL> conn chen/chen@chenpdb  SQL> select * from dept;  no rows selected 恢复dept数据 (DML操作) 源端： SQL> conn cjc/cjc@cjcpdb  SQL> insert into dept select * from dept_bak; SQL> commit;  目标端： SQL> select * from dept;     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. 六 查看日志 1 create index 对应日志： 源端：  [oracle@cjcos01 ogg]$ tail -f ggserr.log  2020-04-10T11:19:21.912+0800  INFO    OGG-01971  Oracle GoldenGate Capture for Oracle, ext_01.prm:  The previous message, 'INFO OGG-00497', repeated 1 times. 2020-04-10T11:19:21.912+0800  INFO    OGG-01487  Oracle GoldenGate Capture for Oracle, ext_01.prm:  DDL found, operation [create index i_dept_01 on dept(LOC) (size 35)], start SCN [3595308], commit SCN [3595317] instance [ (1)], DDL seqno [0], marker seqno [0]. 2020-04-10T11:19:21.912+0800  INFO    OGG-10451  Oracle GoldenGate Capture for Oracle, ext_01.prm:  DDL operation included [INCLUDE ALL], optype [CREATE], objtype [INDEX], catalog "CJCPDB", objowner "CJC", objname "DEPT". 2020-04-10T11:19:22.006+0800  INFO    OGG-00497  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Writing DDL operation to extract trail file. 目标端：   [oracle@cjcos02 ogg]$ tail -f ggserr.log  2020-04-10T11:19:24.767+0800  INFO    OGG-00482  Oracle GoldenGate Delivery for Oracle, rep_01.prm:  DDL found, operation [create index i_dept_01 on dept(LOC) (size 35)]. 2020-04-10T11:19:24.977+0800  INFO    OGG-00489  Oracle GoldenGate Delivery for Oracle, rep_01.prm:  DDL is of mapped scope, after mapping new operation [create index i_dept_01 on "CHEN"."DEPT"(LOC) (size 44)]. 2020-04-10T11:19:24.977+0800  INFO    OGG-10451  Oracle GoldenGate Delivery for Oracle, rep_01.prm:  DDL operation included [INCLUDE MAPPED], optype [CREATE], objtype [INDEX], catalog "CJCPDB", objowner "CHEN", objname "DEPT". 2020-04-10T11:19:26.289+0800  INFO    OGG-01407  Oracle GoldenGate Delivery for Oracle, rep_01.prm:  Setting current schema for DDL operation to CHEN. 2020-04-10T11:19:26.296+0800  INFO    OGG-00484  Oracle GoldenGate Delivery for Oracle, rep_01.prm:  Executing DDL operation. 2020-04-10T11:19:27.919+0800  INFO    OGG-00483  Oracle GoldenGate Delivery for Oracle, rep_01.prm:  DDL operation successful. 2020-04-10T11:19:27.940+0800  INFO    OGG-01408  Oracle GoldenGate Delivery for Oracle, rep_01.prm:  Restoring current schema for DDL operation to OGG. 2 源端：增加列对应日志 [oracle@cjcos01 ogg]$ tail -f ggserr.log  2020-04-10T11:28:46.670+0800  INFO    OGG-01487  Oracle GoldenGate Capture for Oracle, ext_01.prm:  DDL found, operation [alter table dept add col01 varchar2(200) (size 40)], start SCN [3597146], commit SCN [3597171] instance [ (1)], DDL seqno [0], marker seqno [0]. 2020-04-10T11:28:46.727+0800  INFO    OGG-10451  Oracle GoldenGate Capture for Oracle, ext_01.prm:  DDL operation included [INCLUDE ALL], optype [ALTER], objtype [TABLE], catalog "CJCPDB", objowner "CJC", objname "DEPT". 2020-04-10T11:28:46.855+0800  INFO    OGG-00497  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Writing DDL operation to extract trail file. 3 源端truncate操作对应日志 [oracle@cjcos01 ogg]$ tail -f ggserr.log  2020-04-10T12:53:48.884+0800  INFO    OGG-10458  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Metadata not invalidated for "CJC".DEPT because of TRUNCATE, catalog CJCPDB. 2020-04-10T12:53:48.884+0800  INFO    OGG-01487  Oracle GoldenGate Capture for Oracle, ext_01.prm:  DDL found, operation [truncate table dept (size 19)], start SCN [3609971], commit SCN [3609995] instance [ (1)], DDL seqno [0], marker seqno [0]. 2020-04-10T12:53:48.884+0800  INFO    OGG-10451  Oracle GoldenGate Capture for Oracle, ext_01.prm:  DDL operation included [INCLUDE ALL], optype [TRUNCATE], objtype [TABLE], catalog "CJCPDB", objowner "CJC", objname "DEPT". 2020-04-10T12:53:48.921+0800  INFO    OGG-10458  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Metadata not invalidated for "CJC".DEPT because of TRUNCATE, catalog CJCPDB. 2020-04-10T12:53:48.921+0800  INFO    OGG-00497  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Writing DDL operation to extract trail file. 4 源端恢复数据 SQL> insert into dept select * from dept_bak; SQL> commit;  日志： 2020-04-10T12:55:26.856+0800  INFO    OGG-06507  Oracle GoldenGate Capture for Oracle, ext_01.prm:  MAP (TABLE) resolved (entry cjcpdb.cjc.dept): TABLE "CJCPDB"."CJC"."DEPT". 2020-04-10T12:55:26.935+0800  WARNING OGG-02180  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Table CJCPDB.CJC.DEPT will use legacy trail format to support parameter LOGALLSUPCOLS. 2020-04-10T12:55:27.748+0800  WARNING OGG-06439  Oracle GoldenGate Capture for Oracle, ext_01.prm:  No unique key is defined for table DEPT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. 2020-04-10T12:55:27.748+0800  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Using the following key columns for source table CJCPDB.CJC.DEPT: DEPTNO, DNAME, LOC. 2020-04-10T12:55:28.112+0800  INFO    OGG-06507  Oracle GoldenGate Capture for Oracle, ext_01.prm:  MAP (TABLE) resolved (entry cjcpdb.cjc.dept): TABLE "CJCPDB"."CJC"."DEPT". 2020-04-10T12:55:28.112+0800  WARNING OGG-02180  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Table CJCPDB.CJC.DEPT will use legacy trail format to support parameter LOGALLSUPCOLS. 2020-04-10T12:55:28.112+0800  WARNING OGG-06439  Oracle GoldenGate Capture for Oracle, ext_01.prm:  No unique key is defined for table DEPT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key. 2020-04-10T12:55:28.112+0800  INFO    OGG-06509  Oracle GoldenGate Capture for Oracle, ext_01.prm:  Using the following key columns for source table CJCPDB.CJC.DEPT: DEPTNO, DNAME, LOC. 2020-04-10T12:55:28.955+0800  INFO    OGG-02263  Oracle GoldenGate Capture for Oracle, pump_01.prm:  Passthru MAP (TABLE) resolved (entry cjcpdb.cjc.dept): TABLE "CJCPDB"."CJC"."DEPT". 七 常见问题 问题一： 11g之前版本配置DDL需要关闭recyclebin show parameter recyclebin  alter system set recyclebin=off scope=both;  11g以后版本不在需要关闭recyclebin 问题二： 源端执行DDL相关脚本 SQL> @marker_setup.sql SQL> @ddl_setup.sql SQL> @role_setup.sql SQL> @ddl_enable.sql SQL> @marker_status.sql 在19C环境下，只有marker_setup.sql执行成功，后面脚本不支持cdb模式，测试不执行也可以进行DDL同步。 在执行脚本是碰到如下问题： 1 执行marker_setup.sql脚本hang住1小时没结果 SQL> @/ogg/marker_setup.sql 解决方案：  先切换到ogg目录，在执行脚本 [oracle@cjcos01 ~]$ cd /ogg [oracle@cjcos01 ogg]$ sqlplus c##ogg/oracle  SQL> @marker_setup.sql 2 ddl_setup.sql脚本执行的用户需要有sysdba权限 [oracle@cjcos01 ogg]$ sqlplus c##ogg/oracle  SQL> @ddl_setup.sql ERROR at line 1: ORA-20783: Oracle GoldenGate DDL Replication setup: *** Currently logged user does not have SYSDBA privileges, or not logged AS SYSDBA! *** Please login as SYSDBA. ORA-06512: at line 14 3 ddl_setup.sql不在支持多租户环境 [oracle@cjcos01 ogg]$ sqlplus / as sysdba SQL> @ddl_setup.sql ORA-20783: Oracle GoldenGate DDL Replication setup: *** Trigger based DDL Replication is not supported on a Multitenant database. ORA-06512: at line 14 对于源端cdb模式，goldengate官档明确说明只支持integrated capture,而对于integrated capture 有native ddl模式可用。 欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！