溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹了如何實現(xiàn)OGG單到單部署，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。
環(huán)境介紹：
項目源環(huán)境目標環(huán)境
操作系統(tǒng)：Oracle Linux Server release 6.5
主機名：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)境變量，因為本環(huán)境沒有對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.兩臺服務(wù)器創(chuàng)建OGG安裝路徑并更改權(quán)限
mkdir /u01/app/ogg -pv
chown oracle.oinstall /u01/app/ogg -R
4.將OGG安裝文件上傳到兩臺服務(wù)器的OGG路徑下，本人的路徑為“/u01/app/ogg”
OGG軟件的下載網(wǎng)址在Oracle官網(wǎng)，https://edelivery.oracle.com/osdc/faces/SearchSoftware 找到對應(yīng)版本即可。
解壓上傳的文件“V34339-01.zip”，這時你會看到路徑下又會生成其他多個文件，包括文檔、使用說明等
其中有一個tar包“fbo_ggs_Linux_x64_ora11g_64bit.tar”，我們需要解壓這個tar文件。
[oracle@ogg1 ogg]$ unzip V34339-01.zip （兩個服務(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（兩個服務(wù)器都要做）
5.解壓成功之后，登錄OGG測試：
[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è)置正確，會像上面演示一樣正確進入命令行，如果提示找不到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--存放進程報告文件
Checkpoint files /u01/app/ogg/dirchk: created--存放檢查點文件
Process status files /u01/app/ogg/dirpcs: created--存放進程狀態(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文件，也就是捕獲進程的文件
Temporary files /u01/app/ogg/dirtmp: created--當事物需要的內(nèi)存超過已分配內(nèi)存時，默認存儲在這個目錄
Stdout files /u01/app/ogg/dirout: created
7.源端創(chuàng)建OGG表空間和OGG用戶
Oracle建議使用單獨的表空間存放OGG數(shù)據(jù)，表空間大小可以自己定義，也可以設(shè)置數(shù)據(jù)文件是否自動擴展，建議自動擴展
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.建立測試表
源端基于scott用戶的EMP和DEPT表建立相應(yīng)的測試表，包括其中的數(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.
目標端基于scott用戶的EMP和DEPT表建立相應(yīng)的測試表，不包含數(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.
源端和目標端分別在兩個測試表上建立主鍵或唯一鍵約束
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.源端添加最小補充日志和表級補充日志
打開最小補充日志：
SYS@mtx> SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
SUPPLEME
--------
NO
SYS@mtx> alter database add supplemental log data;
Database altered.
打開表級補充日志（需要以最小補充日志為前提）
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管理進程
源端和目標端：
GGSCI (ogg1) 1> edit param mgr
PORT 7809--服務(wù)監(jiān)聽端口，默認7809
DYNAMICPORTLIST 7840-7939--動態(tài)端口，指定端口不可用時，從列表中選擇一個作為可用端口，可以制定最大256個
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3--自動重啟參數(shù)設(shè)置，本處設(shè)置表示每3分鐘嘗試重新啟動所有EXTRACT進程，共嘗試5次
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, minkeepdays 7--定期清理trail文件，本處設(shè)置表示對超過7天的trail文件進行刪除，從此參數(shù)也能看到trail文件的位置
LAGREPORTHOURS 1--定義延遲預(yù)警機制，本處設(shè)置每隔1小時檢查extract的延遲情況
LAGINFOMINUTES 30--定義延遲預(yù)警機制，本處設(shè)置超過了30分鐘就把延遲新信息記錄到錯誤日志中
LAGCRITICALMINUTES 45--定義延遲預(yù)警機制，本處設(shè)置延遲超過45分鐘，則把它作為警告寫到錯誤日志中
啟動MGR管理進程
GGSCI (ogg1) 2> start mgr
12.配置抽取進程
添加捕獲進程：
GGSCI (ogg1) 4> add extract ext1,tranlog,begin now
EXTRACT added.
指定捕獲進程生成的trail文件所在路徑
GGSCI (ogg1) 7> add exttrail /u01/app/ogg/dirdat/ea, extract ext1, megabytes 100
EXTTRAIL added.
配置進程的參數(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;
啟動捕獲進程
GGSCI (ogg1) 31> start ext1
Sending START request to MANAGER ...
EXTRACT EXT1 starting
13.配置傳輸進程
添加傳輸進程：
GGSCI (ogg1) 16> add extract pum1,exttrailsource /u01/app/ogg/dirdat/ea
EXTRACT added.
指定傳輸進程的trail文件路徑
GGSCI (ogg1) 18> add rmttrail /u01/app/ogg/dirdat/pm,extract pum1,megabytes 100
RMTTRAIL added.
配置進程的參數(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.*;
啟動傳輸進程
GGSCI (ogg1) 32> start pum1
Sending START request to MANAGER ...
EXTRACT PUM1 starting
14.做初始化操作
查詢當前數(shù)據(jù)庫的系統(tǒng)改變號
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號導出兩個表的數(shù)據(jù)，用數(shù)據(jù)泵的方式，目錄對象需要自己創(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的方式將導出的數(shù)據(jù)文件傳送到目標端做導入操作
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.目標端配置OGG的檢查點
--此步驟不是必須的，但是為了讓OGG網(wǎng)絡(luò)中斷、服務(wù)器宕機、掉電等在突發(fā)情況下也能正確斷點續(xù)傳，Oracle建議配置OGG的檢查點隊列
--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.目標端配置replycat進程
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進程
GGSCI (ogg2) 18> start replicat rep1,aftercsn 286349
17.測試效果
源端：
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.
目標端：
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.
目標端將數(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
感謝你能夠認真閱讀完這篇文章，希望小編分享的“如何實現(xiàn)OGG單到單部署”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識等著你來學習!
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
