?
Solved

Recover Oracle DB after OS Drive Failure

Posted on 2005-02-26
8
Medium Priority
?
866 Views
Last Modified: 2013-12-11
We recently experienced a hard drive failure that caused our control files for our oracle 8i db to become corrupted.  We tried several different things to attempt recovery (ie, changing from control01.ctl to 02 to 03, etc).  After searching through several log files, we noticed a CRC error on the control files, which do not have the sql in a trace file (to rebuild the ctl file).

Unfortunately, the hard drive failure required a clean install of the system -- now running win 2k3 server, instead of 2k.  Is there a way to recover the db using the dbf files from the old db that are not corrupted?

Thanks for any assistance.
0
Comment
Question by:mviron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13420357
You can create the control files manually but this is VERY dangerous.  Is strongly suggest that if you have a support contract in place you open a TAR through Metalink (This might be a problem since 8i has been de-supported).

So much for the disclaimer.

IMPORTANT:  Make a backup of everything you currently have just in case you need to get back to point A.

Do you have access to another 8i instance?  If so, I suggest generating a control file trace from this instance and then changing the datafiles/names/etc... for the new instance and rebuilding the control file in question.

If you do not have another instance by which to jumpstart the create controlfile syntax:  You are left with creating the controlfile sql from scratch.  I suggest then going to the docs for proper syntax.
0
 

Author Comment

by:mviron
ID: 13421941
Good news -- you helped us to recreate the ctl file -- however, we ran into a few errors:

Connected to an idle instance.
ORACLE instance started.

Total System Global Area  112400412 bytes
Fixed Size                    75804 bytes
Variable Size              57901056 bytes
Database Buffers           54345728 bytes
Redo Buffers                  77824 bytes

Control file created.

ORA-00279: change 2768539 generated at 02/25/2005 17:32:52 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA81\RDBMS\ARC05185.001
ORA-00280: change 2768539 for thread 1 is in sequence #5185
ORA-00373: online log version 8.1.6.0.0 incompatible with ORACLE version
8.1.0.0.0
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\KIREC\REDO03.LOG'


ORA-00308: cannot open archived log '--'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\ORACLE\ORADATA\KIREC\SYSTEM01.DBF'

any suggestions?

Thanks!
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13422026
I hope you have a copy of the archived redo logs around somewhere.  It needs: ARC05185.001 to do a DB recovery.

If you are not able to locate these, you should be able to open the database using 'reset logs'.  Once the DB is forced open you will need to do the following:

full export
drop the database
rebuild the database
import

the reason for this is that by using 'reset logs' you are now in an unsupported state (Oracle cannot guarentee the DB is healthy, no matter how good it looks.  I lived through a 72 hour rebuild because of this........ I did get a 4 hour break though.  I shouldn't complain).
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mviron
ID: 13422258
we attempted to do a "alter database open resetlogs", and got hit with

ERROR at line 1:
ORA-00600: internal error code, arguments: [2765], [135290880], [], [], [], [],
[], []

Any suggestions?
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 13422593
Ora 600s are a bear to troubleshoot.  They're a catch all for internal errors.

Here's all I can find on the specific error.  It points to the COMPATIBLE parameter in the init.ora.  I don't think this is accurate given your current situation but stranger things have happened.

Check out:
http://www.lazydba.com/oracledba.pl?0::39894

If you have access to Metalink you can perform a lookup on ORA-600 errors:
http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=153788.1&blackframe=0
0
 

Author Comment

by:mviron
ID: 13423444
Thank you so much for your assistance!  We have successfully recovered everything that was in that database with your assistance.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 13423522
No problem.  That's what most of us are here for and some of use have actually had to do this in the past (and it wasn't fun).

Just for future readers:  What was causing the ORA-600?
0
 

Author Comment

by:mviron
ID: 13423650
you were correct in suggesting that the ORA-0600 [2765] was the compatible parameter in init.ora.  Ours was set to 8.1.0 locally, but on prod was set to 8.1.6.0.0 .  After comparing the init.ora files against each other (prod vs our recovery instance), we noticed that difference (along with several others).  Once we made those changes, everything was good to go.

Again, thanks so much for your assistance!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question