【GoldenGate】GoldenGate的安装与配置

系统环境

源端:

OS: RedHat Linux 6.3 64-bit

DB: Oracle Database 11gR2(11.2.0.1)

IP: 192.168.1.226

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

 

目标端:

OS: RedHat Linux 6.3 64-bit

DB: Oracle Database 11gR2(11.2.0.1)

IP: 192.168.1.228

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

一、安装GoldenGate

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

sys@ORCL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     338

Next log sequence to archive   340

Current log sequence           340

sys@ORCL> select supplemental_log_data_min from v$database;

SUPPLEME

--------

NO

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亦可

2,源端和目标端安装goldengate

mkdir /app/ogg

3,查看源端ogg版本

[oracle@zjtdbrac1 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.

 

4,创建源端和目标端ogg目录

A，创建源端ogg目录

GGSCI (zjtdbrac1) 1> create subdirs 

Creating subdirectories under current directory /app/ogg

Parameter files                /app/ogg/dirprm: already exists

Report files                   /app/ogg/dirrpt: created

Checkpoint files               /app/ogg/dirchk: created

Process status files           /app/ogg/dirpcs: created

SQL script. files               /app/ogg/dirsql: created

Database definitions files     /app/ogg/dirdef: created

Extract data files             /app/ogg/dirdat: created

Temporary files                /app/ogg/dirtmp: created

Stdout files                   /app/ogg/dirout: created

 

 

B，创建目标端ogg目录

GGSCI (zjtdbrac2) 1> create subdirs 

Creating subdirectories under current directory /app/ogg

Parameter files                /app/ogg/dirprm: already exists

Report files                   /app/ogg/dirrpt: created

Checkpoint files               /app/ogg/dirchk: created

Process status files           /app/ogg/dirpcs: created

SQL script. files               /app/ogg/dirsql: created

Database definitions files     /app/ogg/dirdef: created

Extract data files             /app/ogg/dirdat: created

Temporary files                /app/ogg/dirtmp: created

Stdout files                   /app/ogg/dirout: created

二、配置goldengate
1，创建goldengate管理用户
A，源端：创建goldengate管理用户，并赋予适当权限
sys@ORCL> create user ggs identified by ggs;

User created.

sys@ORCL> grant dba to ggs;

Grant succeeded.

sys@ORCL> grant connect to ggs;

Grant succeeded.

sys@ORCL> grant alter any table to ggs;

Grant succeeded.

sys@ORCL> grant alter session to ggs;

Grant succeeded.

sys@ORCL> grant create session to ggs;

Grant succeeded.

sys@ORCL> grant flashback any table to ggs;

Grant succeeded.

sys@ORCL> grant select any dictionary to ggs;

Grant succeeded.

sys@ORCL> grant resource to ggs;

Grant succeeded.

sys@ORCL> grant drop any table to ggs;

Grant succeeded.

sys@ORCL> grant delete any table to ggs;

Grant succeeded.

sys@ORCL> grant execute on utl_file to ggs;

Grant succeeded.

sys@ORCL> grant execute on dbms_flashback to ggs;

Grant succeeded.

sys@ORCL> grant unlimited tablespace to ggs;

Grant succeeded.

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

sys@ORCL> create user ggs identified by ggs;

User created.

sys@ORCL> grant dba to ggs;

Grant succeeded.

sys@ORCL> grant connect to ggs;

Grant succeeded.

sys@ORCL> grant alter any table to ggs;

Grant succeeded.

sys@ORCL> grant alter session to ggs;

Grant succeeded.

sys@ORCL> grant create session to ggs;

Grant succeeded.

sys@ORCL> grant update any table to ggs;

Grant succeeded.

sys@ORCL> grant select any dictionary to ggs;

Grant succeeded.

sys@ORCL> grant select any table to ggs;

Grant succeeded.

sys@ORCL> grant resource to ggs;

Grant succeeded.

sys@ORCL> grant drop any table to ggs;

Grant succeeded.

sys@ORCL> grant delete any table to ggs;

Grant succeeded.

sys@ORCL> grant execute on utl_file to ggs;

Grant succeeded.

sys@ORCL> grant insert any table to ggs;

Grant succeeded.

sys@ORCL> grant unlimited tablespace to ggs;

Grant succeeded.

 

2，配置mgr

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

[oracle@zjtdbrac1 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 (zjtdbrac1) 1> edit params mgr

port 7500

dynamicportlist 7500-7505

autorestart extract *,waitminutes 2 ,retries 5

 

"dirprm/mgr.prm" [New] 5L, 85C written

 

GGSCI (zjtdbrac1) 2> view params mgr

port 7500

dynamicportlist 7500-7505

autorestart extract *,waitminutes 2 ,retries 5

GGSCI (zjtdbrac1) 3> start mgr

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

查看manager进程

[oracle@zjtdbrac1 ogg]$ ps -ef|grep mgr|grep -v grep

root        11     2  0 11:50 ?        00:00:00 [async/mgr]

postfix   1923  1887  0 11:52 ?        00:00:01 qmgr -l -t fifo -u

oracle    2688     1  1 13:54 ?        00:00:01 ./mgr PARAMFILE /app/ogg/dirprm/mgr.prm REPORTFILE /app/ogg/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

 

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

[oracle@zjtdbrac2 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 (zjtdbrac2) 1> edit params mgr

port 7500

dynamicportlist 7500-7505

autorestart extract *,waitminutes 2 ,retries 5

"dirprm/mgr.prm" [New] 4L, 84C written

GGSCI (zjtdbrac2) 2> view params mgr

port 7500

dynamicportlist 7500-7505

autorestart extract *,waitminutes 2 ,retries 5

GGSCI (zjtdbrac2) 3> start mgr

Manager started.

查看manager进程

[oracle@zjtdbrac2 ogg]$ ps -ef|grep mgr|grep -v grep

root        11     2  0 11:51 ?        00:00:00 [async/mgr]

postfix   1726  1702  0 11:54 ?        00:00:00 qmgr -l -t fifo -u

oracle    3166     1  1 13:58 ?        00:00:01 ./mgr PARAMFILE /app/ogg/dirprm/mgr.prm REPORTFILE /app/ogg/dirrpt/MGR.rpt PROCESSID MGR PORT 7809

 

3，配置表级补充日志
A，源端：

[oracle@zjtdbrac1 ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 11 14:01:10 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

scott@ORCL> create table gg_test(id int primary key);

Table created.

GGSCI (zjtdbrac1) 1> dblogin userid ggs,password ggs

Successfully logged into database.

GGSCI (zjtdbrac1) 5> add trandata scott.gg_test

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

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

 

B，目标端：

scott@ORCL> create table gg_test(id int primary key);

Table created.

4，配置extract进程
A，源端：

GGSCI (zjtdbrac1) 1> edit params eora

extract eora

dynamicresolution

userid ggs,password ggs

setenv(ORACLE_SID=orcl)

exttrail ./dirdat/et

table scott.gg_test;

"dirprm/eora.prm" [New] 6L, 121C written

 

GGSCI (zjtdbrac1) 2> 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 (zjtdbrac1) 3> add extract eora,tranlog,begin now

EXTRACT added.

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

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

GGSCI (zjtdbrac1) 4> add exttrail ./dirdat/et,extract eora

EXTTRAIL added.

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

GGSCI (zjtdbrac1) 5> start eora

Sending START request to MANAGER ...

EXTRACT EORA starting
启动extract进程

 

5，配置pump进程
A，源端：

GGSCI (zjtdbrac1) 6> edit params pump_so

extract pump_so

passthru

dynamicresolution

userid ggs,password ggs

rmthost 192.168.1.228, mgrport 7500

setenv(ORACLE_SID=orcl)

rmttrail ./dirdat/rt

table scott.gg_test;

"dirprm/pump_so.prm" [New] 9L, 168C written

 

 

GGSCI (zjtdbrac1) 7> view params pump_so 

extract pump_so

passthru

dynamicresolution

userid ggs,password ggs

rmthost 192.168.1.228, 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 (zjtdbrac1) 8> add extract pump_so,exttrailsource ./dirdat/et

EXTRACT added.

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

GGSCI (zjtdbrac1) 9> add rmttrail ./dirdat/rt ,extract pump_so

RMTTRAIL added.

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

GGSCI (zjtdbrac1) 10> start pump_so

Sending START request to MANAGER ...

EXTRACT PUMP_SO starting

启动data-pump进程

GGSCI (zjtdbrac1) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

EXTRACT     RUNNING     EORA        00:00:00      00:00:05   

EXTRACT     RUNNING     PUMP_SO     00:00:00      00:00:03      

 

 

6，配置检查点表

A，目标端：

GGSCI (zjtdbrac2) 1>  edit params ./GLOBALS

checkpointtable scott.checkczm

"./GLOBALS" [New] 1L, 31C written

 

GGSCI (zjtdbrac2) 2> view params ./GLOBALS

checkpointtable scott.checkczm

 

创建检查点表

GGSCI (zjtdbrac2) 3> dblogin userid ggs,password ggs

Successfully logged into database.

GGSCI (zjtdbrac2) 5> add checkpointtable scott.checkczm

Successfully created checkpoint table scott.checkczm.

7，配置复制进程
A，目标端

GGSCI (zjtdbrac2) 5> edit params pora

replicat pora

userid ggs, password ggs

assumetargetdefs

discardfile ./dirrpt/pora.dsc, purge

map scott.gg_test, target scott.gg_test;

"dirprm/pora.prm" [New] 5L, 134C written

 

 

GGSCI (zjtdbrac2) 6> 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 (zjtdbrac2) 2> add replicat pora,exttrail ./dirdat/rt 

REPLICAT added.
配置复制进程

GGSCI (zjtdbrac2) 3> start pora

Sending START request to MANAGER ...

REPLICAT PORA starting
启动复制进程

 

三、验证数据同步
A，源端：

SQL> conn scott/tiger;

Connected.

SQL> insert into gg_test values(1);

1 row created.

SQL> commit;

Commit complete.

B，目标端：

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

 

 

 

 

 

 

补充：

1，停止复制

①停止Capture（在源端）

②停止Data Pump（在源端）

③停止Replicat（在目标端）

GGSCI (zjtdbrac1) 4> stop mgr

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)? y

 

Sending STOP request to MANAGER ...

Request processed.

Manager stopped.

 

 

GGSCI (zjtdbrac1) 5> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

 

MANAGER     STOPPED                                          

EXTRACT     RUNNING     EORA        00:00:00      00:00:10   

EXTRACT     RUNNING     PUMP_SO     00:00:00      00:00:09   

 

GGSCI (zjtdbrac1) 6> stop eora

Sending STOP request to EXTRACT EORA ...

Request processed.

 

 

GGSCI (zjtdbrac1) 7> stop pump_so

Sending STOP request to EXTRACT PUMP_SO ...

Request processed.

 

 

GGSCI (zjtdbrac2) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          

REPLICAT    RUNNING     PORA        00:00:00      00:00:00   

 

 

GGSCI (zjtdbrac2) 12> stop mgr

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER ...

Request processed.

Manager stopped.

 

 

GGSCI (zjtdbrac2) 13> stop pora

Sending STOP request to REPLICAT PORA ...

Request processed.

 

2，配置的查询

• 查询配置的主要命令

• 查询配置与状态：INFO命令

• 查询处理情况：VIEW REPORT命令

GGSCI> INFO 名> [detail]

GGSCI> VIEW REPORT 名>

1.jpg

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14359/viewspace-772408/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14359/viewspace-772408/