Oracle disk verification Utility dbv On ASM file system
set feedback off head off echo off linesize 200 pagesize 1000 spool /tmp/dbvchk.txt select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=system/SYS logfile=' ||substr(name, instr(name, '/', -1, 1) +1) ||'.' || file# || '.log' from v$datafile / Output dbv file=+DATA/rac/datafile/system.259.716288417 blocksize=8192 USERID=system/SYS logfile=system.259.716288417.1.log dbv file=+DATA/rac/datafile/undotbs1.260.716288467 blocksize=8192 USERID=system/SYS logfile=undotbs1.260.716288467.2.log dbv file=+DATA/rac/datafile/sysaux.261.716288483 blocksize=8192 USERID=system/SYS logfile=sysaux.261.716288483.3.log dbv file=+DATA/rac/datafile/undotbs2.263.716288533 blocksize=8192 USERID=system/SYS logfile=undotbs2.263.716288533.4.log dbv file=+DATA/rac/datafile/users.264.716288559 blocksize=8192 USERID=system/SYS logfile=users.264.716288559.5.log
without password
set feedback off head off echo off linesize 200 pagesize 1000 spool /tmp/dbvchk.txt select 'dbv file=' || name || ' blocksize='|| block_size || ' USERID=\''/ as sysdba\'' logfile=' ||substr(name, instr(name, '/', -1, 1) +1) ||'.' || file# || '.log' from v$datafile /
=======
corrupt the datafile
dd if=/dev/zero of=/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 bs=8k conv=notrunc seek=10 count=1
dbv file=/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6 blocksize=8192 USERID=\'/ as sysdba\' logfile=data_D-ORCL_TS-USERS_FNO-6.6.log
cat data_D-ORCL_TS-USERS_FNO-6.6.log
DBVERIFY: Release 12.2.0.1.0 - Production on Fri Dec 30 06:30:04 2022
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6
Page 10 is marked corrupt
Corrupt block relative dba: 0x0180000a (file 6, block 10)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 30
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 5
Total Pages Failing (Index): 0
Total Pages Processed (Other): 576
Total Pages Processed (Seg) : 11
Total Pages Failing (Seg) : 0
Total Pages Empty : 17
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1839277 (0.1839277)
[oracle@wcp12cr2 Datafile]$
select * from v$database_block_corruption ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO CON_ID
---------- ---------- ---------- ------------------ --------- ----------
6 10 1 0 ALL ZERO 0
repair failure preview;
repair failure noprompt;
RMAN> list failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
1761 HIGH OPEN 30-12-2022 06:42:46 Datafile 6: '/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6' contains one or more corrupt blocks
==========
RMAN> advise failure;
Database Role: PRIMARY
List of Database Failures
=========================
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
1761 HIGH OPEN 30-12-2022 06:42:46 Datafile 6: '/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6' contains one or more corrupt blocks
analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
no manual actions available
Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Perform block media recovery of block 10 in file 6
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1375281163.hm <<<< check this file
RMAN>
[oracle@wcp12cr2 Datafile]$ cat /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_1375281163.hm
# block media recovery
recover datafile 6 block 10;
=====
define file_no=6
define block_value=8
set linesize 300
col SEGMENT_NAME for a20
col TABLESPACE_NAME for a20
SELECT segment_name, TABLESPACE_NAME,segment_type,file_id, block_id, blocks
FROM dba_extents
WHERE file_id = &file_no
--AND ( &block_value BETWEEN block_id AND ( block_id + blocks -1 ) )
;
define file_id=6
define block_id=144
col OWNER for a15
col SEGMENT_NAME for a20
col TABLESPACE_NAME for a20
SELECT relative_fno, owner, segment_name, segment_type ,file_id, block_id, blocks
FROM dba_extents
WHERE file_id = &file_id
and block_id=&block_id
;
RELATIVE_FNO OWNER SEGMENT_NAME SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS
------------ --------------- -------------------- ------------------------ ---------- ---------- ----------
6 OJVMSYS OJDS$BINDINGS$ TABLE 6 144 8
define file_number=6
define BLOCK_NUMBER=144
SELECT relative_fno, owner, segment_name, segment_type ,file_id, block_id, blocks FROM DBA_EXTENTS WHERE FILE_ID= &file_number
AND &BLOCK_NUMBER BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1;
RELATIVE_FNO OWNER SEGMENT_NAME SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS
------------ --------------- -------------------- ------------------------ ---------- ---------- ----------
6 OJVMSYS OJDS$BINDINGS$ TABLE 6 144 8
5 comments:
Nice post!
http://anuj-singh.blogspot.co.uk/2012_01_19_archive.html
set numf 9999999999999999
select * from v$database_block_corruption ;
set pause on pagesize 200 linesize 300
column segment_name format a25
column segment_type format a25
select owner,tablespace_name,segment_name, segment_type, block_id, blocks from dba_extents ,v$database_block_corruption
where file_id = &file_no
and ( &block_value between block_id and ( block_id + blocks ) )
/
All in one ...
set linesize 200 pagesize 200
col ownerobj for a50
with mytab as (
select
e.owner ||'.'|| e.segment_name ownerobj,
e.partition_name part_name,
e.segment_type seg_type,
e.tablespace_name ts_name
from dba_extents e, v$database_block_corruption c
where 1=1
and e.file_id = c.file#
and c.block# between e.block_id and e.block_id + e.blocks-1
)
select distinct ownerobj, ts_name, seg_type from mytab;
set numf 9999999999999999
select * from v$database_block_corruption ;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----------------- ----------------- ----------------- ------------------ ---------
56 79746 126 15903786851 NOLOGGING
58 280985 103 15903872579 NOLOGGING
set linesize 300 pagesize 300
select
substrb(e.owner,1,20) DB_OWNER,
substrb(e.segment_name,1,30 ) OBJ_NAME,
substrb(e.partition_name,1,20 ) PART_NAME,
substrb(e.segment_type,1,5) TYPE,
substrb(e.tablespace_name,1,15) TS_NAME
from dba_extents e
where 1=1
and e.file_id = 56
and 79746 between e.block_id and e.block_id+ e.blocks-1;
Thanks. the scripts were very useful for prod issue faced
Post a Comment