Weekly CCS_BE_CDR Database Table Partitions and "ORA-01688: unable to extend table" while Filesystem is Not Full (CCSPART) (Doc ID 1373439.1)

Last updated on SEPTEMBER 06, 2016

Applies to:

Oracle Communications Network Charging and Control - Version 4.2.0 and later
Information in this document applies to any platform.
***Checked for relevance on 09-Oct-2013***

Symptoms

Pre-requisites

This document treats the same symptoms as Doc 1304330.1 but when the filesystem holding CCS_BE_CDR partitions has sufficient space to hold a new extent (usually 2001MB).

Please refer to Doc 1304330.1 to get a full understanding of CCSPART scripts and Oracle Partitioning for CCS_BE_CDR.

Extract

CCSPART Capacity Monitor script runs hourly basis (master cluster node only) and raises alarms if problems related to capacity are detected.

Monitors Veritas disk group storage and raises an alarm if the storage utilization level is above the specified threshold.

Monitors tablespace storage of the current week and raises an alarm if the storage utilization level is above the specified threshold. Will optionally extend the current tablespace to create more capacity.


This is on the latest feature that this document will focus.

Symptoms

All the following symptoms must occur:

oracle@sms01$ sqlplus '/ as sysdba'
SQL> SELECT /*+ RULE */ T.TABLESPACE_NAME||' '||
              (100 - ROUND((((T.TOT_AVAIL - NVL(F.TOT_USED,0))*100)/TOT_AVAIL),0))
        FROM (SELECT /*+ RULE */ TABLESPACE_NAME, SUM(BYTES) TOT_USED
       FROM SYS.DBA_EXTENTS GROUP BY TABLESPACE_NAME) F,
      (SELECT /*+ RULE */ TABLESPACE_NAME, COUNT(1) FILE_COUNT,
              SUM(DECODE(maxbytes,0,BYTES,maxbytes)) TOT_AVAIL
       FROM SYS.DBA_DATA_FILES
       GROUP BY TABLESPACE_NAME
       UNION
       SELECT /*+ RULE */ TABLESPACE_NAME,COUNT(1) FILE_COUNT,
              SUM(DECODE(maxbytes,0,BYTES,maxbytes)) TOT_AVAIL
       FROM SYS.DBA_TEMP_FILES
       GROUP BY TABLESPACE_NAME) T,
       SYS.DBA_TABLESPACES D
 WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
 AND D.TABLESPACE_NAME = T.TABLESPACE_NAME
 ORDER BY ROUND((((T.TOT_AVAIL - NVL(F.TOT_USED,0))*100)/TOT_AVAIL),0); 

T.TABLESPACE_NAME||''||(100-R
--------------------------------------------------------------------------------
[...]
CCS_BE_CDR_Y2011_W43 97
[...]

 

 

Oct 28 12:42:47 sms01 ccsCDRLoader: [ID 675952 user.error] ccsCDRLoader(8674) ERROR: flush: INSERT: ORA-01688: unable to extend table CCS_ADMIN.CCS_BE_CDR partition CCS_BE_CDR_Y2011_W43 by 1600 in tablespace CCS_BE_CDR_Y2011_W43
CCS_BE_CDR_Y2011_W43 is full. EDRs can't be inserted in DB and processed by cdrLoader.

 

 

oracle@sms01$ grep EMERGENCY_DATAFILE /IN/service_packages/CCSPART/etc/ccspart.cfg
EMERGENCY_DATAFILE="TRUE"
export EMERGENCY_DATAFILE

 

 

30 * * * * . /home/oracle/.profile; /IN/service_packages/CCSPART/bin/CCSPART_capacity_monitor.sh > /IN/service_packages/CCSPART/tmp/CCSPART_capacity_monitor.sh.log 2>&1

 

 

oracle@sms01:/home/oracle> df -h

Filesystem             size   used  avail capacity  Mounted on
[...]
/dev/vx/dsk/oradg/DATA
                      389G   102G   285G    27%    /oracle/data1
[...]

 

Refer to Doc 1304330.1 to know what filesystem to check

 

 

oracle@sms01:/home/oracle>  . /home/oracle/.profile; /IN/service_packages/CCSPART/bin/CCSPART_capacity_monitor.sh
NOTICE: Parameter file loaded                                                                                                 
ERROR: Tablespace storage utilization above allowed threshold
Adding emergency datafile...
df: (          ) not a block device, directory or mounted resource
ERROR: Unable to add emergency datafile, insufficient filesystem storage. Required 2001 MByte(s), only found  MByte(s)

NOTICE: Filesystem utilization OK
NOTICE: Partition found for current week


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms