Block 151 failed with check code 6108
DBVERIFY - Verification complete
Total Blocks Examined : 1
YHQT@ orcl >create table yhqtest_4(id int,name varchar2(100));
Table created.
begin
for i in 1 .. 5000 loop
insert into yhqtest_4 values(i,'yhq'||i);
commit;
end loop;
end;
/
YHQT@ orcl >SELECT owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
FROM dba_extents
WHERE segment_name='YHQTEST_4' AND owner='YHQT'; 2 3
OWNER SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
------------------------------ ------------------------------- ---------- ---------- ---------- ----------
YHQT YHQTEST_4 0 10 152 8
YHQT YHQTEST_4 1 10 160 8
YHQT@ orcl >SELECT DISTINCT dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk#
FROM YHQTEST_4 ORDER BY 1,2; 2
FILE# BLK#
---------- ----------
10 155
10 156
10 157
10 158
10 159
10 160
10 161
10 163
10 164
10 165
10 166
10 167
12 rows selected.
YHQT@ orcl >conn / as sysdba
Connected.
SYS@ orcl >shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
使用dd命令
[oracle@DSI ~]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/yhqt01.dbf bs=8192 seek=160 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 9.5966e-05 s, 85.4 MB/s
SYS@ orcl >startup
YHQT@ orcl >select count(*) from yhqtest_4;
select count(*) from yhqtest_4
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 10, block # 160)
ORA-01110: data file 10: '/u01/app/oracle/oradata/orcl/yhqt01.dbf'
---坏块检查
YHQT@ orcl >select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
10 135 1 0 FRACTURED
10 143 1 0 FRACTURED
10 160 1 0 ALL ZERO
RMAN> backup validate datafile 10;
Starting backup at 12-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=145 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/oradata/orcl/yhqt01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10 FAILED 0 6233 6400 10394764
File Name: /u01/app/oracle/oradata/orcl/yhqt01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 2 27
Index 0 0
Other 1 140
validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4271.trc for details
Finished backup at 12-JUL-19
[oracle@DSI ~]$ dbv file=/u01/app/oracle/oradata/orcl/yhqt01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Fri Jul 12 16:45:43 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/orcl/yhqt01.dbf
Page 135 is influx - most likely media corrupt
Corrupt block relative dba: 0x02800087 (file 10, block 135)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x02800087
last change scn: 0x0000.009e4d78 seq: 0xff flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x4d780601
check value in block header: 0xf406
computed block checksum: 0x0
Page 143 is influx - most likely media corrupt
Corrupt block relative dba: 0x0280008f (file 10, block 143)
Fractured block found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0280008f
last change scn: 0x0000.009e53f5 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x54f50601
check value in block header: 0x46ca
computed block checksum: 0x0
Block Checking: DBA = 41943191, Block Type = KTB-managed data block
data header at 0x7fdde45ab064
kdbchk: xaction header lock count mismatch
trans=1 ilk=1 nlo=0
Page 151 failed with check code 6108
Page 160 is marked corrupt
Corrupt block relative dba: 0x028000a0 (file 10, block 160)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 6400
Total Pages Processed (Data) : 49
Total Pages Failing (Data) : 1
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 145
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 6203
Total Pages Marked Corrupt : 3
Total Pages Influx : 2
Total Pages Encrypted : 0
Highest block SCN : 10396346 (0.10396346)
--rman修复,没有备份是不行的
RMAN> run {blockrecover datafile 10 block 143;}
Starting recover at 12-JUL-19
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/12/2019 16:33:16
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 10 found to restore
--修复
YHQT@ orcl >conn / as sysdba
Connected.
SYS@ orcl >alter session set db_file_multiblock_read_count=1;
Session altered.
SYS@ orcl >execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('YHQT','YHQTEST_4');
PL/SQL procedure successfully completed.
SYS@ orcl >create table YHQT.YHQTEST_4_new as select * from YHQT.YHQTEST_4;
Table created.
SYS@ orcl >select count(*) from yhqt.yhqtest_4_new;
COUNT(*)
----------
4573
SYS@ orcl >select count(*) from yhqt.yhqtest_4;
COUNT(*)
----------
4573
下一篇介绍逻辑坏块以及坏块的恢复
-->