corrupt block引起的錯誤ORA-01498原因分析

warehouse發表於2011-07-24
BloC

執行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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章