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) 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