The Systems People Inc.

The Systems People Inc.

Customer Focused Service since 1993

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>

Note that SORT operations is successful with AUTO created TEMP FIles on instance restart.

Next post I'll demonstrate the manually creating Temp Files when the Database is Open.

Till then BYE!

 Blog Stats

  • Total posts(18)
  • Total comments(0)

Forgot your password?