OGG雙向DML複製操作
說明：因《OGG單機安裝與配置,並實驗單向DML複製操作》中已配置過單向的dml操作，所以本次配置內容在前一篇的基礎之上。
環境解釋：在《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等程式）
測試資料同步，實現了雙向的DML複製!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
環境解釋：在《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等程式）
測試資料同步，實現了雙向的DML複製!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2146611/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- OGG單向DDL複製操作
- (一)OGG的安裝與配置,並實現單向DML複製操作
- ogg12 oracle to oracle 雙向DDL複製Oracle
- 手把手教你安裝和配置OGG，並實現單向DML複製技術
- OGG 的配置:不使用資料泵完成Oracle-Oracle的雙向複製。Oracle
- MySQL主從雙向同步複製MySql
- oracle goldengate 雙向複製配置OracleGo
- Oracle 11g GoldenGate單向複製配置（DML）OracleGo
- Oracle goldengate 11g （二）【DML and DDL單向複製】OracleGo
- 高階複製-5、測試是否可同步DML操作
- GoldenGate配置（二）之雙向複製配置Go
- OGG 複製程式拆分
- Step by Step TimesTen --- DataStore的雙向複製( 一)AST
- Step by Step TimesTen --- DataStore的雙向複製( 二)AST
- sqlserver2008_ogg單向複製配置文件SQLServer
- oracle---oracle的單向ogg搭建流程（DML+DDL）Oracle
- 實戰goldengate:安裝配置+資料初始化+單向DML複製Go
- OGG 簡單DML同步
- Oracle使用goldengate分別向Oracle和mysql雙路的單向複製OracleGoMySql
- GoldenGate DML複製增刪改表Go
- ogg 併發複製程式自阻塞
- GoldenGate單向複製配置(支援DDL複製)Go
- poi操作excel，複製sheet，複製行，複製單元格，複製styleExcel
- 主從複製、雙主複製及半同步複製、以及基於SSL的複製
- Oracle 19C OGG基礎運維-03DML操作同步Oracle運維3D
- OGG複製程式掛起abended處理
- mysql主主複製（雙主複製）配置步驟MySql
- goldengate不使用資料泵完成Oracle-Oracle的雙向複製GoOracle
- ogg長交易查詢及增加複製表
- OGG複製與加密欄位的衝突加密
- goldengate 單向複製配置Go
- MySQL主主複製（雙主複製）配置過程介紹MySql
- oracle goldengate 雙活複製避免迴圈複製引數OracleGo
- OGG 12c mysql複製到oracle部署方案MySqlOracle
- ogg中抽取或複製程式（extract or replicat）中表的的重複配置
- 架構系列---餓了麼MySQL異地多活的資料雙向複製架構MySql
- goldengate單向複製的配置Go
- OGG_mysql 12c複製到OGG_oracle 11g部署方案MySqlOracle