OGG雙向DML復制怎么實現

本篇內容主要講解“OGG雙向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等進程）

到此，相信大家對“OGG雙向DML復制怎么實現”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！