這篇文章主要介紹了如何實(shí)現(xiàn)OGG單到單部署，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
浉河網(wǎng)站建設(shè)公司創(chuàng)新互聯(lián)建站,浉河網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為浉河上千提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\外貿(mào)網(wǎng)站制作要多少錢，請(qǐng)找那個(gè)售后服務(wù)好的浉河做網(wǎng)站的公司定做！
環(huán)境介紹：
項(xiàng)目源環(huán)境目標(biāo)環(huán)境
操作系統(tǒng)：Oracle Linux Server release 6.5
主機(jī)名：ogg1，ogg2
IP地址：ogg1：192.168.1.11，ogg2：192.168.1.12
數(shù)據(jù)庫及版本：11.2.0.4
數(shù)據(jù)庫字符集：AL32UTF8
SID：ogg1：mtx，ogg2：ymx
OGG用戶：ogg1：ogg，ogg2：ogg
OGG版本：11.2.1.0.1
歸檔模式：archive log
部署步驟：
1.創(chuàng)建OGG操作系統(tǒng)用戶（非必須，使用Oracle用戶也可以）
useradd -u 2100 -g oinstall -G dba ogg
passwd ogg2008
2.修改操作系統(tǒng)用戶的環(huán)境變量，因?yàn)楸经h(huán)境沒有對(duì)OGG創(chuàng)建操作系統(tǒng)用戶，直接使用的Oracle用戶，所以環(huán)境變量為Oracle用戶的環(huán)境變量
export PATH
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2/db
export ORACLE_SID=mtx
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/usr/X11R6/lib64
export CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"
注意：如果是新創(chuàng)建的操作系統(tǒng)用戶，配置環(huán)境變量之后要重載一下：source .bash_profile
3.兩臺(tái)服務(wù)器創(chuàng)建OGG安裝路徑并更改權(quán)限
mkdir /u01/app/ogg -pv
chown oracle.oinstall /u01/app/ogg -R
4.將OGG安裝文件上傳到兩臺(tái)服務(wù)器的OGG路徑下，本人的路徑為“/u01/app/ogg”
OGG軟件的下載網(wǎng)址在Oracle官網(wǎng)，https://edelivery.oracle.com/osdc/faces/SearchSoftware 找到對(duì)應(yīng)版本即可。
解壓上傳的文件“V34339-01.zip”，這時(shí)你會(huì)看到路徑下又會(huì)生成其他多個(gè)文件，包括文檔、使用說明等
其中有一個(gè)tar包“fbo_ggs_Linux_x64_ora11g_64bit.tar”，我們需要解壓這個(gè)tar文件。
[oracle@ogg1 ogg]$ unzip V34339-01.zip （兩個(gè)服務(wù)器都要做）
Archive: V34339-01.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar
inflating: Oracle_GoldenGate_11.2.1.0.3_README.doc
inflating: Oracle GoldenGate_11.2.1.0.3_README.txt
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.3.pdf
[oracle@ogg1 ogg]$ tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar（兩個(gè)服務(wù)器都要做）
5.解壓成功之后，登錄OGG測(cè)試：
[oracle@ogg1 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (ogg1) 1>
注意：如果LD_LIBRARY_PATH變量設(shè)置正確，會(huì)像上面演示一樣正確進(jìn)入命令行，如果提示找不到so文件，就需要查看LD_LIBRARY_PATH變量是否配置正確
6.創(chuàng)建OGG的管理目錄（兩端都要做）
GGSCI (ogg1) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: already exists--存放OGG參數(shù)的配置信息
Report files /u01/app/ogg/dirrpt: created--存放進(jìn)程報(bào)告文件
Checkpoint files /u01/app/ogg/dirchk: created--存放檢查點(diǎn)文件
Process status files /u01/app/ogg/dirpcs: created--存放進(jìn)程狀態(tài)文件
SQL script files /u01/app/ogg/dirsql: created--存放sql腳本文件
Database definitions files /u01/app/ogg/dirdef: created--存放defgen工具生成的數(shù)據(jù)定義文件
Extract data files /u01/app/ogg/dirdat: created--存放Trail文件，也就是捕獲進(jìn)程的文件
Temporary files /u01/app/ogg/dirtmp: created--當(dāng)事物需要的內(nèi)存超過已分配內(nèi)存時(shí)，默認(rèn)存儲(chǔ)在這個(gè)目錄
Stdout files /u01/app/ogg/dirout: created
7.源端創(chuàng)建OGG表空間和OGG用戶
Oracle建議使用單獨(dú)的表空間存放OGG數(shù)據(jù)，表空間大小可以自己定義，也可以設(shè)置數(shù)據(jù)文件是否自動(dòng)擴(kuò)展，建議自動(dòng)擴(kuò)展
SYS@mtx> create tablespace ogg datafile '/u01/app/oracle/oradata/mtx/ogg01.dbf'
2 size 1024m
3 autoextend on;
SYS@mtx> create user ogg identified by ogg2008 default tablespace ogg;
8.給予ogg用戶相應(yīng)的權(quán)限，有些人為了省事，直接給OGG用戶DBA的權(quán)限
SYS@mtx> grant CONNECT, RESOURCE to ogg;
SYS@mtx> grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ogg;
SYS@mtx> grant ALTER ANY TABLE to ogg;
SYS@mtx> grant FLASHBACK ANY TABLE to ogg;
SYS@mtx> grant EXECUTE on DBMS_FLASHBACK to ogg;
SYS@mtx> grant insert any table to ogg;
SYS@mtx> grant update any table to ogg;
SYS@mtx> grant delete any table to ogg;
或者
SYS@mtx> grant resource,connect,dba to ogg;
9.建立測(cè)試表
源端基于scott用戶的EMP和DEPT表建立相應(yīng)的測(cè)試表，包括其中的數(shù)據(jù)
SCOTT@mtx> create table emp_ogg as select * from emp;
Table created.
SCOTT@mtx> create table dept_ogg as select * from dept;
Table created.
目標(biāo)端基于scott用戶的EMP和DEPT表建立相應(yīng)的測(cè)試表，不包含數(shù)據(jù)
SCOTT@ymx> create table emp_ogg as select * from emp where 1=2;
Table created.
SCOTT@ymx> create table dept_ogg as select * from dept where 1=2;
Table created.
源端和目標(biāo)端分別在兩個(gè)測(cè)試表上建立主鍵或唯一鍵約束
SCOTT@mtx> alter table EMP_OGG add constraint PK_EMPNO_OGG primary key (EMPNO);
Table altered.
SCOTT@mtx> alter table DEPT_OGG add constraint PK_DEPTNO_OGG primary key (DEPTNO);
Table altered.
10.源端添加最小補(bǔ)充日志和表級(jí)補(bǔ)充日志
打開最小補(bǔ)充日志：
SYS@mtx> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
SYS@mtx> alter database add supplemental log data;
Database altered.
打開表級(jí)補(bǔ)充日志（需要以最小補(bǔ)充日志為前提）
GGSCI (ogg1) 1> dblogin userid ogg,password ogg2008
Successfully logged into database.
GGSCI (ogg1) 2> add trandata scott.emp_ogg
Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.
GGSCI (ogg1) 3> add trandata scott.dept_ogg
Logging of supplemental redo data enabled for table SCOTT.DEPT_OGG.
SYS@mtx> alter table scott.emp_ogg logging;
Table altered.
SYS@mtx> alter table scott.dept_ogg logging;
Table altered.
11.配置MGR管理進(jìn)程
源端和目標(biāo)端：
GGSCI (ogg1) 1> edit param mgr
PORT 7809--服務(wù)監(jiān)聽端口，默認(rèn)7809
DYNAMICPORTLIST 7840-7939--動(dòng)態(tài)端口，指定端口不可用時(shí)，從列表中選擇一個(gè)作為可用端口，可以制定最大256個(gè)
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3--自動(dòng)重啟參數(shù)設(shè)置，本處設(shè)置表示每3分鐘嘗試重新啟動(dòng)所有EXTRACT進(jìn)程，共嘗試5次
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, minkeepdays 7--定期清理trail文件，本處設(shè)置表示對(duì)超過7天的trail文件進(jìn)行刪除，從此參數(shù)也能看到trail文件的位置
LAGREPORTHOURS 1--定義延遲預(yù)警機(jī)制，本處設(shè)置每隔1小時(shí)檢查extract的延遲情況
LAGINFOMINUTES 30--定義延遲預(yù)警機(jī)制，本處設(shè)置超過了30分鐘就把延遲新信息記錄到錯(cuò)誤日志中
LAGCRITICALMINUTES 45--定義延遲預(yù)警機(jī)制，本處設(shè)置延遲超過45分鐘，則把它作為警告寫到錯(cuò)誤日志中
啟動(dòng)MGR管理進(jìn)程
GGSCI (ogg1) 2> start mgr
12.配置抽取進(jìn)程
添加捕獲進(jìn)程：
GGSCI (ogg1) 4> add extract ext1,tranlog,begin now
EXTRACT added.
指定捕獲進(jìn)程生成的trail文件所在路徑
GGSCI (ogg1) 7> add exttrail /u01/app/ogg/dirdat/ea, extract ext1, megabytes 100
EXTTRAIL added.
配置進(jìn)程的參數(shù)文件
GGSCI (ogg1) 6> edit param ext1
extrail ext1
SETENV (ORACLE_SID="mtx")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg , PASSWORD ogg2008配置
EXTTRAIL /u01/app/ogg/dirdat/ea
NUMFILES 3000
GETTRUNCATES
DYNAMICRESOLUTION
table scott.dept_ogg;
table scott.emp_ogg;
啟動(dòng)捕獲進(jìn)程
GGSCI (ogg1) 31> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
13.配置傳輸進(jìn)程
添加傳輸進(jìn)程：
GGSCI (ogg1) 16> add extract pum1,exttrailsource /u01/app/ogg/dirdat/ea
EXTRACT added.
指定傳輸進(jìn)程的trail文件路徑
GGSCI (ogg1) 18> add rmttrail /u01/app/ogg/dirdat/pm,extract pum1,megabytes 100
RMTTRAIL added.
配置進(jìn)程的參數(shù)文件
extract pum1
SETENV (ORACLE_SID="mtx")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
RMTHOST 192.168.1.12,MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000
CACHEMGR CACHEBUFFERSIZE 64KB, CACHESIZE 1024MB, CACHEDIRECTORY /u01/app/ogg/dirtmp
rmttrail /u01/app/ogg/dirdat/pm
PASSTHRU
DYNAMICRESOLUTION
TABLE scott.*;
啟動(dòng)傳輸進(jìn)程
GGSCI (ogg1) 32> start pum1
Sending START request to MANAGER ...
EXTRACT PUM1 starting
14.做初始化操作
查詢當(dāng)前數(shù)據(jù)庫的系統(tǒng)改變號(hào)
SYS@mtx> select to_char(sysdate,'yyyy-mm-dd hh34:mi:ss') ,dbms_flashback.get_system_change_number || '' from dual;
15158302130192
SYSDATE DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER|
------------------ ----------------------------------------
13-JUL-17 286349
按SCN號(hào)導(dǎo)出兩個(gè)表的數(shù)據(jù)，用數(shù)據(jù)泵的方式，目錄對(duì)象需要自己創(chuàng)建
expdp ogg/ogg2008 directory=pump exclude=grant dumpfile=dept_ogg.dmp tables=SCOTT.DEPT_OGG flashback_scn=286349
expdp ogg/ogg2008 directory=pump exclude=grant dumpfile=emp_ogg.dmp tables=SCOTT.EMP_OGG flashback_scn=286349
通過SCP或者FTP的方式將導(dǎo)出的數(shù)據(jù)文件傳送到目標(biāo)端做導(dǎo)入操作
impdp ogg/ogg2008 directory=pump dumpfile=dept_ogg.dmp tables=SCOTT.DEPT_OGG table_exists_action=append
impdp ogg/ogg2008 directory=pump dumpfile=emp_ogg.dmp tables=SCOTT.EMP_OGG table_exists_action=append
--15.目標(biāo)端配置OGG的檢查點(diǎn)
--此步驟不是必須的，但是為了讓OGG網(wǎng)絡(luò)中斷、服務(wù)器宕機(jī)、掉電等在突發(fā)情況下也能正確斷點(diǎn)續(xù)傳，Oracle建議配置OGG的檢查點(diǎn)隊(duì)列
--GGSCI (ogg2) 75> edit param ./GLOBALS
--checkpointtable ogg.checkpoint_tab
--GGSCI (ogg2) 7> add checkpointtable ogg.checkpoint_tab
--Successfully created checkpoint table ogg.checkpoint_tab.
16.目標(biāo)端配置replycat進(jìn)程
GGSCI (ogg2) 8> add replicat rep1 exttrail /u01/app/ogg/dirdat/pm, checkpointtable ogg.checkpoint_tab
REPLICAT added.
GGSCI (ogg2) 12> edit param rep1
replicat rep1
SETENV (ORACLE_SID="ymx")
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg , PASSWORD ogg2008
sqlexec "Alter session set commit_write=nowait"
DISCARDFILE /u01/app/ogg/dirrpt/rep1err.dsc,append,megabytes 100
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
NUMFILES 3000
EOFDELAYCSECS 30
GETTRUNCATES
BATCHSQL BATCHESPERQUEUE 200,OPSPERBATCH 2000
MAP SCOTT.DEPT_OGG, TARGET SCOTT.DEPT_OGG*;
MAP SCOTT.EMP_OGG, TARGET SCOTT.EMP_OGG*;
開啟replycat進(jìn)程
GGSCI (ogg2) 18> start replicat rep1,aftercsn 286349
17.測(cè)試效果
源端：
SCOTT@mtx> select * from dept_ogg;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@mtx> insert into dept_ogg values (50,'TEC','BEIJING');
1 row created.
SCOTT@mtx> commit;
Commit complete.
源端捕獲到的結(jié)果：
GGSCI (ogg1) 71> stats ext1
Sending STATS request to EXTRACT EXT1 ...
Start of Statistics at 2017-07-13 14:39:28.
Output to /u01/app/ogg/dirdat/ea:
Extracting from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:
*** Total statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (ogg1) 72> stats pum1
Sending STATS request to EXTRACT PUM1 ...
Start of Statistics at 2017-07-13 14:39:33.
Output to /u01/app/ogg/dirdat/pm:
Extracting from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:
*** Total statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2017-07-13 14:37:03 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
目標(biāo)端：
GGSCI (ogg2) 38> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2017-07-13 18:11:18.
Replicating from SCOTT.DEPT_OGG to SCOTT.DEPT_OGG:
*** Total statistics since 2017-07-13 18:11:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2017-07-13 18:11:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2017-07-13 18:11:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2017-07-13 18:11:11 ***
Total inserts 1.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
目標(biāo)端將數(shù)據(jù)應(yīng)用到數(shù)據(jù)庫中的結(jié)果：
SCOTT@ymx> select * from dept_ogg;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 TEC BEIJING
感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“如何實(shí)現(xiàn)OGG單到單部署”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持創(chuàng)新互聯(lián)，關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來學(xué)習(xí)!
重慶分公司
重慶分公司
