Recover from a loss of a temporary tablespace

This article is about a recovery of temporary tablespace. As we know that Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialization parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace.

Let’s look on how to proceed when we lose a temporary tablespace. First of all, considering We 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

Thank you for giving your valuable time to read the above information.

Source

For More Detail , You can join us follow:

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp