共計 6731 個字符，預計需要花費 17 分鐘才能閱讀完成。
本篇內容主要講解“OGG 雙向 DML 復制怎么實現”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“OGG 雙向 DML 復制怎么實現”吧!
環境解釋：hostname：slient,db_name:test 作為源庫，而 hostname:one,db_name:onemo 作為目標庫，
本次只需要配置一次反向的操作即可：即 one 為源端， slient 為目標端.
配置步驟：
1、源端：檢查數據庫是否在歸檔模式，建議在歸檔模式:
SQL archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
SQL
已歸檔；
2. 源庫：添加附加日志來唯一標識一行記錄，要在數據庫級別打開最小開關。
語法：alter database add supplemental log data;
SQL select NAME,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME FOR SUPPLEME
——— — ——–
ONEMO NO YES
3. 源端測試用表
測試數據用的是 scott 用戶的下的表 BONUS。要確保復制的表的日志信息是完整的，相關表必須是 logging，一定要把 nologing 變成 logging。
SQL conn scott/tiger;
Connected.
SQL select * from BONUS;
no rows selected
SQL
SQL desc BONUS
Name Null? Type
—————————————– ——– —————————-
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
SAL NUMBER
COMM NUMBER
SQL conn / as sysdba
Connected.
SQL
– 查看表 BONUS 的 force_logging 屬性
語法：alter table schema.table_name logging;
SQL select OWNER,TABLE_NAME,STATUS,LOGGING from dba_tables where owner= SCOTT and table_name= BONUS
OWNER TABLE_NAME STATUS LOG
—————————— —————————— ——– —
SCOTT BONUS VALID YES
4. 源端：以 goldengate 這個 schema 登陸數據庫 GGSCI (one) 1 dblogin userid ogg,password ogg;
Successfully logged into database.
GGSCI (one as ogg@onemo) 2
GGSCI (one as ogg@onemo) 7 add trandata scott.BONUS
2017-10-27 04:57:07 WARNING OGG-06439 No unique key is defined for table BONUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table SCOTT.BONUS.
TRANDATA for scheduling columns has been added on table SCOTT.BONUS .
TRANDATA for instantiation CSN has been added on table SCOTT.BONUS .
GGSCI (one as ogg@onemo) 8
5. 源端配置抓取進程
GGSCI (one as ogg@onemo) 9 add extract ext_rev, tranlog, begin now,threads 1
EXTRACT added.
GGSCI (one as ogg@onemo) 10 info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXT_REV 00:00:00 00:00:05
REPLICAT RUNNING REP_DEMO 00:00:00 00:00:06
6. 源端：添加隊列文件
GGSCI (one as ogg@onemo) 12 add EXTTRAIL /u01/app/oracle/ogg/dirdat/rv, extract ext_rev,megabytes 100
EXTTRAIL added.
GGSCI (one as ogg@onemo) 13
7. 源端編輯的抓取進程的參數 extract；
GGSCI (one as ogg@onemo) 30 edit param ext_rev
EXTRACT ext_rev
setenv (ORACLE_SID=onemo)
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid ogg,password ogg
exttrail /u01/app/oracle/ogg/dirdat/rv
dynamicresolution
TABLE scott.bonus;
GGSCI (one as ogg@onemo) 31
8. 源庫啟動 extact 抓取進程：
GGSCI (one as ogg@onemo) 28 start ext_rev
Sending START request to MANAGER …
EXTRACT EXT_REV starting
GGSCI (one as ogg@onemo) 29 info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_REV 00:08:37 00:00:03
REPLICAT RUNNING REP_DEMO 00:00:00 00:00:01
GGSCI (one as ogg@onemo) 30
9. 源庫配置 datapump 進程，將抓取數據傳到目標主機。負責 TCPIP 通訊
GGSCI (one as ogg@onemo) 32 add extract dpe_rev, exttrailsource /u01/app/oracle/ogg/dirdat/rv
EXTRACT added.
– 輸出：目標主機怎么寫，也是定義 datapumo 進程的輸出。
GGSCI (one as ogg@onemo) 34 add rmttrail /opt/ogg/ogg_home/dirdat/tv,EXTRACT dpe_rev,MEGABYTES 100
RMTTRAIL added.
GGSCI (one as ogg@onemo) 35
10. 源端配置 datapump 進程參數
GGSCI (one as ogg@onemo) 39 edit param DPE_REV
extract dpe_rev
setenv (ORACLE_SID=onemore)
setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)
passthru
–REPORT AT 01:59
–reportrollover at 02:00
rmthost 192.168.56.20,mgrport 7809, compress
rmttrail /opt/ogg/ogg_home/dirdat/tv
Dynamicresolution
table scott.bonus;
~
dirprm/dpe_rev.prm [New] 10L, 265C written
GGSCI (one as ogg@onemo) 40
GGSCI (one as ogg@onemo) 40 info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED DPE_REV 00:00:00 00:05:17
EXTRACT RUNNING EXT_REV 00:00:00 00:00:02
REPLICAT RUNNING REP_DEMO 00:00:00 00:00:07
– 啟動 DPE_REV
GGSCI (one as ogg@onemo) 41 start DPE_REV
Sending START request to MANAGER …
EXTRACT DPE_REV starting
GGSCI (one as ogg@onemo) 42 info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPE_REV 00:00:00 00:05:34
EXTRACT RUNNING EXT_REV 00:00:00 00:00:07
REPLICAT RUNNING REP_DEMO 00:00:00 00:00:04
GGSCI (one as ogg@onemo) 43
11. 目標端為 replicat 進程創建 checkpoint 表
[ogg@slient ogg_home]$ pwd
/opt/ogg/ogg_home
[ogg@slient ogg_home]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (slient) 1 dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (slient as ogg@test) 2 add checkpointtable ogg.rep_bouns_ckpt
Successfully created checkpoint table ogg.rep_bouns_ckpt.
GGSCI (slient as ogg@test) 3
12. 目標端配置目標端 replicate 進程
GGSCI (slient as ogg@test) 4 add replicat rep_rev,exttrail /opt/ogg/ogg_home/dirdat/tv,checkpointtable ogg.rep_bouns_ckpt
REPLICAT added.
GGSCI (slient as ogg@test) 5
13. 編輯目標端 replicate 參數
GGSCI (slient as ogg@test) 5 edit param rep_rev
replicat rep_rev
setenv (oracle_sid=test)
setenv (nls_lang = american_america.zhs16gbk)
userid ogg,password ogg
–report at 01:59
–reportrollover at 02:00
reperror default,abend
discardfile /opt/ogg/ogg_home/dirrpt/repb.dsc,append, megabytes 10
assumetargetdefs
–allownoopupdates
dynamicresolution
–insertallrecords
map scott.bonus,target scott.bonus;
~
~
dirprm/rep_rev.prm [New] 13L, 356C written
GGSCI (slient as ogg@test) 6
GGSCI (slient as ogg@test) 6 info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPEA 00:00:00 00:00:07
EXTRACT RUNNING EXTA 00:00:00 00:00:10
REPLICAT STOPPED REP_REV 00:00:00 00:02:57
14. 目標端啟動并查看 replicate 進程是否運行
GGSCI (slient as ogg@test) 7 start REP_REV
Sending START request to MANAGER …
REPLICAT REP_REV starting
GGSCI (slient as ogg@test) 8 info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPEA 00:00:00 00:00:02
EXTRACT RUNNING EXTA 00:00:00 00:00:03
REPLICAT RUNNING REP_REV 00:00:00 00:00:02
GGSCI (slient as ogg@test) 9
15. 測試源端和目標端的數據
– 測試前先檢查源庫和目標庫：
源庫：
SQL show user
USER is SCOTT
SQL
SQL select * from bonus;
ENAME JOB SAL COMM
———- ——— ———- ———-
wang sales 1000 .1
SQL
目標庫：
SQL show user
USER is SCOTT
SQL select * from BONUS;
no rows selected
SQL
開始測試：
源庫：
SQL insert into bonus values(li , manager ,10000,0.2);
1 row created.
SQL commmit;
SQL select * from bonus;
ENAME JOB SAL COMM
———- ——— ———- ———-
li manager 10000 .2
wang sales 1000 .1
檢查目標庫：
SQL select * from bonus;
ENAME JOB SAL COMM
———- ——— ———- ———-
li manager 10000 .2
再過一會查看：
源庫：
SQL select * from bonus;
ENAME JOB SAL COMM
———- ——— ———- ———-
li manager 10000 .2
li manager 10000 .2
li manager 10000 .2
wang sales 1000 .1
目標庫：
SQL select * from bonus;
ENAME JOB SAL COMM
———- ——— ———- ———-
li manager 10000 .2
li manager 10000 .2
li manager 10000 .2
li manager 10000 .2
就這樣，來回在兩個庫之間不停的 copy 過來 copy 去（未防止日志不停增長，關閉目標庫、源庫相關 extract、replicate 等進程）
到此，相信大家對“OGG 雙向 DML 復制怎么實現”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！
