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"，共同學習，共同成長！！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2685439/，如需轉載，請註明出處，否則將追究法律責任。