欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

golden gate 加initial load 在rac 上的配置

程序员文章站 2022-08-31 16:09:27
前言goldengate 11g 在oracle 11g rac 上的配置 (源是rac+asm , 目标是单数据库实例) 源端: 1. 配置tnsnames [oracle@rac1 admin]$ more tnsnames.ora # tnsnames.ora Network Configur ......

前言
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的数据,性能实在太差了。