Oracle GoldenGate系統之----雙向同步資料表
1、防止資料迴圈
在EXTRACT程式中忽略REPLICAT的事務，一般排除提取使用者的操作，並且與需要同步的使用者分開
如USERID SCOTT,PASSWORD TIGER
TRANLOGOPTIONS EXCLUDEUSER SCOTT
2、防止資料衝突
一般要從應用層面解決，避免操作相同的資料。
以下是配置步驟和引數。假設是A、B兩個庫之間的同步配置。
從A到B
-- 管理程式配置
GGSCI (dgrac) 1> edit param mgr
PORT 7801
DYNAMICPORTLIST 7802-7820
AUTOSTART ER *
AUTORESTART ER *,RETRIES 3,WAITMINUTES 2,RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7
-- EXTRACT配置
GGSCI (dgrac) 2> add extract exts1,tranlog,begin now
EXTRACT added.
GGSCI (dgrac) 3> edit param exts1
extract exts1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid tiger
tranlogoptions excludeuser scott
exttrail /u01/app/ogg/dirdat/l1
table lrj.s1;
GGSCI (dgrac) 4> add exttrail /u01/app/ogg/dirdat/l1,extract exts1
EXTTRAIL added.
-- PUMP配置
GGSCI (dgrac) 5> add extract pumps1 ,exttrailsource /u01/app/ogg/dirdat/l1,begin now
EXTRACT added.
GGSCI (dgrac) 6> edit param pumps1
extract pumps1
setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid scott tiger
rmthost 192.168.56.101,mgrport 7801
rmttrail /u01/app/ogg/dirdat/s1
PASSTHRU
table lrj.s1;
GGSCI (dgrac) 7> add rmttrail /u01/app/ogg/dirdat/s1,extract pumps1
RMTTRAIL added.
-- REPLICAT配置
GGSCI (dgrac) 8> edit param reps1
replicat reps1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid scott, password tiger
assumetargetdefs
reperror default,discard
discardfile /u01/app/ogg/dirout/reps1.dsc,append,megabytes 100
map lrj.s1,target lrj.s1,&
colmap(usedefaults,"ENAME" = "USERNAME"),&
-- 解決衝突，更新記錄不存在時插入，但只插入更新的列，其他列為空，還是要人工處理
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE));
HANDLECOLLISIONS
-- 啟動所有程式
GGSCI (dgrac) 9> start mgr
MGR is already running.
GGSCI (dgrac) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTS1 00:00:00 00:00:09
EXTRACT RUNNING PUMPS1 00:00:00 00:00:07
REPLICAT RUNNING REPS1 00:00:00 00:00:05
從B到A
-- 管理程式配置
GGSCI (dg) 1> edit param mgr
PORT 7801
DYNAMICPORTLIST 7802-7820
AUTOSTART ER *
AUTORESTART ER *,RETRIES 3,WAITMINUTES 2,RESETMINUTES 60
STARTUPVALIDATIONDELAY 5
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 7
-- EXTRACT配置
GGSCI (dg) 2> add extract exts1,tranlog,begin now
EXTRACT added.
GGSCI (dg) 3> edit param exts1
extract exts1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid tiger
tranlogoptions excludeuser scott
exttrail /u01/app/ogg/dirdat/l1
table lrj.s1;
GGSCI (dg) 4> add exttrail /u01/app/ogg/dirdat/l1,extract exts1
EXTTRAIL added.
-- PUMP配置
GGSCI (dg) 5> add extract pumps1 ,exttrailsource /u01/app/ogg/dirdat/l1,begin now
EXTRACT added.
GGSCI (dg) 6> edit param pumps1
extract pumps1
setenv(NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
userid scott@ tiger
rmthost 192.168.56.131,mgrport 7801
rmttrail /u01/app/ogg/dirdat/s1
PASSTHRU
table lrj.s1;
GGSCI (dg) 7> add rmttrail /u01/app/ogg/dirdat/s1,extract pumps1
RMTTRAIL added.
-- REPLICAT配置
GGSCI (dg) 8> edit param reps1
replicat reps1
setenv(NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid scott, password tiger
assumetargetdefs
reperror default,discard
discardfile /u01/app/ogg/dirout/reps1.dsc,append,megabytes 100
map lrj.s1,target lrj.s1,&
colmap(usedefaults,"ENAME" = "USERNAME"),&
RESOLVECONFLICT (UPDATEROWMISSING, (DEFAULT, OVERWRITE));
HANDLECOLLISIONS
-- 啟動所有程式
GGSCI (dg) 9> start mgr
MGR is already running.
GGSCI (dg) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTS1 00:00:00 00:00:09
EXTRACT RUNNING PUMPS1 00:00:00 00:00:07
REPLICAT RUNNING REPS1 00:00:00 00:00:05
資料測試
B端插入：
02:44:09 * from s1
02:44:12 2 /
EMPNO USERNAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1800 400 30
7521 WARD SALESMAN 7698 22-FEB-81 1450 600 30
7566 JONES MANAGER 7839 02-APR-81 3175 100 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1450 1500 30
7698 BLAKE MANAGER 7839 01-MAY-81 3050 100 30
7782 CLARK MANAGER 7839 09-JUN-81 2650 100 10
7788 SCOTT ANALYST 7566 19-APR-87 3200 100 20
7839 KING PRESIDENT 17-NOV-81 5200 100 10
7844 TURNER SALESMAN 7698 08-SEP-81 1700 100 30
7876 ADAMS CLERK 7788 23-MAY-87 1300 100 20
7900 JAMES CLERK 7698 03-DEC-81 1150 100 30
7902 FORD ANALYST 7566 03-DEC-81 3200 100 20
7934 MILLER CLERK 7782 23-JAN-82 1500 100 10
2000 yojan
15 rows selected.
02:45:41 into s1(empno,username,sal) values(4000,'GZITECH',2000);
1 row created.
02:45:46 ;
Commit complete.
A端檢視：
02:45:50 * from s1;
EMPNO USERNAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1800 400 30
7521 WARD SALESMAN 7698 22-FEB-81 1450 600 30
7566 JONES MANAGER 7839 02-APR-81 3175 100 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1450 1500 30
7698 BLAKE MANAGER 7839 01-MAY-81 3050 100 30
7782 CLARK MANAGER 7839 09-JUN-81 2650 100 10
7788 SCOTT ANALYST 7566 19-APR-87 3200 100 20
7839 KING PRESIDENT 17-NOV-81 5200 100 10
7844 TURNER SALESMAN 7698 08-SEP-81 1700 100 30
7876 ADAMS CLERK 7788 23-MAY-87 1300 100 20
7900 JAMES CLERK 7698 03-DEC-81 1150 100 30
7902 FORD ANALYST 7566 03-DEC-81 3200 100 20
7934 MILLER CLERK 7782 23-JAN-82 1500 100 10
2000 yojan
4000 GZITECH 2000
16 rows selected.
A端插入：
09:34:18 into s1(empno,ename,job) values(3000,'WAILON','CLERK');
1 row created.
09:34:40 ;
Commit complete.
B端檢視：
09:34:42 * from s1;
EMPNO USERNAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 100 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1800 400 30
7521 WARD SALESMAN 7698 22-FEB-81 1450 600 30
7566 JONES MANAGER 7839 02-APR-81 3175 100 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1450 1500 30
7698 BLAKE MANAGER 7839 01-MAY-81 3050 100 30
7782 CLARK MANAGER 7839 09-JUN-81 2650 100 10
7788 SCOTT ANALYST 7566 19-APR-87 3200 100 20
7839 KING PRESIDENT 17-NOV-81 5200 100 10
7844 TURNER SALESMAN 7698 08-SEP-81 1700 100 30
7876 ADAMS CLERK 7788 23-MAY-87 1300 100 20
7900 JAMES CLERK 7698 03-DEC-81 1150 100 30
7902 FORD ANALYST 7566 03-DEC-81 3200 100 20
7934 MILLER CLERK 7782 23-JAN-82 1500 100 10
2000 yojan
3000 WAILON CLERK
4000 GZITECH 2000
17 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17172228/viewspace-1062732/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle GoldenGate系統之----單向同步資料表OracleGo
- Oracle GoldenGate系統之----單向同步資料表(續)OracleGo
- GoldenGate實現oracle和sqlserver雙向資料同步GoOracleSQLServer
- 【GoldenGate】Oracle GoldenGate(二) 雙向同步配置GoOracle
- GoldenGate雙向同步配置Go
- 使用GoldenGate 實現Oracle for Oracle 單向資料同步（實現表的DML操作同步）GoOracle
- GoldenGate單向表DML同步Go
- Oracle GoldenGate系統之----資料初始化OracleGo
- Oracle Dataguard + Goldengate資料同步OracleGo
- Linux下GoldenGate單機雙向同步LinuxGo
- goldengate不使用資料泵完成Oracle-Oracle的雙向複製GoOracle
- oracle goldengate 雙向複製配置OracleGo
- oracle資料庫配置goldengate同步Oracle資料庫Go
- 配置Oracle GoldenGate for DB2(雙向)OracleGoDB2
- GoldenGate配置（二）之雙向複製配置Go
- 【GoldenGate】Oracle GoldenGate(一) 安裝與DML單向同步配置GoOracle
- goldengate對oracle臨時表的同步GoOracle
- unison+inotify實現web資料雙向同步Web
- 【GoldenGate】Oracle GoldenGate(四) 壓力測試同步百萬行資料GoOracle
- Oracle GoldenGate資料同步備份軟體概述OracleGo
- Oracle使用goldengate分別向Oracle和mysql雙路的單向複製OracleGoMySql
- goldengate同步中更改資料表結構維護Go
- Oracle GoldenGate系統之----安裝及使用OracleGo
- 資料結構之雙向連結串列資料結構
- Goldengate單表新增同步Go
- 使用goldengate從mysql同步資料到oracleGoMySqlOracle
- 【GoldenGate】Oracle GoldenGate(三) DDL同步配置GoOracle
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 上OracleGo
- 【OGG】Oracle GoldenGate 11g (二) GoldenGate 11g 單向同步配置 下OracleGo
- 利用Oracle GoldenGate記錄源系統所有表的操作OracleGo
- Oracle GoldenGate系統之----相關引數說明OracleGo
- Oracle GoldenGate 資料同步初始化最佳實戰（Data Pump）OracleGo
- ogg在異構資料庫實時雙向同步中如何防止資料死迴圈同步資料庫
- GoldenGate使用Obey指令碼同步資料Go指令碼
- 配置GoldenGate增量資料同步機制Go
- 資料結構與演算法(三) -- 線性表之雙向連結串列資料結構演算法
- Java學習筆記：資料結構之線性表（雙向連結串列）Java筆記資料結構
- 使用goldengate的資料泵進行Oracle-Oracle的單向複製GoOracle