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

"Tanel Poder" <tanel@@peldik.com> wrote in message news:<3f13de97_1_at_news.estpak.ee>...
> Or alter tablespace temp add tempfile '/file/location' size xxM reuse;
>
> Tanel.
>
> "Anurag Varma" <avdbi_at_hotmail.com> wrote in message
> news:sssQa.617$pk1.243_at_news02.roc.ny...
> >
> > "Jeffrey Yee" <jeffyee_at_hotmail.com> wrote in message
> news:ec4cec95.0307132158.19349895_at_posting.google.com...
> > > Hi,
> > >
> > > I realise that temporary tablespace tempfile (locally managed) cannot
> > > use the alter tablespace begin backup command. Therefore, I wonder
> > > what would be the best way to recover the tempfile.
> > >
> --snip--
> > > Best Regards,
> > > Jeffrey Yee
> >
> > No need to recover them. Drop recreate.
> >
> > Anurag
> >
> >

I have manage to successfully restore & recover the database and manage to start the database, even though I don't have the temp01.dbf file. When I query the dba_temp_files, I get the following error:

The following error has occurred:

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: 'C:\ORACLE\ORADATA\DEMO\TEMP01.DBF'

Details:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file ORA-01110: data file 201: 'C:\ORACLE\ORADATA\DEMO\TEMP01.DBF'

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

Details:
ORA-12906: cannot drop default temporary tablespace

I can add another temp files to this tablespace, but the temp01.dbf is still missing. What should I do to recreate the temp tablespace? The only way, I guess, is to
1. create temporary tablspace temp01 ...; 2. somehow, figure a way to change the default temporary tablespace to temp01;
3. drop the tablespace temp. Wonder if I can drop it, when the temp01.dbf is missing.

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).

Please please advice. Thank you. Received on Tue Jul 15 2003 - 21:29:43 CDT