OGG 抽取进程注册时报OGG-08221 ORA-00001错误总结
2024-03-22 10:52 潇湘隐者 阅读(424) 评论(0) 编辑 收藏 举报OGG部署时,抽取进程(Extract)注册到数据库时遇到下面错误:
REGISTER EXTRACT ***** DATABASE
ERROR OGG-08221 Cannot register or unregister EXTRACT because of the following SQL error: OCI Error ORA (status = 1-ORA-00001: unique constraint (SYSTEM.LOGMNR_SESSION_UK1) violated
关于这个错误,Oracle官方文档Unable To Register OGG Extract (Doc ID 2861271.1)[1]有相关的介绍和分析,如下所示:
SYMPTOMS
Unable to register OGG extract
GGSCI 5> register extract database container ()
ERROR OGG-08221 Cannot register or unregister EXTRACT because of the following SQL error: OCI Error ORA (status = 1-ORA-00001: unique constraint (SYSTEM.LOGMNR_SESSION_UK1) violated
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 617
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 249
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_INTERNAL", line 589
ORA-06512: at "SYS.DBMS_CAPTURE_ADM_IVK", line 177
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 179
CAUSE
possible clean up required on queue or CAPTURE, APPLY process related to
clean up required for the session created for in logminer tables
SOLUTION
sqlplus c##ggs_owner/
delete from system.logmnr_spill$ where session# = 10;
delete from system.logmnr_age_spill$ where session# = 10;
delete from system.logmnr_log$ where session# = 10;
delete from system.logmnr_restart_ckpt$ where session# = 10;
delete from system.logmnr_restart_ckpt_txinfo$ where session# = 10;
delete from system.logmnr_filter$ where session# = 10;
delete from system.logmnr_parameter$ where session# = 10;
delete from system.logmnr_global$ where session# = 10;
delete from system.logmnr_session$ where session# = 10;
commit;
Then register again
官方文档解释,抽取进程REGISTER到数据库出现失败的可能原因有下面两个:
1:可能是因为进程相关的CAPTURE、APPLY的队列没有清理干净 2:需要清理干净进程创建的与logmnr相关的会话。
千万不要直接拷贝官方文档的SQL语句,你首先需要通过下面SQL找出logmnr相关的会话ID信息
SET LINESIZE 720;
COL SESSION_NAME FOR A16
COL GLOBAL_DB_NAME FOR A12
SELECT SESSION#,CLIENT#,SESSION_NAME,DB_ID,GLOBAL_DB_NAME FROM SYSTEM.LOGMNR_SESSION$;
然后用具体的会话ID替换上面的会话10后,执行SQL语句,然后就可以重新注册抽取进程了。
sqlplus c##ggs_owner/ --用实际的ogg用户替换当前用
delete from system.logmnr_spill$ where session# = xxx;
delete from system.logmnr_age_spill$ where session# = xxx;
delete from system.logmnr_log$ where session# = xxx;
delete from system.logmnr_restart_ckpt$ where session# = xxx;
delete from system.logmnr_restart_ckpt_txinfo$ where session# = xxx;
delete from system.logmnr_filter$ where session# = xxx;
delete from system.logmnr_parameter$ where session# = xxx;
delete from system.logmnr_global$ where session# = xxx;
delete from system.logmnr_session$ where session# = xxx;
commit;
参考资料
1: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=390982163154682&id=2861271.1&_afrWindowMode=0&_adf.ctrl-state=dq53pb6ig_80

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
2023-03-22 SQL Server如何找出视图依赖的对象和视图嵌套层数
2016-03-22 XtraBackup出现 Can't connect to local MySQL server through socket '/tmp/mysql.sock'
2016-03-22 CentOS 6.6安装Xtrabackup RPM提示缺少libev.so.4()