OGG下载地址
Download--> Middleware-->
实验环境介绍
OS:源和目标端都是RHEL5.4_64
DB:源和目标端都是11.2.0.1.0_64,都是归档模式
:源和目标端都是(86 MB)
DB_NAME、INSTANCE_NAME:源端都是DB51,目标端都是DB52
SYS、SYSTEM:源和目标端的密码都是123456
IP:源端10.98.20.51,目标端10.98.20.52
linux卸载Oracle GoldenGate产品:
1、进入源或目标GG配置目录,输入以下命令:
gg> stop *
gg> stop mgr
2、删除Oracle GoldenGate目录
实验步骤
1.使用oracle用户把ogg解压到源端的/u02/ogg,目标端的/u02/ogg2,并在源端的.bash_profile中加上export PATH=$PATH:/u02/ogg,目标端的.bash_profile中加上export PATH=$PATH:/u02/ogg2,当然另外两个变量LD_LIBRARY_PATH、CLASSPATH也一并加上。
2.源端cd /u02/ogg,目标端cd /u02/ogg2;再ggsci>create subdirs再退出ggsci
因为create subdirs需要建立在/u02/ogg、/u02/ogg2目录,所以需要进入该目录
3.源库保证如下
alter database archivelog;
select force_logging from v$database;
yes
开启方式:alter database force
logging;
select supplemental_log_data_min from
v$database; yes
开启方式:alter database add supplemental log data;
alter system set recyclebin=off
scope=spfile
4.源库和目标库都执行如下,创建OGG用户和测试表,并增加tns配置
create user ggs identified by ggs
default tablespace users temporary tablespace temp;
grant dba to ggs; //建立goldengate管理用户
conn ggs/ggs
create table demo(id number primary
key,ename varchar2(10)); //创建测试表,生产环境不用这这样创建这样的测试表
源库增加tns如下//ggs@sourcedb要用到
sourcedb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB51)
)
)
目标库增加tns如下//ggs@targetdb要用到
targetdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB52)
)
)
5.源端、目标端配置manger进程
cd /u02/ogg和cd /u02/ogg2
ggsci>edit params mgr
port 7809
6.源端配置extract捕获进程buhuo和extract传输进程chuanshu
捕获进程buhuo
cd /u02/ogg
ggsci>edit params buhuo输入如下
EXTRACT buhuo
USERID ggs@sourcedb, PASSWORD ggs
EXTTRAIL ./dirdat/ss
TABLE ggs.*;
ggsci>add extract buhuo,tranlog,begin now
ggsci>add exttrail ./dirdat/ss,extract buhuo,
megabytes 5
传输进程chuanshu
cd /u02/ogg
ggsci>edit params chuanshu输入如下
EXTRACT chuanshu
PASSTHRU
RMTHOST 10.98.20.52,MGRPORT 7809
RMTTRAIL ./dirdat/dd
TABLE ggs.*;
ggsci>add extract chuanshu,exttrailsource
./dirdat/ss
ggsci>add rmttrail ./dirdat/dd,extract
chuanshu,megabytes 5
传输进程chuanshu把捕获进程捕获存放在源端的/u02/ogg/dirdata/ssXXX文件存放到目标端10.98.20.52:/u02/ogg2/dirdata/ddXXX
7.目标端配置replicate复制进程fuzhi
复制进程fuzhi
cd /u02/ogg2
ggsci>dblogin userid ggs@targetdb,password
ggs
ggsci>add checkpointtable ggs.ggschkpt
ggsci>add replicat fuzhi,exttrail
./dirdat/dd,checkpointtable ggs.ggschkpt
ggsci>edit params fuzhi输入如下
replicat fuzhi
userid ggs@targetdb, password ggs
assumetargetdefs
reperror default, discard
discardfile ./dirrpt/poratt.dsc,
purge
map ggs.*, target ggs.*;
8.源库ggsci执行dblogin userid ggs@sourcedb,password
ggs
add trandata ggs.*
9.开启源端进程和目标端进程
源端
cd /u02/ogg
ggsci>start mgr
ggsci>start *
ggsci>info all
目标端
cd /u02/ogg2
ggsci>start mgr
ggsci>start *
ggsci>info all
上述配置只是配置了dml相关(只要commit后就会同步到目标库,不管目标库是否归档模式),如果源库增加一张表,压根不会同步到目标库
捕获进程buhuo把数据存放在源端/u02/ogg/dirdata/ssXXX
传输进程chuanshu把数据存传输到目标端/u02/ogg2/dirdata/ddXXX
DDL的标准配置新增如下
1.源端执行SQL> GRANT EXECUTE ON utl_file TO
ggs;
2.源和目标都关闭所有进程和服务
ggsci>stop mgr
ggsci>stop *
3.源端cd /u02/ogg下sqlplus / as sysdba执行如下
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> GRANT GGS_GGSUSER_ROLE to
ggs;
SQL> @ddl_enable.sql
SQL> @marker_status.sql
SQL> exit
4.目标端cd /u02/ogg2下sqlplus / as sysdba执行如下
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
5.源端ggsci下执行edit params buhuo新增如下(必须加,否则就不会捕获ddl的相关信息,只会捕获dml相关信息)
DDL INCLUDE ALL
6.源和目标ggsci启动所有进程和服务
ggsci>start mgr
ggsci>start *
后面新增的表没有主键也可以正常同步到目标库,且新增用户及用户权限都会自动同步到目标库;但是以后新建用户后新用户的表不会自动同步到目标库(貌似和windows版本的不一样),需要重新对源端参数文件增加TABLE ggs.*;目标端参数文件新增map schemaname.*, target schemaname.*;
新增配置时分号;不能少