Re: restore a single datafile

"gritto" <agrittini_at_yahoo.com> wrote in message news:3e259441$0$225$4d4efb8e_at_read.news.it.uu.net...
> What do you mean with proper backup ?
> RMAN or a backup utility ??
> I've no enterprise manager environment, only a flat copy of the file with
> the db closed.

By 'proper backup' he means merely one that's been backed up properly. A closed database, followed by O/S commands to copy all the relevant files is perfectly adequate. However, the magic words there are 'all the relevant files'.

What's relevant for a closed database backup? A control file, the data files, possibly (but strictly not necessary) the online redo logs. And of course your archive log files.

Because Dusan is perfectly correct in saying that you can't just wander up to a database and attempt to attach a datafile that doesn't agree with the rest of the database's timestamps. And it is irrelevant whether the file contains only data for one user and has no relationship with data contained elsewhere in the database: Oracle doesn't know that. All the database will know is that your database is at time X and you're trying to get it to open a file from time Q. And that isn't going to work.

[Quoted] You say that you dropped the tablespace. That's a problem (and, as an aside, [Quoted] I always tell my students that this is a most unlikely recovery scenario, since you can't drop a tablespace that's got anything in it casually. You have to be really deliberate about it and use the 'including contents' clause. Not something you are likely to do by accident. Or so I thought).

Recovery of the data file in such a scenario is not sufficient, because your current Control File got modified by the drop command, so it doesn't know anything at all about that data file anymore.

What you must therefore do is restore every single data file, AND the control file, from your closed database backup. But for Heaven's sake don't restore the online redo logs if you happened to have backed those up as well.

With those files restored, you get the database into MOUNT state, and issue the command 'recover database until time 'XXXXX' using backup controlfile'.

[Quoted] The 'XXXXX' there is the time just prior to when you issued the drop tablespace command. The backup controlfile clause is required, because you're using (and have to use) a backed up binary version of the controlfile, meaning that it itself contains the "wrong" timestamp, and you have to alert Oracle to that fact.

You then apply archived redo logs as prompted, and the recovery should stop without re-issuing the drop command. Your datafile is now effective re-plugged into the database.

Finally, you issue an alter database open resetlogs.

Check that all required tables are correctly back in place, and then shut down and take a fresh backup: the resetlogs command renders all prior backups and archives useless.

And that's the only way you're going to achieve this. It means, of course, that your *entire* database goes back to the time of the last backup, and lots of data will be lost.

[Quoted] So you might want to investigate perhaps using your cold backup to create a [Quoted] clone of your live system, and use export and import to retrieve the data that way. You export from the clone, and then import into your real system, [Quoted] having re-created an identically-named tablespace first.

But I'll leave that for you to investigate.

Regards
HJR
> Thanks
> AG
>
>
> "Dusan Bolek" <pagesflames_at_usa.net> wrote in message
> news:1e8276d6.0301150850.61e15f6d_at_posting.google.com...
> > "gritto" <agrittini_at_yahoo.com> wrote in message
> news:<3e254898$0$227$4d4efb8e_at_read.news.it.uu.net>...
> > > Hi all,
> > > is it possible to restore a single datafile (or tablespace) from a
> backup by
> > > attaching the file ??
> > > I've dropped a tablespace yesterday, but I can restore from a backup
the
> > > datafile that is in the tablespace, so I'd like to know if there is a
> mode
> > > to include that old datafile again in the db without repplying all the
> log,
> > > or reimport the data.
> > > The tablespace contains data for a single user, so no data are lost
> around,
> > > all is included in that file.
> >
> > You can't restore single datafile by attaching it to database. It
> > doesn't matter if you have any data around. In fact you have always
> > some data around, even if it is a single tablespace for single user.
> > However, you're not lost. If you have proper backup, you can easily
> > recover database to the time before dropping and then just transfer
> > data between your database and restored one using DB link, exp/imp, as
> > you wish.
> >
> > --
> > _________________________________________
> >
> > Dusan Bolek, Ing.
> > Oracle team leader
> >
> > Note: pagesflames_at_usa.net has been cancelled due to changes (maybe we
> > can call it an overture to bankruptcy) on that server. I'm still using
> > this email to prevent SPAM. Maybe one day I will change it and have a
> > proper mail even for news, but right now I can be reached by this
> > email.
>
>
Received on Thu Jan 16 2003 - 01:44:23 CET