Oracle: How To Recover From Missing Tempfiles or an Empty Temporary Tablespace
Note: My company is running Oracle Grid Infrastructure 11g (on Exadata). Instead of following the below advice, I managed to solve the problem by increasing the ASM process limit and reboot all nodes (one at each time). It is because I have ORA-00020 found at trace files which is about cannot connect to ASM.
How To Recover From Missing Tempfiles or an Empty Temporary Tablespace
[ID 178992.1]
PURPOSE
-------
When using TEMPORARY tablespaces with tempfiles, you may encounter 2 situations where the tempfiles are missing. This bulletin explains how to rapidly recover from both situations.
1. The TEMP01 tempfile (default name) is lost at the OS level.
When a user attempts to sort to the TEMPORARY tablespace, different
errors can be generated.
SQL> select * from dba_objects order by object_name;
select * from dba_objects order by object_name
*
ERROR at line 1:
ORA-01115: IO error reading block from file 201 (block # 3)
ORA-01110: data file 201: '/oracle/oradata/ORCL/temp2_01.tmp'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
or
SQL> select * from dba_objects order by object_name;
select * from dba_objects order by object_name
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
ORA-01110: data file 1026: '/oracle/oradata/ORCL/temp2_01.tmp'
or
SQL> select * from dba_objects order by object_name;
select * from dba_objects order by object_name
*
ORA-01116: error in opening database file 202
ERROR at line 1:
ORA-01110: data file 202: '/oracle/oradata/ORCL/temp2_01.tmp'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
2. Tempfiles were accidentally dropped at the database level by a DROP command :
When a user attempts to sort to the TEMPORARY tablespace, different errors can be generated.
SQL> alter table test add primary key (c);
alter table test add primary key (c)
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
3. Determine what temp files may be in use:
SQL> select * from database_properties where property_name =
'DEFAULT_TEMP_TABLESPACE';
SQL> select TEMPORARY_TABLESPACE from dba_users where username= ...
SCOPE & APPLICATION
-------------------
For DBAs who need to recover from TEMPORARY tablespaces.
1. How to Recover When the tempfile is missing at the OS level ?
--------------------------------------------
Situation
---------
The tempfile was located on a disk that crashed, had a bad
controller, or some other type of media failure.
Since Oracle does not record checkpoint information in tempfiles,
Oracle can start up a database with a missing tempfile.
If a tempfile does not exist when the database is brought up,
DBW0 writes to a trace file indicating the tempfile is not found,
but the database opens normally.
Example
-------
SQL> create temporary tablespace TEMP2
2 TEMPFILE '/oracle/oradata/ORCL/temp2_01.tmp' size 5M;
Tablespace created.
SQL> select tablespace_name, file_name from dba_temp_files;
TABLESPACE_NAME FILE_NAME
------------------ --------------------------------------
TEMP2 /oracle/oradata/ORCL/temp2_01.tmp
SQL> select tablespace_name, contents
2 from dba_tablespaces where tablespace_name = 'TEMP2';
TABLESPACE_NAME CONTENTS
------------------------------ ---------
TEMP2 TEMPORARY
SQL> select * from dba_objects order by object_name;
select * from dba_objects order by object_name
*
ORA-01116: error in opening database file 202
ERROR at line 1:
ORA-01110: data file 202: '/oracle/oradata/ORCL/temp2_01.tmp'
ORA-27041: unable to open file
SVR4 Error: 2: No such file or directory
Additional information: 3
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
....
Database opened.
SQL> connect scott/tiger
Connected.
SQL> select * from dba_objects order by object_name;
select * from dba_objects order by object_name
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 202 - see DBWR trace file
ORA-01110: data file 202: '/oracle/oradata/ORCL/temp2_01.tmp'
Solution : Drop the tempfile at the database level and add a new one
--------
SQL> alter database tempfile '/oracle/oradata/ORCL/temp2_01.tmp' drop;
Database altered.
SQL> select tablespace_name, file_name from dba_temp_files;
no rows selected.
SQL> alter tablespace temp2
2 add tempfile '/oracle/oradata/ORCL/temp2_01.tmp' size 5m;
Tablespace altered.
2. How to Recover When the tempfiles were accidentally dropped at the database level ?
----------------------------------------------------------------------------------
Situation
---------
The tempfiles were dropped accidentally by a DROP command :
It is possible to remove all tempfiles from a temporary tablespace and keep it empty.
But when a user attempts to sort to the TEMPORARY tablespace, an error is generated.
Example
-------
=> in 8i: the drop clause only removes the logical entry from the
tablespace, but not the OS file
SQL> alter tablespace TEMP_TEMPFILE_LOCAL
2 add tempfile '/oracle/oradata/ORCL/temp2_01.tmp';
Tablespace altered.
SQL> alter database tempfile '/oracle/oradata/ORCL/temp2_01.tmp' drop;
Database altered.
=> From 9i: you can use the new clause INCLUDING DATAFILES to remove OS files
SQL> alter database tempfile '/oracle/oradata/ORCL/temp2_01.tmp'
2 drop including datafiles;
Database altered.
SQL> alter table test add primary key (c);
alter table test add primary key (c)
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
Solution : Add a new tempfile
--------
In 8i : Remove the OS tempfile before adding a new tempfile
From 9i : Add a new tempfile straight forward
To add a tempfile :
SQL> alter tablespace TEMP_TEMPFILE_LOCAL
2 add tempfile '/oracle/oradata/ORCL/temp2_01.tmp';
Summary
-------
OS tempfiles missing
----> Drop the logical tempfile from the temporary tablespace
----> Add a new tempfile to the temporary tablespace
Logical tempfiles missing
----> 8i: Remove the OS tempfile from the temporary tablespace
Add a new tempfile to the temporary tablespace
----> 9i and above: Add a new tempfile to the temporary tablespace
Related Documents
-----------------
Note:160426.1 TEMPORARY Tablespaces : Tempfiles or Datafiles ?
Additional Search Words
-----------------------
TEMPFILE TEMPORARY
Comments