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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s