共计 3942 个字符,预计需要花费 10 分钟才能阅读完成。
最近有几项业务下线,需要从一张表中删除 6.8 亿多条数据。想办法把数据删除掉了,但对应的 ogg 灾备端复制时有了的延迟,而且延迟的时间起来越长。
对于表太多造成的延迟可以把所有表分为多个组来做复制,于是想复制进程是否可以对单表复制开并行。上网查到了相关的资料,可以使用 @RANGE 函数对单表作表内的拆分,通过对表上主键列作 hash 算法将该表上发生的变更均分到多个 replicat 上来降低单个 replicat 组件的负载。
动手实验一下:
ogg 搭建过程不再重复,从网上就可以查到。
实验过程:模拟在源端对表 scott.emp1 做大量的 dml 操作,复制进程出现延迟,在目标端对复制表 scott.emp1 开并行 3 个进程。
源端插入数据:
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
目标端有延迟
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 分三个进程复制
# 源复制进程
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));
多复制出两个参数文件:
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 与源进程一致
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.
启动复制进程
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 对应的会话
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
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-05/143493.htm