Re: restore with only one datafile ...

From: koert54 <nospam_at_nospam.com>
Date: Tue, 12 Feb 2002 14:35:17 +0100
Message-ID: <3c691a9f$0$12223$4d4efb8e@news.be.uu.net>

"Howard J. Rogers" <dba_at_hjrdba.com> wrote in message news:3c6916cf$0$12801$afc38c87_at_news.optusnet.com.au...
> A tale of deep doo-doo and woe. Forget it: it's not going to happen.
>
> The best I can think of doing (and having never got myself quite this far
> into the excrement, I've never actually tried it) is to create a new
> database comprising of nothing but the system tablespace. Create a backup
> controlfile to trace. Edit that script to point to the users01 datafile.
> Blow away existing controlfiles. Use the trace script to create new ones.
> When it fails miserably to open the database because the users datafile is
> way out of synch with the rest of the database, opt to do a recover until
> cancel. When it prompts for the application of the first redo log, type
> 'cancel'. Alter database open resetlogs.

naah - won't work
you still need to update the base dictionary tables to update datafile info, object info, extent info, user info,
table info, column info, etc ...
The only way out is a data unloader :
- first scan for all datablocks to get all datablock grouped by segment, basically an extent or block map
- then run a heuristic algorithm to determine (you can not look in the datadictionary because your missing the system tablespace) :

 + number of columns per table
 + datatype of the column (date, number, chars)
 + chained rows

- scan every block based on your block map, decoded based on your heuristic algorithm and dump the records
to flatfiles
- guess which table your dealing with based on the number of records, number of columns and column types

- create sqlloader controlfiles
- recreate the tables (DDL) in the new DB
- load the suckers back in using sqlloader

it's actually a cool programming excercise !

>
> God knows if that will work, but if it does, you are in luck: an export to
> get the data out of your rescue database. An import to get it back into
> your proper, production database.
>
> But frankly: if you take this much care of your database and its data, you
> don't deserve more than a sporting chance, which is about all you've got!
>
> Regards
> HJR
>
>
>
> "Epicentre Team B Annecy" <carmanet_at_epicentre.fr> wrote in message
> news:a4b3j8$jco$1_at_wanadoo.fr...
> > That's it!!
> > We want to build a new database, and be able to fill in the new
> > "users01.dbf" with the objects of the backuped old one!
> >
> > "koert54" <nospam_at_nospam.com> a écrit dans le message news:
> > 3c68f9d4$0$12222$4d4efb8e_at_news.be.uu.net...
> > > What you are saying is :
> > > - we've lost our complete database
> > > - we've lost our complete backup
> > > - somewhere on our tapes we've found one datafile (users01.dbf or
> > something)
> > > - we want to salvage the data in that one datafile
> > >
> > > correct ?
> > >
> > >
> > > "Epicentre Team B Annecy" <carmanet_at_epicentre.fr> wrote in message
> > > news:a4aouj$gd1$1_at_wanadoo.fr...
> > > > Hello,
> > > >
> > > > The problem is the following:
> > > > We had a USERS tablespace with a single "users" datafile (in an
> archive
> > > mode
> > > > DB), and we've made a hot backup, but we've lost all backuped files,
> > > except
> > > > this "users" datafile!
> > > > So, is it possible to move the old datafile's objects to the new
> > datafile,
> > > > for our old users and objects being recognized in the new database?
> > > >
> > > > Thanks,
> > > > Regards.
> > > >
> > > > "NorwoodThree" <norwoodthree_at_my-deja.com> a écrit dans le message
> news:
> > > > ba03e2c.0202111622.6a6ce120_at_posting.google.com...
> > > > > "Epicentre Team ANNECY" <djeanneret_at_epicentre.fr> wrote in message
> > > > news:<a48sgf$mbi$1_at_wanadoo.fr>...
> > > > > > Is possible to restore a tablespace if USER datafile is the only
> > file
> > > > > > backuped?
> > > > > > Of course it's just an exercise for a stage !!
> > > > > >
> > > > > > Thanks
> > > > >
> > > > > I dont think I understand the question. Do you mean that your
have
> > > > > multiple datafiles for the USER tablespace, and 1 of them needs to
> be
> > > > > restored due to corruption? Please clarify; the phrasing of your
> > > > > question makes it sound like you aren't familiar with Oracle
> > > > > architecture...
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Received on Tue Feb 12 2002 - 07:35:17 CST