ITPub博客

首页 > 数据库 > Oracle > [20190311]关于oracle物理与逻辑坏块.txt

[20190311]关于oracle物理与逻辑坏块.txt

原创 Oracle 作者:lfree 时间:2019-03-11 09:47:05 0 删除 编辑

[20190311]关于oracle物理与逻辑坏块.txt


--//别人问的问题,如何区分物理坏块与逻辑坏块,或者讲什么是物理坏块与逻辑坏块,区别在那里?

--//讲句真心话,我自己也不是很清晰,要给一个概念下准确的定义实际上很困难.


--//我的理解能够通过bbed,dump之类的方式读出块的结构,基本可以称为逻辑坏块,只不过里面一些数值不对.

--//oracle的数据块(其他类型的块类似)一般分为几层:


--//数据块的结构,一个0x06的block包含4个layer

---------------------

1- Cache Layer       - kcbh 20byte 该层主要包含block format, type(index, table or cluster and so on)在block中使用kcbh

                       struct来描述

---------------------

2- Transaction Layer - ktbbh 48byte(包含ktbbh,ktbit就是一个ITL的容器,单个ITL结构为ktbbhitl大小为24byte)

--------------------- ---------------------

3- Data Layer  包含  - - Data Header      - kdbh

--------------------- ---------------------

                          - Table Directory  - 包含:offset

                          ---------------------

                          - Row Directory    -

                          ---------------------

                          - Free Space       -

                          ---------------------

                          - Row Data         -

                          ---------------------

---------------------

4- Tailchk          -

---------------------


--//反之:物理坏块,就是根本读不出里面的结构,实际上这样的通过bbed之类的工具无法修复.当然这样的理解有点武断或者不全面.

--//至少是我自己的理解,通过例子说明:


1.环境:

SCOTT@book> @ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


SCOTT@book> create table empy as select * from emp;

Table created.


SCOTT@book> select rowid ,empy.* from empy where rownum=1;

ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO

------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

AAAWQ3AAEAAAALLAAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20


SCOTT@book> @ rowid AAAWQ3AAEAAAALLAAA

    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT

---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------

     91191          4        715          0  0x10002CB           4,715                alter system dump datafile 4 block 715 ;


SCOTT@book> alter system flush buffer_cache;

System altered.


--//使用rman做一个备份:

RMAN> backup as copy datafile 4 format '/u01/backup/%b_20190311';

Starting backup at 2019-03-11 09:24:04

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=67 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=80 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=94 device type=DISK

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf

output file name=/u01/backup/users01.dbf_20190311 tag=TAG20190311T092405 RECID=22 STAMP=1002619446

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finished backup at 2019-03-11 09:24:07

Starting Control File and SPFILE Autobackup at 2019-03-11 09:24:07

piece handle=/u01/app/oracle/fast_recovery_area/BOOK/autobackup/2019_03_11/o1_mf_s_1002619447_g8cg5q3k_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 2019-03-11 09:24:08


RMAN> validate datafile 4;

Starting validate at 2019-03-11 09:24:33

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    OK     0              17           16411           13278827822

  File Name: /mnt/ramdisk/book/users01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              12355

  Index      0              3292

  Other      0              720


Finished validate at 2019-03-11 09:24:34


2.测试:

BBED> set dba 4,715

        DBA             0x010002cb (16777931 4,715)


BBED> p kdbh

struct kdbh, 14 bytes                       @124

   ub1 kdbhflag                             @124      0x00 (NONE)

   sb1 kdbhntab                             @125      1

   sb2 kdbhnrow                             @126      14

   sb2 kdbhfrre                             @128     -1

   sb2 kdbhfsbo                             @130      46

   sb2 kdbhfseo                             @132      7497

   sb2 kdbhavsp                             @134      7451

   sb2 kdbhtosp                             @136      7451


--//修改该块kdbhavsp的数值:

BBED> assign kdbh.kdbhavsp=7450

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

sb2 kdbhavsp                                @134      7450


BBED> sum apply

Check value for File 4, Block 715:

current = 0x99a0, required = 0x99a0


BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 715


Block Checking: DBA = 16777931, Block Type = KTB-managed data block

data header at 0x7fe359db627c

kdbchk: the amount of space used is not equal to block size

        used=613 fsc=0 avsp=7450 dtl=8064

Block 715 failed with check code 6110


--//我以前提到过,一些修复这步我是不做的,我个人人为能读出就ok了.


RMAN> validate datafile 4;

Starting validate at 2019-03-11 09:27:18

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    OK     0              17           16411           13278827822

  File Name: /mnt/ramdisk/book/users01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              12355

  Index      0              3292

  Other      0              720

Finished validate at 2019-03-11 09:27:19


--//你可以发现做validate datafile 4;不会报错,这样的"坏"块不会报,可以称为逻辑坏块.


RMAN> validate check logical datafile 4;


Starting validate at 2019-03-11 09:28:49

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    FAILED 0              17           16411           13278827822

  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  File Name: /mnt/ramdisk/book/users01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       1              12355

  Index      0              3292

  Other      0              720

validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_47908.trc for details

Finished validate at 2019-03-11 09:28:50


--//可以发现1块有问题.这样的块称为逻辑坏块.

--//查看/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_47908.trc可以发现如下信息.

*** 2019-03-11 09:28:49.667

*** ACTION NAME:(0000087 STARTED19) 2019-03-11 09:28:49.667


Block Checking: DBA = 16777931, Block Type = KTB-managed data block

data header at 0x7f3f9671a07c

kdbchk: the amount of space used is not equal to block size

        used=613 fsc=0 avsp=7450 dtl=8064

Error backing up file 4, block 715: logical corruption


SCOTT@book> set numw 12

SCOTT@book> select * from GV$DATABASE_BLOCK_CORRUPTION;

     INST_ID        FILE#       BLOCK#       BLOCKS CORRUPTION_CHANGE# CORRUPTIO

------------ ------------ ------------ ------------ ------------------ ---------

           1            4          715            1        13278827768 CORRUPT


3.修改检查和:

--//修改回来.

BBED> assign kdbh.kdbhavsp=7451

sb2 kdbhavsp                                @134      7451


BBED> verify

DBVERIFY - Verification starting

FILE = /mnt/ramdisk/book/users01.dbf

BLOCK = 715


Block 715 is corrupt

Corrupt block relative dba: 0x010002cb (file 0, block 715)

Bad check value found during verification

Data in bad block:

 type: 6 format: 2 rdba: 0x010002cb

 last change scn: 0x0003.177ad4f8 seq: 0x2 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x0

 consistency value in tail: 0xd4f80602

 check value in block header: 0x99a0

 computed block checksum: 0x1


--//这样检查和不一致.


RMAN> validate datafile 4;

Starting validate at 2019-03-11 09:36:54

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    FAILED 0              17           16411           13278829628

  File Name: /mnt/ramdisk/book/users01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       1              12355

  Index      0              3292

  Other      0              720


validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_47908.trc for details

Finished validate at 2019-03-11 09:36:55


--//检查和不一致,validate datafile 4;一定能发现问题.

RMAN> validate check logical datafile 4;


Starting validate at 2019-03-11 09:38:56

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    FAILED 0              17           16411           13278829701

  File Name: /mnt/ramdisk/book/users01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       1              12355

  Index      0              3292

  Other      0              720


validate found one or more corrupt blocks

See trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_47908.trc for details

Finished validate at 2019-03-11 09:38:57


BBED> sum apply

Check value for File 4, Block 715:

current = 0x99a1, required = 0x99a1



RMAN> validate datafile 4;


Starting validate at 2019-03-11 09:39:39

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

channel ORA_DISK_1: starting validation of datafile

channel ORA_DISK_1: specifying datafile(s) for validation

input datafile file number=00004 name=/mnt/ramdisk/book/users01.dbf

channel ORA_DISK_1: validation complete, elapsed time: 00:00:01

List of Datafiles

=================

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN

---- ------ -------------- ------------ --------------- ----------

4    OK     0              17           16411           13278829701

  File Name: /mnt/ramdisk/book/users01.dbf

  Block Type Blocks Failing Blocks Processed

  ---------- -------------- ----------------

  Data       0              12355

  Index      0              3292

  Other      0              720

Finished validate at 2019-03-11 09:39:40


--//看来我的理解有点片面...^_^.查询一些资料,我认为作者的理解还是不错的,转载如下:

https://blog.pythian.com/what-is-the-difference-between-logical-and-physical-corruption-in-oracle/


Logical (and physical) corruption of data blocks. Block corruptions come in two types:


Physical corruptions (media corrupt blocks) are blocks that have sustained obvious physical damage. When Oracle detects

an inconsistency between the CSN in the block header and the CSN in the block footer, or the expected header and

footer structures are not present or are mangled, then the Oracle session raises an exception upon read of the block

(ORA-01578: ORACLE data block corrupted…). The call to Oracle fails, and the exception is written to the Oracle alert

log and trace files. Physical corruptions are generally the result of infrastructure problems, and can be introduced

in a variety of ways. Some possible sources of physical corruption are storage array cache corruption, array firmware

bugs, filesystem bugs and array controller battery failure combined with power outage. One can imagine at least a dozen

other possible sources of such corruption. Physically corrupt blocks can be repaired using Oracle Recovery Manager's

BLOCKRECOVER command. This operation restores and recovers the block in place in the file without interrupting any other

sessions operating against the database.


Logically corrupt blocks are blocks that have good header and footer CSNs, but that have some other kind of internal

inconsistency. For instance, one of the block header structures, which tracks the number of locks associated with rows

in the block, differs from the actual number of locks present. Another example would be if the header information on

available space differs from the true available space on the block. Upon encountering these types of faults, the calling

session generally will raise ORA-00600 ("internal error") with additional arguments that allow us to diagnose the

specific type of defect, and the call will fail. The exception will be written to the alert log and trace files. Like

physical corruption, there are a wide range of possible ways that the fault could have been introduced, including all

of the ways listed above for physical corruption. However, logically corrupt blocks are much more likely to have been

introduced as a result of a failure in the Oracle software, or as a result of an Oracle bug or cache corruption.


By default, Oracle has features that seek to perform sanity checks on blocks before they are written. However, for

highly risk-averse enterprises, additional checks, including checks for logical inconsistencies and block checksum

verification can be enabled. These features consume additional resources, so should be used judiciously.


--//金山翻译如下:

物理坏块(媒体破坏块)是指遭受明显物理伤害的块。当Oracle检测到块头中的csn与块页脚中的csn之间不一致时,或者预期的页眉和页脚

结构不存在或出现故障时,Oracle会话将在读取该块时引发异常(ora-01578:oracledata块损坏的…)。。对Oracle的调用失败,异常将

写入Oracle警报日志和跟踪文件。物理坏块通常是基础设施问题的结果,可以通过多种方式引入。物理损坏的一些可能来源是存储阵列缓

存损坏、阵列固件错误、文件系统错误和阵列控制器电池故障以及断电。人们可以想象,至少还有十几个其他可能的腐败根源。可以使用

Oracle恢复管理器的BLOCKRECOVER命令修复损坏的块。此操作恢复并恢复文件中的块,而不中断对数据库操作的任何其他会话。


逻辑上损坏的块是具有良好的页眉和页脚CSNS的块,但是它们有一些其他类型的内部不一致性。例如,跟踪与块中行关联的锁数的块头结

构之一不同于当前的实际锁数。另一个例子是,如果可用空间上的头信息与块上的实际可用空间不同的话。当遇到这些类型的故障时,调

用会话通常会使用额外的参数引发Ora-00600("内部错误"),从而使我们能够诊断特定类型的缺陷,并且调用将失败。异常将写入警报日

志和跟踪文件。与实际坏块一样,可以采用多种可能的方法来引入过失,包括上文列出的所有肉体腐败的方式。但是,在逻辑上损坏的

块更有可能是由于Oracle软件的失败或Oracle错误或缓存损坏而引入的。


默认情况下,Oracle的功能是在编写块之前对其执行正常检查。但是,对于高度规避风险的企业,可以启用额外的检查,包括检查逻辑不

一致和块校验和验证。这些特性消耗了额外的资源,因此应该谨慎使用。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2638025/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2859
  • 访问量
    6646617