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

"Syltrem" <joe_at_videotron.ca> wrote in message news:<4GRkd.16883$Kd1.115375_at_weber.videotron.net>...
> "Steve Howard" <stephen.howard_at_us.pwcglobal.com> a écrit dans le message de
> news:6d8b7216.0411111022.50e29210_at_posting.google.com...
> > "Syltrem" <syltremzulu_at_videotron.ca> wrote in message
> news:<GAukd.48$df.1932_at_tor-nn1.netcom.ca>...
> > > I copied ALL of the datafiles for the tablespaces I need in the cloned
> db.
> > >
> > > I can't use EXPort, or trsnaportable tables.
> > > I have 0 seconds to clone the db, at exactly 00:00 hours on the 1st of
> > > December, so what I want is to clone the db, then roll it forward until
> the
> > > "cut" time.
> > >
> > >
> > > --
> > > Syltrem
> >
> > Maybe I am missing something, but why not hot clone your database
> > sometime before midnight on the 1st, and just apply redo to it until
> > midnight. At that time, archive the current online redo log in the
> > source database, and apply this newly archived log to the clone,
> > cancel recovery, and you are current...maybe not in one second, but
> > pretty close.
> >
> > You could then drop whatever you wanted/didn't need.
> >
> > Regards,
> >
> > Steve
>
> Hi
>
> That's exactly what I want to do except:
> a) The cloned db must have a different name (because on same machine)
> b) I do not whish to clone all tablespaces. Don't even have sufficient disk
> space for that.
>
> That's why it gets complicated.
> a) I have to do a CREATE CONTROLFILE and only specifiy the datafiles I moved
> over
> b) Oracle will not let me drop the unrestored (and offline) tablespaces
> after the recovery is finished and the db open. And BEFORE it's open, it
> just doesn't see the missing files yet. Only when I do OPEN does it realize
> the dictionary know about files that do not exist in the controlfile. If
> fixes that but I still cannot drop them.
>
> Syltrem

You won't be able to roll forward without all tablespaces (unless the ones you drop are read only).

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