GoldenGate配置同步Sequence

48 阅读 0 评论 32 点赞

我是靠谱客的博主深情毛豆，最近开发中收集的这篇文章主要介绍GoldenGate配置同步Sequence，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

源/目标授权:
grant execute on ggs.updatesequence to ggs;
grant execute on ggs.replicatesequence to ggs;
grant execute on ggs.updatesequence to scstadmin;
grant execute on ggs.replicatesequence to scstadmin;
GRANT all ON sys.seq$ TO ggs;
GRANT all ON sys.user$ TO ggs;
GRANT all ON sys.obj$ TO ggs;
GRANT all ON dba_sequences TO ggs;
GRANT all ON sys.seq$ TO scstadmin;
GRANT all ON sys.user$ TO scstadmin;
GRANT all ON sys.obj$ TO scstadmin;
GRANT all ON dba_sequences TO scstadmin;

select grantee,granted_role from dba_role_privs where grantee in ('GGS','SCSTADMIN');

ALTER TABLE sys.seq$ add SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

cd /app/ogg
./ggsci
dblogin userid ggs,password ggsci
--add trandata scstadmin.seq_test_only
info trandata scstadmin.seq_test_only

源数据库添加extract组并配置参数
add extract eseq,tranlog,begin now thread 1
info extract *
> edit params eseq
extract eseq
userid ggs,password ggsci
rmthost 172.16.65.4, mgrport 7500
exttrail ./dirdat/lt
sequence scstadmin.seq_test_only;

添加源库抽取进程。注意，名称需要唯一，如被占用需修改成其他名字
> ADD EXTTRAIL ./dirdat/lt, EXTRACT eseq

创建源库传输进程参数文件
> edit params pseq
EXTRACT pseq
passthru
USERID ggs,PASSWORD ggsci
rmthost 172.16.65.4, mgrport 7500
RMTTRAIL ./dirdat/lt
sequence scstadmin.seq_test_only;

GGSCI (localdg) 6> add extract pseq,exttrailsource ./dirdat/lt

添加源库源端trail服务
GGSCI (localdg) 7> add rmttrail ./dirdat/rt,extract pseq

启动服务
GGSCI (localdg) 8> start eseq
GGSCI (localdg) 9> start pseq

目标端配置比较简单，添加复制进程即可，本例将newsduser抽取过来的数据应用到bbuser中：
1、添加复制进程
cd /app/ogg
./ggsci
GGSCI (fk-db1) 1> dblogin userid ogg,password ggsci
GGSCI (fk-db1) 2> add replicat rseq exttrail ./dirdat/rt checkpointtable --ogg.chkpt

2、修改复制进程参数
> edit params rseq
REPLICAT rseq
USERID ggs,PASSWORD ggsci
dboptions suppresstriggers
PURGEOLDEXTRACTS ./dirdat/rt*,usecheckpoints, minkeepdays 7
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rseq.dsc,PURGE
MAP scstadmin.*,TARGET scstadmin.*;

3、启动复制进程
GGSCI (fk-db1) 4> start rseq

4、查看进程状态
GGSCI (localdg) 8> info all

dblogin userid ggs,password ggsci
flush sequence scstadmin.seq_test_only

dblogin userid ggs,password ggsci
flush sequence scstadmin.seq_test_only

select scstadmin.seq_test_only.nextval from dual;

#################################################
源/目标库都在oracle DBA用户下运行OGG安装目录下的sequence.sql脚本，然后输入ggs用户作为生成的过程的属主。
SQL> @sequence.sql
完成后进行授权：
grant execute on ggs.updatesequence to ggs;
grant execute on ggs.replicatesequence to ggs;
grant execute on ggs.updatesequence to scstadmin;
grant execute on ggs.replicatesequence to scstadmin;
GRANT select ON sys.seq$ TO ggs;
GRANT select ON sys.user$ TO ggs;
GRANT select ON sys.obj$ TO ggs;
GRANT select ON dba_sequences TO ggs;
GRANT select ON sys.seq$ TO scstadmin;
GRANT select ON sys.user$ TO scstadmin;
GRANT select ON sys.obj$ TO scstadmin;
GRANT select ON dba_sequences TO scstadmin;

select grantee,granted_role from dba_role_privs where grantee in ('GGS','SCSTADMIN');

源数据库配置：ALTER TABLE sys.seq$ add SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

编辑GLOBALS文件，添加GGSCHEMA参数，退出重新登录生效。
GGSCI (mesdb1) 1> view params ./GLOBALS

GGSCHEMA ggs

GGSCI (mesdb1) 2> view params EXT1

extract ext1
userid ggs,password ggsci
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
rmthost 172.16.65.4, mgrport 7500
rmttrail /app/ogg/dirdat/r1
ddl include all
dynamicresolution
gettruncates
--TABLEEXCLUDE MES.PACKING
table SCSTADMIN.*;
sequence SCSTADMIN.*;

-------------------------------

GGSCI (mesdb2) 1> view params ./GLOBALS

GGSCHEMA ggs
checkpointtable ggs.checkpoint

GGSCI (mesdb2) 2> view params REPL

replicat repl
userid ggs, password ggsci
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
ddl include all
ddlerror default ignore retryop maxretries 3 retrydelay 5
assumetargetdefs
APPLYNOOPUPDATES
reperror default, discard
discardfile /app/ogg/discard/repl.log,append,megabytes 229999
map SCSTADMIN.*,target SCSTADMIN.*;

目标端序列会比源端大1，也就是选择nextval的时候会大2.
SQL> select scstadmin.seq_test_only.nextval from dual;

NEXTVAL
----------
303

SQL> select scstadmin.seq_test_only.nextval from dual;

NEXTVAL
----------
305

PURGEOLDEXTRACTS ./dirdat/r1*,usecheckpoints, minkeepdays 30

dblogin userid ggs,password ggsci
flush sequence scstadmin.seq_test_only

dblogin userid ggs,password ggsci
flush sequence scstadmin.seq_test_only

select scstadmin.seq_test_only.nextval from dual;