How to recover from a loss of a temporary tablespace

This scenario was created after a colleague of mine asked if temporary tablespace is considered an important tablespace by Oracle software like SYSTEM or UNDO during recovery scenarios. I answered with another question: "Do we backup any temporary tablespaces ?" :) Let's look on how to proceed when you lose a temporary tablespace. First of all consider I have just a temporary tablespace formed by one single datafile.

select file_name 
from dba_temp_files 
where tablespace_name = 'TEMP';
                
FILE_NAME
---------
/home/oracle/app/oracle/oradata/orcl/temp01.dbf

Now let's shutdown the instance.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Simulate a loss of your temporary datafile

[oracle@localhost orcl]$ mv temp01.dbf temp01.dbf.20120903_222335.bck

Now start again your database and look at the alert log.

[oracle@localhost ~]$ tail -f -n100 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

Here are some lines... look at each of them carefully...

...
Completed: ALTER DATABASE   MOUNT
Mon Sep 03 22:25:42 2012
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
Started redo scan
Completed redo scan
 read 73 KB redo, 47 data blocks need recovery
Started redo application at
 Thread 1: logseq 19, block 4124
Recovery of Online Redo Log: Thread 1 Group 1 Seq 19 Reading mem 0
  Mem# 0: /home/oracle/app/oracle/oradata/orcl/redo01.log
Completed redo application of 0.05MB
Completed crash recovery at
 Thread 1: logseq 19, block 4271, scn 14003253
 47 data blocks read, 47 data blocks written, 73 redo k-bytes read
LGWR: STARTING ARCH PROCESSES
...
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling cache recovery
SMON: enabling tx recovery
Re-creating tempfile /home/oracle/app/oracle/oradata/orcl/temp01.dbf
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Completed: ALTER DATABASE OPEN
Mon Sep 03 22:25:46 2012
...

After the process of mounting the database is completed and during the opening of it you can find an interesting line... I know you already have noticed it, the alert log traces:

...
Re-creating tempfile /home/oracle/app/oracle/oradata/orcl/temp01.dbf
...

Starting from version 10g, Oracle is able to detect the missing temporary datafile and recreate it on the same location. Indeed you can notice there's a new temporary datafile under my default location:

[oracle@localhost orcl]$ ll temp01.dbf*
-rw-rw---- 1 oracle oracle 20979712 Sep  3 22:25 temp01.dbf
-rw-rw---- 1 oracle oracle 20979712 Sep  3 22:03 temp01.dbf.20120903_222335.bck

Let's see what happens when we have two temporary datafiles and we lose one of them. Is Oracle still able to verify a temporary datafile is missing or it would let you open the database using just the other temporary datafile ? Let's add a new temporary datafile to the same temporary tablespace.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/app/oracle/oradata/orcl/temp02.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 5M  MAXSIZE 50M;

Tablespace altered.

Now simulate again the loss of temp01.dbf datafile and kill the instance. Here is described my situation:

[oracle@localhost orcl]$ ll temp0*
-rw-rw---- 1 oracle oracle 20979712 Sep  3 22:25 temp01.dbf
-rw-rw---- 1 oracle oracle 20979712 Sep  3 22:03 temp01.dbf.20120903_222335.bck
-rw-rw---- 1 oracle oracle 20979712 Sep  3 22:33 temp02.dbf
[oracle@localhost orcl]$ mv temp01.dbf temp01.dbf.20120903_224005.bck
[oracle@localhost orcl]$ ps -ef|grep smon
oracle    3744     1  0 22:35 ?        00:00:00 ora_smon_orcl
oracle    3874  2850  0 22:40 pts/2    00:00:00 grep smon
[oracle@localhost orcl]$ kill -9 3744

Startup the instance

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 3 22:41:33 2012

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

Connected to an idle instance.

SQL> startup

Again have a look at the alert log

[oracle@localhost ~]$ tail -f -n100 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log

You can find again Oracle detects the missing temporary datafile and recreate it in the same location.

...
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Re-creating tempfile /home/oracle/app/oracle/oradata/orcl/temp01.dbf
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Completed: ALTER DATABASE OPEN
Mon Sep 03 22:41:52 2012
...

Querying the database you can verify the two temporary datafiles are there.

SQL> col file_name format a70
SQL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------------------------------------
/home/oracle/app/oracle/oradata/orcl/temp01.dbf
/home/oracle/app/oracle/oradata/orcl/temp02.dbf

That's all.