[20121016]壞塊處理以及資料恢復.txt
[20121016]壞塊處理以及資料恢復.txt
如果產生壞塊,在沒有備份的情況下,如何最大程度的恢復資訊,減少損失.
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
create table t tablespace test as select rownum id , 'test' name from dual connect by level <=1000;
SQL> select dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,t.* from scott.t ;
RFILE# BLOCK# ROW# ID NAME
---------- ---------- ---------- ---------- ----
8 139 0 1 test
8 139 1 2 test
8 139 2 3 test
8 139 3 4 test
8 139 4 5 test
8 139 5 6 test
......
8 140 465 990 test
8 140 466 991 test
8 140 467 992 test
8 140 468 993 test
8 140 469 994 test
8 140 470 995 test
8 140 471 996 test
8 140 472 997 test
8 140 473 998 test
8 140 474 999 test
8 140 475 1000 test
1000 rows selected.
1.產生壞塊.
關閉資料庫,使用bbed的corrupt命令修改8,139塊,產生壞塊.
BBED> set dba 8,139
DBA 0x0200008b (33554571 8,139)
BBED> corrupt
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Block marked media corrupt.
BBED> sum
Check value for File 8, Block 139:
current = 0xf9b2, required = 0xf9b2
BBED> sum apply
Check value for File 8, Block 139:
current = 0xf9b2, required = 0xf9b2
--實際上這個操作就是修改在塊頭處，seq_kcbh（佔用1位元組，塊頭偏移14)有著特殊的含義，如果該值為0xff，則表示該塊被標記為corruption。
2.啟動資料庫,看看如何恢復:
SQL> select dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,t.* from scott.t ;
select dbms_rowid.rowid_relative_fno(rowid) rfile#,dbms_rowid.ROWID_BLOCK_NUMBER(rowid) block#,dbms_rowid.ROWID_ROW_NUMBER(rowid) row#,t.* from scott.t
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 139)
ORA-01110: data file 8: '/u01/app/oracle11g/oradata/test/test01.dbf'
SQL> analyze table scott.t validate structure;
analyze table scott.t validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 8, block # 139)
ORA-01110: data file 8: '/u01/app/oracle11g/oradata/test/test01.dbf'
3.讀取方法:
SQL> column object_name format a20
SQL> select object_id,data_object_id,object_name from dba_objects where object_name='T' and wner=user;
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- --------------------
114889 114889 T
使用rowid確定範圍:
select /*+ rowid */ * from t where rowid between dbms_rowid.rowid_create(1,114889,8,140,0) and dbms_rowid.rowid_create(1,114889,8,140,4095);
--1個塊做多4095行.
alter session set events '10231 trace name context forever,level 10' ;
select * from t;
alter session set events '10231 trace name context off' ;
--這樣可以最大程度匯出資料,減少損失.
4.利用dbms_repair包來處理壞塊:(以sys使用者登入)
begin
dbms_repair.admin_tables (
table_name => 'REPAIR_TABLE',
table_type => dbms_repair.repair_table,
action => dbms_repair.create_action,
tablespace => 'USERS');
end;
/
--完成後建立一張表REPAIR_TABLE.
SQL> desc REPAIR_TABLE
Name Null? Type
-------------------- -------- ---------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
set serveroutput on
declare
rpr_count int;
begin
rpr_count := 0;
dbms_repair.check_object (
schema_name => 'SCOTT',
object_name => 'T',
repair_table_name => 'REPAIR_TABLE',
corrupt_count => rpr_count);
dbms_output.put_line('repair count:' || to_char(rpr_count));end;
/
repair count:1
PL/SQL procedure successfully completed.
SQL> set serveroutput on
SQL> exec scott.print_table('select * from sys.REPAIR_TABLE');OBJECT_ID : 114889
TABLESPACE_ID : 9
RELATIVE_FILE_ID : 8
BLOCK_ID : 139
CORRUPT_TYPE : 6148
SCHEMA_NAME : SCOTT
OBJECT_NAME : T
BASEOBJECT_NAME :
PARTITION_NAME :
CORRUPT_DESCRIPTION :
REPAIR_DESCRIPTION : mark block software corrupt
MARKED_CORRUPT : TRUE
CHECK_TIMESTAMP : 2012-10-16 17:01:42
FIX_TIMESTAMP :
REFORMAT_TIMESTAMP :
-----------------
PL/SQL procedure successfully completed.
declare
fix_count int;
begin
fix_count := 0;
dbms_repair.fix_corrupt_blocks (
schema_name => 'SCOTT',
object_name => 'T',
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => fix_count);
dbms_output.put_line('fix count:' || to_char(fix_count));end;
/
fix count:0
PL/SQL procedure successfully completed.
begin
dbms_repair.skip_corrupt_blocks (
schema_name => 'SCOTT',
object_name => 'T',
object_type => dbms_repair.table_object,
flags => dbms_repair.skip_flag);
end;
/
SQL> select table_name, skip_corrupt from dba_tables where table_name = 'T' and wner='SCOTT';
TABLE_NAME SKIP_COR
------------------------------ --------
T ENABLED
SQL> select count(*) from scott.t;
COUNT(*)
----------
476
--雖然丟失100-476=524條,至少保持1部分資料.實際上就是讀取時跳過corrupt.
5.我的問題是做出來的,裡面的資訊還在,使用bbed恢復看看.關閉資料庫修復!
BBED> set dba 8,139
DBA 0x0200008b (33554571 8,139)
BBED> map /v
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 139 Dba:0x0200008b
------------------------------------------------------------
KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 96 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
sb2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[3], 72 bytes @44
struct kdbh, 14 bytes @124
ub1 kdbhflag @124
sb1 kdbhntab @125
sb2 kdbhnrow @126
sb2 kdbhfrre @128
sb2 kdbhfsbo @130
sb2 kdbhfseo @132
sb2 kdbhavsp @134
sb2 kdbhtosp @136
struct kdbt[1], 4 bytes @138
sb2 kdbtoffs @138
sb2 kdbtnrow @140
sb2 kdbr[524] @142
ub1 freespace[814] @1190
ub1 rowdata[6184] @2004
ub4 tailchk @8188
BBED> p seq_kcbh
ub1 seq_kcbh @14 0xff
BBED> modify /x 01 offset 14
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
BBED> p tailchk
ub4 tailchk @8188 0x000006ff
BBED> dump /v offset 8188
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 139 Offsets: 8188 to 8191 Dba:0x0200008b
---------------------------------------------------------------------------------------------------------------------------------------------------------------
ff060000 l ....
<48 bytes per line>
BBED> modify /x 0106 offset 8188
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 139 Offsets: 8188 to 8191 Dba:0x0200008b
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01060000
<80 bytes per line>
BBED> sum
Check value for File 8, Block 139:
current = 0xf9b2, required = 0xf9b2
BBED> sum apply
Check value for File 8, Block 139:
current = 0xf9b2, required = 0xf9b2
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/app/oracle11g/oradata/test/test01.dbf
BLOCK = 139
DBVERIFY - Verification complete
Total Blocks Examined : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing (Index): 0
Total Blocks Empty : 0
Total Blocks Marked Corrupt : 0
Total Blocks Influx : 0
Message 531 not found; product=RDBMS; facility=BBED
--這樣修復.
SQL> select count(*) from scott.t;
COUNT(*)
----------
1000
--OK,顯示正常.
6.收尾工作:
SQL> select table_name, skip_corrupt from dba_tables where table_name = 'T' and wner='SCOTT';
TABLE_NAME SKIP_COR
------------------------------ --------
T ENABLED
--如何修改skip_corrupt = disable.執行如下:
begin
dbms_repair.skip_corrupt_blocks (
schema_name => 'SCOTT',
object_name => 'T',
object_type => dbms_repair.table_object,
flags => dbms_repair.noskip_flag);
end;
/
SQL> select table_name, skip_corrupt from dba_tables where table_name = 'T' and wner='SCOTT';
TABLE_NAME SKIP_COR
------------------------------ --------
T DISABLED
--執行dbv出現如下錯誤,只要在該塊中做一些dml操作就ok了.
$ dbv file=test01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Wed Oct 17 08:49:10 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle11g/oradata/test/test01.dbf
csc(0x0000.b36bb443) higher than block scn(0x0000.00000000)
Page 139 failed with check code 6054
DBVERIFY - Verification complete
Total Pages Examined : 8192
Total Pages Processed (Data) : 1560
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 1
Total Pages Failing (Index): 0
Total Pages Processed (Other): 166
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6465
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3010182929 (0.3010182929)
SQL> update scott.t set name='test' where id=1;
1 row updated.
SQL> commit ;
Commit complete.
SQL> alter system checkpoint ;
System altered.
$ dbv file=test01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Wed Oct 17 08:53:44 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle11g/oradata/test/test01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 8192
Total Pages Processed (Data) : 1560
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1
Total Pages Failing (Index): 0
Total Pages Processed (Other): 166
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6465
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 3010184891 (0.3010184891)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-746588/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫壞塊處理資料庫
- 處理恢復資料方法
- Oracle壞塊修復處理實驗Oracle
- 恢復資料,資料塊恢復
- ORACLE資料庫壞塊的處理 (處理無物件壞快的方法)Oracle資料庫物件
- Oracle資料壞塊簡介及其恢復（dbv、BMR）Oracle
- 轉載：Oracle資料塊損壞恢復總結Oracle
- 資料檔案壞塊的製造和恢復
- ORACLE資料庫壞塊的處理 (一次壞快處理過程)Oracle資料庫
- Oracle如何進行塊介質的恢復？（有邏輯壞塊是如何處理）Oracle
- 資料庫壞塊Corrupt block的處理方法資料庫BloC
- 【資料庫資料恢復】Oracle資料庫檔案出現壞塊報錯的資料恢復案例資料庫資料恢復Oracle
- Oracle壞塊處理Oracle
- rootvg壞塊處理
- ORACLE 壞塊處理Oracle
- 處理塊損壞
- MySQL資料庫InnoDB壞頁處理修復MySql資料庫
- PostgreSQL 恢復大法 - 恢復部分資料庫、跳過壞塊、修復無法啟動的資料庫SQL資料庫
- ORA-01578(資料塊損壞)跳過壞塊處理辦法
- 一次ORACLE資料庫undo壞塊處理Oracle資料庫
- 教你如何處理Oracle資料庫中的壞塊Oracle資料庫
- 恢復SDS中壞掉的一塊硬碟的資料(轉)硬碟
- 【北亞資料恢復】硬碟壞道故障如何恢復資料？資料恢復硬碟
- 一次不完全恢復中途Kill rman後的問題處理+壞塊處理過程
- rman 恢復資料塊
- rman恢復資料塊
- 修復損壞的資料塊
- Oracle資料庫壞塊修復Oracle資料庫
- 利用DBMS_REPAIR包檢查以及COPY處理壞塊:AI
- [20130530]OS block header破壞以及恢復.txtBloCHeader
- 資料底層損壞的恢復方法—拼碎片恢復資料
- [20150811]模擬壞塊處理.txt
- 使用blockrecover 對有壞塊的資料檔案進行恢復BloC
- 資料庫檔案壞塊損壞導致開啟時報錯的恢復方法資料庫
- DBA實踐---壞塊處理
- Oracle壞塊處理相關Oracle
- Oracle壞塊問題處理Oracle
- oracle corrupt block壞塊處理OracleBloC