Thursday, November 11, 2010

ORA-01172:recovery of thread 1 stuck at block xxxx of file x

Lately, I've been plagued with all sorts of Oracle start up errors on my Vista box. I have Oracle 11gR2 installed, and it seems to crash everytime I force reboot my vista box

Yesterday I got,

ORA-01172:recovery of thread 1 stuck at block xxxx of file x

To solve

1) Open SqlPlus from Oracle Bin folder, login as sys/zzz as sysdba
2) Since the database is not open, you can only query on fixed tables/Views
3) Query on v$datafile for the file name in the error message

If the error is "ORA-01172:recovery of thread 1 stuck at block 1241 of file 3",

then Select name from v$datafile where file#=3

4) Recover datafile name_from_the_above_select
5) If file is recovered, then try
Alter database open

6) In my case, I got some weird ora-6000 [4194], So I did a shutdown and startup force, which resovled my issue

7) Shutdown immediate
8) Startup force

3 comments: