sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle查找物理坏块和逻辑坏块的内容

时间:2016-12-01 22:07   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle坏块的文章,该文章详细介绍了如何制作Oracle逻辑坏块和查看坏块所属用户、表和坏块内容的方法。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 手工构造逻辑坏块一例

以前同事问我,能否构造一个逻辑坏块?我们知道坏块通常分为物理坏块和逻辑坏块两种。

物理坏块:通常是由于硬件损坏如磁盘异常导致;

逻辑坏块:通常是由于oracle bug导致,比如data block和index block数据不一致等;

那么如何构造一个逻辑坏块呢?我们这里就来构造一个index block内容跟其对应的data block内容不一致的情况,其实非常简单,我们随便改下index entry就行。

SQL> SHOW USER
USER IS "ROGER"

SQL> CREATE TABLE t1 AS SELECT * FROM dba_objects WHERE rownum < 100;
TABLE created.

SQL> CREATE INDEX idx_id ON t1(object_id);
INDEX created.

SQL> SELECT dbms_rowid.rowid_relative_fno(rowid) file#,
  2         dbms_rowid.rowid_block_number(rowid) blk#
  3  FROM t1;

     FILE#       BLK#
---------- ----------
         6      28516
         6      28516
         6      28516
         6      28516
         .      .....
         .      .....
         .      .....
         .      .....
         .      .....
         .      .....
         6      28517
         6      28517
         6      28517
         6      28517
         6      28517
         6      28517

99 ROWS selected.

SQL> SELECT object_id FROM dba_objects WHERE object_name=UPPER('idx_id');

OBJECT_ID
----------
     54609

SQL> ALTER SESSION SET EVENTS 'immediate trace name treedump level 54609';

SESSION altered.


++++++++++++++++++ trace file +++++++++++++++++++++++++++++

--- index treedump                                        
*** 2012-03-05 08:59:56.763                               
*** ACTION NAME:() 2012-03-05 08:59:56.762                
*** MODULE NAME:(SQL*Plus) 2012-03-05 08:59:56.762        
*** SERVICE NAME:(SYS$USERS) 2012-03-05 08:59:56.762      
*** SESSION ID:(159.5) 2012-03-05 08:59:56.762            
----- begin tree dump                                     
leaf: 0x1806f9c 25194396 (0: nrow: 99 rrow: 99)           
----- end tree dump                                       

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> SELECT dbms_utility.data_block_address_file(TO_NUMBER('1806f9c', 'XXXXXXXX')) file_id,
  2         dbms_utility.data_block_address_block(TO_NUMBER('1806f9c', 'XXXXXXXX')) block_id
  3    FROM dual;

   FILE_ID   BLOCK_ID
---------- ----------
         6      28572

++++++ index block structure ++++++

BBED> set file 6 block 28572

        FILE#           6
        BLOCK#          28572

BBED> p kdxle

struct kdxle, 32 bytes                      @100    
   struct kdxlexco, 16 bytes                @100    
      ub1 kdxcolev                          @100      0x00
      ub1 kdxcolok                          @101      0x00
      ub1 kdxcoopc                          @102      0x80
      ub1 kdxconco                          @103      0x02
      ub4 kdxcosdc                          @104      0x00000000
      sb2 kdxconro                          @108      99
      b2 kdxcofbo                           @110      234
      b2 kdxcofeo                           @112      6844
      b2 kdxcoavs                           @114      6610
   b2 kdxlespl                              @116      0
   sb2 kdxlende                             @118      0
   ub4 kdxlenxt                             @120      0x00000000
   ub4 kdxleprv                             @124      0x00000000
   ub1 kdxledsz                             @128      0x00
   ub1 kdxleunuse                           @129      0x00


++++++  我们这里修改 kdxlexco 即可,我这里就修改kdxcofbo吧。++++++

BBED> set off 110

        OFFSET          110
Oracleо
BBED> modify /x e9

Warning: contents of previous BIFILE will be lost. Proceed  (Y/N) y
File: /home/ora10g/oradata/roger/roger02.dbf (6)
Block: 28572            Offsets:  110 to  621           Dba:0x01806f9c
------------------------------------------------------------------------
e900bc1a d2190000 00000000 00000000 00000000 0000601f 0000541f 481f3c1f
301f241f 181f0c1f 001ff41e e81edc1e d01ec41e b81eac1e a01e941e 881e7c1e
701e641e 581e4c1e 401e341e 281e1c1e 101e041e f81dec1d e01dd41d c81dbc1d
b01da41d 981d8c1d 801d741d 681d5c1d 501d441d 381d2c1d 201d141d 081dfc1c
f01ce41c d81ccc1c c01cb41c a81c9c1c 901c841c 781c6c1c 601c541c 481c3c1c
301c241c 181c0c1c 001cf41b e81bdc1b d01bc41b b81bac1b a01b941b 881b7c1b
701b641b 581b4c1b 401b341b 281b1c1b 101b041b f81aec1a e01ad41a c81abc1a
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> sum apply

Check value for File 6, Block 28572:
current = 0x7798, required = 0x7798
++++++ CHECK ++++++

SQL> conn /AS sysdba

SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6      50403          1                  0 ALL ZERO
         6      28572          1            2779528 UNKNOWN
         6      51684          1            1527408 UNKNOWN


我们可以发现 block 28572 的CORRUPTION_CHANGE# 大于0. 说明该block为逻辑坏块。
++++++ rman check ++++++

RMAN> backup validate check logical database;

Starting backup at 05-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/home/ora10g/oradata/roger/roger01.dbf
input datafile fno=00002 name=/home/ora10g/oradata/roger/undotbs01.dbf
input datafile fno=00006 name=/home/ora10g/oradata/roger/roger02.dbf
input datafile fno=00001 name=/home/ora10g/oradata/roger/system01.dbf
input datafile fno=00003 name=/home/ora10g/oradata/roger/sysaux01.dbf
input datafile fno=00004 name=/home/ora10g/oradata/roger/users01.dbf
input datafile fno=00008 name=/home/ora10g/oradata/roger/sqlt_01.dbf
input datafile fno=00007 name=/home/ora10g/oradata/roger/test1.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 05-MAR-12

++++++ alert log ++++++

Mon Mar 05 18:30:19 PST 2012
Error backing up file 6, block 28572: logical corruption
Mon Mar 05 18:30:38 PST 2012
Hex dump of (file 6, block 50403) in trace file /home/ora10g/admin/roger/udump/roger_ora_9126.trc
Corrupt block relative dba: 0x0180c4e3 (file 6, block 50403)
Completely zero block found during backing up datafile
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Reread of blocknum=50403, file=/home/ora10g/oradata/roger/roger02.dbf. found same corrupt data
Error backing up file 6, block 51684: logical corruption

SQL> SET LINES 150
SQL> col segment_name FOR a15
SQL> col owner FOR a20

SQL>  SELECT tablespace_name, segment_type, owner, segment_name
  2     FROM dba_extents
  3    WHERE file_id = 6
  4      AND 28572
  5  BETWEEN block_id AND block_id + blocks - 1;



TABLESPACE_NAME                SEGMENT_TYPE       OWNER                SEGMENT_NAME
------------------------------ ------------------ -------------------- ---------------
ROGER                          INDEX              ROGER                IDX_ID
这里可以看到,目前构造出了2个逻辑坏块。file 6 block 50403/51684 是以前实验留下的,不用管。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle查找物理坏块和逻辑坏块的内容

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1351.html

Oracle研究中心

关键词:

如何制造Oracle坏块

如何查看Oracle坏块属于哪个用户

查看Oracle坏块属于哪个表