corrupt block引起的错误ORA-01498原因分析

执行analyze table tt1 validate structure;分析表上的block corrupt时出现了错误ORA-01498困扰了一段时间了，今天仔细分析了一下作了一点总结.

[@more@]

RMAN> BACKUP DATAFILE 4;

Starting backup at 2011-07-24 21:36:08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=G:ORADATATESTUSERS01.DBF
channel ORA_DISK_1: starting piece 1 at 2011-07-24 21:36:08
channel ORA_DISK_1: finished piece 1 at 2011-07-24 21:36:15
piece handle=G:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTBACKUPSET2011_07_24O1_MF_NNNDF_TAG20110724T213608_72R7Y8SJ_.BKP tag=TAG20110724T21360
8 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2011-07-24 21:36:15

RMAN>
--=========================
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--==========================
--通过ultraedit编辑4号文件，表tt1在4号文件上
--==========================
SQL> startup
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1248116 bytes
Variable Size 92275852 bytes
Database Buffers 109051904 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> analyze table tt1 validate structure;
analyze table tt1 validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1663)
ORA-01110: data file 4: 'G:ORADATATESTUSERS01.DBF'

SQL>
--===================================
RMAN> backup validate datafile 4;

Starting backup at 2011-07-24 22:39:59
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=G:ORADATATESTUSERS01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2011-07-24 22:40:03

RMAN>
--========================
SQL> select * from v$database_block_corruption where file#=4;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 3616 1 0 CHECKSUM
4 1663 1 0 CHECKSUM
4 5451 1 0 CHECKSUM

SQL>
--================================
RMAN> blockrecover datafile 4 block 1663;

Starting blockrecover at 2011-07-24 22:41:15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece G:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTBACKUPSET2011_07_24O1_MF_NNNDF_TAG20110724T213608_72
R7Y8SJ_.BKP
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=G:ORACLEPRODUCT10.2.0FLASH_RECOVERY_AREATESTBACKUPSET2011_07_24O1_MF_NNNDF_TAG20110724T213608_72R7Y8SJ_.BKP tag=TAG20110724T21360
8
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished blockrecover at 2011-07-24 22:41:23

RMAN>
--================================
--在rman里执行block recover之后，在udump目录下
生成了一个文件4_1663_0_14820894.BKD，大小正好是8k，应该是
在recover之前对该block的一个备份?不得而知，其中文件命名中的后半部分
0_14820894不知道表示什么意思，14820894应该是一个scn号感觉...
--================================
SQL> show parameter user

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_users integer 0
parallel_adaptive_multi_user boolean TRUE
user_dump_dest string G:ORACLEPRODUCT10.2.0ADMIN
TESTUDUMP
SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:>g:

G:>cd G:ORACLEPRODUCT10.2.0ADMINTESTUDUMP

G:oracleproduct10.2.0admintestudump>dir *1663*
驱动器 G 中的卷没有标签。
卷的序列号是 80B8-59BB

G:oracleproduct10.2.0admintestudump 的目录

2011-07-24 22:41 8,192 4_1663_0_14820894.BKD
1 个文件 8,192 字节
0 个目录 154,893,545,472 可用字节

G:oracleproduct10.2.0admintestudump>
--================================

--把14820894和当前的scn对比一下发现差别比较当，也就是说14820894肯定不是去的当前的scn，估计是block里的scn号

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
15355391

SQL> analyze table tt1 validate structure;
analyze table tt1 validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 3616)
ORA-01110: data file 4: 'G:ORADATATESTUSERS01.DBF'

SQL>
--=============================
--skip corrupted block：
SQL> select table_name,skip_corrupt from dba_tables where table_name='TT1';

TABLE_NAME SKIP_COR
------------------------------ --------
TT1 DISABLED

SQL> exec dbms_repair.skip_corrupt_blocks('SYS','TT1');

PL/SQL procedure successfully completed.

SQL> select table_name,skip_corrupt from dba_tables where table_name='TT1';

TABLE_NAME SKIP_COR
------------------------------ --------
TT1 ENABLED

SQL> analyze table tt1 validate structure;

Table analyzed.

SQL>
--============================
--当dba_tables中字段skip_corrupt的值是enable的时侯，那么执行
analyze table tt1 validate structure到底是否会报错呢也是我的疑问...

上面暂时没有报错
--============================
SQL> select tablespace_id,relative_fno,header_block from sys_dba_segs where segment_name='TT1';

TABLESPACE_ID RELATIVE_FNO HEADER_BLOCK
------------- ------------ ------------
4 4 75

SQL>
--=============================
C:>dbv userid=system/system segment_id=4.4.75

DBVERIFY: Release 10.2.0.1.0 - Production on Sun Jul 24 22:52:33 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

DBVERIFY - Verification starting : SEGMENT_ID = 4.4.75
Page 3616 is marked corrupt
Corrupt block relative dba: 0x01000e20 (file 4, block 3616)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01000e20
last change scn: 0x0000.00ea4ac9 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x4ac90603
check value in block header: 0x6b8d
computed block checksum: 0x636c

DBVERIFY - Verification complete

Total Pages Examined : 4864
Total Pages Processed (Data) : 4779
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 83
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 15355414 (0.15355414)

C:>
--===============================

--很显然tt1里的还有1个corrupted block，可下面检查为什么又报错了呢？

--================================
SQL> analyze table tt1 validate structure;
analyze table tt1 validate structure
*
ERROR at line 1:
ORA-01498: block check failure - see trace file

SQL>
--===============================
--这里为什么执行analyze table tt1 validate structure;会报错，
我怀疑和dba_tables的字段skip_corrupt的值是enable有关...尝试把skip_corrupt改成disable看看：
--===============================
--trace file的信息如下：
Corrupt block relative dba: 0x01000e20 (file 4, block 3616)
Bad check value found during buffer read
Data in bad block:
type: 6 format: 2 rdba: 0x01000e20
last change scn: 0x0000.00ea4ac9 seq: 0x3 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x4ac90603
check value in block header: 0x6b8d
computed block checksum: 0x636c
Reread of rdba: 0x01000e20 (file 4, block 3616) found same corrupted data
skipping corrupted block at rdba: 0x01000e20
skipping corrupted block at rdba: 0x01000e20

--================================
SQL> select to_number('01000e20','xxxxxxxx') from dual;

TO_NUMBER('01000E20','XXXXXXXX')
--------------------------------
16780832

SQL> select dbms_utility.data_block_address_file(16780832) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16780832)
----------------------------------------------
4

SQL> select dbms_utility.data_block_address_block(16780832) from dual;

DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16780832)
-----------------------------------------------
3616

SQL>
--=======================================

--很显然trace文件中skipping corrupted block at rdba: 0x01000e20
中提到的block就是指3616这个block
--========================================
SQL> exec dbms_repair.skip_corrupt_blocks('SYS','TT1',flags=>dbms_repair.noskip_flag);

PL/SQL procedure successfully completed.

SQL> select table_name,skip_corrupt from dba_tables where table_name='TT1';

TABLE_NAME SKIP_COR
------------------------------ --------
TT1 DISABLED

SQL>
SQL> analyze table tt1 validate structure;
analyze table tt1 validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 3616)
ORA-01110: data file 4: 'G:ORADATATESTUSERS01.DBF'

SQL>
--==================================
--很显然执行 analyze table tt1 validate structure时的错误ORA-01498就是由于
我们执行dbms_repair.skip_corrupt_blocks修改了dba_tables中的字段skip_corrupt
而引起的.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1052963/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1052963/