ITPub博客

首页 > 数据库 > Oracle > GoldenGate的安装与配置

GoldenGate的安装与配置

Oracle 作者:shilei1 时间:2014-04-15 02:37:57 0 删除 编辑

系统环境

源端:

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

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

请登录后发表评论 登录
全部评论

注册时间:2018-10-10

  • 博文量
    548
  • 访问量
    31020