[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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2638025/