诗檀软件专业数据库修复团队
目的
-------
在使用有临时文件的TEMPORARY 表空间时，你可能遇到2 种临时文件丢失的情况。本公告解释了如何快速地从两种情况中恢复。
1. TEMP01临时文件（默认名称）在OS级别丢失。
当一个用户尝试sort to 排序到TEMPORARY 表空间时，生成各种错误。
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
或
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'
或
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. 临时文件在数据库级别被DROP命令意外drop：
当用户尝试排序到临时表空间时，生成各种错误。
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. 确认哪些临时文件可能正在使用：
SQL> select * from database_properties where property_name =
'DEFAULT_TEMP_TABLESPACE';
SQL> select TEMPORARY_TABLESPACE from dba_users where username= ...
范围 & 应用
-------------------
适用于想要从临时表空间中恢复的DBA们。
1. 当临时文件在OS级别丢失时，如何恢复？
--------------------------------------------
情况
---------
临时文件位于一个崩溃，有坏的控制器，或者有其他类型媒体故障的磁盘上。由于Oracle不记录在临时文件中的检查点信息，Oracle可以使用一个丢失的临时文件启动数据库。如果当数据库联机时一个临时文件不存在，DBW0写入跟踪文件表明未发现临时文件，但数据库可以正常打开。
例如
-------
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. 当临时文件在数据库级别被意外drop时，如何恢复？
----------------------------------------------------------------------------------
情况
---------
临时文件被一个DROP命令意外drop：
可以从临时表空间中删除所有临时文件并保持为空。
但当用户尝试排序到TEMPORARY表空间时，生成错误。
例如
-------
=> 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
解决方法 :添加一个新的临时文件
--------
8i中：在添加新的临时文件之前删除OS 临时文件
9i起：直接添加一个新的临时文件
要添加临时文件：
SQL> alter tablespace TEMP_TEMPFILE_LOCAL
2 add tempfile '/oracle/oradata/ORCL/temp2_01.tmp';
总结
-------
OS 临时文件丢失
----> 从临时表空间中drop逻辑临时文件
----> 将新的临时文件添加到临时表空间
逻辑临时文件丢失
----> 8i：从临时表空间中删除OS临时文件
将新的临时文件添加到临时表空间中
----> 9i及以上：将新的临时文件添加到临时表空间中
相关文档
-----------------
Note:160426.1 TEMPORARY Tablespaces : Tempfiles or Datafiles ?
其他搜索词
-----------------------
TEMPFILE TEMPORARY