OGG集成注册语法错误导致启动失败"SYS.DBMS_XSTREAM_GG_ADM", line 235

一、复制进程启动报错

如果有遇到相同报错的小伙伴，注意，复制进程使用抽取模式进行集成注册成功后，启动之后报错！！！ 直接删除重建就好了！

但是各种搜索资料没有找到匹配的，不细心害死人，还有一个问题，报错根本没有往模式错误方向走！

这个文档没什么技术，主要是帮助后续也是语法错误小伙伴快速定位问题！

> edit param rep_a1    
replicat rep_a1
setenv ORACLE_SID=gbkt1
userid ogg2,password SGcc_osgsac01
SQLEXEC "ALTER SESSION SET CONSTRAINTS=DEFERRED"
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
DISCARDFILE ./dirrpt/rep_a1.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
GETTRUNCATES
map SCOTT.A1, target SCOTT1.A1;

> add replicat rep_a1,INTEGRATED,exttrail /u01/ogg/base/dirdat/a1/a1, CHECKPOINTTABLE ogg2.a1
dblogin USERID OGG2,PASSWORD SGcc_osgsac01
register EXTRACT rep_a1 database
start rep_a1     Oracle??? 我明明是复制进程，你偏偏还使用抽取模式注册还成功了！！！

 

然后复制进程报错
ERROR   OGG-00664  Oracle GoldenGate Delivery for Oracle, rep_a1.prm:  OCI Error ORA (status = 26668-ORA-26668: GoldenGate process OGG$REP_A1 exists
                              ORA-06512: at "SYS.DBMS_XSTREAM_GG_ADM", line 235
                              ORA-06512: at line 1
                              ).

[oracle@t1 trace]$ oerr ora 26668
26668, 00000, "%s process %s exists"
// *Cause:  An attempt to remove the component failed because it 
//          is associated with the GoldenGate, XStream or Streams process.
// *Action: Either remove the process manually or specify the "cascade" 
//          option in dbms_streams_adm.remove_queue.
[oracle@t1 trace]$ oerr ora 06512
06512, 00000, "at %sline %s"
// *Cause:   Backtrace message as the stack is unwound by unhandled
//           exceptions.
// *Action:  Fix the problem causing the exception or write an exception
//           handler for this condition. Or you may need to contact your
//           application administrator or DBA.
dbms_streams_adm.remove_queue

二.问题分析

这里走了严重的弯路，没有仔细阅读语法，反而根据报错进行分析！！！

模式都不对，因此取消集成注册提示无法进行操作
unregister REPLICAT REP_A1 database
使用这个命令直接删除了非常非常多的级联对象，慎重操作！
exec dbms_streams_adm.remove_queue(queue_name=>'OGG$REP_A1',cascade=>true,drop_unused_queue_table=>true);


select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where upper(object_name) like '%REP_A1%';
OWNER                OBJECT_NAME                    OBJECT_TYPE
-------------------- ------------------------------ -------------------
SYS                  OGG$CAP_REP_A1                 CAPTURE
SYS                  OGG$REP_A1                     APPLY
OGG2                 OGG$Q_TAB_REP_A1               TABLE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_S         TABLE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_V         EVALUATION CONTEXT
OGG2                 AQ$_OGG$Q_TAB_REP_A1_T         TABLE
OGG2                 AQ$OGG$Q_TAB_REP_A1_S          VIEW
OGG2                 AQ$_OGG$Q_TAB_REP_A1_N         SEQUENCE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_H         TABLE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_L         TABLE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_G         TABLE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_I         TABLE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_C         TABLE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_Y         INDEX
OGG2                 AQ$_OGG$Q_TAB_REP_A1_E         QUEUE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_F         VIEW
OGG2                 AQ$OGG$Q_TAB_REP_A1            VIEW
OGG2                 OGG$Q_REP_A1_R                 RULE SET
OGG2                 OGG$Q_REP_A1_N                 RULE SET
OGG2                 AQ$OGG$Q_TAB_REP_A1_R          VIEW
OGG2                 OGG$Q_REP_A1                   QUEUE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_P         TABLE
OGG2                 AQ$_OGG$Q_TAB_REP_A1_D         TABLE

23 rows selected.
SQL> drop user ogg2 cascade;

SQL> SELECT SERVER_NAME FROM SYS.XSTREAM$_SERVER WHERE SERVER_NAME like '%REP_A1%';
SERVER_NAME
------------------------------
OGG$REP_A1
SQL> delete from sys.xstream$_server where SERVER_NAME = 'OGG$REP_A1';
1 row deleted.
SQL> commit;
Commit complete.

SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where upper(object_name) like '%REP_A1%';
no rows selected

Unable To Add Integrated Replicat: OGG-00662 OCI Error ORA-26668: GoldenGate process OGG$<replicat> exists (Doc ID 2557674.1)    


2021-06-06 15:19:25  ERROR   OGG-00446  No data selecting position from checkpoint table ogg2.a1 for group REP_A1, key 2
,895,583,779 (0x00000000ac971a23).
GGSCI (t1) 4> alter replicat rep_a1 begin now
GGSCI (t1) 5> start rep_a1

根据上述操作，无法解决这个问题，使用如下，完整清理了一波，重点是使用抽取集成模式取消注册，重新添加OGG进程就可以了。

Registered Extract - Archivelog Delete Problem (Doc ID 1487374.1)    
1. Run following as SYS user

SQL> connect / as sysdba
SQL> exec DBMS_CAPTURE_ADM.DROP_CAPTURE ('OGG$CAP_REP_A1');
ORA-01338: Other process is attached to LogMiner session

https://www.modb.pro/db/49639
GGSCI (t1 as ogg2@gbkt1) 6> unregister EXTRACT REP_A1 database
2021-06-06 15:45:19  INFO    OGG-01750  Successfully unregistered EXTRACT REP_A1 from database.


SQL> select * from dba_queues where owner='OGG2';
no rows selected

2. Drop queue table as follows
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => '', force => TRUE);

SQL> SELECT * FROM DBA_CAPTURE WHERE QUEUE_OWNER='OGG2';
no rows selected

3. Please follow the solution as per below document to cleanup the Orphan entries from metadata tables.

Ora-1 ''Unique Constraint (System.Logmnr_session_uk1) Violated'' During Streams Config (Doc ID 413774.1)

4. Please enable logretention which recreates the capture process again.
Make sure it has only one session for logminer.

select SESSION#,CLIENT#,SESSION_NAME,DB_ID,GLOBAL_DB_NAME from system.LOGMNR_SESSION$;
 
SQL> drop user ogg2 cascade;

register REPLICAT rep_a1 database