Solved

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

Posted on 2003-11-03
16
11,443 Views
Last Modified: 2012-05-04
Experts,
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.

Thanks
Romans
0
Comment
Question by:Romans
  • 5
  • 5
  • 5
  • +1
16 Comments
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
You have a corrupted block on data file2.

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

0
 
LVL 5

Accepted Solution

by:
DrJekyll earned 250 total points
Comment Utility
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
1. SHUTDOWN IMMEDIATE;
2. STARTUP MOUNT;
3. RECOVER DATABASE;
4. ALTER DATABASE OPEN;

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.

DJ
0
 

Author Comment

by:Romans
Comment Utility
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 ... ?

Romans
0
 
LVL 5

Expert Comment

by:DrJekyll
Comment Utility
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.
0
 

Author Comment

by:Romans
Comment Utility
DrJekyll,

-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)

Romans
0
 
LVL 2

Expert Comment

by:Kong
Comment Utility
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.

0
 
LVL 5

Expert Comment

by:DrJekyll
Comment Utility
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!!!

DJ
0
 

Author Comment

by:Romans
Comment Utility
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 ...

Romans



0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
Romans,
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.


0
 
LVL 5

Expert Comment

by:DrJekyll
Comment Utility
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.

DJ
0
 

Author Comment

by:Romans
Comment Utility
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 ...

Romans


0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:DrJekyll
Comment Utility
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.
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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.
0
 

Author Comment

by:Romans
Comment Utility
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.

Romans
0
 
LVL 23

Expert Comment

by:seazodiac
Comment Utility
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?


0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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 explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now