Re: Complete recovery

From: Robert Freeman <>
Date: Thu, 2 Jul 2009 07:30:26 -0700 (PDT)
Message-ID: <>

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)...


 Robert G. Freeman
Oracle ACE
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: 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! :-)

From: "Mercadante, Thomas F (LABOR)" <> To:
Cc: oracle-l <> Sent: Thursday, July 2, 2009 8:17:36 AM
Subject: RE: Complete recovery  


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 [] On Behalf Of David Pintor Sent: Thursday, July 02, 2009 8:26
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.


2009/7/2 <>
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 []   Sent: 07/02/2009 10:47 AM
  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.


Received on Thu Jul 02 2009 - 09:30:26 CDT

Original text of this message