Re: recover from TRUNCATE TABLE via physical standby?

From: Michael Dinh <>
Date: Fri, 24 Aug 2012 06:24:07 -0700
Message-ID: <>

Depending on the time it takes to restore the primary database and what backup technology is being used,
it might be best to performed a restore and flashback on primary rather than standby database.
It would also depends on the size of the database as well

I actually had to recover from TRUNCATE TABLE once, and leaving you with my shameless plug.

Shameless plug

HTH On Thu, Aug 23, 2012 at 2:39 PM, Adric Norris <>wrote:

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

Received on Fri Aug 24 2012 - 08:24:07 CDT

Original text of this message