为TEMP表空间创建替换临时文件并物理删除后最简单的恢复方法-CSDN博客

数据库物理结构分为数据文件和临时文件，临时文件和数据文件基本相同，只不过是数据文件在逻辑结构上对应着表空间，而临时文件对应着临时表空间而已。它对数据库的影响很小，丢失临时文件的后果是需要利用临时表空间的排序SQL将会失败。
下面我们将为数据库的临时空间进行替换和、删除和恢复测试

1、替换临时表空间

点击(此处)折叠或打开

[oracle@Primary oradata]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Jul 25 09:03:13 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col name for a40
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ----------------------------------------
1 /oracle/database/oradata/jingfan/temp01.dbf
#增加一个临时文件
SQL> alter tablespace temp add tempfile '/oracle/database/oradata/jingfan/temp02.ora' size 25m;
Tablespace altered.
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ----------------------------------------
1 /oracle/database/oradata/jingfan/temp01.dbf
2 /oracle/database/oradata/jingfan/temp02.ora
#s删除一个临时文件
SQL> alter tablespace temp drop tempfile '/oracle/database/oradata/jingfan/temp01.dbf';
Tablespace altered.
SQL> select file#,name from v$tempfile;
FILE# NAME
---------- ----------------------------------------
2 /oracle/database/oradata/jingfan/temp02.ora
SQL> alter tablespace temp drop tempfile '/oracle/database/oradata/jingfan/temp02.ora';
alter tablespace temp drop tempfile '/oracle/database/oradata/jingfan/temp02.ora'
*
ERROR at line 1:
ORA-03261: the tablespace TEMP has only one file
SQL> shutdown
^CORA-01013: user requested cancel of current operation
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !rm /oracle/database/oradata/jingfan/temp02.ora
SQL> host ls -lt /oracle/database/oradata/jingfan/
total 1894160
-rw-r----- 1 oracle oinstall 10174464 Jul 25 09:07 control01.ctl
-rw-r----- 1 oracle oinstall 262152192 Jul 25 09:06 rman.dbf
-rw-r----- 1 oracle oinstall 5251072 Jul 25 09:06 users01.dbf
-rw-r----- 1 oracle oinstall 608182272 Jul 25 09:06 sysaux01.dbf
-rw-r----- 1 oracle oinstall 99622912 Jul 25 09:06 undotbs01.dbf
-rw-r----- 1 oracle oinstall 796925952 Jul 25 09:06 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 Jul 25 09:06 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 25 07:49 redo03.log
-rw-r----- 1 oracle oinstall 52429312 Jul 25 07:49 redo02.log
SQL>

二、恢复临时文件的最简单方式就是重启数据库，在启动时，如果在原来的磁盘目录可用，oracle就会在该目录下创建所有丢失的临时文件
点击( 此处 )折叠或打开

#我们启动数据库是没有错误提示
SQL> startup
ORACLE instance started.
Total System Global Area 776646656 bytes
Fixed Size 2257272 bytes
Variable Size 478154376 bytes
Database Buffers 289406976 bytes
Redo Buffers 6828032 bytes
Database mounted.
Database opened.
#然后，我们跟踪一下alert日志信息，发下有一段Re-creating命令，正是重新穿件临时文件的操作
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the 'no FAL' ARCH
ARC0: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
[95862] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:575491024 end:575491034 diff:10 (0 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /oracle/database/oradata/jingfan/temp02.ora
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Mon Jul 25 09:07:43 2016
QMNC started with pid=25, OS id=95878
Completed: ALTER DATABASE OPEN