[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 
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)
				
时间: 2024-09-20 21:26:09

[20121016]坏块处理以及数据恢复.txt的相关文章

[20150811]模拟坏块处理.txt

[20150811]模拟坏块处理.txt --如果存在备份,修复坏块还是相对简单的.在11g下: select * from V$DATABASE_BLOCK_CORRUPTION; --在rman下执行: blockrecover corruption list; --如果数据块没有使用,没有分配data_object_id而出现坏块,如何恢复呢?一般采用的方法建立新对象的方法,格式化这个数据块. --具体测试如下: 1.建立测试环境: SCOTT@test> @ver1 PORT_STRIN

[20160722]对象C_OBJ#_INTCOL#有坏块.txt

[20160722]对象C_OBJ#_INTCOL#有坏块.txt --前几天看到的帖子,一直没时间测试,链接如下: http://www.itpub.net/thread-2063836-1-1.html --我以前按照eygle的链接http://www.eygle.com/archives/2012/05/event_38003_c_obj_intcol.html做过测试,测试在11.2.0.2下做的. --通过设置alter system set event='38003 trace n

[20160816]11G dataguard坏块修复.txt

[20160816]11G dataguard坏块修复.txt --11GR2 不仅仅支持在备库在只读的情况下,日志应用(ACTIVE Data Guard),还提供主备库的坏块修复.自己以前也做过相关测试, --我记得上次测试的仅仅是主库数据块损坏,没有测试备库的数据块损坏.补充一些测试: 1.环境: SYS@test> @ ver1 PORT_STRING                    VERSION        BANNER ---------------------------

[20150601]rman备份出现坏块.txt

[20150601]rman备份出现坏块.txt --昨天看链接: http://www.jydba.net/磁盘损坏造成RMAN备份文件有坏块的恢复案例/ --提到如果备份片存在坏块的恢复案例,他使用的参数,我自己从来没见过. alter system set event='19548 trace name context forever', '19549 trace name context forever' scope=spfile; -- oerr ora 19548,oerr ora

[20140424]oracle的逻辑坏块.txt

[20140424]oracle的逻辑坏块.txt 今天上午本来想做一个11GR2的Automatic block media repair,链接如下:http://blog.itpub.net/267265/viewspace-1148315/ 但是我遇到一个奇怪的问题,检查和的计算问题: SYS@test> @ver BANNER --------------------------------------------------------------------------------

如何在Linux上检测硬盘上的坏道和坏块

让我们从坏道和坏块的定义开始说起,它们是一块磁盘或闪存上不再能够被读写的部分,一般是由于磁盘表面特定的物理损坏或闪存晶体管失效导致的. 随着坏道的继续积累,它们会对你的磁盘或闪存容量产生令人不快或破坏性的影响,甚至可能会导致硬件失效. 同时还需要注意的是坏块的存在警示你应该开始考虑买块新磁盘了,或者简单地将坏块标记为不可用. 因此,在这篇文章中,我们通过几个必要的步骤,使用特定的磁盘扫描工具让你能够判断 Linux 磁盘或闪存是否存在坏道. 以下就是步骤: 在 Linux 上使用坏块工具检查坏道

Oracle 12C的第一次异常恢复—文件头坏块

接到第一个使用Oracle 12C作为生产库的恢复救援.有两个业务数据文件报文件头损坏,其他数据文件全部是9月份的一次备份,在当前的条件下,希望我们能够帮他们恢复出来业务文件中的数据数据库版本信息  代码如下 复制代码 SQL> select * from v$version; BANNER                                                                               CON_ID-------------------

oracle中系统回滚段坏块恢复记录

数据库启动报错  代码如下 复制代码 SQL> startup mount pfile='c:\pfile.txt' ORACLE 例程已经启动.    代码如下 复制代码 Total System Global Area  452984832 bytes Fixed Size                  1291120 bytes Variable Size             201329808 bytes Database Buffers          243269632 b

在 Linux 上检测硬盘上的坏道和坏块

让我们从坏道和坏块的定义开始说起,它们是一块磁盘或闪存上不再能够被读写的部分,一般是由于磁盘表面特定的物理损坏或闪存晶体管失效导致的. 随着坏道的继续积累,它们会对你的磁盘或闪存容量产生令人不快或破坏性的影响,甚至可能会导致硬件失效. 同时还需要注意的是坏块的存在警示你应该开始考虑买块新磁盘了,或者简单地将坏块标记为不可用. 因此,在这篇文章中,我们通过几个必要的步骤,使用特定的磁盘扫描工具让你能够判断 Linux 磁盘或闪存是否存在坏道. 以下就是步骤: 在 Linux 上使用坏块工具检查坏道