oracle 8.1.6 - recover lost system datafile

[Tim Krego_2]

I'm trying to recover a dev database. The Oracle database was running on a K460 with 4 internal hard drives (no raid). The database was in archive log mode and I have 2700 archive log files dating back to 04/24/2003. I also have an export dump file from 05/29/2003 of the schema I'm concerned with recovering.

I do not have cold backups of the data files.

The system went down and the database won't come up. When I try to restart the database it says it needs to recover system.dbf file.

I have all the files from the database when it was shutdown abnormally. The info I am seeing says restore system datafile from tape as the only way to restore system tablespace.

I have shutdown our production database and copied it in the past. I have then used the metalink article to rename the database. We used it to copy production data to a development database.

I only need the one schema from the downed database to import into a fresh database.

Can that be done without a system datafile/tablespace?

Thanks.

[Brian Crabtree]

Login as internal, and type:

recover database;

If this returns with "Media Recovery Complete" (or something like that) go ahead and type "alter database open;".

This can happen if the database goes down wrong.

Thanks,

Brian

[Tim Krego_2]

Thanks Brian.

The database is not started or mounted.

SVRMGR> connect internal
Connected.
SVRMGR> recover database;
ORA-01034: ORACLE not available
SVRMGR>

Should I do startup mount? Or startup nomount?

Thanks again.
Tim

[Tim Krego_2]

I did startup mount.

SVRMGR> startup mount
ORACLE instance started
Database mounted.
SVRMGR> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
SVRMGR>

I dont' have a backup control file. I do have the control files that are part of the database file set I have.

[Brian Crabtree]

You shouldn't be getting that error message unless you rebuilt the controlfiles. Can you post your alertlog, and the output from "v$recover_file"?

Thanks,

Brian

[Tim Krego_2]

I think I did rebuild the control files last night.

I am working on copies of the downed database from when it crashed. I suppose I can copy them over again and not rebuild the control files.

Since I have the system.dbf file will I be able to recover? I'm beginning to think I'm making this harder than it is.

When we lost power could I have just restarted Oracle and used 'recover database'? I assumed I had to something else.

The reason I ask is that my last archive log file is number 16705. When I try to recover after rebuilding the control files it is asking for log file 16706. I don't have that file, I don't think it exists. I bet the resetlogs command has something to do with it.

I am learning. :)

[Brian Crabtree · Accepted solution]

Hmm. If you didn't specify the "alter database open resetlogs" then the logs should be valid.

Try this. Do a "select * from v$log" and "select * from v$logfile". Then try running a "recover database using backup controlfile until cancel". When it asks for a file (hopefully a number higher than 2), make sure that it is not a log that has been archived (shouldn't be) and try cutting and pasting one of the log members from the v$logfile into the specification. This should say something like "log xxxxx is not needed. Redo this for one member from each group and see if the log that is needed is still available on disk and has not been reset.

This is going with the assumption that the system datafile needed recovery, and was not completely missing or corrupted.

Post back if this doesn't work.

Thanks,

Brian

[Tim Krego_2]

I did make it more difficult than it should have been.

I had too many things going on at once. My dev box went down so I moved that database to another box and made a new instance. Or I should say I copied over an existing instance. That was the source of my confusion.

I did do a 'alter database open resetlogs' command when I was renaming the instance.

I usually try to work on researching problems before I start a new thread asking the same questions that have been answered.

I copied over all my files from the temp location.

SVRMGR> connect internal
SVRMGR> startup mount;
SVRMGR> alter database open;

That was it.

The past 2 days of self-placed H3ll are over.

Thanks again.