golden gate 加initial load 在rac 上的配置

前言
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的數據，性能實在太差了。