RE: The Idiot's Guide to Recovering from Truncating a Production Table
Date: Thu, 20 Mar 2008 10:22:40 -0600
You can't beat a place where you screw up and truncate a table and they thank you for quickly recovering from it.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Taft Sent: Thursday, March 20, 2008 9:24 AM
Cc: Oracle-L Freelists
Subject: Re: The Idiot's Guide to Recovering from Truncating a Production Table
All excellent points. I should clarify for everyone who read this post that this was not intended as advice on what to do in a similar scenario. This was a tongue-in-cheek procedure I wrote to poke fun at myself back when the event happened which was then shared with my co-workers who were well aware of the surrounding circumstances.
Some of the surrounding circumstances were as follows:
- I was very familiar with the business nature of the system.
- new transactions were failing due to the truncated table;
- a full telephone conference was held with all the principal players after the initial shutdown;
- I recommended cloning the database and then doing export/imports, but there was no space available and none of the principals thought it was worth the effort to acquire the space, etc., etc.
- because of the nature of the data (user complaints), the principals were not worried about a few lost transactions and jokingly welcomed it.
- In the end everyone was glad for the expedience and the fact that they could now say with confidence that their system could be recovered quickly.
- The principals were pretty laid back and understanding saying, these things happen.
- In the end they thanked us for the quick response.
There is a lot more detail I could share, but I won't bore you. All I can say is that I was very fortunate.
P.S. Based on your response, I have modified step 14 to, "Thank your coworkers for all their help, go out for drinks on you and be grateful you still have a job".
On Wed, Mar 19, 2008 at 9:10 PM, Tim Gorman <tim_at_evdbt.com> wrote:
> It is not always easy to determine the importance of a single table
> complex application, never mind the downstream impact of a unilateral
> decision to SHUTDOWN in the middle of a work day. In many
> circumstances, step #2, not step #1, could be a a true
> move. Personally, I'd reverse the order of steps #2 and #3...
> Also, step #13 is missing an important step between the SHUTDOWN and
> STARTUP command -- BACKUP DATABASE (full or level-0). Before you
> that newly-recovered database for users, after a prolonged and
> unexpected outage, you need to ensure that you can recover that
> mutilated database from any further failures. And, you may very well
> have to do that full database backup "cold", with the users shut out.
> Think about it -- if some failure should occur prior to the
> of a "hot" backup? So, step #13 can be maddeningly prolonged, but it
> necessary, unless one wishes to start from step #1 all over again...
> But all this can be avoided if you instead perform a point-in-time
> recovery to a separate "clone" database, which consists of just all
> tablespaces that contain undo/rollback segments (including SYSTEM)
> the set of datafiles containing the table that was truncated. Once
> offline clone database is restored, you can then import the recovered
> table back into the production database. The step involving some
> of import of the table may seem inefficient, but the advantages are
> you can retain any records added after the truncate, you don't lose
> data in the other tables, and you do not have to perform the
> database backup in order to maintain recoverability.
> The last time I had to do this in production was prior to the advent
> RMAN. We did a PITR recovery to a clone database, imported the
> brought the applications back online within 90 mins of the DROP
> then went out and got smashed the rest of the afternoon. We returned
> the office after dark to get our cars, and found a new production
> in progress. As I was smashed and this happened back in the 20th
> century, 13 years ago, I don't recall what that second outage was,
> remember our happy buzz turning to mean hangover in an instant,
> by a late and painful night. Funny the things you remember... ;-)
> Hope this helps...