The Idiot’s Guide to Recovering from Truncating a Production Table
- From: "David Taft" <oradbt054@xxxxxxxxx>
- To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 19 Mar 2008 11:21:04 -0400
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.
10) 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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
