Re: The Idiot’s Guide to Recovering from Truncating a Production Table

From: David Taft <>
Date: Thu, 20 Mar 2008 11:24:15 -0400
Message-ID: <>


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.



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 <> 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

Received on Thu Mar 20 2008 - 10:24:15 CDT

Original text of this message