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"，共同学习，共同成长！！！