Issue:
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 199.30.132.203:5045
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: 199.30.132.203:5045 / CDR File Name :199.30.132.203-5045-cdr_2012_06_15__17_00_00.cdr / 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
Troubleshoot:
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
/home/ospadmin/OSP/OSPreyPackage/ospnexus/xitami/osp_ui/cdrs/ARCHIVE
[ospadmin@osprey1-121411 ARCHIVE]$
Solution:
Rename the ARCHIVED file on NexOSS and removed the entry for the filename in the Audit table.
[ossadmin@newoss ARCHIVE]$ ls -l *199.30.132.203-5045-cdr_2012_06_15__17_00_00*
-rw-r--r-- 1 ossadmin oinstall 388115 Jun 15 17:30 199.30.132.203-5045-cdr_2012_06_15__17_00_00.cdr.e.r.s_file1.gz
-r--r--r-- 1 ossadmin oinstall 446607 Jun 15 17:22 199.30.132.203-5045-cdr_2012_06_15__17_00_00.cdr_file1.gz
SQL> delete from CDR_FILE_Audit where FileName ='199.30.132.203-5045-cdr_2012_06_15__17_00_00.cdr';
1 row deleted.
SQL> commit;
Commit complete.
SQL>
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 199.30.132.204-5045-cdr_2012_06_18__15_00_00.cdr.e.r
-r--r--r-- 1 ossadmin oinstall 3051345 Jun 18 15:10 199.30.132.203-5045-cdr_2012_06_18__15_00_00.cdr.e.r
-rw-r--r-- 1 ossadmin oinstall 16279301 Jun 18 15:10 199.30.132.204-5045-cdr_2012_06_18__15_05_00.cdr
-rw-r--r-- 1 ossadmin oinstall 3125178 Jun 18 15:10 199.30.132.203-5045-cdr_2012_06_18__15_05_00.cdr
-rw-r--r-- 1 ossadmin oinstall 3404084 Jun 18 15:10 199.30.132.203-5045-cdr_2012_06_15__17_00_00.cdr
[ossadmin@newoss WORK]$ pwd
/OSS/nexoss/WORK
[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
cdr_2012_06_15__17_00_00.cdr
[ospadmin@osprey1-121411 ARCHIVE]$ pwd