Corrupt block in database - error 1242

We experienced a corrupt block this morning.. Actually it started after an autoextend command by oracle failed.. The database file it is complaining about - music3.dbf was out of space..

What are my choices to fix this problem? I have the database up with this music3.dbf file in offline mode. Losing a little bit of data is no issue here.. Can I restore just music3.dbf from yesterdays backup, and will Oracle come up with that? I cannot afford to restore all database files from tape, because there are other log and purge tablespaces also that reside on this database. Any ideas?

Thanks!


Corrupt block dba: 0x1b000001 file=27. blocknum=1. found during data file header
read
on disk type:0. ver:0. dba: 0x00000000 inc:0x00000000 seq:0x00000000 incseq:0x00
000000
on disk type=0., expected type=11.

Entire contents of block is zero - block never written
Reread of block=1b000001 file=27. blocknum=1. found same corupted data
error 1242 detected in background process
OPIRIP: Uncaught error 447. Error stack:
ORA-00447: fatal error in background process
ORA-01242: data file suffered media failure: database in NOARCHIVELOG mode
ORA-01122: database file 27 failed verification check
ORA-01110: data file 27: '/log22_ora/MUSIC/music3.dbf'
ORA-01210: data file header is media corrupt

We are on Oracle 7.3.3, and platform is AIX 4.2.1, so RMAN is out of the question.

Thanks

Since your database is in NOARCHIVELOG mode, you'll have to recover from a cold backup (where the database was down when the data files were backed up).

Ken,

We have a cold backup from last night. My question is - can I restore only the music3.dbf file, and will that be sufficient to bring up the database? or do I need to restore all the dbf files for all 3 tablespaces - music, log, and purge in order to bring this database up?

Thanks

And before restoring do perform a check disk just to make sure that part of disk has not gone bad.

Amar,

I restored just the music3.dbf file from tape, and after starting database, it brings up some processes, but gives 2 oracle errors 01113 and 01110.. and says I need to do a media recovery for that file.. Is it as simple as
startup mount;
alter database datafile 'xxx' offline;
alter database open;
alter database recover datafile 'xxx';
alter database datafile 'xxx' online;

Remember I dont have archivelog turned on..
My knowledge of oracle is limited.. so parts of my question may not make sense.. sorry..

Obviously the recover datafile did not work.. I tried that.. The only thing left for me to do was to
a) make a backup.. (to cover your back).
b) startup database and take datafile offline drop (just offline didnt work).
c) Export data from other tables
d Restore all database files from yesterday evening backup.
e) Add the exported data back..
f) process the transactions to catch the music3.dbf file back upto date..

Lesson learned: RMAN is a wonderful tool to have.. In the meantime.. make sure archivelog is turned ON.

Unless someone has other novel ideas to add to this, this is a shut case..

Thanks for your inputs..

Sorry for the delay...

Yes, you have to use all the database files as they were at the time of the cold backup.

.dbf's, .ctl's and rdo's

Your initSID.ora is fine if you didn't make any changes in it from the time of the cold backup and now.

The export/import idea is a good one.

Better yet, put your database in ARCHIVELOGMODE and do hot backups. Then you'll have faster recovery, point-in-time recovery and alot more options when you lose a single file.