1 oracle GlodenGate 概述

GlodenGate是oracle 公司收購glodendate的實時異構資料庫資料整合產品，目前已經成功應用於美國銀行，中華人民共和國海關總署，國家稅務總局等。

兩套虛擬機器Oracle 10.2.0.1+windows 2003 32 bit

源端oracle資料庫使用者source 主機名為source

目標端oracle資料庫使用者target 主機名為target

安裝

進入以下網站下載oracle glodendate安裝軟體

這裡為32bit for windows平臺下的安裝軟體

GGV18162-01ForOracle10g.zip

直接用unzip解壓到c:\GG目錄下

點選執行cmd，進入到c:\gg目錄

C:\Documents and Settings\Oracle.XP_ONE>cd ..

C:\Documents and Settings>cd ..

C:\>cd gg

C:\gg>ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55

GGSCI (source) 1> create subdirs

Creating subdirectories under current directory C:\gg

Parameter files C:\gg\dirprm: created

Report files C:\gg\dirrpt: created

Checkpoint files C:\gg\dirchk: created

Process status files C:\gg\dirpcs: created

SQL script. files C:\gg\dirsql: created

Database definitions files C:\gg\dirdef: created

Extract data files C:\gg\dirdat: created

Temporary files C:\gg\dirtmp: created

Veridata files C:\gg\dirver: created

Veridata Lock files C:\gg\dirver\lock: created

Veridata Out-Of-Sync files C:\gg\dirver\oos: created

Veridata Out-Of-Sync XML files C:\gg\dirver\oosxml: created

Veridata Parameter files C:\gg\dirver\params: created

Veridata Report files C:\gg\dirver\report: created

Veridata Status files C:\gg\dirver\status: created

Veridata Trace files C:\gg\dirver\trace: created

Stdout files C:\gg\dirout: created

Exit;

Turn on supplemental logging at the database level.

C:\Documents and Settings\Oracle.XP_ONE>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 24 00:20:38 2010

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> create user source identified by source;

User created.

SQL> grant dba,connect,resource to source;

Grant succeeded.

SQL> sqlplus source/source

SQL> conn source/source

Connected.

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> exit;

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Install the glodendate manager process

C:\gg>ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55

GGSCI (source) 1> edit params ./GLOBAlS

新建個文字檔案輸入以下內容

MGRSERVNAME PAUL,然後儲存

新增管理程式到windows 服務中，以便隨windows作業系統自動啟動

C:\gg>install addservice addevents

Oracle GoldenGate messages installed successfully.

Service 'PAUL' created.

Install program terminated normally.

然後檢查windows服務，已經存在paul這個服務了

PAUL Automatic LOCAL SYSTEM

方法

source端配置mgr埠和啟動mgr程式

cd c:\gg

c:\gg\ggsci

GGSCI (source) 2> edit param mgr

輸入以下檔案，然後按儲存

--GoldenGate Manager parameter file

port 7809

輸入7809的埠

GGSCI (source) 4> start mgr

Starting Manager as service ('PAUL')...

Service started.

GGSCI (source) 5> info mgr

Manager is running (IP port source.7809).

可以看到啟動mgr程式成功

source端建立table和加入初始資料

C:\gg>sqlplus source/source

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 24 00:51:57 2010

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @demo_ora_create

Table created.

SQL> @demo_ora_insert

1 row created.

Commit complete.

SQL> select * from tcustmer;

CUST NAME CITY ST

---- ------------------------------ -------------------- --

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

SQL> select * from tcustord;

CUST ORDER_DA PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID

---- -------- -------- ---------- ------------- -------------- --------------

WILL 94-09-30 CAR 144 17520 3 100

JANE 95-11-11 PLANE 256 133300 1 100

端Add supplemental logging

GGSCI (source) 2> dblogin userid source,password source

Successfully logged into database.

GGSCI (source) 3> add trandata source.tcustmer

Logging of supplemental redo data enabled for table SOURCE.TCUSTMER.

GGSCI (source) 4> add trandata source.tcustord

Logging of supplemental redo data enabled for table SOURCE.TCUSTORD.

驗證是否新增supplemental logging是否成功

GGSCI (source) 6> info trandata source.tcust*

Logging of supplemental redo log data is enabled for table SOURCE.TCUSTMER

Logging of supplemental redo log data is enabled for table SOURCE.TCUSTORD

target端配置mgr程式和啟動mgr程式

Install the glodendate manager process

C:\gg>ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 10.4.0.19 Build 002

Windows (optimized), Oracle 10 on Sep 18 2009 15:54:55

GGSCI (source) 1> edit params ./GLOBAlS

新建個文字檔案輸入以下內容

MGRSERVNAME PAUL,然後儲存

新增管理程式到windows 服務中，以便隨windows作業系統自動啟動

C:\gg>install addservice addevents

Oracle GoldenGate messages installed successfully.

Service 'PAUL' created.

Install program terminated normally.

然後檢查windows服務，已經存在paul這個服務了

PAUL Automatic LOCAL SYSTEM

GGSCI (source) 2> edit param mgr

輸入以下檔案，然後按儲存

--GoldenGate Manager parameter file

port 7809

輸入7809的埠

GGSCI (source) 4> start mgr

Starting Manager as service ('PAUL')...

Service started.

GGSCI (source) 5> info mgr

Manager is running (IP port source.7809).

可以看到啟動mgr程式成功

端建立target使用者和建立表

SQL> create user target identified by target;

User created.

SQL> grant dba,connect,resource to target;

Grant succeeded.

SQL>@demo_ora_create

Verify the results:

SQL> desc tcustmer;

SQL> desc tcustord;

SQL> exit

在source端

GGSCI (source) 7> add extract eini01,sourceistable

EXTRACT added.

GGSCI (source) 8> info extract *,tasks

EXTRACT EINI01 Initialized 2010-01-24 01:18 Status STOPPED

Checkpoint Lag Not Available

Log Read Checkpoint Not Available

First Record Record 0

Task SOURCEISTABLE

GGSCI (source) 9> edit params eini01

輸入以下引數，並且儲存

--

-- GoldenGate Initial Data Capture

-- for TCUSTMER and TCUSTORD

--

EXTRACT EINI01

USERID source, PASSWORD source

RMTHOST target, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI01

TABLE source.TCUSTMER;

TABLE source.TCUSTORD;

在target端

GGSCI (target) 6> ADD REPLICAT RINI01,SPECIALRUN

REPLICAT added.

GGSCI (target) 7> INFO REPLICAT *, TASKS

REPLICAT RINI01 Initialized 2010-01-24 01:23 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:10 ago)

Log Read Checkpoint Not Available

Task SPECIALRUN

GGSCI (target) 8> edit params RINI01

--

-- GoldenGate Initial Load Delivery

--

REPLICAT RINI01

ASSUMETARGETDEFS

USERID target, PASSWORD target

DISCARDFILE ./dirrpt/RINI01.dsc, PURGE

MAP source.*, TARGET target.*;

在source端

GGSCI (source) 10> START EXTRACT EINI01

Sending START request to MANAGER ('PAUL') ...

EXTRACT EINI01 starting

view report eini01

*********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2010-01-24 01:28:40 (activity since 2010-01-24 01:28:27)

Output to RINI01:

From Table SOURCE.TCUSTMER:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

From Table SOURCE.TCUSTORD:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

view report rini01

*********************************************************************

* ** Run Time Statistics ** *

***********************************************************************

Report at 2010-01-24 01:29:00 (activity since 2010-01-24 01:28:54)

From Table SOURCE.TCUSTMER to TARGET.TCUSTMER:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

From Table SOURCE.TCUSTORD to TARGET.TCUSTORD:

# inserts: 2

# updates: 0

# deletes: 0

# discards: 0

在target端檢視資料

SQL> select * from tcustmer;

CUST NAME CITY ST

---- ------------------------------ -------------------- --

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

SQL> select * from tcustord;

CUST ORDER_DA PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID

---- -------- -------- ---------- ------------- -------------- --------------

WILL 94-09-30 CAR 144 17520 3 100

JANE 95-11-11 PLANE 256 133300 1 100

可以看到從source端到目標端初始化資料成功

Configure Change Capture

GGSCI (source) 12> ADD EXTRACT EORA01,TRANLOG, BEGIN NOW, THREADS 1

EXTRACT added.

GGSCI (source) 13> info extract eora01

EXTRACT EORA01 Initialized 2010-01-24 01:39 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:00:32 ago)

Log Read Checkpoint Oracle Redo Logs

2010-01-24 01:39:33 Thread 1, Seqno 0, RBA 0

端create the extract parameter file

GGSCI (source) 14> edit param eora01

--

-- Change Capture parameter file

-- TCUSTMER and TCUSTORD Changes

--

EXTRACT EORA01

USERID source, PASSWORD source

RMTHOST target, MGRPORT 7809

RMTTRAIL ./dirdat/01

TABLE source.TCUSTMER;

TABLE source.TCUSTORD;

端define the glodendate trail

GGSCI (source) 15> ADD RMTTRAIL ./dirdat/01,extract eora01 MEGABYTES 5

RMTTRAIL added.

GGSCI (source) 16> INFO RMTTRAIL *

Extract Trail: ./dirdat/01

Extract: EORA01

Seqno: 0

RBA: 0

File Size: 5M

端Start the capture process

GGSCI (source) 17> start extract eora01

Sending START request to MANAGER ('PAUL') ...

EXTRACT EORA01 starting

端編輯globals引數

ggsci

EDIT PARAMS ./GLOBALS

加入以下引數

CHECKPOINTTABLE target.ggschkpt

然後退出

重新登入ggsci才生效

端add a replicat checkpoint table

GGSCI (target) 1> dblogin userid target,password target

Successfully logged into database.

GGSCI (target) 2> add checkpointtable

No checkpoint table specified, using GLOBALS specification (target.ggschkpt)...

Successfully created checkpoint table TARGET.GGSCHKPT.

GGSCI (target) 3> ADD REPLICAT RORA01,EXTTRAIL ./dirdat/01

REPLICAT added.

GGSCI (target) 4> edit param rora01

--

-- Change Delivery parameter file to apply

-- TCUSTMER and TCUSTORD Changes

--

REPLICAT RORA01

USERID target, PASSWORD target

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA01.DSC, PURGE

MAP source.tcustmer, TARGET target.tcustmer;

MAP source.tcustord, TARGET target.tcustord;

GGSCI (target) 6> info replicat rora01

REPLICAT RORA01 Last Started 2010-01-24 01:59 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:03 ago)

Log Read Checkpoint File ./dirdat/01000000

First Record RBA 886

source端新增加些資料和target端驗證資料

C:\gg>sqlplus source/source

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 24 02:00:32 2010

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> @demo_ora_misc

1 row created.

Commit complete.

1 row created

1 row created.

Commit complete.

1 row updated.

Commit complete.

1 row deleted.

Commit complete.

3 rows deleted.

Rollback complete.

SQL>

檢視結果：

SQL> select * from tcustmer;

CUST NAME CITY ST

---- ------------------------------ -------------------- --

DAVE DAVE'S PLANES INC. TALLAHASSEE FL

BILL BILL'S USED CARS DENVER CO

ANN ANN'S BOATS NEW YORK NY

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

SQL> select * from tcustord;

CUST ORDER_DA PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID

---- -------- -------- ---------- ------------- -------------- --------------

BILL 95-12-31 CAR 765 14000 3 100

BILL 96-01-01 TRUCK 333 25000 15 100

WILL 94-09-30 CAR 144 16520 3 100

GGSCI (source) 21> send extract eora01,report

Sending REPORT request to EXTRACT EORA01 ...

Request processed.

GGSCI (source) 22> view report eora01

Report at 2010-01-24 02:04:38 (activity since 2010-01-24 02:00:53)

Output to ./dirdat/01:

From Table SOURCE.TCUSTMER:

# inserts: 3

# updates: 1

# deletes: 0

# discards: 0

From Table SOURCE.TCUSTORD:

# inserts: 3

# updates: 3

# deletes: 2

# discards: 0

在target端驗證資料是否傳過來

SQL> select * from tcustmer;

CUST NAME CITY ST

---- ------------------------------ -------------------- --

WILL BG SOFTWARE CO. SEATTLE WA

JANE ROCKY FLYER INC. DENVER CO

DAVE DAVE'S PLANES INC. TALLAHASSEE FL

BILL BILL'S USED CARS DENVER CO

ANN ANN'S BOATS NEW YORK NY

SQL> select * from tcustord;

CUST ORDER_DA PRODUCT_ ORDER_ID PRODUCT_PRICE PRODUCT_AMOUNT TRANSACTION_ID

---- -------- -------- ---------- ------------- -------------- --------------

WILL 94-09-30 CAR 144 16520 3 100

BILL 95-12-31 CAR 765 14000 3 100

BILL 96-01-01 TRUCK 333 25000 15 100

GGSCI (target) 7> SEND REPLICAT RORA01,report

Sending REPORT request to REPLICAT RORA01 ...

Request processed.

GGSCI (target) 8> view report rora01

Reading ./dirdat/01000000, current RBA 2960, 12 records

Report at 2010-01-24 02:08:17 (activity since 2010-01-24 02:01:14)

From Table SOURCE.TCUSTMER to TARGET.TCUSTMER:

# inserts: 3

# updates: 1

# deletes: 0

# discards: 0

From Table SOURCE.TCUSTORD to TARGET.TCUSTORD:

# inserts: 3

# updates: 3

# deletes: 2

# discards: 0

可以看到資料已經實時傳送過來。

Oracle GlodenGate 安裝與配置

1 oracle GlodenGate 概述

安裝

方法

source端配置mgr埠和啟動mgr程式

source端建立table和加入初始資料

端Add supplemental logging

target端配置mgr程式和啟動mgr程式

端建立target使用者和建立表

端create the extract parameter file

端define the glodendate trail

端Start the capture process

端編輯globals引數

端add a replicat checkpoint table

source端新增加些資料和target端驗證資料

相關文章