GoldenGate的安装与配置(原创)

系统环境

源端:

OS: Oracle Linux 5.4 32-bit

DB: Oracle Database 10gR2(10.2.0.1)

IP: 192.168.0.111

同步表名: scott.tcustmer,scott.tcustord

目标端:

OS: Oracle Linux 5.4 32-bit

DB: Oracle Database 10gR2(10.2.0.1)

IP: 192.168.0.113

同步表: scott.gg_test
复制架构图

安装GoldenGate

源端:将数据库设为Archive Mode，安装之前需要打开Oracle的supplemental log。目标端不需要此操作

SQL> archive log list

DATABASE log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log SEQUENCE     7

NEXT log SEQUENCE TO archive   9

CURRENT log SEQUENCE           9

SQL> ALTER DATABASE ADD supplemental log DATA;

DATABASE altered.

SQL> ALTER system switch logfile;

System altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database

SUPPLEME

--------

YES

注：SUPPLEMENTAL_LOG_DATA_MIN值为implicit亦可

查看源端ogg版本

$ ggsci -v

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x86, 32bit (optimized), Oracle 10g on Apr 23 2012 07:06:02

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
源端和目标端安装goldengate。

创建源端和目标端ogg目录

GGSCI (rac) 1> CREATE SUBDIRS

Creating subdirectories under current directory /home/oracle/ggs

Parameter files                /home/oracle/ggs/dirprm: created

Report files                   /home/oracle/ggs/dirrpt: created

Checkpoint files               /home/oracle/ggs/dirchk: created

Process status files           /home/oracle/ggs/dirpcs: created

SQL script. files               /home/oracle/ggs/dirsql: created

Database definitions files     /home/oracle/ggs/dirdef: created

Extract data files             /home/oracle/ggs/dirdat: created

Temporary files                /home/oracle/ggs/dirtmp: created

Veridata files                 /home/oracle/ggs/dirver: created

Veridata Lock files            /home/oracle/ggs/dirver/lock: created

Veridata Out-Of-Sync files     /home/oracle/ggs/dirver/oos: created

Veridata Out-Of-Sync XML files /home/oracle/ggs/dirver/oosxml: created

Veridata Parameter files       /home/oracle/ggs/dirver/params: created

Veridata Report files          /home/oracle/ggs/dirver/report: created

Veridata Status files          /home/oracle/ggs/dirver/status: created

Veridata Trace files           /home/oracle/ggs/dirver/trace: created

Stdout files                   /home/oracle/ggs/dirout: created
配置goldengate
创建goldengate管理用户
源端：创建goldengate管理用户，并赋予适当权限
SQL>Create user ggs identified by ggs;

SQL>GRANT CONNECT TO ggs;

SQL>GRANT ALTER ANY TABLE TO ggs;

SQL>GRANT ALTER SESSION TO ggs;

SQL>GRANT CREATE SESSION TO ggs;

SQL>GRANT FLASHBACK ANY TABLE TO ggs;

SQL>GRANT SELECT ANY DICTIONARY TO ggs;

SQL>GRANT SELECT ANY TABLE TO ggs;

SQL>GRANT RESOURCE TO ggs;

SQL>GRANT drop ANY TABLE TO ggs;

SQL>GRANT delete any table TO ggs;

SQL>GRANT execute on utl_fileE TO ggs;

SQL>GRANT execute on dbms_flashback TO ggs;

SQL>GRANT unlimited tablespace TO ggs;

目标端：创建goldengate管理用户，并赋予适当权限

SQL>Create user ggs identified by ggs;

SQL>GRANT CONNECT TO ggs;

SQL>GRANT ALTER ANY TABLE TO ggs;

SQL>GRANT ALTER SESSION TO ggs;

SQL>GRANT CREATE SESSION TO ggs;

SQL>GRANT update any table TO ggs;

SQL>GRANT SELECT ANY DICTIONARY TO ggs;

SQL>GRANT SELECT ANY TABLE TO ggs;

SQL>GRANT RESOURCE TO ggs;

SQL>GRANT drop ANY TABLE TO ggs;

SQL>GRANT delete any table TO ggs;

SQL>GRANT execute on utl_fileE TO ggs;

SQL>GRANT insert any table TO ggs;

SQL>GRANT unlimited tablespace TO ggs;

配置mgr

源端：创建编辑manager参数文件,PORT为7500,保存退出。

$ ggsci 

Oracle GoldenGate Command Interpreter FOR Oracle

Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100 

Solaris, x64, 64bit (optimized), Oracle 11g ON Oct  4 2011 23:54:06

Copyright (C) 1995, 2011, Oracle AND/OR its affiliates. ALL rights reserved.

GGSCI (rac1) 1> EDIT PARAMS MGR

GGSCI (rac1) 6> view params mgr

port 7500

dynamicportlist 7500-7505

autorestart extract *,waitminutes 2 ,retries 5

GGSCI (rac1) 4> START MGR

Manager started.
其中port参数标示mgr进程通信的端口，是配置mgr进程必须的。dynamicportlist(可选),表示manager进程可以为源端和目标端的动态通信时采用的动态端口。autorestart(可选)，表示如果extract进程失败，则每隔2分钟尝试重启一次，最多重启5次。默认为2分钟重启一次。

查看manager进程

$ ps -ef|grep mgr|grep -v grep

oracle    5277     1  0 10:12 ?        00:00:56 ./mgr PARAMFILE /home/oracle/ggs/dirprm/mgr.prm REPORTFILE /home/oracle/ggs/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

目标端：创建编辑manager参数文件,PORT为7500,保存退出。

$ ggsci 

Oracle GoldenGate Command Interpreter FOR Oracle

Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100 

Solaris, x64, 64bit (optimized), Oracle 11g ON Oct  4 2011 23:54:06

Copyright (C) 1995, 2011, Oracle AND/OR its affiliates. ALL rights reserved.

GGSCI (rac1) 1> EDIT PARAMS MGR

GGSCI (rac1) 6> view params mgr

port 7500

dynamicportlist 7500-7505

autorestart extract *,waitminutes 2 ,retries 5

GGSCI (rac1) 4> START MGR

Manager started.

查看manager进程

$ ps -ef|grep mgr|grep -v grep

oracle    5277     1  0 10:12 ?        00:00:56 ./mgr PARAMFILE /home/oracle/ggs/dirprm/mgr.prm REPORTFILE /home/oracle/ggs/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

配置表级补充日志
源端：

SQL> create table gg_test(id int primary key);

Table created.

GGSCI (rac1) 12> dblogin userid ggs,password ggs

Successfully logged into database.

GGSCI (rac1) 13> add trandata scott.gg_test 

Logging of supplemental redo data enabled for table SCOTT.GG_TEST.

注：goldengate中在表级配置补充日志必须先开启数据库级补充日志才能生效。

目标端：

SQL> create table gg_test(id int primary key);

Table created.
配置extract进程
源端：

GGSCI (rac1) 15> edit params eora

GGSCI (rac1) 16> view params eora

extract eora

dynamicresolution

userid ggs,password ggs

setenv(ORACLE_SID=orcl)

exttrail ./dirdat/et

table scott.gg_test;

dynamicresolution，默认值，表示在分析日志时遇到元数据即立刻操作，如创建表等。而不是一次性操作所有的元数据，nodynamicresolution。setenv，设置ORACLE_SID环境变量。exttrail，抽取的临时文件存放位置。table，需要操作的表。

GGSCI (rac1) 2> ADD EXTRACT EORA, TRANLOG, BEGIN NOW

EXTRACT added.

TRANLOG 表示使用事务日志的方式

BEGIN NOW 表示从现在开始,你也可以指定一个具体的开始时间

GGSCI (rac1) 42> add exttrail ./dirdat/et,extract eora

EXTTRAIL added.

配置extract进程的本地trail文件路径

GGSCI (rac1) 47> start eora

Sending START request to MANAGER ...

EXTRACT EORA starting
启动extract进程

配置pump进程
源端：

GGSCI (rac1) 18> edit params pump_so

GGSCI (rac1) 19> view params pump_so

extract pump_so

passthru

dynamicresolution

userid ggs,password ggs

rmthost 192.168.0.113, mgrport 7500

setenv(ORACLE_SID=orcl)

rmttrail ./dirdat/rt

table scott.gg_test;

rmthost配置远程主机ip或者主机名。rmttrail，配置远程主机存放的trail文件。passthru,data-pump在抽取时使用pass-through模式，不会检查表定义，速度更快。但是由于没有检查表定义，也就无法使用映射和转换函数。使用passthru时可以将数据抽取到没有安装数据库的中转服务器上。passthru可以和nopassthru配合使用，这样就可以针对某些表使用pass-through模式，某些表不使用pass-through模式。具体如下：
passthru

table scott.gg_passthru;

nopassthru

table scott.gg_nopassthru;

GGSCI (rac1) 46> add extract pump_so,exttrailsource ./dirdat/et

EXTRACT added.

配置pump_so进程的本地trail文件路径，exttrailsource直接指定trail文件的路径

GGSCI (rac1) 37> add rmttrail ./dirdat/rt  ,extract pump_so 

RMTTRAIL added.

配置pump_so进程的远程trail文件路径

GGSCI (rac1) 48> start pump_so

Sending START request to MANAGER ...

EXTRACT PUMP_SO starting

启动data-pump进程

GGSCI (rac1) 62> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           

EXTRACT     RUNNING     EORA        00:00:00      00:00:01    

EXTRACT     RUNNING     PUMP_SO     00:00:00      00:00:06  

配置检查点表

目标端：

GGSCI (rac2) 146> edit params ./GLOBALS

GGSCI (rac2) 147> view params ./GLOBALS

checkpointtable scott.checkczm

创建检查点表

GGSCI (rac2) 149> add checkpointtable

No checkpoint table specified, using GLOBALS specification (ggs.checkczm)...

Successfully created checkpoint table ggs.checkczm.
配置复制进程
目标端

GGSCI (rac2) 150> edit params pora

GGSCI (rac2) 151> view params pora

replicat pora

userid ggs, password ggs

assumetargetdefs

discardfile ./dirrpt/pora.dsc, purge

map scott.gg_test, target scott.gg_test;

assumetargetdefs，不进行表结构检查，直接复制数据。discardfile，将没有成功复制的数据信息记录到文件中，后面的purge选项，表示每次有记录新信息时删除之前的信息。map，对表进行映射，这里只是指定要操作的表scott.gg_test。

GGSCI (rac2) 161> add replicat pora ,exttrail ./dirdat/rt

REPLICAT added.
配置复制进程

GGSCI (rac2) 162> start pora

Sending START request to MANAGER ...

REPLICAT PORA starting
启动复制进程

验证数据同步
源端：

SQL> conn scott/scott;

Connected.

SQL> insert into gg_test values(1);

1 row created.

SQL> commit;

Commit complete.
目标端：

SQL> select * from gg_test;

        ID

----------

         1

数据成功同步

参考至：《GoldenGate Windows and UNIX Reference Guide 11g Release 2 Patch Set 1》
               http://www.code365.org/?p=1594
如有错误，欢迎指正
邮箱:[e-mail]