Recovery from loss of temporary tablespace without RMAN

Recover from loss of temporary datafile
=============================

[oracle@node1 ~]$ cd /u01/app/oracle/oradata/orcl/
[oracle@node1 orcl]$ ll
total 2642760
-rw-r----- 1 oracle oinstall   9781248 Feb 16 15:38 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 example01.dbf
-rw-r----- 1 oracle oinstall  41951232 Feb 16 14:47 inv01.dbf
-rw-r----- 1 oracle oinstall  31465472 Feb 16 14:47 inv02.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 inventory02.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log01_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:38 log02_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log03_02.log
drwxr-x--- 3 oracle oinstall      4096 Feb  9  2012 ORCL
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:38 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 soeindex01.dbf
-rw-r----- 1 oracle oinstall 650125312 Feb 16 15:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Feb 16 15:38 system01.dbf
-rw-r----- 1 oracle oinstall  30416896 Feb 16 15:17 temp01.dbf
-rw-r----- 1 oracle oinstall 524296192 Feb 16 15:36 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Feb 16 14:47 users01.dbf

[oracle@node1 orcl]$ rm -rf temp01.dbf;

[oracle@node1 orcl]$ ll
total 2636308
-rw-r----- 1 oracle oinstall   9781248 Feb 16 15:38 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 example01.dbf
-rw-r----- 1 oracle oinstall  41951232 Feb 16 14:47 inv01.dbf
-rw-r----- 1 oracle oinstall  31465472 Feb 16 14:47 inv02.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 inventory02.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log01_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:38 log02_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log03_02.log
drwxr-x--- 3 oracle oinstall      4096 Feb  9  2012 ORCL
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:38 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 soeindex01.dbf
-rw-r----- 1 oracle oinstall 650125312 Feb 16 15:37 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Feb 16 15:38 system01.dbf
-rw-r----- 1 oracle oinstall 524296192 Feb 16 15:38 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Feb 16 14:47 users01.dbf

[oracle@node1 orcl]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Feb 16 15:39:49 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from hr.employees;

  COUNT(*)
----------
       107
SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 100M;

Tablespace altered.

SQL> alter tablespace temp drop tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf';

Tablespace altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@node1 orcl]$ ll
total 2637336
-rw-r----- 1 oracle oinstall   9781248 Feb 16 15:48 control01.ctl
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 example01.dbf
-rw-r----- 1 oracle oinstall  41951232 Feb 16 14:47 inv01.dbf
-rw-r----- 1 oracle oinstall  31465472 Feb 16 14:47 inv02.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 inventory02.dbf
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log01_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:48 log02_02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 log03_02.log
drwxr-x--- 3 oracle oinstall      4096 Feb  9  2012 ORCL
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 15:48 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Feb 16 14:40 redo03.log
-rw-r----- 1 oracle oinstall 104865792 Feb 16 14:47 soeindex01.dbf
-rw-r----- 1 oracle oinstall 650125312 Feb 16 15:46 sysaux01.dbf
-rw-r----- 1 oracle oinstall 796925952 Feb 16 15:45 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb 16 15:47 temp02.dbf
-rw-r----- 1 oracle oinstall 524296192 Feb 16 15:47 undotbs01.dbf
-rw-r----- 1 oracle oinstall  13115392 Feb 16 14:47 users01.dbf

[oracle@node1 orcl]$