startupSQL> select * from lyj.lyj_003;select * from lyj.lyj_003 *ERROR at line 1:ORA-01578: ORACLE data block corrupted (file # 5, block # 637)ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/lyj_01.dbf'# alert logCorrupt block relative dba: 0x0140027d (file 5, block 637)Bad check value found during validationData in bad block: type: 6format: 2 rdba: 0x0140027dlast change scn: 0x0000.00277a38 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x7a380601 check value in block header: 0x9d9b computed block checksum: 0x1Reread of blocknum=637, file=/u01/app/oracle/oradata/orcl/lyj_01.dbf. found same corrupt dataReread of blocknum=637, file=/u01/app/oracle/oradata/orcl/lyj_01.dbf. found same corrupt data
SQL> analyze table tab_logical_block_corruption validate structure cascade online;analyze table tab_logical_block_corruption validate structure cascade online*ERROR at line 1:ORA-01499: table/index cross reference failure - see trace file
使用rman检查
rman target /RMAN> backup validate check logical database;#---------------------------------------------------------------------------------------Starting backup at 2018-05-2313:57:26using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=67 device type=DISKchannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfinput datafile file number=00005 name=/u01/app/oracle/oradata/orcl/lyj_01.dbfinput datafile file number=00006 name=/u01/app/oracle/oradata/orcl/disdb01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07List of Datafiles#=================......File Status Marked Corrupt Empty Blocks Blocks Examined High SCN---- ------ -------------- ------------ --------------- ----------6 FAILED 0624964002658373 File Name: /u01/app/oracle/oradata/orcl/disdb01.dbf Block Type Blocks Failing Blocks Processed ---------- -------------- ---------------- Data 013 Index 11# 6号文件索引有块块 Other 1137validate found one or more corrupt blocksSee trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_20658.trc for detailschannel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01List of Control File and SPFILE#===============================File Type Status Blocks Failing Blocks Examined------------ ------ -------------- ---------------SPFILE OK 02Control File OK 0594Finished backup at 2018-05-2313:57:35#---------------------------------------------------------------------------------------
查看视图
SQL> select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO---------- ---------- ---------- ------------------ ---------562310 FRACTURED562910 FRACTURED563710 CHECKSUM614010 ALL ZERO615512658375 CORRUPT # CORRUPTION_CHANGE#=2658375大于0,表示是逻辑坏块# 通过上面的文件号和块号,可以找到坏块所在的对象set autot offset lines 150col segment_name for a15col owner for a20SELECT tablespace_name, segment_type, owner, segment_nameFROM dba_extentsWHERE file_id = 6and155 between block_id AND block_id + blocks - 1;TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME------------------------------ ------------------ -------------------- ---------------DISDB INDEX LYJ INDEX_ID
DBV检测逻辑坏块
$ dbv file=/u01/app/oracle/oradata/orcl/disdb01.dbf#---------------------------------------------------------------------------------------DBVERIFY: Release 11.2.0.4.0 - Production on Wed May 2314:16:072018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/disdb01.dbfPage 140 is marked corruptCorrupt block relative dba: 0x0180008c (file 6, block 140)Completely zero block found during dbv: Block Checking: DBA = 25165979, Block Type = KTB-managed data block**** kdxcofbo = 434 != 428# 逻辑坏块 位置不对---- end index block validation Page 155 failed with check code 6401DBVERIFY - Verification completeTotal Pages Examined : 6400Total Pages Processed (Data) : 13Total Pages Failing (Data) : 0Total Pages Processed (Index): 1# 判断出是index blockTotal Pages Failing (Index): 1# index block FailingTotal Pages Processed (Other): 136Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 6249Total Pages Marked Corrupt : 1Total Pages Influx : 0Total Pages Encrypted : 0Highest block SCN : 2658375 (0.2658375)#---------------------------------------------------------------------------------------
SQL> show parameter db_block_checksumNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_block_checksum string TYPICAL
SQL> show parameter db_block_checkingNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_block_checking string FALSESQL>
db_block_checking参数默认关闭,该参数的设置,可能增加1~10%的资源消耗
db_block_checksum和db_block_checking的性能影响
重现数据块内空间计算错误
conn lyj/lyjcreate table lyj_004(id int,name varchar2(10));insert into lyj_004 values (1,'AAAAAA');commit;select dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block#from lyj_004; FILE# BLOCK#---------- ----------5157alter system flush buffer_cache;BBED> set file 5 block 157 FILE# 5 BLOCK# 157BBED> p kdbhstruct kdbh, 14 bytes @100 ub1 kdbhflag @100 0x00 (NONE) sb1 kdbhntab @101 1 sb2 kdbhnrow @102 1 sb2 kdbhfrre @104 -1 sb2 kdbhfsbo @106 20 sb2 kdbhfseo @108 8075 sb2 kdbhavsp @110 8055# 改此值 sb2 kdbhtosp @112 8055# 改此值BBED> dump /v offset 110 File: /u01/app/oracle/oradata/orcl/disdb01.dbf (5) Block: 157 Offsets: 110 to 125 Dba:0x0140009d-------------------------------------------------------771f771f 000001008b1f000000000000 l w.w............. <16 bytes per line>SQL> select to_char(8055,'xxxxx') from dual;TO_CHA------1f77SQL> select to_char(8054,'xxxxx') from dual;TO_CHA------1f76 -> 761fBBED> modify /x761f offset 110 File: /u01/app/oracle/oradata/orcl/disdb01.dbf (5) Block: 157 Offsets: 110 to 125 Dba:0x0140009d------------------------------------------------------------------------761f781f 000001008b1f000000000000 <32 bytes per line>BBED> modify /x761f offset 112 File: /u01/app/oracle/oradata/orcl/disdb01.dbf (5) Block: 157 Offsets: 112 to 127 Dba:0x0140009d------------------------------------------------------------------------761f000001008b1f 0000000000000000 <32 bytes per line>BBED> sum applyCheck value for File 5, Block 157:current = 0x90ad, required = 0x90adBBED> verifyDBVERIFY - Verification startingFILE = /u01/app/oracle/oradata/orcl/disdb01.dbfBLOCK = 157Block Checking: DBA = 20971677, Block Type = KTB-managed data blockdata header at 0x169ba64kdbchk: the amount of space used is not equal to block size used=33 fsc=0 avsp=8054 dtl=8088# 记录 dtl=8088 used=33Block 157 failed with check code 6110DBVERIFY - Verification completeTotal Blocks Examined : 1Total Blocks Processed (Data) : 1Total Blocks Failing (Data) : 1Total Blocks Processed (Index): 0Total Blocks Failing (Index): 0Total Blocks Empty : 0Total Blocks Marked Corrupt : 0Total Blocks Influx : 0Message 531not found; product=RDBMS; facility=BBEDdbv file=/u01/app/oracle/oradata/orcl/disdb01.dbf#---------------------------------------------------------------------------------------DBVERIFY: Release 11.2.0.4.0 - Production on Wed May 2317:35:262018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/disdb01.dbfBlock Checking: DBA = 20971677, Block Type = KTB-managed data blockdata header at 0x7ff0dcbfb064kdbchk: the amount of space used is not equal to block size used=33 fsc=0 avsp=8054 dtl=8088Page 157 failed with check code 6110DBVERIFY - Verification completeTotal Pages Examined : 6400Total Pages Processed (Data) : 23Total Pages Failing (Data) : 1Total Pages Processed (Index): 0Total Pages Failing (Index): 0Total Pages Processed (Other): 136Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 6241Total Pages Marked Corrupt : 0Total Pages Influx : 0Total Pages Encrypted : 0Highest block SCN : 2707283 (0.2707283)#---------------------------------------------------------------------------------------