一、需求,使用OGG同步软件,将Oracle 11g Sequence实时同步到19c新库中
参考文档
二、测试同步
本次测试环境进行介绍：
DB，Source 11.2.0.4 Target 19.3
OGG,在第三台机器上,版本19.1，使用tns远程捕获及复制！
2.1 源端
2.1.1源库
sqlplus / as sysdba 运行SQL脚本，schema name输入OGG01 SQL>@sequence.sql grant execute on OGG01.updateSequence to OGG01; grant execute on OGG01.replicateSequence to OGG01; alter table sys.seq$ add supplemental log data (primary key) columns;
权限！
GRANT SELECT ANY TRANSACTION TO "OGG01";
OGG是如何进行同步sequence值？
How to Set 'Cache Size' of a Sequence Object? (Doc ID 378302.1)
alter sequence slow_sequence nocache;
The drawback to this is that every call for a new value will result in an update to
the SYS.SEQ$ table; an overhead that is unlikely to be acceptable in a high-speed OLTP system.
In fact I often advise DBAs to check their database for sequences which are high-usage
but defined with the default cache size of 20 - the performance benefits of altering
the cache size of such a sequence can be noticeable: a cache size of 1,000 is usually
sufficient. (NOTE: increasing the cache size of a sequence does not waste space, the
cache is still defined by just two numbers, the last used and the high water mark; it is
just that the high water mark is jumped by a much larger value every time it is reached
截取部分是通过SYS.SEQ$基表的改变，实现sequence值的同步！
dba_sequences LAST_NUMBER 值！！！
如果存在cache的情况，next sequence不可能百分百源端目标端一致, 但是保障目标端next>=source就可以了
2.1.2源OGG Global配置
Global配置 抽取进程排除OGG用户,不对这个用户抽取DDL！ ./ggsci edit params ./GLOBALS 增加下面一行内容 GGSCHEMA OGG01 Use the GGSCHEMA parameter to specify the name of the schema that contains the database objects that are owned by Oracle
GoldenGate, such as those that support DDL replication for trigger based replication and those that are part of the heartbeat
table implementation.The schema name specified with GGSCHEMA will be considered an excluded schema. Tables in this schema can
only be captured if explicitly specified with a non-wildcarded inclusion specification.
2.1.3源OGG Extract配置
DDL & INCLUDE OBJTYPE SEQUENCE & INCLUDE MAPPED OPTYPE ALTER & INCLUDE MAPPED OPTYPE CREATE & INCLUDE MAPPED OPTYPE DROP & INCLUDE MAPPED OBJTYPE 'TABLE' & INCLUDE MAPPED OBJTYPE 'INDEX' & EXCLUDE OPTYPE TRUNCATE & EXCLUDE OPTYPE COMMENT table testgbk1.*; sequence testgbk1.*;
注意： SEQUENCE需要放前面！ 如果放在INDEX下一行会被忽略，无法理解，但是就是这样。
可以重启抽取进程，开始抽取sequence的变化！
> FLUSH SEQUENCE testgbk1.seq5 2021-05-07 03:44:30 INFO OGG-15311 Successfully flushed 1 sequence(s) testgbk1.seq5. 这个步骤可以忽略！ 详情可以看最开始的MOS文档
存在问题可以进行操作,并且如果提示GGSCHEMA XXX 参数未配置,但是实际配置了的情况下,需要exit,重新./ggsci,最好restart extract在进行刷新
2.2 目标端
2.2.1 目标数据库
GRANT CREATE ANY sequence TO "C##OGG1" container=all; GRANT ALTER ANY sequence TO "C##OGG1" container=all; GRANT DROP ANY sequence TO "C##OGG1" container=all; GRANT SELECT ANY sequence TO "C##OGG1" container=all;
执行sequence脚本，建议在PDB中执行！
SQL>@sequence.sql
需要注意：其中ALTER 添加最小补充日志的操作，需要在CDB执行！
grant execute on C##OGG1.updateSequence to C##OGG1;
grant execute on C##OGG1.replicateSequence to C##OGG1;
alter table sys.seq$ add supplemental log data (primary key) columns;
SQL> ALTER TABLE "SYS"."SEQ$" ADD SUPPLEMENTAL LOG GROUP "GGS_100" ("OBJ#") ALWAYS /* GOLDENGATE_DDL_REPLICATION */;
2.2.2 复制进程配置
map testgbk1.*, target testgbk1.*,filter(@GETENV('TRANSACTION','CSN')>1027848);
本例是*，因此无需改变！
map testgbk1.seq5 , target testgbk1.seq5;
如果是基于每个表的操作,建议对每个seq和表一样的操作即可！
三、遇到的问题
3.1 权限不足,OGG复制进程不报错延迟一直递增
自从开始同步sequence后，复制进程重启后，Time Since Chkpt 一直递增，也不abend!!!???
REPLICAT RUNNING REP_DA 00:00:00 11:15:29
SESSION
SQL> select INST_ID,sid,serial#,USERNAME,STATUS,MACHINE,SQL_ID,EVENT,(sysdate-LOGON_TIME)*86400 as "s",LAST_CALL_ET
from gv$session where status='ACTIVE' and username is not null; USERNAME STATUS SQL_ID EVENT s LAST_CALL_ET --------- --------------------- ------------------------ ------------ C##OGG1 ACTIVE 2shzygd7syhy2 latch: shared pool 69 35 ---------------------------------------------- SELECT "SEQ_SEQ".NEXTVAL FROM DUAL
DB ALERT
2021-05-07T02:25:58.096895-07:00 PDBODB(3):----- PL/SQL Call Stack ----- object line object handle number name 0x678dd5e8 259 procedure C##OGG1.REPLICATESEQUENCE 0x67919d98 1 anonymous block PDBODB(3):WARNING: too many parse errors, count=164500 SQL hash=0x4f8f43c2 PDBODB(3):PARSE ERROR: ospid=19982, error=1031 for statement: 2021-05-07T02:25:58.181508-07:00 PDBODB(3):SELECT "SEQ_SEQ".NEXTVAL FROM DUAL PDBODB(3):Additional information: hd=0x67af0f58 phd=0x67b8ca40 flg=0x20 cisid=113 sid=113 ciuid=114 uid=114 sqlid=2shzygd7syhy2 PDBODB(3):...Current username=C##OGG1 PDBODB(3):...Application: GoldenGate Action: OGG$REP_DA - Apply Receiver
19C DB ALERT一直在刷屏！！！ error !!!
权限TEST
Source create sequence TESTGBK1.seq_seq minvalue 1 maxvalue 9999999999 start with 101 increment by 1 cache 50; Target SQL> select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where SEQUENCE_OWNER='TESTGBK1'; SEQUENCE_OWNER SEQUENCE_NAME -------------------- -------------------- TESTGBK1 SEQ_SEQ SQL> conn C##OGG1/oracle@T2UTF8 SELECT TESTGBK1."SEQ_SEQ".NEXTVAL FROM DUAL; ERROR at line 1: ORA-01031: insufficient privileges
Temp handle
Target ogg replicat stop ，db session 还是存在的！
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='C##OGG1';
Permanent treatment
GRANT SELECT ANY sequence TO "C##OGG1" container=all;
3.2 参数冲突
复制进程一直报啥调用REPLICATESEQUENCE遇到第七行XX报错！
PLS-00201: identifier 'OGG01.REPLICATESEQUENCE' must be declared
OGG-01004 Oracle GoldenGate Delivery for Oracle, repchl_c.prm: Aborted grouped transaction on 'CHANNEL.SEQ_POLICY_REPORT',
Database error 6550 (). OGG-01003 Oracle GoldenGate Delivery for Oracle, repchl_c.prm: Repositioning to rba 28315843 in seqno 5. OGG-01154 Oracle GoldenGate Delivery for Oracle, repchl_c.prm: SQL error 6550 mapping CHANNEL.SEQ_POLICY_REPORT to
CHANNEL.SEQ_POLICY_REPORT. OGG-01003 Oracle GoldenGate Delivery for Oracle, repchl_c.prm: Repositioning to rba 29190828 in seqno 5. OGG-01444 Oracle GoldenGate Delivery for Oracle, repchl_c.prm: Error in replicating sequence value [ORA-06550: line 1, column 7: PLS-00201: identifier 'OGG.REPLICATESEQUENCE' must be declared ORA-06550: line 1, column 7: 处理方案: 1).结合报错中出现的ORA错误进行分析; 2).如果类似本次报出的PLS-00201则表示sequence.sql没有执行引起的 ———————————————— 版权声明：本文为CSDN博主「司马松儆」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/smasegain/article/details/47050769
有点类似上面的报错，但是sequence.sql执行n次了！！！
最终检查发现，源端目标端共享一个./GLOBALS参数！ 参数里面 GGSCHEMA OGG01 写的是源端的OGG用户，注释参数后，问题解决！