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就可以了

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.

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下一行會被忽略，無法理解，但是就是這樣。

> 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在進行刷新

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 */;

map testgbk1.*, target testgbk1.*,filter(@GETENV('TRANSACTION','CSN')>1027848);
本例是*，因此無需改變！
map testgbk1.seq5 , target testgbk1.seq5;
如果是基於每個表的操作,建議對每個seq和表一樣的操作即可！

自從開始同步sequence後，複製進程重啟後，Time Since Chkpt 一直遞增，也不abend!!!???
REPLICAT    RUNNING     REP_DA      00:00:00      11:15:29    

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

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 !!!

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

Target ogg replicat stop ，db session 還是存在的！
select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where username='C##OGG1';

GRANT SELECT ANY sequence TO "C##OGG1" container=all;

複製進程一直報啥調用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