【备份恢复】自动tempfile恢复

1.删除临时表空间数据文件
SYS@PROD1>select file_name from dba_temp_files;

FILE_NAME
------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/temp01.dbf

SYS@PROD1>! rm /u01/app/oracle/oradata/PROD1/temp01.dbf

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/temp01.dbf
ls: /u01/app/oracle/oradata/PROD1/temp01.dbf: No such file or directory

2.创建张大表，并对大表进行排序

由于PGA空间有限，所以会使用临时表空间，由于临时表空间的数据文件已经丢失，故会报错
SYS@PROD1>create table bigtab as select * from dba_objects;

Table created.

SYS@PROD1>insert into bigtab select * from bigtab;

86972 rows created.

SYS@PROD1>/

173944 rows created.

SYS@PROD1>/

347888 rows created.

SYS@PROD1>commit;

Commit complete.

SYS@PROD1>select * from bigtab order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15;  
select * from bigtab order by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
             *
ERROR at line 1:
ORA-01565: error in identifying file '/u01/app/oracle/oradata/PROD1/temp01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

3.重启数据库，数据库会自动重建临时数据文件
SYS@PROD1>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@PROD1>startup;
ORACLE instance started.

Total System Global Area  835104768 bytes
Fixed Size                  2257840 bytes
Variable Size             528485456 bytes
Database Buffers          301989888 bytes
Redo Buffers                2371584 bytes
Database mounted.
Database opened.

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/temp01.dbf
/u01/app/oracle/oradata/PROD1/temp01.dbf

注 此功能为11g新特性，当然，读者也可以自行手工创建临时表空间数据文件，解决此问题。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31400681/viewspace-2130953/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31400681/viewspace-2130953/