Re: recover from TRUNCATE TABLE via physical standby?
- From: wblanchard@xxxxxxxxxxxxxxx
- To: landstander668@xxxxxxxxx
- Date: Fri, 24 Aug 2012 10:29:07 -0500
Adric,
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.
http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmtspit.htm
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 <landstander668@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Date: 08/23/2012 04:40 PM
Subject: recover from TRUNCATE TABLE via physical standby?
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
