Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dropping a datafile from test db (ORA-01111)

Re: Dropping a datafile from test db (ORA-01111)

From: Syltrem <syltremzulu_at_videotron.ca>
Date: Sat, 13 Nov 2004 21:15:03 -0500
Message-ID: <Ffzld.61095$Kd1.1568627@weber.videotron.net>

"Joel Garry" <joel-garry_at_home.com> a écrit dans le message de news:91884734.0411121122.ae36c25_at_posting.google.com...
>
> You won't be able to roll forward without all tablespaces (unless the
> ones you drop are read only).
>

Wrong !
Oracle lets me and does not complain. Actually, I have used this way of recovering a single table 2-3 times already, by restoring just the tablespace I needed (along with SYSTEM and ROLLBACK). But on these occasions, after EXPorting the required table I just dropped the restored db.
This time, I need to keep it, clean it up and keep it for production (it's a long story).

I just made a test with tons of updates in the datafiles I did not copy over (updates committed after the backup), and I can recover to a time later than the commit time for those updates, open the database and drop those unrestored tablespaces.

All is fine, and I can have the cloned db containing transactions up to a certain time, and have it ready for production 1 minute after this time. Exactly what I needed.

Syltrem

> I haven't totally thought it through, but it sounds like you need to
> do something like:
>
> create empty database
> CTAS all desired tables
> rebuild all referential integrity
> rebuild indices
> analyse everything.
> merge update or somehow get records after CTAS to cutoff. (perhaps
> get flat files of tables at ctas and cutoff time, use posix diff and
> sort to get sqlloader-able files).
> analyse again.
>
> What happened when you tried to recreate empty files and then drop
> them?
>
> jg
> --
> @home.com is bogus.
> http://www.signonsandiego.com/uniontrib/20041112/news_1b12intel.html
Received on Sat Nov 13 2004 - 20:15:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US