| | Find segments with corrupted block from alert log with ORA-01578
1. Get the list of corrupted blocks
grep 'ORA-01578' alert_DB.log | sort -u > tmp.txt
2. Generate SQL script to find segments with corrupted blocks
cat tmp.txt | awk '{ printf("%s %s\n", $8, $11); }' | \
awk -F\, '{ printf ("%s %s\n", $1, $2); }' | \
awk -F\) '{ printf("%s\n", $1); }' | ... | | More...
07/11/07 | | | | |
|
| | Rebuilding index partitions / subpartitions with corrupted block
This procedure assumes you had this error (corrupted block in index partition):
ORA-01578: ORACLE data block corrupted (file # 459, block # 15)
ORA-01110: data file 459: '/path/to/datafile.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
1. Find out which partition has corrupted block
select distinct partition_name, index_name
from dba_ind_subpartitions
where subpartition_name in
(
select partition_name
from dba_segments
where tablespace_name ... | |  
More...
11/17/05 | | | | |
|
| | RMAN reports corrupted blocks after migration
After database migration from 7.3.4 to 8.1.7 RMAN may report block corruption in number of segments (when using logical check in the backup script) but in fact there isn't corruption.
If index blocks are reported corrupted you can check it by running:
ANALYZE INDEX index_name VALIDATE STRUCTURE;
or if you cannot afford locking ... | | More...
10/28/05 | | | | |
|
| | Find segments with corrupted blocks
RMAN picks up logical block corruption when you use the following option in your backup script:
backup full check logical
The following procedure helps to identify segments with corrupted blocks if there are many occurrences of block corruption in the alert log file:
1. Get the list of all currupted blocks (replace alert_DB.log ... | |
More...
10/28/05 | | | | |
|
| | Find out which object has corrupted block
--
-- Find out the file id and block id, usually from alert.log
-- or from error message when running sql statement
--
-- Replace F with the file id and B with block id
--
SELECT SEGMENT_NAME, SEGMENT_TYPE, RELATIVE_FNO
FROM DBA_EXTENTS
WHERE FILE_ID = F
AND B BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1
/ | | 
More...
10/04/05 | | | | |
|
|
