Tag Archives: Script to get list of all datafiles

DBV (DBVERIFY) utility for check corruption in Oracle DB

Leave a reply

DBV (DBVERIFY) utility for check corruption in DB

DBVERIFY is the command line utility used in Oracle to check the physical integrity of the database files and segments.
Its verify the corruption blocks and segments. You can also verify backup before restore.

Note: DBVERIFY is only for use with datafiles, it will not work against control files or redo logs.

DBVERIFY to Validate Disk Blocks of a Single Datafile

dbv FILE=C:\ORACLE\ORADATA\XE\PDB2\USERS01.DBF FEEDBACK=100

Output:

DBVERIFY: Release 18.0.0.0.0 - Production on Tue Sep 24 16:09:55 2019
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = C:\ORACLE\ORADATA\XE\PDB2\USERS01.DBF
.......
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 244
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 139
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 257
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1813568 (0.1813568)

DBVerify to validate the Segment
Note: User must have sysdba privilieges

col segment_name for a12
col tablespace_name for a15
select tablespace_name, segment_name, TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK
from sys.sys_user_segs where SEGMENT_NAME like 'TEST%';

TABLESPACE_NAME SEGMENT_NAME TABLESPACE_ID HEADER_FILE HEADER_BLOCK
--------------- ------------ ------------- ----------- ------------
SYSTEM TEST10 0 1 32776
SYSTEM TEST20 0 1 32784

-- User has the sysdba priviliges
dbv userid=test/test SEGMENT_ID=0.1.32784

Script to get list of DBV commands of all datafiles

set echo off
set feedback off
set verify off
set pages 0
set termout off
set linesize 150
spool dbv.cmd
select 'dbv file=' || name || ' blocksize=' || block_size || ' feedback=' || round(blocks*.10,0) from v$datafile;
spool off

Check the complete command of DBV

C:\windows\system32>dbv help=y
DBVERIFY: Release 18.0.0.0.0 - Production on Wed Sep 25 11:10:24 2019
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Keyword     Description                    (Default)
----------------------------------------------------
FILE        File to Verify                 (NONE)
START       Start Block                    (First Block of File)
END         End Block                      (Last Block of File)
BLOCKSIZE   Logical Block Size             (8192)
LOGFILE     Output Log                     (NONE)
FEEDBACK    Display Progress               (0)
PARFILE     Parameter File                 (NONE)
USERID      Username/Password              (NONE)
SEGMENT_ID  Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN    Highest Block SCN To Verify    (NONE)
            (scn_wrap.scn_base OR scn)