Re: The Idiot’s Guide to Recovering from Truncating a Production Table
Date: Thu, 20 Mar 2008 11:24:15 -0400
Message-ID: <69b058e80803200824l3dc92818kc0c225a7d875df2@mail.gmail.com>
Tim,
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.
Cheers,
David
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 in a
> 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 career-limiting
> 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 open
> 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 completion
> of a "hot" backup? So, step #13 can be maddeningly prolonged, but it is
> 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 the
> tablespaces that contain undo/rollback segments (including SYSTEM) and
> the set of datafiles containing the table that was truncated. Once this
> offline clone database is restored, you can then import the recovered
> table back into the production database. The step involving some form
> of import of the table may seem inefficient, but the advantages are that
> you can retain any records added after the truncate, you don't lose the
> data in the other tables, and you do not have to perform the full/level0
> database backup in order to maintain recoverability.
>
> The last time I had to do this in production was prior to the advent of
> RMAN. We did a PITR recovery to a clone database, imported the table,
> brought the applications back online within 90 mins of the DROP TABLE,
> then went out and got smashed the rest of the afternoon. We returned to
> the office after dark to get our cars, and found a new production outage
> 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, but I
> remember our happy buzz turning to mean hangover in an instant, followed
> by a late and painful night. Funny the things you remember... ;-)
>
> Hope this helps...
>
> -Tim
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 20 2008 - 10:24:15 CDT