linux下單節點oracle資料庫間ogg搭建

環境說明：   linux為Linux 2.6.32-573.el6.x86_64     oracle為 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                 ogg為 ogg112101_fbo_ggs_Linux_x64_ora11g_64bit

技術背景：

 

 

 

說明：  goldengate為ogg管理使用者                 oggdemo為生產使用者

源庫操作：

  1.  上傳ogg安裝包與安裝ogg軟體。

[oracle@11g ~]$ mkdir ogg

[oracle@11g ~]$ cd ogg
[oracle@11g ogg]$ ls
ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@11g ogg]$ unzip ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip 
Archive: ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip
inflating: fbo_ggs_Linux_x64_ora11g_64bit.tar 
inflating: OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf 
inflating: Oracle GoldenGate 11.2.1.0.1 README.txt 
inflating: Oracle GoldenGate 11.2.1.0.1 README.doc 
[oracle@11g ogg]$ tar vxf fbo_ggs_Linux_x64_ora11g_64bit.tar 
UserExitExamples/
UserExitExamples/ExitDemo_more_recs/
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.HPUX
UserExitExamples/ExitDemo_more_recs/Makefile_more_recs.SOLARIS

。。。。省略大量輸出。。。。。

ucharset.h
ulg.sql
usrdecs.h
zlib.txt

2.  資料庫為ogg傳遞資料做環境調整。  （歸檔，資料庫級最小附加日誌）

[oracle@11g ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Nov 9 03:03:20 2014

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SQL> alter database add supplemental log data;

Database altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

3. 建立專用的ogg表空間與ogg使用者，ogg使用者授權 （以後解除安裝ogg直接刪除表空間即可）

SQL> create tablespace goldgate datafile '/home/oracle/app/oradata/orcl/ogg01.dbf' size 2G AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

Tablespace created.

SQL> CREATE USER goldengate IDENTIFIED BY goldengate DEFAULT TABLESPACE goldgate;

User created.

SQL> GRANT CONNECT TO goldengate;

Grant succeeded.

SQL> GRANT CREATE SESSION TO goldengate;

Grant succeeded.

SQL> GRANT ALTER SESSION TO goldengate;

Grant succeeded.

SQL> GRANT RESOURCE TO goldengate;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO goldengate;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO goldengate;

Grant succeeded.

SQL> GRANT FLASHBACK ANY TABLE TO goldengate;

Grant succeeded.

SQL> GRANT ALTER ANY TABLE TO goldengate;

Grant succeeded.

 

說明：

在goldengate目錄下，執行 ./ggsci 進入命令列介面，左側提示出現GGSCI (myhost) 1>，
表示進入成功
如果ggsci進入出錯，設定一下環境變數：
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

 

[oracle@11g ~]$ cd ogg                  --驗證是否可以進入ggsci命令介面
[oracle@11g ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

 

GGSCI (11g) 1>      -- 成功進入介面

——————————————————————————————————————————————————————————————————

目標庫操作：

 

說明 ： 目標庫的操作與源庫以上操作全部相同（請參考上面的部分），操作完以上部分後，目標庫的附加操作如下>

SQL> GRANT INSERT ANY TABLE TO goldengate;

Grant succeeded.

SQL> GRANT UPDATE ANY TABLE TO goldengate;

Grant succeeded.

SQL> GRANT DELETE ANY TABLE TO goldengate;

Grant succeeded.

 ————————————————————————————————————————————————————————————————————

4.  建立ogg子目錄

源庫與目標庫都執行如下：  

GGSCI (11g) 3> create subdirs

Creating subdirectories under current directory /home/oracle/ogg

Parameter files /home/oracle/ogg/dirprm: already exists
Report files /home/oracle/ogg/dirrpt: created
Checkpoint files /home/oracle/ogg/dirchk: created
Process status files /home/oracle/ogg/dirpcs: created
SQL script files /home/oracle/ogg/dirsql: created
Database definitions files /home/oracle/ogg/dirdef: created
Extract data files /home/oracle/ogg/dirdat: created
Temporary files /home/oracle/ogg/dirtmp: created
Stdout files /home/oracle/ogg/dirout: created

引數說明：

該命令會在OGG安裝目錄下建立若干子目錄，其中幾個主要目錄如下所示：
dirchk：用於存放各個程式的檢查點
dirdat：用於存放資料佇列檔案
dirprm：用於存放各程式引數檔案
dirrpt：用於存放各程式報告
dirpcs：存放各個正在執行的程式資訊

 

 5. 建立mgr程式

 

GGSCI (11g) 4> edit param mgr    （--在mgr檔案中加入  port 7809）

GGSCI (11g) 8> view param mgr

port 7809

GGSCI (11g) 5> start mgr

Manager started.

GGSCI (11g) 6> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

————————————————————————————————————————————————————

源庫操作：

6. 建立oggdemo測試使用者

SQL> create user oggdemo identified by oggdemo;

User created.

SQL> grant dba to oggdemo;

Grant succeeded.

SQL> conn oggdemo;
Enter password: 
Connected.

SQL> create table oggdemo(id int);

Table created.

7.   GGSCI命令列中登入資料庫，為所有要複製的資料表新增trandata

 

GGSCI (11g) 9> dblogin userid oggdemo , password oggdemo
Successfully logged into database.

GGSCI (11g) 10> add trandata oggdemo.oggdemo

2014-11-09 04:12:37 WARNING OGG-00869 No unique key is defined for table 'OGGDEMO'. 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 OGGDEMO.OGGDEMO.

————————————————————————————————————————————————————————————————

目標端操作：

SQL> create user oggdemo identified by oggdemo;

User created.

SQL> grant dba to oggdemo;

Grant succeeded.

SQL> conn oggdemo;
Enter password: 
Connected.

SQL> create table oggdemo(id int);

Table created.

————————————————————————————————————————————————————————

源庫操作：

8.   建立ext程式，tail檔案，data_pump程式

GGSCI (11g) 11> add ext exta, tranlog, begin now
EXTRACT added.

GGSCI (11g) 12> add exttrail /home/oracle/ogg/dirdat/la, ext exta, MEGABYTES 20      -- 建立源trail檔案
EXTTRAIL added.

GGSCI (11g) 13> add extract dpea, EXTTRAILSOURCE /home/oracle/ogg/dirdat/la        -- 建立data_pump程式
EXTRACT added.

GGSCI (11g) 14> add rmttrail /home/oracle/ogg/dirdat/ra, ext dpea, MEGABYTES 20     --建立目標端trail檔案
RMTTRAIL added.

GGSCI (11g) 15> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING 
EXTRACT STOPPED DPEA 00:00:00 00:02:48 
EXTRACT STOPPED EXTA 00:00:00 00:09:36

GGSCI (11g) 17> edit param exta    --exta檔案新增內容顯示如下

GGSCI (11g) 35> view param exta

EXTRACT exta
setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
setenv (ORACLE_SID = orcl)
USERID goldengate, PASSWORD goldengate
EXTTRAIL /home/oracle/ogg/dirdat/la
dynamicresolution
table oggdemo.oggdemo;

GGSCI (11g) 33> start exta

Sending START request to MANAGER ...
EXTRACT EXTA starting

GGSCI (11g) 34> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING 
EXTRACT STOPPED DPEA 00:00:00 01:40:39 
EXTRACT RUNNING EXTA 01:47:26 00:00:01

GGSCI (11g) 36> edit param dpea         --dpea檔案新增內容顯示如下

GGSCI (11g) 39> view param dpea

extract dpea
setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
passthru
rmthost 10.100.25.14,mgrport 7809, compress
rmttrail /home/oracle/ogg/dirdat/ra
dynamicresolution
table oggdemo.oggdemo;

 

引數說明：

passthru表示本程式是一個傳輸程式data pump，無需跟資料庫互動，只需要搬運資料即可；
因為data pump要傳輸資料到目標，所以需要配置rmthost和rmttrail指定目標主機和佇列資訊

 

GGSCI (11g) 37> start dpea

Sending START request to MANAGER ...
EXTRACT DPEA starting

GGSCI (11g) 38> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING 
EXTRACT RUNNING DPEA 00:00:00 00:00:00 
EXTRACT RUNNING EXTA 00:00:00 00:00:04

————————————————————————————————————————————————————————————

 

目標資料庫操作：

9.     建立目標資料庫的replicate程式

GGSCI (11g) 6> add rep repa, exttrail /home/oracle/ogg/dirdat/ra, nodbcheckpoint
REPLICAT added.

引數說明： 

repa為程式名，一般為rep開頭表示是replicat程式，後面可以加1-2位字元標識，一般與ext程式對應；

exttrail表示要抽取的資料佇列，注意是目標端的佇列位置；

nodbcheckpoint表示不使用資料庫檢查點。

 

GGSCI (11g) 17> edit param repa     -- repa檔案新增內容顯示如下

GGSCI (11g) 20> view param repa

replicat repa
setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK)
setenv (ORACLE_SID = orcl)
userid goldengate, password goldengate
reperror default,abend
discardfile /home/oracle/ogg/dirrpt/repa.dsc,append, megabytes 10
assumetargetdefs
dynamicresolution
map oggdemo.oggdemo, target oggdemo.oggdemo;

 

GGSCI (11g) 18> start repa

Sending START request to MANAGER ...
REPLICAT REPA starting

GGSCI (11g) 19> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING 
REPLICAT RUNNING REPA 00:00:00 00:00:01

 ————————————————————————————————————————————

ok 一切正常結束。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31383567/viewspace-2130206/，如需轉載，請註明出處，否則將追究法律責任。