Posts Tagged ‘tempfile’

Error altering tempfile: ORA-00376: file 202 cannot be read at this time

I noticed a lot of ORA-01652 errors while gathering some table statistics (Oracle 10g Release 2 –

BEGIN DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE', cascade=>true, estimate_percent=>60); END;

ERROR at line 1:
ORA-01652: Unable to extend temp segment by 256 in tablespace TEMP

ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

When I’m trying to resize the tempfile I get an ORA-00376 error:

SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G;
alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G
ERROR at line 1:
ORA-00376: file 202 cannot be read at this time
ORA-01110: datafile 202: '/app/oracle/oradata/temp01.dbf'

I checked the file on the filesystem and it exists and has the correct permissions:

$ ls -lrth /app/oracle/oradata/temp01.dbf
-rw-r----- 1 oracle dba 15G 17 nov 02:01 /app/oracle/oradata/temp01.dbf

After bouncing the database, I try to alter again the tempfile and now I get an ORA-01516:

SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G;
alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile '/app/oracle/oradata/temp01.dbf'

But the file is there as I checked before. With this new ORA-01516 error I checked the status of the tempfile and the error was there:

SQL> select tablespace_name , status from dba_tablespaces where tablespace_name = 'TEMP';

------------------------------ ---------

SQL> select name ,status from v$tempfile where name like '%temp%';

NAME                                          STATUS
/app/oracle/oradata/temp01.dbf                OFFLINE

The tempfile is offline. This is because our server ran out of space this morning, as you can see in the alert log:

Errors in file /app/oracle/admin/db1/udump/db11_ora_18815.trc:
ORA-01114:IO error writing block to file 202 (block # 1218066)
ORA-27072: I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1218066
Additional information: -1

After freeing disk space, I try to bring back the datafile online:

SQL> alter database datafile '/app/oracle/oradata/temp01.dbf' online;
 alter database datafile '/app/oracle/oradata/temp01.dbf' online
 ERROR at line 1:
 ORA-01516: nonexistent log file, datafile or tempfile '/app/oracle/oradata/temp01.dbf'

I neither can take the TEMP tablespace offline:

SQL> alter tablespace temp offline;
 alter tablespace temp offline
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

The solution is to recreate the tempfile:

  • First, delete the existing tempfile from the database
  • Remove the file from the filesystem (rm)
  • Create a new tempfile.
SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' drop;

Database modified.

SQL> !ls -lrt /app/oracle/oradata/temp01.dbf

-rw-r----- 1 oracle dba 15728644096 17 nov 02:01 /app/oracle/oradata/temp01.dbf

SQL> !rm /app/oracle/oradata/temp01.dbf

SQL> alter tablespace temp add tempfile '/app/oracle/oradata/temp01.dbf' size 15000M;

Tablespace modified.

SQL> select name, status from v$tempfile;

NAME                                     STATUS
---------------------------------------- -------
/app/oracle/oradata/temp01.dbf           ONLINE

Now the tablespace is online and everithing returns to normal.

Any questions? Please feel free to comment below.

Categories: 10gR2, oracle Tags: ,