Loss of TEMP file(s) in Oracle DB

Scenario 1: Recovery from loss of Temp File(s) in Temporary Tablespace
In Oracle Temporary Tablespace is used for sort / merge operations that cannot be done within memory. When some datafile for Temporary Tablespace (Tempspace) is lost or inaccessible, database will perform normally, however certain user queries that require the use of Tempspace will return error.

In 11g Oracle can re-create missing TEMP files at the start of instance. Also you can manually create the missing / damaged Temp Files. This entry describes the steps for the same.

Set up for testing recovery from loss of temp files…

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

PL/SQL Release 11.1.0.7.0 - Production

CORE    11.1.0.7.0      Production

TNS for Linux: Version 11.1.0.7.0 - Production

NLSRTL Version 11.1.0.7.0 - Production

SQL> col name format a60;

SQL> select file#, bytes, blocks, name from v$tempfile;

     FILE#      BYTES     BLOCKS NAME

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

         1  105906176      12928 /u02/oradata/orcl/temp01.dbf

SQL>

 Let us quickly check the sort area allocated in memory in our instance

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE

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

sort_area_retained_size              integer     0

sort_area_size                       integer     65536

SQL>

 We’ll reduce it to small size so that SORT operation will be forced to go to disk.

SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE

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

sort_area_retained_size              integer     0

sort_area_size                       integer     50

SQL>

 Now we’ll create a table and issue query with Order By clause to force the use of Tempspace.

SQL> set autotrace off;

SQL> create table t1 as select * from all_objects;

Table created.

SQL> insert into t1 select * from all_objects;

68938 rows created.

SQL> /

68938 rows created.

… REPEAT REPEAT …

SQL> /

68938 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)

----------

    620442

SQL>

SQL> set autotrace traceonly statistics;

SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc;

620442 rows selected.

Statistics

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

         77  recursive calls

          4  db block gets

       9179  consistent gets

      12609  physical reads

          0  redo size

   11896316  bytes sent via SQL*Net to client

     455402  bytes received via SQL*Net from client

      41364  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     620442  rows processed

SQL>

Now we have a query, which ensures the use of Tempspace;

We’ll remove the temp file at OS level, and reissue the query. We’ll get an error.

Automatic re-creation (recovery) of TEMP datafiles on instance restart

SQL> select name from v$tempfile;

NAME

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

/u02/oradata/orcl/temp01.dbf

SQL> !rm /u02/oradata/orcl/temp01.dbf;

SQL> !ls /u02/oradata/orcl/temp01.dbf;

ls: /u02/oradata/orcl/temp01.dbf: No such file or directory

Note: In UNIX/Linux environment the deletion of file is NOT reflected immediately or rerunning the same query may use the DB Cache and the rerun of the query may still continue successfully.

SQL>  select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8;

 select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8

               *

ERROR at line 1:

ORA-01565: error in identifying file '/u02/oradata/orcl/temp01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL>

In Oracle 11g the instance startup will re-create the Temporary datafiles. If require they can be created manually as well.
Since this is a test instance, I am going to restart the instance to see re-creation of Tempfiles.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

SQL> !ls /u02/oradata/orcl/temp01.dbf;

/u02/oradata/orcl/temp01.dbf

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE

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

sort_area_retained_size              integer     0

sort_area_size                       integer     65536

SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE

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

sort_area_retained_size              integer     0

sort_area_size                       integer     50

SQL>

SQL>  set autotrace traceonly statistics;

ERROR:

ORA-24315: illegal attribute type

SP2-0619: Error while connecting

SP2-0611: Error enabling STATISTICS report

SQL> exit;

I noticed, after restart of the database, the existing SQLPlus session had some problem in setting autotrace. However exiting and restarting SQLPlus session resolved this issue.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

oracle@domU-12-31-39-00-7C-94:[/home/oracle]

$ sqlplus  / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Sat Jul 14 11:41:03 2012

Copyright (c) 1982, 2008, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE

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

sort_area_retained_size              integer     0

sort_area_size                       integer     65536

SQL> alter session set sort_area_size=50;

Session altered.

SQL> show parameter sort_area;

NAME                                 TYPE        VALUE

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

sort_area_retained_size              integer     0

sort_area_size                       integer     50

SQL> set autotrace traceonly statistics;

SQL>

Note that upon restart of the instance the temporary Datafile was recreated.

SQL>  !ls /u02/oradata/orcl/temp01.dbf;

/u02/oradata/orcl/temp01.dbf

SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8;

827256 rows selected.

Statistics

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

       1549  recursive calls

        441  db block gets

      12452  consistent gets

      24801  physical reads

          0  redo size

   14888276  bytes sent via SQL*Net to client

     607070  bytes received via SQL*Net from client

      55152  SQL*Net roundtrips to/from client

         37  sorts (memory)

          1  sorts (disk)

     827256  rows processed

SQL>

Manual re-creation (recovery) of TEMP datafiles when database is open
Alternatively, if the database cannot be restarted (which probably will be the case most of the time), we can manually recreate the temporary datafile when the database is open.
Note however, ever after removal of the tempfile, Oracle is actually performing sort on the disk.
Where is Oracle actually doing the disk sort operation?

SQL> !rm  /u02/oradata/orcl/temp01.dbf;

SQL>  !ls /u02/oradata/orcl/temp01.dbf;

ls: /u02/oradata/orcl/temp01.dbf: No such file or directory

SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7;

827256 rows selected.

Statistics

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

        102  recursive calls

          6  db block gets

      12204  consistent gets

      24550  physical reads

          0  redo size

   14888276  bytes sent via SQL*Net to client

     607070  bytes received via SQL*Net from client

      55152  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     827256  rows processed

SQL> select * from t1 order by 1 desc, 2, 3 desc, 4, 5 desc,6,7,8,9 desc,10;

827256 rows selected.

Statistics

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

        102  recursive calls

          6  db block gets

      12204  consistent gets

      24574  physical reads

          0  redo size

   14888276  bytes sent via SQL*Net to client

     607070  bytes received via SQL*Net from client

      55152  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     827256  rows processed

SQL>

It seems, as long as the result set remains the same Oracle is resilient enough to perform required operations to return the correct result set (probably still using DB_CACHE) and not actually accessing the sort area.

SQL> delete from t1 where rownum < 10;

9 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from t1 order by 11,1 desc, 2, 3 desc, 4, 5 desc,6,7,8,9 desc,10;

827247 rows selected.

Statistics

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

        102  recursive calls

          6  db block gets

      12204  consistent gets

      24574  physical reads

          0  redo size

   14886464  bytes sent via SQL*Net to client

     607059  bytes received via SQL*Net from client

      55151  SQL*Net roundtrips to/from client

          0  sorts (memory)

          1  sorts (disk)

     827247  rows processed

SQL>

Though when I add more row to the table (actually changing the result set) the disk-sort-area is actually used again.

SQL> !ls /u02/oradata/orcl/temp01.dbf;

ls: /u02/oradata/orcl/temp01.dbf: No such file or directory

SQL> insert into t1 select * from all_objects;

68938 rows created.

SQL> commit;

Commit complete.

SQL>  select * from t1 order by 11,1 desc, 2, 3 desc, 4, 5 desc,6,7,8,9 desc,10;

 select * from t1 order by 11,1 desc, 2, 3 desc, 4, 5 desc,6,7,8,9 desc,10

               *

ERROR at line 1:

ORA-01565: error in identifying file '/u02/oradata/orcl/temp01.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

We can MANUALLY add tempfile to temp tablespace.

SQL> alter tablespace temp add tempfile '/u02/oradata/orcl/temp01.dbf';

alter tablespace temp add tempfile '/u02/oradata/orcl/temp01.dbf'

*

ERROR at line 1:

ORA-01537: cannot add file '/u02/oradata/orcl/temp01.dbf' - file already part

of database

SQL> alter tablespace temp drop tempfile  '/u02/oradata/orcl/temp01.dbf';

alter tablespace temp drop tempfile  '/u02/oradata/orcl/temp01.dbf'

*

ERROR at line 1:

ORA-03261: the tablespace TEMP has only one file

SQL>  alter tablespace temp add tempfile '/u02/oradata/orcl/temp02.dbf';

 alter tablespace temp add tempfile '/u02/oradata/orcl/temp02.dbf'

*

ERROR at line 1:

ORA-01119: error in creating database file '/u02/oradata/orcl/temp02.dbf'

ORA-17610: file '/u02/oradata/orcl/temp02.dbf' does not exist and no size

specified

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> alter tablespace temp add tempfile '/u02/oradata/orcl/temp02.dbf' size 100m;

Tablespace altered.

SQL> select file#, bytes, blocks, name from v$tempfile;

     FILE#      BYTES     BLOCKS

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

NAME

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

         1  103809024      12672

/u02/oradata/orcl/temp01.dbf

         2  104857600      12800

/u02/oradata/orcl/temp02.dbf

SQL> alter tablespace temp drop tempfile '/u02/oradata/orcl/temp01.dbf';

Tablespace altered.

SQL> ALTER DATABASE TEMPFILE '/u02/oradata/orcl/temp02.dbf' AUTOEXTEND ON MAXSIZE 500M;

Tablespace altered.

SQL>

We are back to normal business.