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

From: David Taft <oradbt054_at_gmail.com>
Date: Wed, 19 Mar 2008 11:21:04 -0400
Message-ID: <69b058e80803190821r3e994783n8d7eb0295fdf5ec7@mail.gmail.com>

All,

After reading some of the posts on the "DBA Future" thread, it reminded me of the worse mistake I ever made as a DBA. Immediately after the incident I documented the actual recovery procedures partly in jest. Thankfully I haven't had a job interview in 15 years and don't anticipate having one anytime soon, so I feel relatively safe sharing this here for anyones possible enjoyment. :-)

Cheers,

David

---

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

In this scenario you just truncated a table in production that you meant to truncate in development. Now what do you do?

1. Shout OHHH SH**! (fill in the '*' with whatever letters you think are appropriate)
2. After you've gotten over the initial shock of this monumental mistake, do a

           Shutdown immediate;

3) Stop, think and immediately tell your manager or coworkers what you did.

4) Curse a coworker who had nothing to do with YOUR mistake. It won't help the situation, but now you can feel bad for two stupid mistakes.

5) Apologize for cursing your coworker and accept FULL responsibility for YOUR mistake.

6) Now that you are rational, calmly discuss with your manager/coworkers the best course of action. If it is decided to proceed with a point-in-time recovery, then continue with the following steps.

7) Backup the controlfile in case you have to restart the recovery:

           Alter database backup controlfile to '$ORACLE_HOME/dbs/$ORACLE_SID_backup.ctl';

8) Check the alert log for the exact time you started the "shutdown immediate".

9) Think about how much time may have passed between step 1 and 2, then decide on a safe point-in-time before the truncate occurred.

1. Run 'rman'.

            RMAN> connect target /
            RMAN> restore database;
            -- After restore completes
            RMAN> exit

11)	Run 'sqlplus "/ as sysdba'

            SQL> recover database until time 'YYYY-MM-DD:HH24:MI:SS';
            SQL> alter database open resetlogs;
            --shutdown may take a while to apply undo from recovery.
            SQL> shutdown immediate;
            SQL> Startup restrict;

12)	Check to make sure everything looks OK.

13)	If everything looks good, then open the database to the users:

            SQL> shutdown immediate;
            SQL> startup;

14)	Thank your coworkers for all their help and be grateful you still

have a job.

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

Received on Wed Mar 19 2008 - 10:21:04 CDT