recover from TRUNCATE TABLE via physical standby?

From: Adric Norris <>
Date: Thu, 23 Aug 2012 16:39:19 -0500
Message-ID: <>

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 Thu Aug 23 2012 - 16:39:19 CDT

Original text of this message