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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 19C OGG基礎運維-03DML操作同步Oracle運維3D
- Oracle 19C OGG基礎運維-09OGG-15121錯誤Oracle運維
- Oracle 19C OGG基礎運維-04DML同步常見問題Oracle運維
- Oracle 19C OGG基礎運維-01環境準備Oracle運維
- Oracle 19C OGG基礎運維-06增加複製表Oracle運維
- Oracle 19C OGG基礎運維-08Error code [942]Oracle運維Error
- Oracle 19C OGG基礎運維-02資料初始化Oracle運維
- Oracle 19C OGG基礎運維-07減少複製表Oracle運維
- Oracle 19C Data Guard基礎運維-02 Switchovers(物理)Oracle運維
- Oracle 19C Data Guard基礎運維-03 Failovers(物理)Oracle運維AI
- Oracle 19C Data Guard基礎運維-06 PROTECTION MODEOracle運維
- ORACLE基礎運維命令操作手冊Oracle運維
- Oracle 19C Data Guard基礎運維-08 DML重定向Oracle運維
- Oracle 19C Data Guard基礎運維-04 Failovers疑問？Oracle運維AI
- Oracle 19C Data Guard基礎運維-05Failovers (GAP)Oracle運維AI
- Oracle 19C Data Guard基礎運維-01安裝物理standbyOracle運維
- ORACLE OGG運維及日常監控Oracle運維
- Oracle 19C Data Guard基礎運維-07 failover後閃回恢復dg架構Oracle運維AI架構
- Oracle OGG日常維護Oracle
- ogg for oracle 19c 非cdb安裝配置Oracle
- Oracle分割槽表基礎運維-08Coalescing PartitionsOracle運維
- 基於OGG Datahub外掛將Oracle資料同步上雲Oracle
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- linux運維基礎2Linux運維
- ogg 同步pg資料到oracle--步驟Oracle
- MySQL基礎運維——percona-toolkit運維工具MySql運維
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- 智慧運維基礎－運維知識庫之ETL運維
- 《前端運維》一、Linux基礎--基礎命令（1）前端運維Linux
- Glance基礎服務運維運維
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- Oracle+Ogg 歸檔丟失 重新導資料建立ogg同步步驟Oracle
- Flutter Bloc 03 - 基礎物件 同步、非同步 await yield 操作FlutterBloC物件非同步AI
- Oracle 11g RAC到單例項OGG同步Oracle單例
- 使用ogg 從oracle 同步mysql遇到問題記錄OracleMySql