dbv for ASM disk

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