ORA-01172 - ORA-01151 Recovery errors ...

I get these error messages when starting a db instance in oracle 8i. Any idea what I am to do with these two errors.

ORA-01172:  recovery of thread 1 stuck at block 2 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You have a corrupted block on data file2.

you probably need to recover it using the data file backup.

From MetaLink

Error:  ORA 1172  
Text:   recovery of thread <num> stuck at block <num> of file <name > 
Cause:  Crash recovery or instance recovery could not apply a change to a block because it was not the next change.  This can happen if the block was corrupted and then repaired during recovery. Action: Perform a RECOVER datafile for the file containing the block. If this does not resolve the problem, then restore the file from a   backup and recover it again.

Are you archivelog or noarchivelog mode?

Try this first

If this does not work then you will probably have to do media recovery. The type
of recovery depends on type of archive mode you are running.

Look at alert log for additional info such as file needing media recovery. The type of file needing recovery also determines type of recovery.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RomansAuthor Commented:
I tried RECOVER DATABASE and got the following:

ORA-00283: recovery session cancelled due to errors
ORA-00600: internal error code, arguments [3020] , [8383610], [1], [5237], [22], [16], [], []

Now what ... ?

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Unfortuneatley the first argument of the ora-600 [3020] is not in Oracle ora-600 lookup.  The ora-600 errors are very generic and Oracle recommends contacting them as you should never get an internal error.  
Shutdown the database
startup nomount  -- Any errors
alterdatabase mount; -- any errors
Alter database open; -- any errors.

Have you reviewed alert log and trace files for error messages.  You must have at least gotten the database in mount stage in order to recover database.
Again what archive mode is your database? Again, have you reviewed alert log for errors that may indicate problem and file needing media recovery.
RomansAuthor Commented:

-I can not fine an Alert log on my hard drive.
-How can I tell what archive mode my database has?

* startup nomount (no errors)
* alter database mount (no errors)
* alter database open (ORA-01172:  recovery of thread 1 stuck at block 2 of file 2 and ORA-01151: use media recovery to recover block, restore backup if needed)

KongAWS Certified Solutions Architect - ProfessionalCommented:
If this is a production database:

Take datafile 2 offline and open the database. Then worry about recovering it while users are able to perform some queries.

What's the second datafile most people usually create? TEMP?

Find out what datafile 2 is and determine whether you need to recover it...

I doubt it's in archivelog mode, I think offline drop may be the only solution.

To find your alert log look in your init<sid>.ora file and find
background_dump_dest.  This is where your <sid>alrt.log file is. Review it for all errors. It may indicate what file needs recovery.  What type of backups are you currently doing?  When did you do the last backup.  You need to provide us with all the info you can about type of backup to offer best advice.
Do NOT drop anything at this point...very dangerous!!!

RomansAuthor Commented:
Dr Jekyll,
The database I am working is on my laptop only. I use this as a test vehicle for delivery to a production acceptance database followed by a move to production on other servers. Normally the data I have in my db is test scenarios etc and I test client code to ensure it functions. The data itself is not from an active production system so I stand to lose only my specific data.

I gave up last night and completly rebuilt my database from a db dump from one of my coworkers laptop so I am back in business - minus all my test scenario data. I would welcome some additional comments on how to avoid this problem in the future. I am considering a nightly dump to my hard drive but will gladly try anything else to avoid my nightmare yesterday. I have multiple instances running on my laptop the two that were running when Windows crashed were lost. The instances that were not started remained in tact.

Many thanks for all your comments ...


Most likely, The windows OS on your laptop is NOT oracle certified.
the reason I say this is that what happened to you is very typical of OS crash or Instance failure,  could happen anytime on other systems, Oracle is built to overcome these issues. from my experience, I had 4 instances on a LINUX machine once and had them running for a year during which they experienced a lot of power outage or system hangs , but they can all survive.

So maybe it's NOT a good idea to run Oracle in your current Windows OS version, let alone multiple instances.

What is your OS version?  If certified then multiple instances is not an issue assuming you have the resources to support multiple instances.  Glad to hear you have your instances running.  There is nothing wrong with doing a periodic refresh of your test instance with production data.  I do that very thing on several of my dev instances.

RomansAuthor Commented:
I am running Windows 2000 Professional Version 5.0.2195 Service Pack 2 Build 2195.

Some of my other coworkers have also experienced this kind of problem - and also have never been able to recover from it. I was hoping I would be the first but no such luck. I have 5 instances on my laptop but never start all 5 at once. I run 2 instances at a time (maybe three on occasion) and don't in general have any problems. This is the first one really.

I have heard that Windows 2000 starts to have problems when disc memory gets below 6 gig but ... is it fact or urban ledgend? I have cleaned up my hard drive anyways just in case ...


the fact that ORACLE database ON WINDOWS OS family shows poor or bad recovery capability is mainly due to the OS LAYER. the architecture how Oracle is implemented in windows is fundamentally different from the one in UNIX. (Thread vs. PROCESS). there are of course pros and cons on both implementation. But PROCESS seems to excel in the segregation of different processes, memory space.
Wow seazodiac that is quite a BOLD statement which may generate a lively debate.  
Where did you get "the fact that ORACLE database ON WINDOWS OS family shows poor or bad recovery"?  I have been working in Oracle Windows environment for about 10 years and cannot say this is true.  We have approximately 40 Window servers, ~80 instances and no problems thusfar with B&R. Both OS certainly have their strengths and weakness but to corrleate THREAD vs Process pertaining to Oracle B&R is stretching it somewhat. Just another view point.
I will reserved my opinions to avoid such a debate.
But WHAT I INTEND to say is that Oracle on WINDOWS 2000 professional or windows XP.
these OSs are JUST NOT BUILT FOR running applications like ORACLE database.
RomansAuthor Commented:
I'm not certain I am qualified to get into a Windows vs Linux debate. If you'd like to leave this open and continue the discussion I'll read your comments and learn. If not - let me know and I'll close this out.

Thnaks for all the comments.

Like I said, in actuality, there is NO point of arguing here.

Windows Server family product are great!!! but i kind of staying away from WIndows personal users products when you are hosting enterprise-level application like ORacle.

I think you should close this , assign points....

unless DJ has sth else to say?

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.