ORACLE data block corrupted

[Former Member]

Hello,

System : Windows/4.6c/oracle 9i

Observed from Short dumps and System logs :

ORA-01578: ORACLE data block corrupted (file # 4, block #117760)#ORA-01110: data file 4:

'H:\ORACLE\AB5\SAPDATA1\STABI_1\STABI.DATA1'#

So we Executed Verification of database blocks in Offline mode to verify if any other part of database is corrupted .

DBVERIFY detected corrupted blocks in TWO locations :

1. H:\ORACLE\AB5\SAPDATA2\BTABD_8\BTABD.DATA8

2. H:\ORACLE\AB5\SAPDATA1\STABI_1\STABI.DATA1

Currently system is up and running .Can someone suggest how to overcome those corruptions ?

We tried to restore those corrrupted files from earlier backup but Database could not be started ,asking for resotoration of other files of database . So we had to revert back .

Do we have only one option to restore complete database with earlier backup ? Any other options ??

Regards

ganesh

[Former Member]

Issue resolved,

[Former Member]

Please suggest how to peform DBVerification for backup to make sure this backup is consistent and can be used to restore for corrupted data blocks . I could find some information in Notes 365481 ,23345 but not sure

Thanks a lot everyone ..

[Former Member]

To run DBV on a restored copy of the file:

1) Restore the file from the backup to a non-database directory as explained in note 365481

2) Run dbverify:

dbv file=<file> blocksize=8192

If everything is OK then output will look something like

DBVERIFY - Verification complete

Total Pages Examined         : 64000
Total Pages Processed (Data) : 10835
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 10136
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4460
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 38569
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 95369225 (0.95369225)

Good luck,

Mark

[Former Member]

Thanks Mark ..I have tested the files and backup files look good .The note 23345 can be handy to confirm Oracle Database consistency Check.

[Former Member]

Hi,

We need som more info on what happen before blocks got corrupted.

When a segment is defined with the NOLOGGING attribute and if a NOLOGGING/UNRECOVERABLE operation updates the segment, the online redo log file is updated with minimal information to invalidate the affected blocks when a RECOVERY is later performed.

If the associated redo/archived log file is used to RECOVER the data files, Oracle invalidates such blocks and the error ORA-26040 along with error ORA-1578 are reported by SQL statements in the next block reads.

Pls chk the s-note 365481,923919,849485 which has detailed information of corrupt blocks.

Regards,

Sitarama

[fidel_vales]

>

> We tried to restore those corrrupted files from earlier backup but Database could not be started ,asking for resotoration of other files of database . So we had to revert back .

Would you mind to explain EXACTLY how you tried to recover the mentioned "restored" files.

Restoring files alone is not enough.

You can restore the files into a different location and check with DBV if the corruption is there.

Note that, in most of the cases, the corruptions are caused by hardware issues, you should also check your hardware.

[Former Member]

Here is the process i have followed for restoration ..

1. As i know what data files are corrupted , so i had picked the same files from previous backups and replaced ...offcourse i have done when database is stopped .As the database is set to NOARCHIVE log mode , i believe archive files are not necessary.

2.Then i started database to MOUNT state , the executed RECOVER DATABASE UNTIL CANCEL.

3.Now it started asking for other sequence of files ..to restore . So i thought these process would contiune asking for each every other files for restoration..

3.I had to revert back the Old files and started the database. first to mount state then alter database open noresetlogs.

Please suggest ..

[Former Member]

Hi,

Your attempt at restore/recovery did not succeed because the database is in NOARCHIVELOG mode. When you do a selective restore of just 2 datafiles, then the purpose of the recovery is to bring those datafiles back to the same point in time as the other datafiles, which you didn't restore. Recovery is only possible using archive logs. A NOARCHIVELOG database can only be restored as a whole and in doing so reverts to the state it was in at the time of the backup; all changes made after the backup are lost.

You should first restore the two affected files to a separate location and run DBV on them, as Fidel Vales explained. If the corruption is not there, then you'll have to restore the entire database.

Rgds,

Mark

[fidel_vales]

Hi,

Very nice explanation from Mark.

In addition you should know that SAP does NOT "support" Production Database in NOARCHIVELOG mode.

There is no real reason to run a production DB in such mode, the drawbacks are too big.

It mak be possible to perform very specific operations in such mode, but a good BACKUP is required before and after the operation.

[anindya_bose]

Ganesh

Have a look at

https://service.sap.com/sap/support/notes/365481

>We tried to restore those corrrupted files from earlier backup but Database could not be started ,asking for resotoration of >other files of database . So we had to revert back

I think restoration of individual datafile is possible. Commad to restore individual datafile is given below. Here I am using utl file and restoring datafile from tape backup.

brrestore -p initSID.sap -b <backupid.anf> -d util_file -r %ORACLE_HOME%\database\initSID.utl -m 1 -l E

But, I have one question. Are you sure this corruption is not there in the backup?

Best Regards

Anindya

[Former Member]

Hi Ganesh,

Please send the logs from sm21 and also check the sap note number 3542932 and 99962. This note will help to solve this issue.

Regards,

Jitendra

[Former Member]

Looks like SAP Note '0003542932' does not exist in Service market place...