运行环境源数据端 linux + oracle11.2.0 ip=192.168.44.128目标数据端 window xp + oracle10.2.0.1.0 ip=192.168.1.103
一 下载下载goldengate软件,由于测试环境在两个不同的系统,所以分别下载windows和linux两个版本的goldengate下载地址:http://www.oracle.com/technetwork/cn/middleware/goldengate/downloads/index.html
window版本 ogg112101_ggs_Windows_x86_ora10g_32bit.ziplinux版本 ogg112101_fbo_ggs_Linux_x86_ora11g_32bit.zip
二 安装goldengate在源端和目标端分别安装goldengate,只需要解压缩就可以了。
(1)源端,通过mount命令将解压后的goldengate放在/usr/oracle_11/app 下修改权限chown -R oracle:oinstall ogg (ogg解压后改的文件夹名字)oracle用户登录 su - oraclecd /usr/oracle_11/app/ogg 到目录执行 ./ggsciGGSCI (ssbServer) 1> create subdirs 创建目录可能会出错关于oracle共享池解决办法[root@ssbServer lib]# ln -s /usr/oracle_11/app/oracle/product/11.2.0/lib/libnnet11.so /lib/libnnetl11.so[root@ssbServer lib]# ln -s /usr/oracle_11/app/oracle/product/11.2.0/lib/libclntsh.so.11.1 /lib/libclntsh.so.11.1
(2)目标端,直接将解压后goldengate放在E:\oracle\ogg (ogg解压后改的文件夹名字)E:\>cd E:\oracle\oggE:\oracle\ogg>ggsciGGSCI (songsb) 1> create subdirs
Creating subdirectories under current directory E:\oracle\ogg
Parameter files E:\oracle\ogg\dirprm: already existsReport files E:\oracle\ogg\dirrpt: createdCheckpoint files E:\oracle\ogg\dirchk: createdProcess status files E:\oracle\ogg\dirpcs: createdSQL script files E:\oracle\ogg\dirsql: createdDatabase definitions files E:\oracle\ogg\dirdef: createdExtract data files E:\oracle\ogg\dirdat: createdTemporary files E:\oracle\ogg\dirtmp: createdStdout files E:\oracle\ogg\dirout: created
三 配置数据库(1)源端:1) 查看规档与日志模式sqlplus / as sysdba
select log_mode,supplemental_log_data_min,force_logging from v$database;2) 配置为规档模式
shutdown immediate;
startup mount;
alter database archivelog;
alter database open;
alter system set log_archive_dest_1='location=/u01/archive' scope=both;3) 配置日志模式(打开强制规档与补充日志模式)
alter database add supplemental log data;
alter database force logging;
4)查看配置结果:
SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE SUPPLEME FOR
------------ -------- ---
ARCHIVELOG YES YES
5)源端:创建oracle用户 ogg 和ogg表空间 grant dba to ogg6)目标端:创建oracle用户 ogg 和ogg表空间 grant dba to ogg
四 配置goldengate 先配置DML同步源端 oracle用户1 编辑主进程组[oracle@ssbServer ~]$ cd /usr/oracle_11/app/ogg/[oracle@ssbServer ogg]$ ./ggsciGGSCI (ssbServer) 1> edit params mgr
port 7809
dynamicportlist 7800-8000
autorestart extract *,retries 5,waitminutes 2,resetminutes 5
说明:
port 指定mgr进程通信端口
dynamicportlist 表示mgr进程可以为源与目的端动态通信指定端口
autorestart extract 表示自动重启extract进程组,每2分钟尝试重启所有进程,重试5次,每5分钟清零。
配置参数后,重启mgr进程生效
2) 启动主管理进程
GGSCI (ssbServer) 2> start mgr
2. 配置Extract进程组
1) 编辑配置文件
edit params eora
说明:
extract eora定义extract进程名字
dynamicresolution
setenv设置环境变量
userid 登录数据库
exttrail指定本地trail文件地址
table 定义同步的表
2) 添加抽取进程
add extract eora, tranlog,begin now
3) 添加本地trail文件
add exttrail /usr/oracle_11/app/ogg/dirdat/et,extract eora
说明:
创建本地trail文件,主extract进程负责写这部分文件,pump负责把这部分文件传到目标服务器端。
4) 启动服务
start extract eora
GGSCI (ssbServer) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EORA 00:00:00 00:02:58
检查是否启动到RUNNING
如果没有启动到RUNNING,查看日志ggserr.log检查问题 位置:/u01/app/oracle/ogg
3. 配置Pump进程组
1) 编辑配置文件
edit params pump_so
extract pump_so
dynamicresolution
passthru
rmthost 192.168.1.103,mgrport 7809,compress
rmttrail E:\oracle\ogg\dirdat\pt
table ogg.*;
2) 添加pump进程
GGSCI (ssbServer) 13> add extract pump_so,exttrailsource /usr/oracle_11/app/ogg/dirdat/et
3) 添加远程trail文件
GGSCI (ssbServer) 14> add rmttrail E:\oracle\ogg\dirdat\pt,extract pump_so
说明: 指定远程trail文件
4) 启动pump进程
GGSCI (ssbServer) 15> start extract pump_so
查看状态
GGSCI (ssbServer) 16> 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:51
-------------源端配置完成--------
目标端配置
E:\oracle>cd ogg
E:\oracle\ogg>ggsci
GGSCI (songsb) 1> edit params mgr
port 7809
dynamicportlist 7800-8000
autostart er *
autorestart extract *,waitminutes 2,resetminutes 5
lagreporthours 1
laginfominutes 3
lagcriticalminutes 5
purgeoldextracts E:\oracle\ogg\dirdat\rt*,usecheckpoints,minkeepdays 3
GGSCI (songsb) 2> start mgr
Manager started.
查看状态
GGSCI (songsb) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
2. 添加检查表
GGSCI (songsb) 1> dblogin userid ogg,password ogg
Successfully logged into database.
GGSCI (songsb) 2> add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
GGSCI (songsb) 3> edit params ./GLOBALS
CHECKPOINTTABLE ogg.checkpoint
3. 配置目标端Peplicat进程组
1) 编辑配置文件
edit params repl
replicat repl
setenv (ORACLE_SID=ORCL) 本例中有两个实例,所以定义了环境变量
userid ogg,password ogg
assumetargetdefs
reperror default,discard
discardfile /u01/app/oracle/ogg/dirrpt/repl.dsc,append,megabytes 50
dynamicresolution
map hr.*, target hr.*;
2) 添加复制进程
add replicat repl,exttrail /u01/app/oracle/ogg/dirdat/pt, CHECKPOINTTABLE ogg.checkpoint
GGSCI (songsb) 8> start repl
Sending START request to MANAGER ...
REPLICAT REPL starting
查看状态
GGSCI (songsb) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPL 00:00:00 00:00:04
ogg用户登录后,发现有两张表CHECKPOINT,CHECKPOINT_LOX
注1:ogg_11.1.1.1.2只有CHECKPOINT一张表。
注2:ogg_11.2.x.x.1有CHECKPOINT,CHECKPOINT_LOX两张表。
5. DML配置测试
1)源端,创建一个表 create table test (id number,name varchar(20))
2)目标端,创建一个表 create table test (id number,name varchar(20))
3)源端insert into test values (1,'a')
4)查看目标端 select * From test
---------------------------------以上为dml的配置----------------------------------------
由于配置好后,源库执行了resetlogs操作后,golden无法正常工作,下面的重置过程
1.stop mgr
2.stop eora
3.stop pump_so
4.
GGSCI (ssbServer) 27> dblogin userid ogg,password ogg
Successfully logged into database.
5.
GGSCI (ssbServer) 28> delete extract eora
Deleted EXTRACT EORA.
6.GGSCI (ssbServer) 29> delete extract pump_soDeleted EXTRACT PUMP_SO.
7.重建extract进程,原eora和pump_so的参数不变GGSCI (ssbServer) 40> add extract eora,tranlog,begin nowEXTRACT added.GGSCI (ssbServer) 41> add exttrail /usr/oracle_11/app/ogg/dirdat/et,extract eoraEXTTRAIL added.GGSCI (ssbServer) 42> add extract pump_so,exttrailsource /usr/oracle_11/app/ogg/dirdat/etEXTRACT added.GGSCI (ssbServer) 43> add rmttrail E:\oracle\ogg\dirdat\pt,extract pump_soRMTTRAIL added.
start mgrstart eorastart pump_so
GGSCI (ssbServer) 50> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING EORA 00:00:00 00:00:08 EXTRACT RUNNING PUMP_SO 00:00:00 00:00:28
原文链接:oracle goldengate DML配置,转载请注明来源!