Re: recover from TRUNCATE TABLE via physical standby?

From: Kevin Jernigan <kevin.jernigan_at_oracle.com>
Date: Thu, 23 Aug 2012 14:52:11 -0700
Message-ID: <5036A60B.2050300_at_oracle.com>

You could enable Flashback Data Archive (Total Recall) on the table, then use Flashback Query to select from the table AS OF a timestamp right before the TRUNCATE. This would be much easier to implement, with no need to muck around with disconnecting the standby db etc...KJ

-- 
Kevin Jernigan
Senior Director Product Management
Advanced Compression, Hybrid Columnar
Compression (HCC), Database File System
(DBFS), SecureFiles, Database Smart Flash
Cache, Total Recall, Database Resource
Manager (DBRM), Direct NFS Client (dNFS),
Continuous Query Notification (CQN),
Index Organized Tables (IOT), Information
Lifecycle Management (ILM)
(650) 607-0392 (o)
(415) 710-8828 (m)

On 8/23/12 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 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.

>


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

Received on Thu Aug 23 2012 - 16:52:11 CDT