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;