Error DB13

[former_member209959]

Hi,

We have a strange issue in our ECC system. In DB13 appears the next message:

BR0970W Database administration alert - level: WARNING, type: CRITICAL_FILE, object: /oracle/<SID>/sapdata1/temp_1/temp.data1, value: (max_alloc) 7698530 KB (> 2418993 KB).

PSAPTEMP has only a temp file and it is in autoextend off. In DB02 the PSAPTEMP size is 10.485.760 Kb and in the operating system (Solaris) if you do a ls -ltra:

10737426432 May 15 23:00 temp.data1

But with du -ks we get:

2787232

If you substract this number from the size in DB02 (what is the same that the outcome of ls -ltra) the result is the same than the max_alloc in DB13

I don't know if is related, but a few days ago we have changed the temp files of PSAPTEMP to autoextend off.

Could anybody help us with this problem?

Regards.

[former_member206552 · Accepted solution]

Hi Maximino

We do not have any tempfiles that has autoextend on, i would suggest you recreate the temp file with the autoextend flag off, and see if this helps

this can be done online

How to drop and recreate TEMP Tablespace in Oracle 9i/10g/11g

1. Create Temporary Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/u01/app/oradata/temp01′ SIZE 2000M, ‘/u01/app/oradata/temp02′ SIZE 2000M';

2. Move Default Database temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

3. Make sure No sessions are using your Old Temp tablespace

   a.  Find Session Number from V$SORT_USAGE:

       SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

   b.  Find Session ID from V$SESSION:

       If the resultset contains any tows then your next step will be to find the SID from the V$SESSION view. You can find session id by using SESSION_NUM or SESSION_ADDR from previous resultset.

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#=SESSION_NUM;

       OR

       SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SADDR=SESSION_ADDR;

    c.  Kill Session:

    Now kill the session with IMMEDIATE.

    ALTER SYSTEM KILL 'SID,SERIAL#' IMMEDIATE;

4. Drop temp tablespace

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

5. Recreate Tablespace Temp

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE /u01/app/temp/temp01′ SIZE 2000M;

6 Move Tablespace Temp, back to new temp tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

7. Drop temporary for tablespace temp

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Best Regards

Marius

[former_member209959 · Accepted solution]

Thank Marius, I suppose the only solution is to drop and create the temp again. I have used it in a test system and it works fine. I'll do it in the rest of systems with the same problem.

Best regards to everybody.

[former_member209959]

Sorry, I forgot to post that we have SAP CRM 7.0, Oracle 11.2.0.3.0 and Solaris 5.10

Regards.

[former_member188883]

Hi MAximino,

Cause of the error message

There are data files with an activated auto extend feature that would consume the entire remaining free disk space if extended. A check is made to see whether the file system can be brought to overflow, due to the existing parameter setting (NEXT and MAXSIZE) during the automatic file extension.

Check the current status using the SQL

SQL> select BYTES, BLOCKS, AUTOEXTENSIBLE, MAXBYTES, MAXBLOCKS, INCREMENT_BY from DBA_DATA_FILES

where FILE_NAME = '/oracle/<SID>/sapdata1/temp_1/temp.data1';

Based on the status from above command you shall have following options to work with

1. increase disk space

2. decrease maximum size of datafile

3. move datafile on other disk

4. a combination of previous 3 solutions.

Hope this helps.

Regards,

Deepak Kori

[former_member209959]

Hi Kori,

The temp file is in autoextend off. Firstly it was in autoextend on and we changed it. There are not rows in the output of your query.

Regards.