最近有几项业务下线，需要从一张表中删除6.8亿多条数据。想办法把数据删除掉了，但对应的ogg灾备端复制时有了的延迟，而且延迟的时间起来越长。
对于表太多造成的延迟可以把所有表分为多个组来做复制，于是想复制进程是否可以对单表复制开并行。上网查到了相关的资料，可以使用@RANGE函数对单表作表内的拆分，通过对表上主键列作hash算法将该表上发生的变更均分到多个replicat上来降低单个replicat组件的负载。
动手实验一下：
ogg搭建过程不再重复，从网上就可以查到。
实验过程：模拟在源端对表scott.emp1做大量的dml操作，复制进程出现延迟，在目标端对复制表scott.emp1开并行3个进程。
源端插入数据：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
insert
into
scott.emp1
select
*
from
scott.emp;
14
rows
created.
SQL>
commit
;
Commit
complete.
SQL>
insert
into
scott.emp1
select
*
from
scott.emp1;
14
rows
created.
SQL> /
28
rows
created.
SQL> /
.......
SQL> /
1835008
rows
created.
SQL>
commit
;
Commit
complete.
SQL>
select
count
(*)
from
scott.emp1;
COUNT
(*)
----------
3670016
|
目标端有延迟
|
1
2
3
4
5
6
|
GGSCI (rhel5) 15> info
all
Program Status
Group
Lag
Time
Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPTAB 00:09:08 00:00:04
|
停掉复制进程
拆分复制进程，对表scott.emp1分三个进程复制
#源复制进程
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
GGSCI (rhel5) 23>
view
params reptab
replicat reptab
SETENV (NLS_LANG=
"AMERICAN_AMERICA.ZHS16GBK"
)
SETENV (ORACLE_SID=
"orcl"
)
userid ogg,
password
123456
reperror
default
,discard
assumetargetdefs
discardfile /goldengate/dirrpt/reptab.dsc,append,megabytes 1024
gettruncates
dynamicresolution
map scott.emp1, target scott.emp1 ;
map scott.emp, target scott.emp ;
|
源进程修改为
map scott.emp1, target scott.emp1 ,FILTER(@RANGE(1,3));多复制出两个参数文件：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
GGSCI (rhel5) 1>
view
params reptab02
replicat reptab02
SETENV (NLS_LANG=
"AMERICAN_AMERICA.ZHS16GBK"
)
SETENV (ORACLE_SID=
"orcl"
)
userid ogg,
password
123456
reperror
default
,discard
assumetargetdefs
discardfile /goldengate/dirrpt/reptab.dsc,append,megabytes 1024
gettruncates
dynamicresolution
map scott.emp1, target scott.emp1 ,FILTER (@RANGE(2,3));
GGSCI (rhel5) 2>
view
params reptab03
replicat reptab03
SETENV (NLS_LANG=
"AMERICAN_AMERICA.ZHS16GBK"
)
SETENV (ORACLE_SID=
"orcl"
)
userid ogg,
password
123456
reperror
default
,discard
assumetargetdefs
discardfile /goldengate/dirrpt/reptab.dsc,append,megabytes 1024
gettruncates
dynamicresolution
map scott.emp1, target scott.emp1 ,FILTER (@RANGE(3,3));
|
添加两个复制进程，extseqno和extrba与源进程一致
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
GGSCI (rhel5) 9> info reptab
REPLICAT REPTAB
Last
Started 2017-05-05 16:18 Status ABENDED
Checkpoint
Lag 00:09:08 (updated 00:09:38 ago)
Log
Read
Checkpoint
File ./dirdat/tl000003
2017-05-05 16:09:11.000187 RBA 194186157
GGSCI (rhel5) 10>
add
replicat reptab02, exttrail ./dirdat/tl,extseqno 3 extrba 194186157,checkpointtable ogg.
checkpoint
REPLICAT added.
GGSCI (rhel5) 11>
add
replicat reptab03, exttrail ./dirdat/tl,extseqno 3 extrba 194186157,checkpointtable ogg.
checkpoint
REPLICAT added.
|
启动复制进程
|
1
2
3
4
5
6
7
8
9
10
|
GGSCI (rhel5) 12> start reptab*
Sending START request
to
MANAGER ...
REPLICAT REPTAB starting
Sending START request
to
MANAGER ...
REPLICAT REPTAB02 starting
Sending START request
to
MANAGER ...
REPLICAT REPTAB03 starting
|
查看数据库里ogg对应的会话
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
select
module,sql_id
from
v$session
where
username=
'OGG'
;
MODULE SQL_ID
------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------
OGG-REPTAB03-OPEN_DATA_SOURCE 1cxrusnmn01hz
OGG-REPTAB-OPEN_DATA_SOURCE 1cxrusnmn01hz
OGG-REPTAB02-OPEN_DATA_SOURCE 1cxrusnmn01hz
SQL>
select
sql_text
from
v$sqlarea
where
sql_id=
'1cxrusnmn01hz'
;
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT
INTO
"SCOTT"
.
"EMP1"
(
"EMPNO"
,
"ENAME"
,
"JOB"
,
"MGR"
,
"HIREDATE"
,
"SAL"
,
"COMM"
,
"DEPTNO"
)
VALUES
(:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7)
|
可以看到出现了三个会话，都是对应的对表scott.emp1的插入语句。也就是说实现了对scott.emp1表的并行复制。
MOS上也有相关的文档介绍相应的功能，文档：1320133.1和1512633.1
参考：blog.itpub.net/15187685/viewspace-1219731/
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1922484，如需转载请自行联系原作者
