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.
