前言
goldengate 11g 在oracle 11g rac 上的配置 （源是rac+asm , 目标是单数据库实例）
源端：
1. 配置tnsnames
[oracle@rac1 admin]$ more tnsnames.ora
# tnsnames.ora network configuration file: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# generated by oracle configuration tools.
sunrac =
(description =
(address = (protocol = tcp)(host = rac1)(port = 1522))
(connect_data =
(server = dedicated)
(service_name = sunrac)
)
)
asm =
(description =
(address = (protocol = tcp)(host = rac1)(port = 1522))
(connect_data =
(server = dedicated)
(service_name = +asm)
(sid_name = +asm1)
)
)
我只是测试，所以只在第一个节点上做
2. 数据库环境准备，添加最小附加日志
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
创建测试表：
sql> conn test/test
connected.
sql> create table mxm (id int,name varchar2(80));
table created.
sql> begin
2 for i in 1 .. 1000000 loop
3 insert into mxm values (i,’mic’);
4 end loop;
5 commit;
6 end;
7 /
pl/sql procedure successfully completed.
sql> select count(*) from mxm;
count(*)
———-
1000000
sql> select bytes/1024/1024 from user_segments where segment_name=’mxm’; —看一下数据量，最后好算算传输率
bytes/1024/1024
—————
16
创建goldengate 用户并赋予dba 权限（避免权限的麻烦）
sql> create user ogg identified by ogg;
user created.
sql> grant dba to ogg;
grant succeeded.
3. 安装ogg
略
4. 配置ogg mgr
[oracle@rac1 goldengate]$ ./ggsci
ggsci (rac1) 1> create subdirs
配置mgr 端口：
ggsci (rac1) 1> edit param mgr
ggsci (rac1) 2> view param mgr
port 7809
ggsci (rac1) 3> start mgr
ggsci (rac1) 4> info all
5.配置抽取进程和传输进程
ggsci (rac1) 3> edit param exttest
ggsci (rac1) 4> view param exttest
extract exttest
userid ogg@sunrac,password ogg
tranlogoptions asmuser sys@asm,asmpassword beijing123 –登录asm的
exttrail ./dirdat/mm
table test.mxm;
ggsci (rac1) 6> edit param pumptest
ggsci (rac1) 7> view param pumptest
extract pumptest
rmthost 192.168.56.109,mgrport 7809,compress
rmttrail ./dirdat/mm
passthru
table test.mxm;
ggsci (rac1 as ogg@sunrac1) 10> add extract exttest,tranlog,begin now threads 2 –因为我的rac 是2个节点的，所以是threads 2
extract added.
ggsci (rac1 as ogg@sunrac1) 11> add exttrail ./dirdat/mm,extract exttest,megabytes 5
exttrail added.
ggsci (rac1 as ogg@sunrac1) 12> add extract pumptest,exttrailsource ./dirdat/mm –添加source dir
extract added.
ggsci (rac1 as ogg@sunrac1) 14> add rmttrail ./dirdat/mm,extract pumptest,megabytes 5 –添加remote dir
rmttrail added.
添加trandata
ggsci (rac1) 15> dblogin userid ogg@sunrac,password ogg
successfully logged into database.
ggsci (rac1 as ogg@sunrac1) 16> add trandata test.mxm
6. 配置init 进程
ggsci (rac1) 2> edit param inittest
ggsci (rac1) 4> view param inittest
extract inittest
userid ogg,password ogg
rmthost 192.168.56.109,mgrport 7809
rmttask replicat,group initrep –目标端init接收进程名
table test.mxm;
ggsci (rac1) 6> add extract inittest, sourceistable
extract added.
目标端
1. 创建相应的表
sql> create table mxm (id int,name varchar2(80));
table created.
2. 安装ogg
略
3. 配置mgr
ggsci (oracledg) 1> edit param mgr
ggsci (oracledg) 2> view param mgr
port 7809
accessrule, prog *, ipaddr 192.168.56.101, allow –没有这行，inittest 进程无法启动目标端的initrep进程
ggsci (oracledg) 4> start mgr
ggsci (oracledg) 5> edit params ./globals
ggsci (oracledg) 6> view params ./globals
ggschema ogg
checkpointtable ogg.checkpointtab
ggsci (oracledg) 8> dblogin userid ogg,password ogg
successfully logged into database.
ggsci (oracledg as ogg@mic) 9> add checkpointtable
4. 配置rep 进程
ggsci (oracledg as ogg@mic) 11> edit param reptest
ggsci (oracledg as ogg@mic) 12> view param reptest
replicat reptest
userid ogg,password ogg
handlecollisions
assumetargetdefs
discardfile ./dirrpt/reptest.dsc,append
map test.mxm, target test.mxm;
ggsci (oracledg as ogg@mic) 13> add replicat reptest,exttrail ./dirdat/mm
replicat added.
5.配置initrep 的进程
ggsci (oracledg as ogg@mic) 16> edit param initrep –名字必须和inittest进程中的配置一样
ggsci (oracledg as ogg@mic) 17> view param initrep
replicat initrep
userid ogg,password ogg
assumetargetdefs
discardfile ./dirrpt/initrep.dsc,append,megabytes 100
map test.mxm, target test.mxm;
开始同步和初始化
源端：
ggsci (rac1) 5> info all
program status group lag at chkpt time since chkpt
manager running
extract stopped exttest 00:00:00 02:46:12
extract stopped pumptest 00:00:00 02:43:19
这里看不到inittest 进程
启动抽取进程
ggsci (rac1) 6> start exttest
sending start request to manager …
extract exttest starting
启动pump进程
ggsci (rac1) 8> start pumptest
sending start request to manager …
extract pumptest starting
ggsci (rac1) 9> info all
program status group lag at chkpt time since chkpt
manager running
extract running exttest 00:00:00 00:00:11
extract running pumptest 00:00:00 00:00:02
启动inittest 进程
ggsci (rac1) 10> start inittest — 这个进程可以直接连通目标端的initrep进程，所以目标端不用再启动initrep进程
sending start request to manager …
extract inittest starting
模拟插入数据
sql> begin
2 for i in 1000000 .. 1000100 loop
3 insert into mxm values (i,’mic’);
4 dbms_lock.sleep(10);
5 end loop;
6 commit;
7 end;
8 /
目标端
启动rep进程，
ggsci (oracledg as ogg@mic) 1> start reptest
sending start request to manager …
replicat reptest starting
这里不需要启动initrep进程
源端
观察inittest进程情况
ggsci (rac1) 12> view report inittest
…
2019-02-26 14:48:31 warning ogg-06439 no unique key is defined for table mxm. all viable columns will be used to represent the key, but may not guarantee uniqueness. keycols
may be used to define the key.
2019-02-26 14:48:31 info ogg-06509 using the following key columns for source table test.mxm: id, name.
2019-02-26 14:48:37 info ogg-02911 processing table test.mxm.
***********************************************************************
* ** run time statistics ** *
***********************************************************************
report at 2019-02-26 14:52:39 (activity since 2019-02-26 14:48:31)
output to initrep:
from table test.mxm:
# inserts: 1000052
# updates: 0
# deletes: 0
# discards: 0
redo log statistics
bytes parsed 0
bytes output 76893373
可以看到初始或进程完成，用时大概4分钟多
停止insert sql,并查询总行数
sql> select count(*) from mxm;
count(*)
———-
1000121
目标端
sql> select count(*) from mxm;
count(*)
———-
1000121
至此，goldengate 同步initial load初始化配置完成 ， 但是用4分钟的时间传输了16m的数据，性能实在太差了。