Re: Block Corruption, cant start DB -SOS

A couple of questions....

Any reason the thing is trying to do an instance recovery? Presumably you had an instance failure, or have been doing shutdown aborts??

I take it you are using 8.1.7??

And the problem appears to be on file 111, which I take it isn't the SYSTEM datafile??

Ordinarily, when you have media failure like this, you simply restore the backup of file 111 you took a night or so ago, and issue the command 'recover datafile 111' -at which point, all the redo contained in the archives and online logs since the time of the backup of that file will be applied automatically.

So, the next obvious question is: why haven't you done this, and is it because (a) you have no backups and (b) you're not running in archivelog mode??

In which case, you're looking at some data loss. A couple of approaches to try: since this is file 111, and since we are assuming that this is not part of the SYSTEM tablespace, you could at least get the rest of your database around the problem, and try and sort it out once that's done. To do that, at MOUNT stage, you issue the command 'alter database datafile 111 offline'. Then you can alter database open (assuming no other problems then rear their ugly heads).

How you then deal with the problem depends on all sorts of factors, but in the absence of backups and/or archives, I'd be looking at using the dbms_repair package. That allows you to mark individual blocks as corrupted, and thus skippable by full table scans. You'll lose whatever data is in that block, but since there seem to be quite a few of them in that datafile, you'll probably prefer to do that than lose the lot.

Also: talk to Oracle Support. They have tools that might help in this situation.

And if you can, take a complete backup of the entire database as it is currently, before attempting anything. Everything you do to fix this problem will modify things, and potentially modify them in such a way that the entire database turns into a complete pile of poo. With a precautionary backup, at least you can get back to square 1.

Regards
HJR "SGA" <zeevikm_at_hotmail.com> wrote in message news:b0fb5246.0209180049.5f335a3f_at_posting.google.com...
> Hello All,
> This is what I get in alert log when trying to start DB.
> If anyone had similar problem before and have any idea how to startup
> the db anyway please help!
>
> Thanks alot.
>
> ---------------------------------------------
> Alert log errors:
> ---------------------------------------------
>
> Tue Sep 17 16:51:25 2002
> ALTER DATABASE OPEN
> Beginning crash recovery of 1 threads
> Tue Sep 17 16:51:27 2002
> Thread recovery: start rolling forward thread 1
> Recovery of Online Redo Log: Thread 1 Group 2 Seq 112952 Reading mem 0
> Mem# 0 errs 0: /db/dwhs/data2/redo_raw/redo_g2_1.log
> Mem# 1 errs 0: /db/dwhs/data2/redo_raw/redo_g2_2.log
> ***
> Corrupt block relative dba: 0x1bcecb04 (file 111, block 969476)
> Bad header found during preparing block for write
> Data in bad block -
> type: 112 format: 114 rdba: 0x20646973
> last change scn: 0x0003.1a484def seq: 0x5 flg: 0x70
> consistency value in tail: 0x4def7005
> check value in block header: 0x0, block checksum disabled
> spare1: 0x6f, spare2: 0x64, spare3: 0x7465
> ***
> Tue Sep 17 16:53:03 2002
> Errors in file /local/oracle/product/admin/DWHS/bdump/dwhs_dbw1_13174.trc:
> ORA-00600: internal error code, arguments: [kcbzpb_1], [466537220],
> [4], [0], [], [], [], []
> Tue Sep 17 16:53:06 2002
> Errors in file /local/oracle/product/admin/DWHS/bdump/dwhs_dbw1_13174.trc:
> ORA-00600: internal error code, arguments: [kcbzpb_1], [466537220],
> [4], [0], [], [], [], []
> DBW1: terminating instance due to error 600
> Instance terminated by DBW1, pid = 13174
> Tue Sep 17 16:55:36 2002
>
>
> ---------------------------------------------
> DBV output on suspected dbf:
> ---------------------------------------------
>
> DBVERIFY: Release 8.1.7.3.0 - Production on Tue Sep 17 20:31:53 2002
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> DBVERIFY - Verification starting : FILE =
> /db/dwhs/data2/DWHS_USA_31_1.dbf
> Block Checking: DBA = 466537220, Block Type = KTB-managed data block
> kdbchk: row locked by non-existent transaction
> table=0 slot=3
> lockid=32 ktbbhitc=1
> Page 969476 failed with check code 6101
>
> DBVERIFY - Verification complete
>
> Total Pages Examined : 1280000
> Total Pages Processed (Data) : 1264148
> Total Pages Failing (Data) : 1
> Total Pages Processed (Index): 15369
> Total Pages Failing (Index): 0
> Total Pages Processed (Other): 323
> Total Pages Empty : 160
> Total Pages Marked Corrupt : 0
> Total Pages Influx : 0
Received on Thu Sep 19 2002 - 18:43:31 CDT