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雙向條件複製的部署與測試
- PostgreSQL雙向複製教程SQL
- OGG 簡單DML同步
- Oracle 19C OGG基礎運維-03DML操作同步Oracle運維3D
- ogg 併發複製程式自阻塞
- OGG classic模式maxtransops引數提升複製效率模式
- poi操作excel，複製sheet，複製行，複製單元格，複製styleExcel
- OGG_mysql 12c複製到OGG_oracle 11g部署方案MySqlOracle
- OGG 12c mysql複製到oracle部署方案MySqlOracle
- ogg複製程式報ORA-01438錯誤處理
- 不可複製的PDF轉成雙層可複製PDF
- OGG複製程式延遲高，優化方法一(使用索引)優化索引
- OGG同步複製時與相容觸發器解決方法觸發器
- SharePlex qview工具 vs OGG logdump工具探究兩個複製工具事務開始 or 事務提交複製？View
- 架構系列---餓了麼MySQL異地多活的資料雙向複製架構MySql
- OGG複製同步,提示欄位長度不夠ORA-01704
- Oracle 19C OGG基礎運維-06增加複製表Oracle運維
- python 複製以及更改列表操作Python
- 前端er怎樣操作剪下複製以及禁止複製+破解等前端
- MySQL 傳統複製與 GTID 複製原理及操作詳解MySql
- 使用事件溯源、Kafka和OGG從Oracle內部複製資料事件KafkaOracle
- Oracle 19C OGG基礎運維-07減少複製表Oracle運維
- MySQL 主從複製，雙機熱備MySql
- SQLServer DML操作阻塞SELECT查詢SQLServer
- DDL、DML、DCL、DQL相關操作
- Sysbench-0.5改成只有DML操作
- 整明白 Golang slice 宣告方式、淺複製現象、深複製、append操作GolangAPP
- ODPS主備叢集雙向資料複製導致主備中心網路打爆問題
- MySQL全面瓦解5：資料操作-DMLMySql
- MySQL的DDL和DML操作語法MySql
- [20181213]ogg大量讀取操作.txt
- 淺複製和深複製的概念與值複製和指標複製（引用複製）有關 淺複製 “指標複製 深複製 值複製指標
- Mysql實現主從複製（一主雙從）MySql
- 剪貼簿複製貼上操作彙總
- 前端模擬使用者的複製操作前端
- oracle全文索引之commit與DML操作Oracle索引MIT
- Oracle 檢視可以DML操作的條件Oracle
- Oracle 19C OGG基礎運維-04DML同步常見問題Oracle運維
- Java引用複製、淺複製、深複製Java