Recovering from Crash

A poor soul landed in my blog after searching for “oracle db won’t come up after crash” I can just imagine the lonely DBA sitting in front of the crashed DB without a clue on how to proceed.

Bummer, isn’t it? The best solution is to find the nearest experienced DBA and get him to help you. But if you don’t have one around, here’s my take on the situation:


It can be that the same issue that caused the crash still exists. Lack of disk space and other storage issues are the first things that come to mind, but I’m sure there can be others.


Another possibility is that while the DB was up you made some changes to the SPFile and when it tries to start with the new SPFile, the changes you made prevent it from starting. In this case, I’d try starting from a backup of the spfile or init.ora (you did backup these very important files, right?).


The most common possibility is that nothing prevents the DB from starting, but when it crashed it got into a slightly inconsistent state and now it needs some recovery. You can use Oreilly’s “Recovering Oracle” page, which is very comprehensive and covers almost every scenario.

Most likely you have a damaged data file, and when you try to do “alter database open” Oracle will tell you exactly which data file is damaged. In this case I hope you have your archive log files ready, and you can do "Recover datafile '<datafile-name>';". You can also try recovering to a known good time before the crash by doing “Recover database until time '2001-03-06:16:00:00'“.


There are tons of other options – recover until cancel, bring datafiles back from backup, bring control file back from backup, etc. Which is why I recommend looking up Oreilly’s page in emergencies and reading Oracle’s Backup and Recovery book cover to cover while you are not handling emergencies.

Understanding Oracle’s concepts of datafiles, redo logs, log archives and control files goes a long way toward having calm and successful recoveries.

I also really recommend practicing recovery from backup on a development system every month or two, because if you are used to doing recovery it will be that much easier to do it when disaster strikes.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s