故障分析：ORA-00001: unique constraint violated异常处理

数据和云

发布于 2021-03-09 14:47:06

9.4K0

发布于 2021-03-09 14:47:06

文章被收录于专栏：数据和云

墨墨导读：本文记录SYS.SCHEDULER$_INSTANCE_PK冲突异常分析及处理过程，希望对大家有帮助。

一、前言

最近，客户经常收到告警信息，一台数据库的安装目录/u01经常使用率经常达到96%以上。通过对该目录的空间使用情况进行分析发现，在$ORACLE_BASE数据库中短时间就会产生大量的trace文件和alert日志；所以造成空间不足；

二、问题分析过程及定位

通过对alert日志分析，发现数据库在运行期间出现了大量的主键冲突异常：

Fri Feb 19 11:23:27 2021
Errors in file /oracle/app/oracle/diag/rdbms/inas/inas2/trace/inas2_j003_29198.trc:
ORA-00001: unique constraint (.) violated
ORA-00001: unique constraint (SYS.SCHEDULER$_INSTANCE_PK) violated

通过相应的trc文件，可以发现，由于该主键冲突，所以导致job无法正常运行，从而产生大量的job错误的trace文件。

根据报错信息进行定位：

select INDEX_OWNER,INDEX_NAME,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION from dba_ind_columns where index_name='SCHEDULER$_INSTANCE_PK';

INDEX_OWNER                    INDEX_NAME                     TABLE_NAME                     COLUMN_NAME                    COLUMN_POSITION
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------
SYS                            SCHEDULER$_INSTANCE_PK         SCHEDULER$_EVENT_LOG           LOG_ID                                       1
SYS                            SCHEDULER$_INSTANCE_PK         SCHEDULER$_EVENT_LOG           DBID                                         2

可以发现该索引是一张系统表，该表的作用是用于记录SCHEDULER job执行情况的表的联合主键，是由log_id和dbid构成；

该主键发生冲突，则说明log_id的生成出现了重复；所以第一个想法就是该系统表中的内容是否可以清理；清理之后是否可以恢复正常。于是通过mos查了一下，这张表可以通过一些手段进行清理：

exec dbms_scheduler.purge_log();
truncate table sys.dbms_scheduler$event_log;

清理之后，发现alert日志还是报错，说明清理也无效；此时，查询sys.dbms_scheduler$event_log表的数量，发现该表中只有1条，而通过对该表的两个主键列查询，发现这两个列的值为(-1,null)。因此，怀疑问题处在log_id的生成上。

通过mos查询，发现当scheduler的log记录到log表里面，主要是通过一个序列：sys.scheduler_instance_s来生成的，但是sys.dbms_schedulerevent_log表中的log_id的大小被限制为4字节，当超限的时候，则会出现错误(参考mos上Doc ID 2057968.1)。

此时查询了下该序列：

select sys.scheduler$_instance_s.nextval from dual;

发现该序列确实超过了log_id限制的4字节大小。

因此，问题可以定位到是由于sys.scheduler$_instance_s序列生成的大小已经超过了log_id限制的大小范围，所以会出现无法记录的异常。

三、问题处理

处理方式即为将该序列进行重建，重新生成序列值，从而对log_id进行填充，具体重建方法如下(Doc ID 2057968.1给出的方式)：

--修改参数，停止job
alter system set job_queue_processes=0 scope=memory;
--删除sequence
DROP SEQUENCE sys.scheduler$_instance_s;
--重建sequence
CREATE SEQUENCE sys.scheduler$_instance_s;
--执行recomp过程
EXECUTE UTL_RECOMP.RECOMP_SERIAL();
--修改参数，启动job
alter system set job_queue_processes=1000 scope=memory;
--设置执行窗口
exec dbms_scheduler.set_attribute('MONDAY_WINDOW','SYSTEM',TRUE);
--执行recomp过程
EXECUTE UTL_RECOMP.RECOMP_SERIAL();

重建之后，可以发现alert日志中不再出现异常错误，通过对sys.dbms_scheduler$event_log中的数值进行查看，可以发现log_id和dbid的值恢复正常增加的情况：

 select log_id,dbid from SCHEDULER$_EVENT_LOG where rownum < 10;

    LOG_ID       DBID
---------- ----------
       281
       282
       283
       284
       285
       286
       287
       288
       289

9 rows selected.

问题解决。

最后，提一句，通过mos中的案例，在11g升级到12c的时候也可能会出现该情况，导致升级有问题；而本次则是在11g数据库中正常使用的情况下出现了该问题，所以说明该问题在scheduler运行频繁的系统中也会有发生问题的可能性。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-03-03，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库