RE: The Idiot's Guide to Recovering from Truncating a Production Table

You can't beat a place where you screw up and truncate a table and they thank you for quickly recovering from it.

Steve Smith
Desk: 303-231-5499
Fax: 303-231-5696

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Taft Sent: Thursday, March 20, 2008 9:24 AM
To: tim_at_evdbt.com
Cc: Oracle-L Freelists
Subject: Re: The Idiot's Guide to Recovering from Truncating a Production Table

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:

1. I was very familiar with the business nature of the system.
2. new transactions were failing due to the truncated table;
3. a full telephone conference was held with all the principal players after the initial shutdown;
4. 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.
5. because of the nature of the data (user complaints), the principals were not worried about a few lost transactions and jokingly welcomed it.
6. 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.
7. The principals were pretty laid back and understanding saying, these things happen.
8. 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-l


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