Re: How to handle temporary tablespace (locally managed) during restoration from a hot backup?

From: Tanel Poder <tanel_at_@peldik.com>
Date: Wed, 16 Jul 2003 19:15:25 +0300
Message-ID: <3f157a21$1_1@news.estpak.ee>

Hi!

> When I tried to drop the tablespace, I get the following:
>
> DROP TEMPORARY TABLESPACE TEMP;
>
> The following error has occurred:
>
> ORA-12906: cannot drop default temporary tablespace

You can't drop this TS because it's set as database default temporary tablespace (new 9i feature). But don't worry about that.

> Can someone help me to understand why the locally managed temporary
> tablespace is better, compare to dictionary managed temporary
> tablespace. I feel that the backup is a bit more complicated. I
> previously do a hot backup even on the dictionary managed temp
> tablespace (even though it's redundant).

Since temporary tablespace contents are temporary, and IO to them is mostly direct IO (no buffer cache involved), there is no need for checkpointing or writing/checking any headers of tempfiles. That means, noone really cares about the contents of tempfile much after a crash or recovery.

Anyway, I simulated the same problem you got, see my example:

SQL> select file_name from dba_temp_files; select file_name from dba_temp_files

                      *

ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: 'C:\ORACLE\ORA92\ORCL\TEMP01.DBF'

Try v$tempfile instead

SQL> select name from v$tempfile;

NAME

---

C:\ORACLE\ORA92\ORCL\TEMP01.DBF Or x$kccfn (fntyp 7 is tempfile (at least in 9.2)).

SQL> select fnnam from x$kccfn where fntyp = 7;

FNNAM

---

C:\ORACLE\ORA92\ORCL\TEMP01.DBF First "drop" the tempfile (actually removes tempfile entry from controlfiles)

SQL> alter database tempfile 'C:\ORACLE\ORA92\ORCL\TEMP01.DBF' drop;

Database altered.

Use alter tablespace command to add the same tempfile back (if the file exists in OS, then just use REUSE, otherwise specify size as well)

SQL> alter tablespace temp add tempfile 'C:\ORACLE\ORA92\ORCL\TEMP01.DBF' size 10m reuse autoextend on;

Tablespace altered.

Now it works.

SQL> select file_name from dba_temp_files;

FILE_NAME

---

C:\ORACLE\ORA92\ORCL\TEMP01.DBF SQL> Cheers,
Tanel. Received on Wed Jul 16 2003 - 11:15:25 CDT