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

Popular Posts