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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- How to check Database corrupt BlockDatabaseBloC
- oracle corrupt block壞塊處理OracleBloC
- TensorFlow不同版本引起的錯誤
- 記憶體拷貝引起的錯誤記憶體
- 由JS註釋引起的JS錯誤JS
- 資料庫壞塊Corrupt block的處理方法資料庫BloC
- 引起SAP WebClient UI頁面出現超時(time out)錯誤的另一個原因WebclientUI
- 行資料 引起的hot blockBloC
- Git 錯誤：fatel: loose object ... is corrupt 解決辦法GitObject
- Double型別精度問題引起的錯誤型別
- Redhat防火牆引起的一個NDB錯誤。Redhat防火牆
- SAP Fiori應用裡出現http request錯誤的原因分析HTTP
- 動態建立 @ViewChild 導致執行時錯誤的原因分析View
- MySQL在刪除表時I/O錯誤原因分析MySql
- ORA-32701錯誤原因分析及處理方法
- Oracle RMAN備份中對壞塊(corrupt block)的處理OracleBloC
- Kubelet 錯誤日誌 broken pipe 和 connection reset by peer 的原因分析
- MySQL server has gone away錯誤的一些常見原因分析MySqlServerGo
- 檔案格式引起的指令碼執行錯誤指令碼
- 檔名與庫名相似引起的錯誤——randomrandom
- mysql memory引擎引起的主從 1032 錯誤MySql
- DB2 DDL操作引起的GoldenGate錯誤DB2Go
- 常見的授權錯誤及原因
- 雲伺服器出現502錯誤的原因分析和解決方案伺服器
- 關於Oracle8i回退段引起的錯誤Oracle
- HEAD請求方法引起方法未定義的錯誤
- 物理DG的FAL_CLIENT設定錯誤引起的主庫報錯client
- 伺服器內部錯誤500的原因伺服器
- Python 編碼錯誤的本質原因Python
- c++連結錯誤原因整理C++
- 引起索引失效的原因和解決方法索引
- ORA-1578 Corrupt Block Found in Encrypted Database (Doc ID 1329437.1)BloCDatabase
- convert函式引起的ora-01482錯誤函式
- IllegalArgumentException:Unknown URL content 錯誤的原因Exception
- ora-371錯誤的原因和解決方法
- 沒有備份的情況下如何處理logical & physical corrupt blockBloC
- 評“MySQL 隱式轉換引起的執行結果錯誤”MySql
- Oracle 11g匯入到10g引起的錯誤Oracle