最近研究GoldenGate，此文记录入门第一步--如何安装。这里的操作系统为Solaris 10 64bit。

大家可以先从这里下载各种文档：

http://download.oracle.com/docs/cd/E15881_01/index.htm

1. 根据操作系统和数据库版本下载相应GG版本。下载地址：

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

由于我的机器是Solaris 10 64bit的，所以下载的文件为Oracle GoldenGate v10.4.0.x for Oracle 10g 64bit on Solaris 10 (53 MB)。

到相应目录解压。

2. 设定环境变量PATH和LD_LIBRARY_PATH

我在c shell下建立一个文件.gg，执行source .gg即可。
setenv PATH :$PATH
setenv LD_LIBRARY_PATH :$LD_LIBRARY_PATH

3. 目录安装

在GG的安装目录下，执行如下命令：

./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Solaris, sparc, 64bit (optimized), Oracle 10 on Sep 23 2009 15:19:42

GGSCI (xxx) 1> CREATE SUBDIRS

Creating subdirectories under current directory /xxx

Parameter files                /xxx/dirprm: created
Report files                   /xxx/dirrpt: created
Checkpoint files               /xxx/dirchk: created
Process status files           /xxx/dirpcs: created
SQL script. files               /xxx/dirsql: created
Database definitions files     /xxx/dirdef: created
Extract data files             /xxx/dirdat: created
Temporary files                /xxx/dirtmp: created
Veridata files                 /xxx/dirver: created
Veridata Lock files            /xxx/dirver/lock: created
Veridata Out-Of-Sync files     /xxx/dirver/oos: created
Veridata Out-Of-Sync XML files /xxx/dirver/oosxml: created
Veridata Parameter files       /xxx/dirver/params: created
Veridata Report files          /xxx/dirver/report: created
Veridata Status files          /xxx/dirver/status: created
Veridata Trace files           /xxx/dirver/trace: created
Stdout files                   /xxx/dirout: created

4. DDL support安装

建立一个专为GGS DDL安装的Oracle user，并赋予相应权限：

SQL> create user ggs identified by ggs;

User created.

SQL> grant connect,resource to ggs;

Grant succeeded.

SQL> GRANT EXECUTE ON UTL_FILE TO ggs;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO ggs;

Grant succeeded.

SQL> GRANT SELECT ANY TABLE TO ggs;

Grant succeeded.

由于GGS_DDL_HIST和GGS_MARKER会持续增长，所以最好单独建立一个tablespace给他们：

SQL> alter user ggs default tablespace GGSTBS;

User altered.

SQL> alter user ggs quota unlimited on GGSTBS;

User altered.

5. 打开Oracle supplemental log

SQL> select supplemental_log_data_min from v$database;

SUPPLEME
--------
NO

SQL> alter database add supplemental log data;

Database altered.

6. 启动Manager

在GGS安装目录下：

> ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 10.4.0.19 Build 002
Solaris, sparc, 64bit (optimized), Oracle 10 on Sep 23 2009 15:19:42

GGSCI (xxx) 1> EDIT PARAMS MGR

PORT 7809
~
"dirprm/mgr.prm" [New File] 1 line, 10 characters written

GGSCI (xxx) 2> start mgr

Manager started.

GGSCI (qadb120) 3> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

注：以上步骤需要在Source和Target都执行。

7. 在Source添加Extract Group

首先登入Source的Schema，添加EXTRACT进程和放在Target的TRAIL文件地址

GGSCI (xxx) 1> DBLOGIN USERID GGS@MOT, PASSWORD GGS
Successfully logged into database.

GGSCI (xxx) 2> add EXTRACT HAOEXT, tranlog,begin now
EXTRACT added.

GGSCI (xxx) 3> add RMTTRAIL ./dirdat/zh, extract HAOEXT, megabytes 50
RMTTRAIL added.

GGSCI (xxx) 4> info EXTRACT HAOEXT

EXTRACT    HAOEXT    Initialized   2010-04-21 09:43   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:00:23 ago)
Log Read Checkpoint Oracle Redo Logs
                     2010-04-21 09:43:39 Seqno 0, RBA 0

添加EXTRACT的parameter file，然后启动EXTRACT

GGSCI (xxx) 9> edit param HAOEXT

EXTRACT HAOEXT
SETENV (ORACLE_SID=MOT)
USERID GGS@MOT, PASSWORD GGS
RMTHOST xxx.xxx.xxx.xxx, MGRPORT 7809
RMTTRAIL ./dirdat/zh
TABLE HAOZHU_USER.GG1;
~
"dirprm/haoext.prm" [New File] 6 lines, 161 characters written

GGSCI (xxx) 10> start EXTRACT HAOEXT

Sending START request to MANAGER ...
EXTRACT HAOEXT starting

GGSCI (xxx) 12> info EXTRACT HAOEXT

EXTRACT    HAOEXT    Last Started 2010-04-21 10:19   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Log Read Checkpoint Oracle Redo Logs
                     2010-04-21 10:20:01 Seqno 59, RBA 157803008

8. 在Target端添加Replicat Group

先添加参数

GGSCI (xxx) 1> edit params mgr

PORT 7809
~
"dirprm/mgr.prm" 2 lines, 27 characters written

再添加REPLICAT Group

GGSCI (xxx) 5> add replicat HAOREP, EXTTRAIL ./dirdat/zh, nodbcheckpoint
REPLICAT added.

GGSCI (xxx) 6> edit param HAOREP

REPLICAT HAOREP
ASSUMETARGETDEFS
SETENV (ORACLE_SID=DC1)
USERID GGS, PASSWORD GGS
map HAOZHU_USER.GG1 , target HAOZHU_USER.GG2;
~
"dirprm/haorep.prm" 5 lines, 128 characters written

GGSCI (xxx) 7> start REPLICAT HAOREP

Sending START request to MANAGER ...
REPLICAT HAOREP starting

GGSCI (qadb121) 8> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING HAOREP 00:00:00 00:00:01

注意：在EDIT PARAMS 的时候，一定要加上最后的逗号，否则会报如下错，我就在这搞了很久。。

2010-04-21 12:30:45 GGS ERROR 101 Oracle GoldenGate Delivery for Oracle, haorep.prm: Parameter unterminated.
2010-04-21 12:30:45 GGS ERROR 190 Oracle GoldenGate Delivery for Oracle, haorep.prm: PROCESS ABENDING.

9. 测试复制

首先在Source检测EXTRACT没有任何信息：

GGSCI (xxx) 5> stats HAOEXT

Sending STATS request to EXTRACT HAOEXT ...

No active extraction maps.

在Source插入一行但不commit：

SQL> insert into gg1(OBJECT_ID,OBJECT_NAME) values(20122012,'test by hao');

1 row created.

这时，如预期，没有commit的信息不会被抓取：

GGSCI (xxx) 6> stats HAOEXT

Sending STATS request to EXTRACT HAOEXT ...

No active extraction maps.

但commit之后：

SQL> commit;

Commit complete.

就有EXTRACT的数据了：

GGSCI (xxx) 7> stats HAOEXT

Sending STATS request to EXTRACT HAOEXT ...

Start of Statistics at 2010-04-21 12:58:23.

Output to ./dirdat/zh:

Extracting from HAOZHU_USER.GG1 to HAOZHU_USER.GG1:

*** Total statistics since 2010-04-21 12:58:07 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

*** Daily statistics since 2010-04-21 12:58:07 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

*** Hourly statistics since 2010-04-21 12:58:07 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

*** Latest statistics since 2010-04-21 12:58:07 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

End of Statistics.

REPLICAT也有相应数据：

GGSCI (xxx) 1> stats haorep

Sending STATS request to REPLICAT HAOREP ...

Start of Statistics at 2010-04-21 13:05:51.

Replicating from HAOZHU_USER.GG1 to HAOZHU_USER.GG2:

*** Total statistics since 2010-04-21 12:58:10 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

*** Daily statistics since 2010-04-21 12:58:10 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

*** Hourly statistics since 2010-04-21 13:00:00 ***

No database operations have been performed.

*** Latest statistics since 2010-04-21 12:58:10 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

End of Statistics.

复制成功：

SQL> select OBJECT_ID,OBJECT_NAME from gg2 where OBJECT_ID=20122012;

OBJECT_ID
----------
OBJECT_NAME
--------------------------------------------------------------------------------
20122012
test by hao

【由此想到的问题：】

对于这种最简单的配置，是由Source端的EXTRACT进程直接写Target端的trail文件，但如果网络出现问题，就会对Source端产生问题。

我实验在Target端stop replicat and mgr,发现Traget的trail文件仍被写入。

> fuser zh000000
zh000000: 11482o
> ptree 11482
18089 zsched
11482 ./server -p 7840 -k -l /xxx/ggserr.log

于是kill -9 11482。

这时我再在Source端产生一点dml，结果发现此时Source端的EXTRACT进程死了。

GGSCI (xxx) 14> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING
EXTRACT ABENDED HAOEXT 00:00:00 00:02:18

然后在ggserr.log中我们发现有146 error：

2010-04-22 03:48:54 GGS WARNING 150 Oracle GoldenGate Capture for Oracle, haoext.prm: TCP/IP error 146 (Connection refused).

如何恢复？

首先在Target start mgr and replicat，然后在Source start extract。一切恢复正常。

但你会发现stats命令重新计数了：

GGSCI (xxx) 20> stats HAOEXT

Sending STATS request to EXTRACT HAOEXT ...

Start of Statistics at 2010-04-22 03:54:01.

Output to ./dirdat/zh:

Extracting from HAOZHU_USER.GG1 to HAOZHU_USER.GG1:

*** Total statistics since 2010-04-22 03:53:47 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

*** Daily statistics since 2010-04-22 03:53:47 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

*** Hourly statistics since 2010-04-22 03:53:47 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

*** Latest statistics since 2010-04-22 03:53:47 ***
        Total inserts                                1.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             1.00

End of Statistics.

所以我个人比较偏向于再在Source端配置一个data pump进程，用来专门负责网络传输。

而由EXTRACT进程抓出来的信息，先存放在本地的trail文件里。

这样，就和shareplex的capture queue是异曲同工了。

待续。。

ITPub博客

GoldenGate<一> step by step installation and configuration

最新文章