資料庫壞塊解決案例一則
收到開發人員報告,oracle出現壞塊,檢視alert.log,資訊如下:
Hex dump of (file 7, block 1407500) in trace file /data/oracle/admin/orcl/bdump/orcl_p004_7383.trc
Corrupt block relative dba: 0x01d57a0c (file 7, block 1407500)
Fractured block found during crash/instance recovery
Data in bad block:
type: 6 format: 2 rdba: 0x01d57a0c
last change scn: 0x0000.e02e5f2d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x88150601
check value in block header: 0x462f
computed block checksum: 0xd738
Reread of rdba: 0x01d57a0c (file 7, block 1407500) found same corrupted data
解決思路:
首先確認該壞塊所屬物件,發現為索引,名字為IDX_D_D_PRODUCT_ID
確認該壞塊屬於索引IDX_D_D_PRODUCT_ID。先重建索引暫時解決問題:
SQL> alter index IDX_D_D_PRODUCT_ID rebuild online tablespace pur_index ;
Index altered.
注:必須加上online關鍵字,一則因為是線上環境,二則不加Online會在原索引基礎上重建,不會解決問題,即加上關鍵字online, 重建後的索引會從表裡取資料
使用dbv檢查:
[oracle@rac1 ~]$ dbv file=/data/oracle/oradata/orcl/justin03.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Jul 29 10:53:48 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/oradata/orcl/justin03.dbf
DBV-00200: Block, dba 30767628, already marked corrupted
DBVERIFY - Verification complete
Total Pages Examined : 4194302
Total Pages Processed (Data) : 3508146
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 605887
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75876
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 3781089739 (0.3781089739)
我們也可以使用如下過程確定壞塊屬於哪個block:
SQL> select dbms_utility.data_block_address_file(30767628) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(30767628)
--------------------------------------------------------------------------------
7
Elapsed: 00:00:00.23
SQL> select dbms_utility.data_block_address_block(30767628) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(30767628)
--------------------------------------------------------------------------------
1407500
其次要確認壞塊型別是物理壞塊還是邏輯壞塊
physical corruption check: backup validate datafile 'filename';
logical corruption check: backup check logical validate datafile 'filename'
先進行物理壞塊檢測
RMAN> backup validate datafile 7;
Starting backup at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1736 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/data/oracle/oradata/orcl/justin03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 29-JUL-10
sys@std01> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------------------------------------------------------------------------
---------- ---------- ------------------ ---------
7 1407500 1 0 CORRUPT
確認為物理壞塊
最後也是最重要的一步,進行壞塊恢復,
RMAN> BLOCKRECOVER CORRUPTION LIST;
Starting blockrecover at 29-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=455 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /backup/rman/rac1_ORCL_20100729_eoljvn41_1_1.bak
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/rman/rac1_ORCL_20100729_eoljvn41_1_1.bak tag=TAG20100729T050504
channel ORA_DISK_1: block restore complete, elapsed time: 00:57:56
failover to previous backup
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /backup/rman/rac1_ORCL_20100728_eiljt2ju_1_1.bak
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/rman/rac1_ORCL_20100728_eiljt2ju_1_1.bak tag=TAG20100728T050253
channel ORA_DISK_1: block restore complete, elapsed time: 00:58:36
starting media recovery
media recovery complete, elapsed time: 00:12:15
Finished blockrecover at 29-JUL-10
命令完成,分別使用DBV和backup validate命令進行驗證
[oracle@rac1 ~]$ dbv file=/data/oracle/oradata/orcl/justin03.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Jul 29 19:04:45 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/oradata/orcl/justin03.dbf
DBVERIFY - Verification complete
Total Pages Examined : 4194302
Total Pages Processed (Data) : 3507791
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 606239
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75879
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3785553739 (0.3785553739)
使用 backup validate datafile 進行驗證:
驗證前:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------------------------------------------------------------------------
---------- ---------- ------------------ ---------
7 1407500 1 0 CORRUPT
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 29 21:17:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1185342296)
RMAN> backup validate datafile 7;
Starting backup at 29-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=455 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/data/oracle/oradata/orcl/justin03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
Finished backup at 29-JUL-10
執行完上述命令,壞塊從資料字典裡消失:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
問題得到解決。
[ 本帖最後由 myownstars 於 2010-12-15 15:43 編輯 ]
Hex dump of (file 7, block 1407500) in trace file /data/oracle/admin/orcl/bdump/orcl_p004_7383.trc
Corrupt block relative dba: 0x01d57a0c (file 7, block 1407500)
Fractured block found during crash/instance recovery
Data in bad block:
type: 6 format: 2 rdba: 0x01d57a0c
last change scn: 0x0000.e02e5f2d seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x88150601
check value in block header: 0x462f
computed block checksum: 0xd738
Reread of rdba: 0x01d57a0c (file 7, block 1407500) found same corrupted data
解決思路:
首先確認該壞塊所屬物件,發現為索引,名字為IDX_D_D_PRODUCT_ID
確認該壞塊屬於索引IDX_D_D_PRODUCT_ID。先重建索引暫時解決問題:
SQL> alter index IDX_D_D_PRODUCT_ID rebuild online tablespace pur_index ;
Index altered.
注:必須加上online關鍵字,一則因為是線上環境,二則不加Online會在原索引基礎上重建,不會解決問題,即加上關鍵字online, 重建後的索引會從表裡取資料
使用dbv檢查:
[oracle@rac1 ~]$ dbv file=/data/oracle/oradata/orcl/justin03.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Jul 29 10:53:48 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/oradata/orcl/justin03.dbf
DBV-00200: Block, dba 30767628, already marked corrupted
DBVERIFY - Verification complete
Total Pages Examined : 4194302
Total Pages Processed (Data) : 3508146
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 605887
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75876
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 3781089739 (0.3781089739)
我們也可以使用如下過程確定壞塊屬於哪個block:
SQL> select dbms_utility.data_block_address_file(30767628) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(30767628)
--------------------------------------------------------------------------------
7
Elapsed: 00:00:00.23
SQL> select dbms_utility.data_block_address_block(30767628) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(30767628)
--------------------------------------------------------------------------------
1407500
其次要確認壞塊型別是物理壞塊還是邏輯壞塊
physical corruption check: backup validate datafile 'filename';
logical corruption check: backup check logical validate datafile 'filename'
先進行物理壞塊檢測
RMAN> backup validate datafile 7;
Starting backup at 29-JUL-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1736 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/data/oracle/oradata/orcl/justin03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 29-JUL-10
sys@std01> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------------------------------------------------------------------------
---------- ---------- ------------------ ---------
7 1407500 1 0 CORRUPT
確認為物理壞塊
最後也是最重要的一步,進行壞塊恢復,
RMAN> BLOCKRECOVER CORRUPTION LIST;
Starting blockrecover at 29-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=455 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /backup/rman/rac1_ORCL_20100729_eoljvn41_1_1.bak
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/rman/rac1_ORCL_20100729_eoljvn41_1_1.bak tag=TAG20100729T050504
channel ORA_DISK_1: block restore complete, elapsed time: 00:57:56
failover to previous backup
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00007
channel ORA_DISK_1: reading from backup piece /backup/rman/rac1_ORCL_20100728_eiljt2ju_1_1.bak
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/backup/rman/rac1_ORCL_20100728_eiljt2ju_1_1.bak tag=TAG20100728T050253
channel ORA_DISK_1: block restore complete, elapsed time: 00:58:36
starting media recovery
media recovery complete, elapsed time: 00:12:15
Finished blockrecover at 29-JUL-10
命令完成,分別使用DBV和backup validate命令進行驗證
[oracle@rac1 ~]$ dbv file=/data/oracle/oradata/orcl/justin03.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Jul 29 19:04:45 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/oradata/orcl/justin03.dbf
DBVERIFY - Verification complete
Total Pages Examined : 4194302
Total Pages Processed (Data) : 3507791
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 606239
Total Pages Failing (Index): 0
Total Pages Processed (Other): 75879
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 4393
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 3785553739 (0.3785553739)
使用 backup validate datafile 進行驗證:
驗證前:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
--------------------------------------------------------------------------------
---------- ---------- ------------------ ---------
7 1407500 1 0 CORRUPT
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 29 21:17:10 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1185342296)
RMAN> backup validate datafile 7;
Starting backup at 29-JUL-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=455 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00007 name=/data/oracle/oradata/orcl/justin03.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35
Finished backup at 29-JUL-10
執行完上述命令,壞塊從資料字典裡消失:
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
問題得到解決。
[ 本帖最後由 myownstars 於 2010-12-15 15:43 編輯 ]
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15480802/viewspace-688357/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle資料庫壞塊典型案例分析Oracle資料庫
- Oracle資料庫壞塊典型案例擴充Oracle資料庫
- Oracle資料庫恢復:歸檔日誌損壞案例一則Oracle資料庫
- Oracle資料庫壞塊(corruption)-物理壞塊Oracle資料庫
- 修復資料庫壞塊之一資料庫
- 資料庫壞塊處理資料庫
- 資料庫損壞解決:資料庫已損壞,無法分配空間資料庫
- Oracle資料庫壞塊修復Oracle資料庫
- PostgreSQL資料庫toast表損壞解決SQL資料庫AST
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- 跳過Oracle資料庫壞塊方法Oracle資料庫
- 修復資料庫壞塊之五資料庫
- 修復資料庫壞塊之四資料庫
- 修復資料庫壞塊之三資料庫
- 修復資料庫壞塊之二資料庫
- undo表空間出現壞塊導致資料庫重啟問題解決資料庫
- 一則資料庫無法重啟的案例分析資料庫
- AMDU資料抽取案例一則
- ora_01578 資料庫壞塊資料庫
- 11g資料庫出現壞塊資料庫
- Oracle___專題研究__資料庫壞塊Oracle資料庫
- 怎樣檢查資料庫壞塊(DBV)資料庫
- ORACLE資料庫壞塊的處理 (一次壞快處理過程)Oracle資料庫
- Oracle資料庫塊的物理損壞與邏輯損壞Oracle資料庫
- 一個簡單易用的資料庫壞塊處理方案資料庫
- 一則資料庫無法啟動的奇怪案例分析資料庫
- 資料庫壞塊Corrupt block的處理方法資料庫BloC
- undo壞塊引起資料庫無法啟動資料庫
- 【資料庫資料恢復】MongoDB資料庫檔案損壞的資料恢復案例資料庫資料恢復MongoDB
- oracle壞塊問題的解決Oracle
- Oracle___診斷案例__解決佛山**支隊資料壞塊的問題(20061109)Oracle
- oracle檢查資料庫是否有壞塊的命令Oracle資料庫
- 教你如何處理Oracle資料庫中的壞塊Oracle資料庫
- ORA-01578(資料塊損壞)跳過壞塊
- 資料塊損壞ORA-1578(發現損壞塊)
- ORACLE資料庫壞塊的處理 (處理無物件壞快的方法)Oracle資料庫物件
- oracle 資料庫中壞塊概念和檢查指令碼Oracle資料庫指令碼