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

Thanks, Tanel. It works beautifully. I make a mistake of focusing too much on dropping the temp tablespace, intead of dropping the tempfile itself. This solution also preserve the default temporary tablespace as temp.

Once again, thank you.

"Tanel Poder" <tanel@@peldik.com> wrote in message news:<3f157a21$1_1_at_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 - 21:09:36 CDT