本篇內(nèi)容主要講解“OGG雙向DML復(fù)制怎么實現(xiàn)”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“OGG雙向DML復(fù)制怎么實現(xiàn)”吧!

創(chuàng)新互聯(lián)建站主營玉林網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,app軟件定制開發(fā),玉林h5小程序開發(fā)搭建,玉林網(wǎng)站營銷推廣歡迎玉林等地區(qū)企業(yè)咨詢

環(huán)境解釋：hostname：slient,db_name:test作為源庫，而hostname:one,db_name:onemo作為目標庫，
本次只需要配置一次反向的操作即可：即one為源端，  slient為目標端.

配置步驟：
1、源端：檢查數(shù)據(jù)庫是否在歸檔模式，建議在歸檔模式:

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.源庫：添加附加日志來唯一標識一行記錄，要在數(shù)據(jù)庫級別打開最小開關(guān)。

語法：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.源端測試用表
測試數(shù)據(jù)用的是scott用戶的下的表BONUS。要確保復(fù)制的表的日志信息是完整的，相關(guān)表必須是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登陸數(shù)據(jù)庫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.源端編輯的抓取進程的參數(shù)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進程，將抓取數(shù)據(jù)傳到目標主機。負責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進程參數(shù)
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進程創(chuàng)建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參數(shù)
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. 測試源端和目標端的數(shù)據(jù)

--測試前先檢查源庫和目標庫：
源庫：
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去（未防止日志不停增長，關(guān)閉目標庫、源庫相關(guān)extract、replicate等進程）

到此，相信大家對“OGG雙向DML復(fù)制怎么實現(xiàn)”有了更深的了解，不妨來實際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！

網(wǎng)頁標題：OGG雙向DML復(fù)制怎么實現(xiàn)
網(wǎng)站URL：http://m.rwnh.cn/article28/jeppjp.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、動態(tài)網(wǎng)站、云服務(wù)器、網(wǎng)站營銷、軟件開發(fā)、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)