Forum OpenACS Q&A: Oracle Recovery Needed

Posted by Colin Summers on 11/12/04 04:12 PM

Hello ACS People,

I have a web service that I built five years ago on Oracle and aolserver, following Greenspun's book and cribbing a lot from pieces of what would become the ACS. (I can't remember if it was a real package then or not. A lot of the code that I learned from was in an MIT course he was teaching, which the code snippets posted on a web site.)

First I had a hacker attack, and just this past week I had a drive die. And apparently the RAID has go flaky.

A bunch of Oracle files (.CTL files) disappeared. My Oracle wizard checked in and took a look and said it was beyond the usual magic, that I needed a "recovery expert."

My wizard said this would be a good place to ask for one. Does anyone know one? Is one? Has used one?

I'm in Los Angeles, but I think the Internet means that does matter.

It is a small amount of data (as Oracle goes) just 1g. It's in about twelve tables or so.

Thanks,
--Colin

2: Re: Oracle Recovery Needed (response to 1)

Posted by Janine Ohmer on 11/12/04 07:14 PM

I'm Colin's "wizard" so let me fill in a few more details.

The system had a single IDE drive which contained both Linux and the Oracle executables, and then a RAID setup for everything else. The internal drive died, and there were no backups. He had a local sys admin put Linux back together and we were able to recover Oracle (8.1.6) from his development system, and the data files are on the RAID so we still have those, but the control files are lost.

In addition, it seems there were no database dumps being performed and/or we have lost those too. I'm not exactly sure. Colin and I are both sure that the dumps were happening a couple of years ago (because I set them up), but somewhere along the way they seem to have stopped. There have been several admins working on thes system over time, so we don't know exactly who has done what.

Anyway, the only way to recover his user's data is to get Oracle up and running. I was working on it and having trouble with it not finding a library that is plainly there, when I realized that the control files were missing. I've never done this type of recovery before, and I told him that he'd be better off hiring someone who really knows how to do this than waiting for me to figure it out.

I know you're all cringing by now... let me just say that I've been helping Colin out over the last few years as a favor, just stepping in when things get sticky. This should not be taken as an example of how furfly runs their servers! :)

3: Re: Oracle Recovery Needed (response to 1)

Posted by Dion Sweat on 11/13/04 04:18 AM

If your database was running in archivelog mode and you have all of the archived log files you should be able to get the database back. You need to recreate the control file(s). You're going to do something like:

startup nomount

create controlfile set database "yourdb" archivelog
...
your datafiles and other settings
...

If you were backing up your control file to trace ('backup control file to trace') you will have a script (usually in your udump directory) you can start with, but it will need some minor tweaking.

Then you will roll forward through your log files. Even if you don't have everyone of them you may be able to get all the file headers synced in order to open Oracle.

recover database until cancel using backup controlfile

And finally open your database with
alter database open resetlogs

That's the basic idea anyway.

Good luck.

4: Re: Oracle Recovery Needed (response to 1)

Posted by Andrew Piskorski on 11/13/04 09:07 AM

Hopefully you had multiple control files and you moved one of them to a differnt disk. (See Moving a Control File in my Oracle notes.) If not, then hopefully you have results of doing a "backup control file to trace", like Don was talking about. If not, then you have to recreate the control file from scratch. I think those are the only three options.

Janine, what do you mean by "recover Oracle from his development system"? You mean the actual Oracle software?

Dion, since the database is intact, why does it matter whether it was running in archive log mode or not? Something to do with having to recreate the control file? I don't remember whether that matters...

5: Re: Oracle Recovery Needed (response to 1)

Posted by Dion Sweat on 11/13/04 03:37 PM

In my experience this will always require recovery since all of the data file's SCNs must be synced to open Oracle. I think his site would have had to be totally inactive for it not to need recovery, even if he has all of the data files. If it was active and not in archivelog mode, wouldn't he be required to go back to a previous cold backup?

I usually use this process (recreating the control file) to refresh test databases from running production ones, which always requires recovery. But Oracle is pretty robust in this manner, so hopefully it will have a happy ending...