[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
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 ....
BBED> modify /x 0106 offset 8188
File: /u01/app/oracle11g/oradata/test/test01.dbf (8)
Block: 139 Offsets: 8188 to 8191 Dba:0x0200008b
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
01060000
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)