Re: Complete recovery
Date: Thu, 2 Jul 2009 07:30:26 -0700 (PDT)
Message-ID: <121695.40573.qm_at_web38905.mail.mud.yahoo.com>
If you are using RMAN, and if it would be acceptable to recover the entire *tablespace* back to the point-in-time before the table drop (assuming it's not in the recyclebin as already suggested) then you could use RMAN tablespace point-in-time recovery. That will allow you to restore a tablespace to a point-in-time different than that of the rest of the database.
This is available in Oracle Database 10 and later (can't remember right off hand if it was a Release 2 thing or not)...
RF
Robert G. Freeman
Oracle ACE
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Features (Oracle Press)
Other various titles out of print now...
Blog: http://robertgfreeman.blogspot.com
The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)
http://pages.sssnet.com/messndal/church/parachurch.pdf
From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante_at_labor.state.ny.us> To: painterman_at_gmail.com
Cc: oracle-l <oracle-l_at_freelists.org> Sent: Thursday, July 2, 2009 8:17:36 AM
Subject: RE: Complete recovery
David,
When this happens here, we duplicate the database from a backup to a point in time, Export and Import the table, and toss the duplicated database. All with Rman. Works great!
Tom
From:oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Pintor
Sent: Thursday, July 02, 2009 8:26
AM
To: TESTAJ3_at_nationwide.com;
norman.dunbar_at_environment-agency.gov.uk; giantpanda_at_gmx.net;
niall.litchfield_at_gmail.com
Cc: oracle-l
Subject: Re: Complete recovery
The database doesn't look
corrupted as the recovery is complete, but yes, just because it's complete all
the logs are applied and therefore, the table is dropped again while
recovering. I got it now...
Unfortunately the table was dropped and purged so can't use flashback.
So I think the best solution is to export the table from the cold backup somewhere else and import it where is missing. It will do the job.
Thanks a lot for your help - much appreciated.
David
2009/7/2 <TESTAJ3_at_nationwide.com>
David
what you want is incomplete recovery, if u do complete, as u have seen it will
not be recovered, assuming u know when it was dropped, u will need to recover
to just before then and do open resetlogs, if this is not feasible since
you will lose all txs since the drop, then restore the to some other server, do
incomplete recovery over there, open resetlogs and export the table from there,
then import that to the original db.
Hth, joe
Sent from the crackberry, so please excuse the typos and terseness.
From: David Pintor [painterman_at_gmail.com]
Sent: 07/02/2009 10:47 AM
CET
To: oracle-l_at_freelists.org
Subject: Complete recovery
Hi guys,
- Oracle 10.2 on Solaris 10 box.
Someone removed a user table this morning. I have a cold backup from last night. I want to restore just the USERS tablespace (where the table was) and perform a complete recovery. Is that possible? I've tried recovering the datafile by stopping the database, copying the datafile manually, and then doing a recover database. The recover is complete but my table is still missing... what am I doing wrong?
Thanks for your help.
David
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 02 2009 - 09:30:26 CDT