Добрый день
После аварийного отключения сервера ( по питанию ) в БД появились
ORA-01578: ORACLE data block corrupted (file # 24, block # 208009) ,
я их пометил :
dbv file=/oracle/DEV/sapdata1/dev_10/dev.data10 blocksize=8192
вопрос как их теперь востановить ? Я не слишком большой спец по Oracle , если возможно подскажите как с этим справиться .

Вот кусок alert_log :
ARC0: Evaluating archive log 3 thread 1 sequence 12395
ARC0: Beginning to archive log 3 thread 1 sequence 12395
Creating archive destination LOG_ARCHIVE_DEST_1: '/oracle/DEV/oraarch/DEVarch/1_
12395.dbf'
ARC0: Completed archiving log 3 thread 1 sequence 12395
***
Corrupt block relative dba: 0x03410319 (file 13, block 66329)
Bad header found during buffer read
Data in bad block -
type: 32 format: 2 rdba: 0x01c3d0c1
last change scn: 0x0000.060ba024 seq: 0x3 flg: 0x04
consistency value in tail: 0xa0242003
check value in block header: 0x3512, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x03410319 (file 13, block 66329) found same corrupted data
***
Corrupt block relative dba: 0x06032c89 (file 24, block 208009)
Bad header found during buffer read
Data in bad block -
type: 11 format: 2 rdba: 0x05400001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
consistency value in tail: 0x00000b01
check value in block header: 0x946, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of rdba: 0x06032c89 (file 24, block 208009) found same corrupted data
Wed Feb 20 03:00:24 2008
Errors in file /oracle/DEV/920_64/rdbms/log/dev_ora_25986.trc:
ORA-01578: ORACLE data block corrupted (file # 24, block # 208009)
ORA-01110: data file 24: '/oracle/DEV/sapdata1/dev_10/dev.data10'
ORA-01578: ORACLE data block corrupted (file # 24, block # 208009)
ORA-01110: data file 24: '/oracle/DEV/sapdata1/dev_10/dev.data10'
Wed Feb 20 03:00:27 2008
Errors in file /oracle/DEV/920_64/rdbms/log/dev_ora_26234.trc:
ORA-01578: ORACLE data block corrupted (file # 24, block # 208009)
ORA-01110: data file 24: '/oracle/DEV/sapdata1/dev_10/dev.data10'
ORA-01578: ORACLE data block corrupted (file # 24, block # 208009)
ORA-01110: data file 24: '/oracle/DEV/sapdata1/dev_10/dev.data10'
Wed Feb 20 03:36:24 2008
Errors in file /oracle/DEV/920_64/rdbms/log/dev_ora_26237.trc:
ORA-01578: ORACLE data block corrupted (file # 24, block # 208009)
ORA-01110: data file 24: '/oracle/DEV/sapdata1/dev_10/dev.data10'
ORA-01578: ORACLE data block corrupted (file # 24, block # 208009)
ORA-01110: data file 24: '/oracle/DEV/sapdata1/dev_10/dev.data10'
Wed Feb 20 05:00:25 2008
Completed checkpoint up to RBA [0x306c.2.10], SCN: 0x0000.042284a8

ОС - HP-UX , БД - Oracle 9

последняя проверка была успешной 6.02

Нота 365481 поможет идентифицировать объект, который принадлежит поврежденному блоку. Дальнейшие действия будут зависеть от типа объекта (таблица, индекс, сегмент отката...).

Пометил блоки чтобы не использовались

dev:oradev 6> dbv file=/oracle/DEV/sapdata1/dev_10/dev.data10 blocksize=8192

DBVERIFY: Release 9.2.0.7.0 - Production on Tue Feb 19 13:33:25 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/DEV/sapdata1/dev_10/dev.data10
Page 208009 is marked corrupt
***
Corrupt block relative dba: 0x06032c89 (file 24, block 208009)
Bad header found during dbv:
Data in bad block -
type: 11 format: 2 rdba: 0x05400001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
consistency value in tail: 0x00000b01
check value in block header: 0x946, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 208257 is marked corrupt
***
Corrupt block relative dba: 0x06032d81 (file 24, block 208257)
Bad header found during dbv:
Data in bad block -
type: 11 format: 2 rdba: 0x03c00001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
consistency value in tail: 0x00000b01
check value in block header: 0x51b, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 361580 is marked corrupt
***
Corrupt block relative dba: 0x0605846c (file 24, block 361580)
Bad header found during dbv:
Data in bad block -
type: 6 format: 2 rdba: 0x03c256ec
last change scn: 0x0000.060ba1b5 seq: 0x1 flg: 0x06
consistency value in tail: 0xa1b50601
check value in block header: 0x3fc6, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

показывает таблицы и индексы которые надо востановить или перестроить , вот кусок

SQL> SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS
2 WHERE FILE_ID = 24 AND 208009 BETWEEN BLOCK_ID AND (BLOCK_ID+BLOCKS -1);

SEGMENT_TYPE
------------------
OWNER||'.'||SEGMENT_NAME
--------------------------------------------------------------------------------
TABLE
SAPDEV.MONI


SQL> SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS
2 WHERE FILE_ID = 24 AND 208257 BETWEEN BLOCK_ID AND (BLOCK_ID+BLOCKS -1);

SEGMENT_TYPE
------------------
OWNER||'.'||SEGMENT_NAME
--------------------------------------------------------------------------------
INDEX
SAPDEV.DBTABLOG~0
¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬

SQL> SELECT SEGMENT_TYPE,OWNER||'.'||SEGMENT_NAME FROM DBA_EXTENTS
2 WHERE FILE_ID = 24 AND 361580 BETWEEN BLOCK_ID AND (BLOCK_ID+BLOCKS -1);

SEGMENT_TYPE
------------------
OWNER||'.'||SEGMENT_NAME
--------------------------------------------------------------------------------
TABLE
SAPDEV.VBPA

Подскажите как проще это сделать ( не очень силён в Oracle )

С индексом SAPDEV.DBTABLOG~0 проще - удалить и пересоздать.
А вот по таблицам нужно будет прогнать 'аnalyze table ... validate structure cascade online;' и с результатом проверки к пункту 7 ноты 365481.
Удачи.

Часть таблиц проходит нормально , запускаю alter
но когда потом запускаю DBVERIFY блок всё равно в файле Corrupt

SQL> analyze table sapdev.moni validate structure cascade online;

Table analyzed.

SQL> alter table sapdev.moni deallocate unused;

Table altered.



dbv file=/oracle/DEV/sapdata1/dev_10/dev.data10 blocksize=8192

DBVERIFY: Release 9.2.0.7.0 - Production on Tue Feb 26 17:07:27 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/DEV/sapdata1/dev_10/dev.data10
Page 208009 is marked corrupt
***
Corrupt block relative dba: 0x06032c89 (file 24, block 208009)
Bad header found during dbv:
Data in bad block -
type: 11 format: 2 rdba: 0x05400001
last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
consistency value in tail: 0x00000b01
check value in block header: 0x946, computed block checksum: 0x0
spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Часть показывает при анализе с ошибками

SQL> analyze table sapdev.vbpa validate structure cascade online;
analyze table sapdev.vbpa validate structure cascade online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 24, block # 361580)
ORA-01110: data file 24: '/oracle/DEV/sapdata1/dev_10/dev.data10'

не совсем разобрался что прописать из пункта 7 ноты 365481.

a) dbverify without errors/analyze without errors
You probably do not have a corruption. Repeat the access that indicated the corruption. If this is successful this time AND if you can read the table using a
select * from sapr3."<TABLE>";
AND if none of the procedures described in Note 23345 reports a problem, it is very likely that a temporary corruption existed in the memory but not on the hard disk. The block (which was only corrupt in the main memory) was not reimported to the hard disk because it was not changed. Always check your hardware in this case. Note 786645 contains additional reasons for temporary corruption.
b) dbverify with errors/analyze without errors
The corruption is probably located in a block of the table that has never contained data. Execute the following command:
alter table <tablename> deallocate unused;
The corruption should then appear in the free space and should therefore be deleted as described there.
Furthermore, in tables with LOB columns, the corruption may be located in the lob segment. The analysis is not capable of recognizing this type of corruption. In this case, proceed as described under "dbverify with errors/analyze with errors".
c) dbverify with errors/analyze with errors
Save the corrupt data file and transfer a backup of this file from a backup that does not contain the corruption. Recover this file up to the current time.
If you do not have a backup that you can definitely say does not contain the corruption, you can restore a file from a backup that is still available in A DIRECTORY THAT DOES NOT BELONG TO THE DB and check this backup using dbv. If a corruption is not reported, you have subsequently verified your backup because, on the one hand, dbverify can recognize the corruption and, on the other, your backup does not have any corruptions.
To import a file back into another directory using brrestore, use the following command:

Abstract:
brrestore -b <log> -m <number of file>=<Restore Directory>

For example:
brrestore -b bddcbjcq.afd -m 59=c:\temp
d) dbverify without errors/analyze with errors
This case differs from the previous case in that dbverify does not recognize an existing corruption that an analysis finds. If you do not have a backup that you can definitely say does not contain the corruption, you can check this easily at a later stage.
After you have made a backup of the corrupt file, you can reimport and recover the backup of the file without checking it and hope that the corruption was not yet contained in the backup OR
you can create a temporary check system using a (partial) DB copy from this backup (consisting of the rollback, system and tablespace that contains the corruption) and analyze the table on this check system. If this is possible without errors, in retrospect you have verified your backup and you can restore and recover this.