Re: Hot backup and recovery

In article <83kai2$6qj_at_chronicle.concentric.net>,   "Jack Zhu" <jackzhu_at_qwestinternet.net> wrote:
> Oracle 8.0.5 for RedHat Linux, which is in archive log mode.
>
> At 10 am, I did a hot back up. Then at 10:15, a user dropped a table
by
> mistake. I want to recover the database back tp 10am status, for
recovering
> the dropped table.
>
> My question is: what's the precedure? Can I do the following:
>
> 0. shutdown the database first
> 1. startup mount;
> 2. alter database datafile 'fileA' offline; (The dropped table is
only in
> fileA.)
> 3. restore the fileA from my backup at 10am.
> 4. issue "recover datafile 'fileA'; " Now it's supposed to display
some
> messgaes like 'log file xxx applied...'. But I didn't get them at
all, the
> only message is 'media recovery complete'
> 5. alter database datafile 'fileA' online.
>
> Of course, database runs normally, but the dropped wasn't recovered.
What's
> wrong with my procedure?
> Do I have to also restore the control file(or other files) in stop 3,
or
> before step 1? If yes and there're several copies control files in
different
> location, restore them all?
>
> And other better way to solve this dropped table problem? Thanks a
lot!
>
> Pls also email me a copy.
>

When you apply the archived redo logs to the hot backup to roll forward by default you roll forward to the most recent checkpoint. So you reapplied the drop.

To recovery the dropped table you would have to do a point in time recovery to a point prior to the drop. The entire database must be at the same point in time to be consistent so you have to apply the recovery to the entire database. This is normally not an acceptable solution for a production system due on a problem with only one user table.

There are work arounds such as creating a copy database where you use the alter database drop datafile to remove all datafiles except those that make up system, your temp tablespace, your rbs tablespace, and the target tablespace. Apply recovery.

I would suggest looking at the backup and recovery manual before attempting this. Unless this is a mission critical table I would tell the user all you can do is restore it from you last export and to reconstruct his data. Otherwise you will end up doing an incomplete recovery, resetting your logs, and forcing your database to run with possible inconsistencies in its tables/indexes.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 20 1999 - 08:09:17 CST