Re: recover from TRUNCATE TABLE via physical standby?

From: <>
Date: Fri, 24 Aug 2012 10:29:07 -0500
Message-ID: <>

I just went through this and it was a mess. I see one flaw with your plan. You're assuming that you catch the truncate or the users notify you in a timely manner. If you're using data guard, this seems unlikely. If you're using log shipping, it's more plausible but still not probable that you'll be notified in time to stop the log shipping or apply.

The best bet is to run some tablespace point-in-time tests. 11g automates it by actually creating the auxiliary instance, cloning it to a point-in-time and copies the tablespace from the auxiliary to the primary database.

Your other options are to manually create an auxiliary and copy the table or use rman to refresh your test/dev instance and copy the table.

WGB From: Adric Norris <> To: oracle-l <> Date: 08/23/2012 04:40 PM

Subject:        recover from TRUNCATE TABLE via physical standby?
Sent by:

I've got a hypothetical question for y'all. :) I'm considering possible options for recovering data following a TRUNCATE TABLE command, with the help of a physical standby database, without requiring downtime on the primary side. The following assumptions are being made:

  • Database version is, running on Linux
  • Forced logging is enabled on both sides
  • Flashback database is enabled on both sides, with an adequate retention target
  • The standby is applying redo as fast as it's received (no configured delay)
  • No export (data pump or otherwise) is being run, because the DB is just too darned big for it to complete in a reasonable timeframe

Here's the basic procedure which *seems* plausible, assuming that my understanding of the various pieces is correct, performed on the standby side:

  • Stop the Dataguard APPLY processes
  • Restart the standby database in MOUNT mode, and note the current SCN
  • Flashback to a point (shortly) before the TRUNCATE TABLE statement
  • Open the database read-only, and extract the desired data
  • Restart the standby database in MOUNT mode
  • Flashback/recover the standby database back to the original SCN
  • Restart the Dataguard APPLY process

Anyone care to comment on whether or not the above is actually viable? I'm planning to perform all necessary testing/validation, but would like to ensure that I'm at least nominally sane before kicking off that kind of effort. <g>

More mundane options, such as TSPITR, are of course also under consideration.

"I'm too sexy for my code." -Awk Sed Fred


Although this e-mail and any attachments are believed to be free of any virus or other defect which might affect any computer system, it is the responsibility of the recipient to check that it is virus-free and the sender accepts no responsibility or liability for any loss, injury, damage, cost or expense arising in any way from receipt or use thereof by the recipient.

The information contained in this electronic mail message is confidential information and intended only for the use of the individual or entity named above, and may be privileged.  If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.  If you have received this transmission in error, please  contact the sender immediately, delete this material from your computer and destroy all related paper media.  Please note that the documents transmitted are not intended to be binding until a hard copy has been manually signed by all parties.
Thank you.

Received on Fri Aug 24 2012 - 10:29:07 CDT

Original text of this message