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.