GoldenGate的装配与配置(原创)

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 ggsSuccessfully 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 eoraGGSCI (rac1) 16> view params eoraextract eoradynamicresolutionuserid ggs,password ggssetenv(ORACLE_SID=orcl)exttrail ./dirdat/ettable scott.gg_test;dynamicresolution，默认值，表示在分析日志时遇到元数据即立刻操作，如创建表等。而不是一次性操作所有的元数据，nodynamicresolution。setenv，设置ORACLE_SID环境变量。exttrail，抽取的临时文件存放位置。table，需要操作的表。
GGSCI (rac1) 2> ADD EXTRACT EORA, TRANLOG, BEGIN NOWEXTRACT added.
TRANLOG 表示使用事务日志的方式BEGIN NOW 表示从现在开始,你也可以指定一个具体的开始时间
GGSCI (rac1) 42> add exttrail ./dirdat/et,extract eoraEXTTRAIL added.配置extract进程的本地trail文件路径
GGSCI (rac1) 47> start eoraSending START request to MANAGER ...EXTRACT EORA starting
启动extract进程配置pump进程
源端：GGSCI (rac1) 18> edit params pump_soGGSCI (rac1) 19> view params pump_soextract pump_sopassthrudynamicresolutionuserid ggs,password ggsrmthost 192.168.0.113, mgrport 7500setenv(ORACLE_SID=orcl)rmttrail ./dirdat/rttable 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/etEXTRACT 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_soSending START request to MANAGER ...EXTRACT PUMP_SO starting启动data-pump进程GGSCI (rac1) 62> info allProgram ? ? Status ? ? ?Group ? ? ? Lag at Chkpt ?Time Since ChkptMANAGER ? ? 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 poraGGSCI (rac2) 151> view params porareplicat porauserid ggs, password ggsassumetargetdefsdiscardfile ./dirrpt/pora.dsc, purgemap scott.gg_test, target scott.gg_test;assumetargetdefs，不进行表结构检查，直接复制数据。discardfile，将没有成功复制的数据信息记录到文件中，后面的purge选项，表示每次有记录新信息时删除之前的信息。map，对表进行映射，这里只是指定要操作的表scott.gg_test。GGSCI (rac2) 161> add replicat pora ,exttrail ./dirdat/rtREPLICAT added.
配置复制进程GGSCI (rac2) 162> start poraSending 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
如有错误，欢迎指正
邮箱:czmcj@163.com

查看更多 下一篇