Unique Constraint Violated


The “SQLException ORA-00001” error message is logged.

Log message:

Error: SQLException: ORA-00001: unique constraint (MBCOSS.CDR_FILE_AUDIT_PK) violated
2012-06-18 11:22:07|CDRPull        |INFO   |Starting to download past CDR files from the server
2012-06-18 11:22:07|CDRPull        |INFO   |Connect and read timeouts are 5000 and 900000 milliseconds
2012-06-18 11:22:07|CDRPull        |INFO   |Audit info ServerName: / CDR File Name : / Time Stamp: 2012_06_15__17_00_00
2012-06-18 11:22:07|CDRPull        |ERROR  |SQLException:ORA-00001: unique constraint (MBCOSS.CDR_FILE_AUDIT_PK) violated
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (MBCOSS.CDR_FILE_AUDIT_PK) violated


This ERROR indicates that NexOSS has already written an entry for a CDR in the Audit table for a CDR file name that it is still trying to collect from OSPrey.

Possible causes for the error are a time shift at OSPrey, or CDR Archive failure at OSPrey. The CDR does indeed exist in the cdrs directory, but not in cdrs/ARCHIVE at OSPrey.

[ospadmin@osprey1-121411 cdrs]$ ls -ltr
total 4276
-rw-r--r-- 1 ospadmin osp 3404084 Jun 15 17:00 cdr_2012_06_15__17_00_00.cdr
drwxr-xr-x 2 ospadmin osp 933888 Jun 18 11:30 ARCHIVE
-rw-r--r-- 1 ospadmin osp 14252 Jun 18 11:31 cdr_2012_06_18__11_30_00.cdr
[ospadmin@osprey1-121411 cdrs]$
[ospadmin@osprey1-121411 ARCHIVE]$ ls cdr_2012_06_15__17_00_00.cdr*
ls: cdr_2012_06_15__17_00_00.cdr*: No such file or directory
[ospadmin@osprey1-121411 ARCHIVE]$ pwd
[ospadmin@osprey1-121411 ARCHIVE]$


Rename the ARCHIVED file on NexOSS and removed the entry for the filename in the Audit table.

[ossadmin@newoss ARCHIVE]$ ls -l **
-rw-r--r-- 1 ossadmin oinstall 388115 Jun 15 17:30
-r--r--r-- 1 ossadmin oinstall 446607 Jun 15 17:22

SQL> delete from CDR_FILE_Audit where FileName ='';
1 row deleted.
SQL> commit;
Commit complete.

This allowed NexOSS to be able to collect the file from OSPrey.

[ossadmin@newoss WORK]$ ls -ltr
total 40320
-r-xr-xr-x 1 ossadmin oinstall 330 Feb 27 19:32 WORK_README.txt
-r--r--r-- 1 ossadmin oinstall 15337468 Jun 18 15:10
-r--r--r-- 1 ossadmin oinstall 3051345 Jun 18 15:10
-rw-r--r-- 1 ossadmin oinstall 16279301 Jun 18 15:10
-rw-r--r-- 1 ossadmin oinstall 3125178 Jun 18 15:10
-rw-r--r-- 1 ossadmin oinstall 3404084 Jun 18 15:10
[ossadmin@newoss WORK]$ pwd
[ospadmin@osprey1-121411 ~]$ cd /home/ospadmin/OSP/OSPreyPackage/ospnexus/xitami/osp_ui/cdrs/
[ospadmin@osprey1-121411 cdrs]$ ls -ltr
total 3572
drwxr-xr-x 2 ospadmin osp 933888 Jun 18 15:10 ARCHIVE
-rw-r--r-- 1 ospadmin osp 2705859 Jun 18 15:14 cdr_2012_06_18__15_10_00.cdr
[ospadmin@osprey1-121411 cdrs]$ cd ARCHIVE/
[ospadmin@osprey1-121411 ARCHIVE]$ ls cdr_2012_06_15__17_00_00.cdr
[ospadmin@osprey1-121411 ARCHIVE]$ pwd