OGG雙向DML復(fù)制怎么實(shí)現(xiàn)
本篇內(nèi)容主要講解“OGG雙向DML復(fù)制怎么實(shí)現(xiàn)”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“OGG雙向DML復(fù)制怎么實(shí)現(xiàn)”吧!
創(chuàng)新互聯(lián)建站主營(yíng)玉林網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營(yíng)網(wǎng)站建設(shè)方案,app軟件定制開(kāi)發(fā),玉林h5小程序開(kāi)發(fā)搭建,玉林網(wǎng)站營(yíng)銷推廣歡迎玉林等地區(qū)企業(yè)咨詢
環(huán)境解釋：hostname：slient,db_name:test作為源庫(kù)，而hostname:one,db_name:onemo作為目標(biāo)庫(kù)，
本次只需要配置一次反向的操作即可：即one為源端， slient為目標(biāo)端.
配置步驟：
1、源端：檢查數(shù)據(jù)庫(kù)是否在歸檔模式，建議在歸檔模式:
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.源庫(kù)：添加附加日志來(lái)唯一標(biāo)識(shí)一行記錄，要在數(shù)據(jù)庫(kù)級(jí)別打開(kāi)最小開(kāi)關(guān)。
語(yǔ)法：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.源端測(cè)試用表
測(cè)試數(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 屬性
語(yǔ)法： 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這個(gè)schema登陸數(shù)據(jù)庫(kù)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.源端配置抓取進(jìn)程
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.源端：添加隊(duì)列文件
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.源端編輯的抓取進(jìn)程的參數(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.源庫(kù)啟動(dòng)extact抓取進(jìn)程：
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.源庫(kù)配置datapump進(jìn)程，將抓取數(shù)據(jù)傳到目標(biāo)主機(jī)。負(fù)責(zé)TCPIP通訊
GGSCI (one as ogg@onemo) 32> add extract dpe_rev, exttrailsource /u01/app/oracle/ogg/dirdat/rv
EXTRACT added.
--輸出：目標(biāo)主機(jī)怎么寫，也是定義datapumo進(jìn)程的輸出。
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進(jìn)程參數(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
--啟動(dòng)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.目標(biāo)端為replicat進(jìn)程創(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.目標(biāo)端配置目標(biāo)端replicate進(jìn)程
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.編輯目標(biāo)端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.目標(biāo)端啟動(dòng)并查看replicate進(jìn)程是否運(yùn)行
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. 測(cè)試源端和目標(biāo)端的數(shù)據(jù)
--測(cè)試前先檢查源庫(kù)和目標(biāo)庫(kù)：
源庫(kù)：
SQL> show user
USER is "SCOTT"
SQL>
SQL> select * from bonus;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
wang sales 1000 .1
SQL>
目標(biāo)庫(kù)：
SQL> show user
USER is "SCOTT"
SQL> select * from BONUS;
no rows selected
SQL>
開(kāi)始測(cè)試：
源庫(kù)：
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
檢查目標(biāo)庫(kù)：
SQL> select * from bonus;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
li manager 10000 .2
再過(guò)一會(huì)查看：
源庫(kù)：
SQL> select * from bonus;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
li manager 10000 .2
li manager 10000 .2
li manager 10000 .2
wang sales 1000 .1
目標(biāo)庫(kù)：
SQL> select * from bonus;
ENAME JOB SAL COMM
---------- --------- ---------- ----------
li manager 10000 .2
li manager 10000 .2
li manager 10000 .2
li manager 10000 .2
就這樣，來(lái)回在兩個(gè)庫(kù)之間不停的copy過(guò)來(lái)copy去（未防止日志不停增長(zhǎng)，關(guān)閉目標(biāo)庫(kù)、源庫(kù)相關(guān)extract、replicate等進(jìn)程）
到此，相信大家對(duì)“OGG雙向DML復(fù)制怎么實(shí)現(xiàn)”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
網(wǎng)頁(yè)標(biāo)題：OGG雙向DML復(fù)制怎么實(shí)現(xiàn)
網(wǎng)站URL：http://muchs.cn/article28/jeppjp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供微信小程序、動(dòng)態(tài)網(wǎng)站、云服務(wù)器、網(wǎng)站營(yíng)銷、軟件開(kāi)發(fā)、搜索引擎優(yōu)化
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 淺談物理服務(wù)器與云服務(wù)器的區(qū)別 2022-10-06
- 還可以建一個(gè)站，區(qū)別于云服務(wù)器和虛擬主機(jī)嗎? 2023-04-28
- 云服務(wù)器哪家好 如何選擇云服務(wù)器配置 2021-03-06
- 云服務(wù)器是什么？怎么買云服務(wù)器？ 2022-10-07
- 便宜好用的云服務(wù)器怎么挑？ 2022-10-05
- 物理服務(wù)器、云服務(wù)器、虛擬主機(jī)的區(qū)別是什么 2015-01-08
- 建網(wǎng)站要云服務(wù)器么？網(wǎng)站服務(wù)器怎么選？ 2022-10-06
- 云服務(wù)器與虛擬主機(jī)到底有何不同？ 2017-04-12
- 空間，輕云服務(wù)器，云主機(jī)三者的區(qū)別 2021-05-09
- 云服務(wù)器和虛擬主機(jī)的特點(diǎn)及其區(qū)別 2022-08-24
- 云服務(wù)器有什么作用？云服務(wù)器成為趨勢(shì)的理由 2022-10-05
- 云服務(wù)器如何測(cè)試？怎么確定是否適合？ 2022-10-04