recover from TRUNCATE TABLE via physical standby?

From: Adric Norris <landstander668_at_gmail.com>
Date: Thu, 23 Aug 2012 16:39:19 -0500
Message-ID: <CAJueESpUf73_OvFsXhrwmGcKj8gB7Pm3azg=_MHCnTG-ozROKw_at_mail.gmail.com>

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


--
http://www.freelists.org/webpage/oracle-l

Received on Thu Aug 23 2012 - 16:39:19 CDT