Re: Corrupted datafile

An interesting contradiction in terms!!

    "We do not run in archivelogmode and cannot afford loss of data."

I suggest that if you cannot afford loss of data, you have an excellent argument to management to get the disk ( or tape ) that you need to run in archivelog mode.

That said, it appears that your corrupted block is in your users tablespace. Sometimes I have been able to recover from this type of error by selecting all rows into a new table, then deleting all rows and loading the old table from the new table. The corrupted data will sometimes be skipped and you can get the rest. You may also try an export/import to see if that will skip the bad data. You need to try and identify what row(s) are affected by the bad block and re-create that data if possible.

A full restore from the previous night may not fix your problem as the corrupted data could well be on that and several previous backups. Of course all updates since your last, good back up will be lost.

As another note, regular exports will verify the integrity of your database and help to catch these kinds of problems early. If this is mission critical production database, you must find a way to convince management to spring for the disk ( or tape ) to do archived re-do logs.

--
Jim Gregory
Principal Consultant for Keane, Inc.
Currently assigned to NCR
"Opinions are my own and do not reflect
  those of Keane or my clients"
<diaboliko_at_my-deja.com> wrote in message news:8qqbs6$leg$1_at_nnrp1.deja.com...


> Hi all;

>

> We have a problem on our oracle database. the following query produced

> an error (see below):

>

> select * from q_biauto_deb_suspwhere (substr(bi_data_record,63,6))

> ='028975'

>

> ERROR:

> ORA-01578: ORACLE data block corrupted (file # 5, block # 53047)

> ORA-01110: data file 5: '/db01/data/PROD/users_1.dbf'

>

> Can this be recovered without restoring the entire database? We back up

> nightly after shutting down the database. We do not run in

> archivelogmode and cannot afford loss of data.

>

> rgds

> Dean

>

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.