Error:
Corrupt block relative dba: 0x1a0975d9 (file 104, block 619993)
Bad check value found during user buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x1a0975d9
last change scn: 0x0726.9a3a3fc5 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x3fc50601
check value in block header: 0x287e
computed block checksum: 0xfe81
Reread of rdba: 0x1a0975d9 (file 104, block 619993) found same corrupted data
Tue Feb 18 20:17:59 IST 2014
Corrupt Block Found
TSN = 47, TSNAME = EVENT_TAB
RFN = 104, BLK = 619993, RDBA = 436827609
OBJN = 141322, OBJD = 141322, OBJECT = EVENT_PK, SUBOBJECT =
SEGMENT OWNER = EVA_ADMIN, SEGMENT TYPE = Index Segment
Background:
Oracle 10g database sizing over 1 TB with no RMAN backup.
Backup being taken using legacy hot backup image copy to disk/tape.
Block being corrupted belongs to IOT index wherein data resides itself inside index sizing over 500 GB.
Alert log has provided enough information about corruption like tablespace_name , object_name , object_id , object_type , corrupted block number and more to initiate corruption recovery plan.
Verification:
Important step for any type of corruption in database and should include integrity check for complete database to identify if corruption list includes any more blocks other than that mentioned in alert log
1. Identify possible objects impacted due to corruption based on corrupted block id, 619993 is the corrupted block id appeared in view or alert log as BLK = 619993
SELECT e.file_id,
e.block_id,
e.owner,
e.segment_name,
e.segment_type
FROM dba_extents e
WHERE
file_id=104 and 619993 BETWEEN 619993 AND 619993 + blocks - 1
/
2. Perform logical database / datafile integrity check which will populate v$database_block_corruption
RMAN> run {backup validate datafile 104;}
Starting backup at 19-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2190 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00104 name=/data02/EVACP/event_tab_90.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 19-FEB-14
RMAN>
Get the progress of rman validate command using below ,
select MBYTES_PROCESSED,STATUS,OPERATION from V_$RMAN_STATUS where OPERATION like '%VALIDATE%';
MBYTES_PROCESSED STATUS OPERATION
---------------- ----------------------- ---------------------------------
9216 COMPLETED BACKUP VALIDATE
Similarly , complete database integrity can also be validated using below rman command.
backup validate check logical database ;
3. Corrupt block detected from RMAN validate integrity check will get populated in v$database_block_corruption as well as in alert log
select * from v$database_block_corruption ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
104 619993 1 0 CHECKSUM
select file#, block# as blk#, corruption_type as type from v$database_block_corruption;
FILE# BLK# TYPE
---------- ---------- ---------
104 619993 CHECKSUM
4. Offline datafile physical corruption check
dbv FILE=/corp/data02/EVACP/event_tab_90.dbf FEEDBACK=100
Total Pages Processed (Data) : 134144
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 536576
Total Pages Failing (Index): 0
Total Pages Processed (Other): 8
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 508920
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 378599356 (1831.378599356)
Resolution:
Corruption fixes plan depends on type of the recent DB backup being taken before first corruption incident time.
Here , db backup image is a part of hot backup & not RMAN backupset
1. Restore all the archivelog files since end backup time to a staging location (/tempfs) or in the same location of current archivelog destination
2. Restore backup of corrupted datafile id 104 to staging location (/tempfs)
3. Catalog archivelog restored to make control file aware of their existence.
RMAN> catalog archivelog '/tempfs/1_54763_668965414.arc';
cataloged archive log
archive log filename=/tempfs/1_54763_668965414.arc recid=70182 stamp=839895943
Make sure all archivelog files are restored since end backup & cataloged to avoid below error during block media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 02/19/2014 00:30:13
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 54769 lowscn 7864522937984 found to restore
4. Catalog backup datafile restored to make control file aware of their existence.
RMAN> catalog datafilecopy '/tempfs/event_tab_90.dbf';
cataloged datafile copy
datafile copy filename=/tempfs/event_tab_90.dbf recid=22055 stamp=839895977
5. Re-verify the current datafile to get corrupted block list
RMAN> run {backup validate datafile 104;}
Starting backup at 19-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2190 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00104 name=/data02/EVACP/event_tab_90.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 19-FEB-14
RMAN>
6. perform block recovery
RMAN> RUN
{
BLOCKRECOVER DATAFILE 104 BLOCK 619993
FROM DATAFILECOPY;
}2> 3> 4> 5>
Starting blockrecover at 19-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /tempfs/event_tab_90.dbf
starting media recovery
archive log thread 1 sequence 54744 is already on disk as file /tempfs/1_54744_668965414.arc
archive log thread 1 sequence 54745 is already on disk as file /tempfs/1_54745_668965414.arc
archive log thread 1 sequence 54746 is already on disk as file /tempfs/1_54746_668965414.arc
archive log thread 1 sequence 54747 is already on disk as file /tempfs/1_54747_668965414.arc
archive log thread 1 sequence 54748 is already on disk as file /tempfs/1_54748_668965414.arc
archive log thread 1 sequence 54749 is already on disk as file /tempfs/1_54749_668965414.arc
archive log thread 1 sequence 54750 is already on disk as file /tempfs/1_54750_668965414.arc
archive log thread 1 sequence 54751 is already on disk as file /tempfs/1_54751_668965414.arc
archive log thread 1 sequence 54752 is already on disk as file /tempfs/1_54752_668965414.arc
archive log thread 1 sequence 54753 is already on disk as file /tempfs/1_54753_668965414.arc
archive log thread 1 sequence 54754 is already on disk as file /tempfs/1_54754_668965414.arc
archive log thread 1 sequence 54755 is already on disk as file /tempfs/1_54755_668965414.arc
archive log thread 1 sequence 54756 is already on disk as file /tempfs/1_54756_668965414.arc
archive log thread 1 sequence 54757 is already on disk as file /tempfs/1_54757_668965414.arc
archive log thread 1 sequence 54758 is already on disk as file /tempfs/1_54758_668965414.arc
archive log thread 1 sequence 54759 is already on disk as file /tempfs/1_54759_668965414.arc
archive log thread 1 sequence 54760 is already on disk as file /tempfs/1_54760_668965414.arc
archive log thread 1 sequence 54761 is already on disk as file /tempfs/1_54761_668965414.arc
archive log thread 1 sequence 54762 is already on disk as file /tempfs/1_54762_668965414.arc
archive log thread 1 sequence 54763 is already on disk as file /tempfs/1_54763_668965414.arc
archive log thread 1 sequence 54764 is already on disk as file /tempfs/1_54764_668965414.arc
archive log thread 1 sequence 54765 is already on disk as file /tempfs/1_54765_668965414.arc
archive log thread 1 sequence 54766 is already on disk as file /tempfs/1_54766_668965414.arc
archive log thread 1 sequence 54767 is already on disk as file /tempfs/1_54767_668965414.arc
archive log thread 1 sequence 54768 is already on disk as file /tempfs/1_54768_668965414.arc
archive log thread 1 sequence 54769 is already on disk as file /tempfs/1_54769_668965414.arc
media recovery complete, elapsed time: 00:04:06
Finished blockrecover at 19-FEB-14
RMAN>
7. Verify the corrupted block status by re-running the verification step 2 & 3. Note immediately querying v$database_block_corruption post block media recovery will reflect the old corrupt block.integrity check needs to be performed again to refresh the corruption view.
SQL> select file#, block# as blk#, corruption_type as type from v$database_block_corruption;
no rows selected
Corrupt block relative dba: 0x1a0975d9 (file 104, block 619993)
Bad check value found during user buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x1a0975d9
last change scn: 0x0726.9a3a3fc5 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x3fc50601
check value in block header: 0x287e
computed block checksum: 0xfe81
Reread of rdba: 0x1a0975d9 (file 104, block 619993) found same corrupted data
Tue Feb 18 20:17:59 IST 2014
Corrupt Block Found
TSN = 47, TSNAME = EVENT_TAB
RFN = 104, BLK = 619993, RDBA = 436827609
OBJN = 141322, OBJD = 141322, OBJECT = EVENT_PK, SUBOBJECT =
SEGMENT OWNER = EVA_ADMIN, SEGMENT TYPE = Index Segment
Background:
Oracle 10g database sizing over 1 TB with no RMAN backup.
Backup being taken using legacy hot backup image copy to disk/tape.
Block being corrupted belongs to IOT index wherein data resides itself inside index sizing over 500 GB.
Alert log has provided enough information about corruption like tablespace_name , object_name , object_id , object_type , corrupted block number and more to initiate corruption recovery plan.
Verification:
Important step for any type of corruption in database and should include integrity check for complete database to identify if corruption list includes any more blocks other than that mentioned in alert log
1. Identify possible objects impacted due to corruption based on corrupted block id, 619993 is the corrupted block id appeared in view or alert log as BLK = 619993
SELECT e.file_id,
e.block_id,
e.owner,
e.segment_name,
e.segment_type
FROM dba_extents e
WHERE
file_id=104 and 619993 BETWEEN 619993 AND 619993 + blocks - 1
/
2. Perform logical database / datafile integrity check which will populate v$database_block_corruption
RMAN> run {backup validate datafile 104;}
Starting backup at 19-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2190 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00104 name=/data02/EVACP/event_tab_90.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 19-FEB-14
RMAN>
Get the progress of rman validate command using below ,
select MBYTES_PROCESSED,STATUS,OPERATION from V_$RMAN_STATUS where OPERATION like '%VALIDATE%';
MBYTES_PROCESSED STATUS OPERATION
---------------- ----------------------- ---------------------------------
9216 COMPLETED BACKUP VALIDATE
Similarly , complete database integrity can also be validated using below rman command.
backup validate check logical database ;
3. Corrupt block detected from RMAN validate integrity check will get populated in v$database_block_corruption as well as in alert log
select * from v$database_block_corruption ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
104 619993 1 0 CHECKSUM
select file#, block# as blk#, corruption_type as type from v$database_block_corruption;
FILE# BLK# TYPE
---------- ---------- ---------
104 619993 CHECKSUM
4. Offline datafile physical corruption check
dbv FILE=/corp/data02/EVACP/event_tab_90.dbf FEEDBACK=100
DBVERIFY - Verification complete
Total Pages Examined
: 1179648 Total Pages Processed (Data) : 134144
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 536576
Total Pages Failing (Index): 0
Total Pages Processed (Other): 8
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 508920
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 378599356 (1831.378599356)
Resolution:
Corruption fixes plan depends on type of the recent DB backup being taken before first corruption incident time.
Here , db backup image is a part of hot backup & not RMAN backupset
1. Restore all the archivelog files since end backup time to a staging location (/tempfs) or in the same location of current archivelog destination
2. Restore backup of corrupted datafile id 104 to staging location (/tempfs)
3. Catalog archivelog restored to make control file aware of their existence.
RMAN> catalog archivelog '/tempfs/1_54763_668965414.arc';
cataloged archive log
archive log filename=/tempfs/1_54763_668965414.arc recid=70182 stamp=839895943
Make sure all archivelog files are restored since end backup & cataloged to avoid below error during block media recovery
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of blockrecover command at 02/19/2014 00:30:13
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 54769 lowscn 7864522937984 found to restore
4. Catalog backup datafile restored to make control file aware of their existence.
RMAN> catalog datafilecopy '/tempfs/event_tab_90.dbf';
cataloged datafile copy
datafile copy filename=/tempfs/event_tab_90.dbf recid=22055 stamp=839895977
5. Re-verify the current datafile to get corrupted block list
RMAN> run {backup validate datafile 104;}
Starting backup at 19-FEB-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=2190 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00104 name=/data02/EVACP/event_tab_90.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 19-FEB-14
RMAN>
6. perform block recovery
RMAN> RUN
{
BLOCKRECOVER DATAFILE 104 BLOCK 619993
FROM DATAFILECOPY;
}2> 3> 4> 5>
Starting blockrecover at 19-FEB-14
using channel ORA_DISK_1
channel ORA_DISK_1: restoring block(s) from datafile copy /tempfs/event_tab_90.dbf
starting media recovery
archive log thread 1 sequence 54744 is already on disk as file /tempfs/1_54744_668965414.arc
archive log thread 1 sequence 54745 is already on disk as file /tempfs/1_54745_668965414.arc
archive log thread 1 sequence 54746 is already on disk as file /tempfs/1_54746_668965414.arc
archive log thread 1 sequence 54747 is already on disk as file /tempfs/1_54747_668965414.arc
archive log thread 1 sequence 54748 is already on disk as file /tempfs/1_54748_668965414.arc
archive log thread 1 sequence 54749 is already on disk as file /tempfs/1_54749_668965414.arc
archive log thread 1 sequence 54750 is already on disk as file /tempfs/1_54750_668965414.arc
archive log thread 1 sequence 54751 is already on disk as file /tempfs/1_54751_668965414.arc
archive log thread 1 sequence 54752 is already on disk as file /tempfs/1_54752_668965414.arc
archive log thread 1 sequence 54753 is already on disk as file /tempfs/1_54753_668965414.arc
archive log thread 1 sequence 54754 is already on disk as file /tempfs/1_54754_668965414.arc
archive log thread 1 sequence 54755 is already on disk as file /tempfs/1_54755_668965414.arc
archive log thread 1 sequence 54756 is already on disk as file /tempfs/1_54756_668965414.arc
archive log thread 1 sequence 54757 is already on disk as file /tempfs/1_54757_668965414.arc
archive log thread 1 sequence 54758 is already on disk as file /tempfs/1_54758_668965414.arc
archive log thread 1 sequence 54759 is already on disk as file /tempfs/1_54759_668965414.arc
archive log thread 1 sequence 54760 is already on disk as file /tempfs/1_54760_668965414.arc
archive log thread 1 sequence 54761 is already on disk as file /tempfs/1_54761_668965414.arc
archive log thread 1 sequence 54762 is already on disk as file /tempfs/1_54762_668965414.arc
archive log thread 1 sequence 54763 is already on disk as file /tempfs/1_54763_668965414.arc
archive log thread 1 sequence 54764 is already on disk as file /tempfs/1_54764_668965414.arc
archive log thread 1 sequence 54765 is already on disk as file /tempfs/1_54765_668965414.arc
archive log thread 1 sequence 54766 is already on disk as file /tempfs/1_54766_668965414.arc
archive log thread 1 sequence 54767 is already on disk as file /tempfs/1_54767_668965414.arc
archive log thread 1 sequence 54768 is already on disk as file /tempfs/1_54768_668965414.arc
archive log thread 1 sequence 54769 is already on disk as file /tempfs/1_54769_668965414.arc
media recovery complete, elapsed time: 00:04:06
Finished blockrecover at 19-FEB-14
RMAN>
7. Verify the corrupted block status by re-running the verification step 2 & 3. Note immediately querying v$database_block_corruption post block media recovery will reflect the old corrupt block.integrity check needs to be performed again to refresh the corruption view.
SQL> select file#, block# as blk#, corruption_type as type from v$database_block_corruption;
no rows selected
perfect..
ReplyDelete