1 oracle GlodenGate 概述

GlodenGate是oracle 公司收购glodendate的实时异构数据库数据集成产品，目前已经成功应用于美国银行，中华人民共和国海关总署，国家税务总局等。

2 实验环境

两套虚拟机Oracle 10.2.0.1+windows 2003 32 bit

源端oracle数据库用户source 主机名为source

目标端oracle数据库用户target 主机名为target

3 Oracle GlodenGate for windows安装

进入以下网站下载oracle glodendate安装软件

http://www.oracle.com/technology/software/products/goldengate/index.html

这里为32bit for windows平台下的安装软件

GGV18162-01ForOracle10g.zip

直接用unzip解压到c:\GG目录下

3.1 创建文件夹

点击运行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;

3.2 在源库创建数据库source用户给予dba权限

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

3.3 安装GlodenGate 管理进程

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

4 GlodenGate oracle to oracle initial data load using Direct Load方法

4.1 在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进程成功

4.2 在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

4.3 在source端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

4.4 在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进程成功

4.5 在target端创建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

4.6 Initial data load using direct load method

在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端到目标端初始化数据成功

5 配置捕捉变化数据进程

Configure Change Capture

5.1 在source端ADD the Extract group

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

5.2 在source端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;

5.3 在source端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

5.4 在source端Start the capture process

GGSCI (source) 17> start extract eora01

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

EXTRACT EORA01 starting

5.5 在target端编辑globals参数

ggsci

EDIT PARAMS ./GLOBALS

加入以下参数

CHECKPOINTTABLE target.ggschkpt

然后退出

重新登录ggsci才生效

5.6 在target端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.

5.7 在target端add the replicat group

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

REPLICAT added.

5.8 Create Replicat parameter file

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;

5.9 Start the Replicat process

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

5.10在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

可以看到数据已经实时传送过来。

ITPub博客

Oracle GlodenGate 安装与配置

1 oracle GlodenGate 概述

2 实验环境

3 Oracle GlodenGate for windows安装

3.1 创建文件夹

3.2 在源库创建数据库source用户给予dba权限

3.3 安装GlodenGate 管理进程

4 GlodenGate oracle to oracle initial data load using Direct Load方法

4.1 在source端配置mgr端口和启动mgr进程

4.2 在source端创建table和加入初始数据

4.3 在source端Add supplemental logging

4.4 在target端配置mgr进程和启动mgr进程

4.5 在target端创建target用户和创建表

4.6 Initial data load using direct load method

5 配置捕捉变化数据进程

5.1 在source端ADD the Extract group

5.2 在source端create the extract parameter file

5.3 在source端define the glodendate trail

5.4 在source端Start the capture process

5.5 在target端编辑globals参数

5.6 在target端add a replicat checkpoint table

5.7 在target端add the replicat group

5.8 Create Replicat parameter file

5.9 Start the Replicat process

5.10在source端新增加些数据和target端验证数据

最新文章