Oracle壞塊問題處理
曾經遇到過ORA-8103,01578的壞塊錯誤記錄下方法
首先,製造壞塊
select tablespace_name,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='ETMCDB';
TABLESPACE_NAME EXTENT_MAN SEGMEN
------------------------------ ---------- ------
ETMCDB LOCAL AUTO
create table t1 (id number, c1 char(2000), c2 char(2000), c3 char(2000)) tablespace ETMCDB;
insert into t1 VALUES (1, 'A', 'A', 'A');
insert into t1 VALUES (2, 'A', 'A', 'A');
insert into t1 VALUES (3, 'A', 'A', 'A');
insert into t1 VALUES (4, 'A', 'A', 'A');
insert into t1 VALUES (5, 'A', 'A', 'A');
insert into t1 VALUES (6, 'A', 'A', 'A');
insert into t1 VALUES (7, 'A', 'A', 'A');
commit;
select id,rowid,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) from t1;
ID ROWID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)||'_'||DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------ ---------------------------------------------------------------------------------
1 AAAOZnAAHAAAAAUAAA 7_20
2 AAAOZnAAHAAAAAVAAA 7_21
3 AAAOZnAAHAAAAAWAAA 7_22
4 AAAOZnAAHAAAAAXAAA 7_23
5 AAAOZnAAHAAAAAYAAA 7_24
6 AAAOZnAAGAAAABhAAA 6_97
7 AAAOZnAAGAAAABiAAA 6_98
7 rows selected.
SQL> select file#,name from v$datafile;
FILE# NAME
---------- -----------------------
7 /u02/oradata/ETMCDB02.dbf
SQL> shutdown immediate;
修改Checksum的值
匯出第31個塊(算上os header block,物理上是32個塊)來把它的變成壞塊,使用dd考出data block 【更多關於DD請參考這裡】
node1*orcl-/u02/oradata >dd if=/u02/oradata/ETMCDB02.dbf f=/u02/oradata/ETMCDB02_7_21.dd skip=21 bs=8192 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.00417351 seconds, 2.0 MB/s
這裡涉及到了notrunc模式,如果沒有指定notrunc的話那麼需要再dd出65505個塊,
node1*orcl-/u02/oradata >dd if=/u02/oradata/ETMCDB02.dbf f=/u02/oradata/ETMCDB02_7_65505.dd skip=22 bs=8192 count=65505
原因
node1*orcl-/home/oracle >ls -al /u02/oradata/
total 1159260
-rw-r--r-- 1 oracle dba 8192 Nov 15 15:23 ETMCDB02_7_21.dd
-rw-r----- 1 oracle dba 536879104 Nov 15 14:51 ETMCDB02.dbf
SQL> select (536879104-32*8192)/8192 from dual;
(536879104-32*8192)/8192
----------------------
65505
也可以不用這麼麻煩,可以在導回時使用notrunc方法
之後用Ultraedit開啟修改offset 16隨便一個>0的數值,然後傳回
node1*orcl-/u02/oradata > dd if=/u02/oradata/ETMCDB02_7_21.dd f=/u02/oradata/ETMCDB02.dbf seek=21 bs=8192 count=1 conv=notrunc
這裡還有個小技巧就是直接用Ultraedit開啟確定檔案7號ETMCDB02.dbf後在UE中按CTRL+G,輸入這個 塊號*塊大小的結果就可以了,比如這個例子就是21*8192
SQL> startup
SQL> select * from t1;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 7, block # 21)
ORA-01110: data file 7: '/u02/oradata/ETMCDB02.dbf'
no rows selected
SQL> SELECT tablespace_name, owner,segment_name,segment_type FROM dba_extents
WHERE file_id = &file and &block between block_id AND block_id + blocks - 1 ;
WHERE file_id = &file and &block between block_id AND block_id + blocks - 1 ;
Enter value for file: 7
Enter value for block: 21
old 1: SELECT tablespace_name, owner,segment_name,segment_type FROM dba_extents
WHERE file_id = &file and &block between block_id AND block_id + blocks - 1
WHERE file_id = &file and &block between block_id AND block_id + blocks - 1
new 1: SELECT tablespace_name, owner,segment_name,segment_type FROM dba_extents
WHERE file_id = 7 and 21 between block_id AND block_id + blocks - 1
WHERE file_id = 7 and 21 between block_id AND block_id + blocks - 1
TABLESPACE OWNER SEGMENT_NAME SEGMENT_TYPE
---------- ---------- -------------------- ------------------
ETMCDB XXD T1 TABLE
node1*orcl-/u02/oradata >dbv file=ETMCDB02.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Wed Nov 17 18:53:08 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = ETMCDB02.dbf
Page 21 is marked corrupt
Corrupt block relative dba: 0x01c00015 (file 7, block 21)
Bad header found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01c00015
last change scn: 0x0000.005552a8 seq: 0x1 flg: 0x02
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x52a80601
check value in block header: 0x11
block checksum disabled
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 461
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1
Total Pages Failing (Index): 0
Total Pages Processed (Other): 45
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 132
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 5591720 (0.5591720)
使用DBMS_REPAIR “修復”壞塊
set serveroutput on
BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'ETMCDB');
END;
/
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SYS',
OBJECT_NAME => 'T1',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
corrupt_count => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
/
BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'SYS',
OBJECT_NAME => 'T1',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.SKIP_FLAG);
END;
/
SQL> select id from t1;
ID
----------
1
3
4
5
6
7
6 rows selected
SQL> select object_name, block_id, corrupt_type, marked_corrupt,corrupt_description,repair_description from repair_table;
OBJECT_NAME BLOCK_ID CORRUPT_TYPE MARKED_CORCORRUPT_DESCRIPTION REPAIR_DESCRIPTION
----------- -------- ------------ ------------------------------ --------------------------
T1 21 6148 TRUE mark block software corrupt
SQL> alter system checkpoint;
System altered.
看看這個DBMS_REPAIR“修復”到底做了些什麼
node1*orcl-/u01/app/oracle/product/10.2.0/db_1/bin >bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Nov 17 19:01:05 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set file 7
FILE# 7
BBED> set block 21
BLOCK# 21
BBED> dump
File: /u02/oradata/ETMCDB02.dbf (7)
Block: 21 Offsets: 0 to 511 Dba:0x01c00015
------------------------------------------------------------------------
06a20000 1500c001 a8525500 00000102 11000000 01000000 67e60000 a4525500
00000000 02003200 1100c001 0a000300 1e0a0000 87008000 0a061500 01200000
a8525500 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00010100 ffff1400 19080508 05080000 01001908 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
對比這個資料庫沒有被破壞時的情況
BBED> dump
File: /u02/oradata/ETMCDB02.dbf (7)
Block: 21 Offsets: 0 to 511 Dba:0x01c00015
------------------------------------------------------------------------
06a20000 1500c001 a8525500 00000102 00000000 01000000 67e60000 a4525500
<32 bytes per line>
而這時候檢視Block內部,實際上DBMS_REPAIR沒有對塊做任何的修改只是跳過了該塊。實際情況下,checksum壞了往往意味著壞內的資料已經壞了因為我們無法的值正確的Checksum的值,只能跳過。
那麼看看RMAN能夠對壞塊做些什麼
首先使用rman檢查含有壞塊的資料檔案:
RMAN> backup validate datafile 7;
隨後檢視壞塊資訊v$database_block_corruption
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
7 21 1 0 FRACTURED
使用rman進行塊恢復:
RMAN> blockrecover datafile 7 block 21 from backupset;
如果執行BLOCKRECOVER CORRUPTION LIST會自動按照V$DATABASE_BLOCK_CORRUPTION進行修復
RMAN> BLOCKRECOVER CORRUPTION LIST;
隨後確認壞塊資訊v$database_block_corruption
SQL> select * from v$database_block_corruption;
no rows selected
如果用RMAN備份該檔案,而後還原該檔案後,則這個壞塊的seq_kcbh則被設為0xff。
神器BBED或者DD+UltraEdit
大概說一下如果資料塊的損壞應該是offset@18也就是seq_kcbh被標記成0xff。這時檢查alert.log如果看到 computed block checksum那麼使用BBED其實很容易,進入然後開啟損壞的資料塊,offset到16,sum apply後oracle會算出正確的checksum值並且寫回去。具體演算法就是 computed block checksum後邊會有一個數值那麼轉換成二進位制和現有的offset 16 17做異或運算就可以算出正確的值。我也是最近才弄明白這種演算法,正好複習。
最後還有一種exp+10231事件的終極方法,具體看連結內的eygle的文章。不過對於大表就是個災難,希望能夠在面臨這類災難時有可靠的RMAN備份可用,不然為了幾個資料塊而去exp..........簡直就是侮辱了DBA這個職位。
參考文章
記一次ORA-8103錯誤的處理
Oracle怎樣標記壞塊及一次資料恢復
Oracle中模擬及修復資料塊損壞
利用dd修改checksum值的過程
利用BBED修改checksum值的過程
怎樣計算出正確的checksum值
http://www.cnblogs.com/buro79xxd/archive/2010/11/22/1884491.html
http://www.itpub.net/thread-991044-1-1.html
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/14710393/viewspace-758050/,如需轉載,請註明出處,否則將追究法律責任。
下一篇:
用BBED修復壞塊
請登入後發表評論
登入
全部評論
相關文章
- Oracle壞塊處理Oracle
- Oracle資料庫處理壞塊問題常用命令Oracle資料庫
- 如何處理Oracle資料庫中的壞塊問題(轉)Oracle資料庫
- 【BLOCK】Oracle壞塊處理命令參考BloCOracle
- Oracle日常問題-壞塊修復Oracle
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- RAC磁碟頭損壞問題處理
- oracle SP2-問題處理Oracle
- Oracle日常問題處理ORA-04031Oracle
- ORACLE問題處理十個指令碼Oracle指令碼
- oracle壞塊(二)Oracle
- Oracle資料庫出現ORA-19566 LOB壞塊的處理記錄Oracle資料庫
- linux處理oracle問題常用命令LinuxOracle
- 一次壞塊的處理過程(一)
- 一次壞塊的處理過程(二)
- Oracle CPU使用率過高問題處理Oracle
- ORACLE懸疑分散式事務問題處理Oracle分散式
- [20190718]12c壞塊處理一例.txt
- pyinstaller打包cx_Oracle庫問題處理記錄Oracle
- oracle系統表空間過大問題處理Oracle
- Oracle 記一次ORA-00001問題處理Oracle
- Oracle資料庫中的逐行處理問題NEOracle資料庫
- 打Oracle PSU時碰到的一些問題處理Oracle
- Oracle日常問題處理-資料庫無法啟動Oracle資料庫
- Oracle 11g ORA-600 [kjbrcrcvt:lms] 問題處理Oracle
- 一個簡單易用的資料庫壞塊處理方案資料庫
- truncate操作消除ORACLE SEG壞塊解析Oracle
- redhat7 搭建oracle 11g RAC 問題與處理RedhatOracle
- 【ERROR】儲存鏈路問題造成oracle錯誤,ora-600[4193] 問題處理ErrorOracle
- Oracle 無備份情況下undo檔案損壞處理Oracle
- golang json處理問題GolangJSON
- [git] git問題處理Git
- Oracle OER 7451 in Load Indicator : Error Code = OSD-04500的問題處理OracleIndicatorError
- Oracle X9M ORA-15001 ORA-15018問題處理Oracle
- 銀河麒麟系統安裝ORACLE資料庫問題處理Oracle資料庫
- oracle redo各種狀態(inactive、active、current)損壞的處理方式Oracle Redo
- Oracle資料庫壞塊典型案例分析Oracle資料庫
- 併發問題處理方式
- Linux 問題處理集錦Linux