Oracle 19C OGG基礎運維-03DML操作同步
Oracle 19C OGG基礎運維-03DML操作同步
一 源端和目標端配置OGG檢查點
二 源端和目標端配置MGR
三 源端：配置抽取程式ext_01
四 源端：配置投遞程式pump_01
五 目標端：配置應用程式rep_0l
六 啟動程式
七 DML(insert,update,delete)操作同步測試
源端：補充日誌
GGSCI (cjcos01) 1> dblogin userid c##ogg@cjcpdb password oracle
Successfully logged into database CJCPDB.
GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 2> add trandata cjc.*
一 源端和目標端配置OGG檢查點
源端：
GGSCI (cjcos01) 1> dblogin userid c##ogg password oracle
Successfully logged into database CDB$ROOT.
GGSCI (cjcos01) 1> EDIT PARAMS ./GLOBALS
--加入以下資訊
CHECKPOINTTABLE CDB$ROOT.c##ogg.ggschkpt
退出 OGG，重新登入，並使用 OGG 使用者登入資料庫
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 3> exit
[oracle@cjcos01 ogg]$ ggsci
GGSCI (cjcos01) 1> dblogin userid c##ogg,password oracle
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 8> ADD CHECKPOINTTABLE CDB$ROOT.c##ogg.ggschkpt
This operation will modify an object at the root level of a consolidated database, continue? (Y/N): Y
Successfully created checkpoint table CDB$ROOT.c##ogg.ggschkpt.
---檢視資料庫表
[ogg@dbdream ogg]$ sqlplus c##ogg/oracle@cjcdb
SQL> select tname from tab where tname like 'GGSCHKPT%';
TNAME
--------------------------------------------------------------------------------
GGSCHKPT
GGSCHKPT_LOX
目標端：
GGSCI (cjcos02) 1> dblogin userid ogg@chenpdb password oracle
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 2> EDIT PARAMS ./GLOBALS
--加入以下資訊
CHECKPOINTTABLE chenpdb.ogg.ggschkpt
退出 OGG，重新登入，並使用 OGG 使用者登入資料庫
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 3> exit
[oracle@cjcos02 ogg]$ ggsci
GGSCI (cjcos02) 1> dblogin userid ogg@chenpdb password oracle
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 2> ADD CHECKPOINTTABLE
No checkpoint table specified. Using GLOBALS specification chenpdb.ogg.ggschkpt...
Successfully created checkpoint table chenpdb.ogg.ggschkpt.
---檢視資料庫表
SQL> conn ogg/oracle@chenpdb
SQL> select tname from tab where tname like 'GGSCHKPT%';
TNAME
--------------------------------------------------------------------------------
GGSCHKPT
GGSCHKPT_LOX
二 源端和目標端配置MGR
源端：
GGSCI (cjcos01) 1> dblogin userid c##ogg password oracle
---配置MGR
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7809-8000
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 3
目標端：
GGSCI (cjcos01) 1> dblogin userid ogg password oracle
---配置mgr
GGSCI (cjcos02 as chen@chendb/CHENPDB) 43> view param mgr
PORT 7809
DYNAMICPORTLIST 7810-8000
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 3
ACCESSRULE, PROG *, IPADDR 192.168.31.*, ALLOW
三 源端：配置抽取程式ext_01
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
TABLE cjcpdb.cjc.emp;
TABLE cjcpdb.cjc.dept;
TABLE cjcpdb.cjc.bonus;
TABLE cjcpdb.cjc.salgrade;
TABLE cjcpdb.cjc.dummy;
新增抽取程式
ADD EXTRACT ext_01,INTEGRATED TRANLOG,BEGIN NOW
---註冊
register extract ext_01 database container (cjcpdb)
2020-04-09 13:03:22 INFO OGG-02003 Extract EXT_01 successfully registered with database at SCN 3466632.
新增本地 trail 檔案
add exttrail ./dirdat/ex,extract ext_01
四 源端：配置投遞程式pump_01
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 2> edit 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.emp;
TABLE cjcpdb.cjc.dept;
TABLE cjcpdb.cjc.bonus;
TABLE cjcpdb.cjc.salgrade;
TABLE cjcpdb.cjc.dummy;
新增 pump 程式
add extract pump_01,exttrailsource ./dirdat/ex
新增遠端 trail 檔案
add rmttrail ./dirdat/dp,extract pump_01
說明 : 指定遠端 trail 檔案
五 目標端：配置應用程式rep_01
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
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;
新增應用程式
add replicat rep_01 integrated,exttrail ./dirdat/dp
add replicat rep_01,exttrail ./dirdat/dp,checkpointtable chenpdb.ogg.ggschkpt
六 啟動程式
源端：
start mgr
start extract ext_01
start extract pump_01
檢視程式狀態：
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 47> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_01 00:01:59 00:00:37
EXTRACT RUNNING PUMP_01 00:00:00 00:00:04
目標端：
start mgr
start replicat rep_01
檢視程式狀態：
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 40> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_01 00:00:00 00:00:04
七 DML(insert,update,delete)操作同步測試
源端：
SQL> insert into dept values(50,'DBA','DUNHUA');
SQL> insert into dept values(60,'DBA','DAPUCHAI');
commit;
SQL> update dept set dname='CJC' where deptno=60;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete dept where deptno=50;
1 row deleted.
SQL> commit;
Commit complete.
目標端：資料已同步
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 CJC DAPUCHAI
檢視插入時對應的ggserr.log日誌
源端：
[oracle@cjcos01 ogg]$ tail -f ggserr.log
......
2020-04-09T18:04:15.194+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-09T18:04:15.470+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-09T18:04:15.670+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-09T18:04:15.670+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-09T18:04:15.709+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-09T18:04:15.709+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-09T18:04:15.709+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-09T18:04:15.709+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-09T18:04:17.286+0800 INFO OGG-02263 Oracle GoldenGate Capture for Oracle, pump_01.prm: Passthru MAP (TABLE) resolved (entry cjcpdb.cjc.dept): TABLE "CJCPDB"."CJC"."DEPT".
2020-04-09T18:04:17.286+0800 INFO OGG-01890 Oracle GoldenGate Capture for Oracle, pump_01.prm: Compression level is set to 1.
目標端：
[oracle@cjcos01 ogg]$ tail -f ggserr.log
2020-04-09T18:04:18.593+0800 WARNING OGG-02760 Oracle GoldenGate Delivery for Oracle, rep_01.prm: ASSUMETARGETDEFS is ignored because trail file /ogg/dirdat/dp000000000 contains table definitions.
2020-04-09T18:04:20.657+0800 INFO OGG-03506 Oracle GoldenGate Delivery for Oracle, rep_01.prm: The source database character set, as determined from the trail file, is UTF-8.
2020-04-09T18:04:20.738+0800 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle, rep_01.prm: MAP resolved (entry cjcpdb.cjc.dept): MAP "CJCPDB"."CJC"."DEPT", TARGET chenpdb.chen.dept.
2020-04-09T18:04:34.942+0800 WARNING OGG-06439 Oracle GoldenGate Delivery for Oracle, rep_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-09T18:04:34.942+0800 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle, rep_01.prm: The definition for table CJCPDB.CJC.DEPT is obtained from the trail file.
2020-04-09T18:04:34.947+0800 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, rep_01.prm: Using following columns in default map by name: DEPTNO, DNAME, LOC.
2020-04-09T18:04:34.947+0800 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, rep_01.prm: Using the following key columns for target table CHENPDB.CHEN.DEPT: DEPTNO, DNAME, LOC.
檢視程式資訊
源端：
檢視ext_01程式資訊
GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 116> stats ext_01
Sending STATS request to EXTRACT EXT_01 ...
Start of Statistics at 2020-04-09 18:22:29.
Output to ./dirdat/ex:
Extracting from CJCPDB.CJC.DEPT to CJCPDB.CJC.DEPT:
*** Total statistics since 2020-04-09 18:04:15 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2020-04-09 18:04:15 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2020-04-09 18:04:15 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2020-04-09 18:04:15 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
End of Statistics.
檢視pump_01程式資訊
GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 117> stats pump_01
Sending STATS request to EXTRACT PUMP_01 ...
Start of Statistics at 2020-04-09 18:25:55.
Output to ./dirdat/dp:
Extracting from CJCPDB.CJC.DEPT to CJCPDB.CJC.DEPT:
*** Total statistics since 2020-04-09 18:04:17 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2020-04-09 18:04:17 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2020-04-09 18:04:17 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2020-04-09 18:04:17 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
End of Statistics.
目標端：
檢視rep_01程式資訊
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 77> stats rep_01
Sending STATS request to REPLICAT REP_01 ...
Start of Statistics at 2020-04-09 18:28:34.
Integrated Replicat Statistics:
Total transactions 4.00
Redirected 0.00
Replicated procedures 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Operation type functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from CJCPDB.CJC.DEPT to CHENPDB.CHEN.DEPT:
*** Total statistics since 2020-04-09 18:04:34 ***
Total inserts 2.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2020-04-09 18:04:34 ***
Total inserts 2.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2020-04-09 18:04:34 ***
Total inserts 2.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2020-04-09 18:04:34 ***
Total inserts 2.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
End of Statistics.
歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2685309/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle 19C OGG基礎運維-05DDL操作同步Oracle運維
- 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運維
- ogg 同步pg資料到oracle--步驟Oracle
- linux運維基礎2Linux運維
- 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
- 使用ogg 從oracle 同步mysql遇到問題記錄OracleMySql
- Oracle 11g RAC到單例項OGG同步Oracle單例