BSI - Log server sending all log files to disk

Products

• CA Business Service Insight

Issue/Introduction

Log server is sending all log files to disk instead of to the database
The log files on the disk contain messages as follow:

15/04/2020 09:59:59 --->Caught COM ERROR in operation EXECUTE
(INSERT INTO T_LOG ( MESSAGE_ID, TIME_STAMP, USER_ID, SESSION_ID, LEVEL_ID,
CODE, MESSAGE, REPORTER_OBJECT, INFO, FILE_NAME, LINE_NUMBER, IP_ADDRESS,
CREATION_DATE ) VALUES (SEQ_SLA_LOG.NEXTVAL , To_Date('15/04/2020
09:55:24','dd/mm/yyyy hh24:mi:ss') ,1003,3281852,'D','o3ee','Entering
function','PslWriter2','Entering CGlobalAgent::Execute
method','GlobalAgent.cpp','98','10.0.2.254', To_Date('15/04/2020
09:59:59','dd/mm/yyyy hh24:mi:ss') ))  [ORA-01653: unable to extend table
OBLICORE.T_LOG by 8192 in tablespace TBS_OBL_DATA_01 (HR: 0x80040e14)
(IDispatch error #3092) (Source = OraOLEDB)][(Error #80040e14) (Source =
OraOLEDB) (Description = ORA-01653: unable to extend table OBLICORE.T_LOG by
8192 in tablespace TBS_OBL_DATA_01) (NativeError: 675)]

Cause

This is caused by the tablespace running out of space.

Environment

Release : 8.0

Component : CA Business Service Insight

Resolution

Always consult your DBA before making any changes below.  The following guide is provided as a courtesy.

First, it is necessary to determine the filename of the problematic tablespace, and find out if there are already multiple tablespace files. 

Log into Oracle as your Oblicore user
Execute the following SQL query:

SELECT file_name FROM dba_data_files WHERE tablespace_name = 'TBS_OBL_DATA_01';

Sample Output:

FILE_NAME                                                                                                                                                                                                                                                                                                                       
-------------------------------------------------------------------------------------------------------------------------------------------------
C:\OBLICORE_DB\OBLICORE\OBLICOREDB\DATABASE\DBF\TBS_OBL_DATA_01.DBF

Note the file_name from the output above.

In this case, we have one single file and a decision needs to be made on whether to allow the tablespace to grow larger or add a new file.  The maximum size of a tablespace file is 32 GB under Windows.  If this has been met, you will need to add a new file.

To enlarge the existing file execute the following SQL query with the file_name noted above:

ALTER DATABASE DATAFILE '<file_name>' autoextend on maxsize unlimited --unlimited NEXT 10K MAXSIZE 100K --extends 10K up to max 100k;

Example:

ALTER DATABASE DATAFILE 'C:\OBLICORE_DB\OBLICORE\OBLICOREDB\DATABASE\DBF\TBS_OBL_DATA_01.DBF' autoextend on maxsize unlimited --unlimited NEXT 10K MAXSIZE 100K --extends 10K up to max 100k;

To add an additional tablespace file, use the file_name above, however increment the highest number from the file_name list above by one for the following SQL query:

ALTER TABLESPACE tablespace_name ADD DATAFILE '/ora01/oracle/oradata/newfile.dbf' size 100m autoextend on maxsize unlimited;

Example:

ALTER TABLESPACE tablespace_name ADD DATAFILE 'C:\OBLICORE_DB\OBLICORE\OBLICOREDB\DATABASE\DBF\TBS_OBL_DATA_02.DBF size 100m autoextend on maxsize unlimited;