Oracle Database Corruption Issues (Doc ID 1088018.1)

Oracle Database Corruption Issues (Doc ID 1088018.1)

Oracle block corruptions check via DBV

DBV untility - to check oracle Database Corruption

 

DB Verify
DB_VERIFY (dbv)
Oracle dbvverify Utility



col file_name format a20
select substr(name,instr(name,'/',-1)+1) file_name from v$datafile

FILE_NAME
--------------------
system01.dbf
sysaux01.dbf
users_compress.dbf
users01.dbf
example01.dbf
anujtest.dbf
tsapexf01.dbf
tsapexu01.dbf
test.dbf
rman.dbf
drop.dbf
anuj_perfstat.dbf
undotbR.dbf







oracle@apt-amd-02:~> cat dbv.sh

# !/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/opt/app/oracle
export ORACLE_HOME=/opt/app/oracle/product/11.2
export PATH=${ORACLE_HOME}/bin:$PATH
sqlplus -s "/ as sysdba" <<EOF
set echo off feedback off verify off pages 0 termout off linesize 150
spool dbv-${ORACLE_SID}.sh
select 'dbv file=' || name || ' blocksize=' || block_size || ' feedback=' || round(blocks*.10,0) ||' logfile=/tmp/'||substr(name,instr(name,'/',-1)+1)||'.log'
from v\$datafile;
spool off
set feedback on verify on pages24 echo on termout on
EOF




this script will create

-rw-r--r-- 1 oracle oinstall     1963 2012-01-19 09:41 dbv-orcl.sh




sh dbv.sh
dbv file=/opt/app/oracle/oradata/orcl/system01.dbf blocksize=8192 feedback=11136 logfile=/tmp/system01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/sysaux01.dbf blocksize=8192 feedback=10624 logfile=/tmp/sysaux01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/users_compress.dbf blocksize=8192 feedback=640 logfile=/tmp/users_compress.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/users01.dbf blocksize=8192 feedback=8688 logfile=/tmp/users01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/example01.dbf blocksize=8192 feedback=1280 logfile=/tmp/example01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/anujtest.dbf blocksize=8192 feedback=128 logfile=/tmp/anujtest.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/tsapexf01.dbf blocksize=8192 feedback=640 logfile=/tmp/tsapexf01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/tsapexu01.dbf blocksize=8192 feedback=1408 logfile=/tmp/tsapexu01.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/test.dbf blocksize=8192 feedback=256 logfile=/tmp/test.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/rman.dbf blocksize=8192 feedback=640 logfile=/tmp/rman.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/drop.dbf blocksize=8192 feedback=26 logfile=/tmp/drop.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/anuj_perfstat.dbf blocksize=8192 feedback=12800 logfile=/tmp/anuj_perfstat.dbf.log
dbv file=/opt/app/oracle/oradata/orcl/undotbR.dbf blocksize=8192 feedback=6400 logfile=/tmp/undotbR.dbf.log




dbv help=y

DBVERIFY: Release 11.2.0.1.0 - Production on Thu Jan 19 09:48:16 2012

Copyright (c) 1982, 2009, 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)           

refer this link for file name

http://anuj-singh.blogspot.com/2012/01/oracle-how-to-get-only-file-name-with.html

Oracle How to get only file name ( with out path )

Oracle file name

Oracle How to get only file name ( with out path )

Oracle file name

 select substr(name,instr(name,'/',-1)+1) file_name from v$datafile;

FILE_NAME
--------------------
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
example01.dbf

set linesize 200
col file_name format a15
col name format a70

select name, substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) file_name from v$datafile;

NAME                                                                   FILE_NAME
---------------------------------------------------------------------- ---------------
/u01/app/oracle/oradata/vihaan/system01.dbf                            system01
/u01/app/oracle/oradata/vihaan/sysaux01.dbf                            sysaux01
/u01/app/oracle/oradata/vihaan/undotbs01.dbf                           undotbs01
/u01/app/oracle/oradata/vihaan/users01.dbf                             users01
/u01/app/oracle/oradata/vihaan/example01.dbf                           example01

set linesize 200
col file_name format a15
col name format a70
col path for a50
select name,substr(name, 1, instr(name, '/',-1)) path , substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) file_name from v$datafile;

NAME                                                                   PATH                                               FILE_NAME
---------------------------------------------------------------------- -------------------------------------------------- ---------------
/u01/app/oradata/Orclsex/system01.dbf                                  /u01/app/oradata/Orclsex/                          system01
/u01/app/oradata/Orclsex/sysaux01.dbf                                  /u01/app/oradata/Orclsex/                          sysaux01
/u01/app/oradata/Orclsex/undotbs01.dbf                                 /u01/app/oradata/Orclsex/                          undotbs01
/u01/app/oradata/Orclsex/users01.dbf                                   /u01/app/oradata/Orclsex/                          users01

set linesize 200
col file_name format a50
col name format a70
col path for a50
select FILE_NAME,substr(FILE_NAME, 1, instr(FILE_NAME, '/',-1)) path , substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1, instr(substr(FILE_NAME,instr(FILE_NAME,'/',-1)+1),'.')-1 ) file_name from dba_data_files
where TABLESPACE_NAME='USERS' ;

FILE_NAME                                          PATH                                               FILE_NAME
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
/u01/app/oradata/Orclsex/users01.dbf               /u01/app/oradata/Orclsex/                          users01
/u01/app/oradata/Orclsex/users02.dbf               /u01/app/oradata/Orclsex/                          users02
/u01/app/oradata/Orclsex/users03.dbf               /u01/app/oradata/Orclsex/                          users03
/u01/app/oradata/Orclsex/users04.dbf               /u01/app/oradata/Orclsex/                          users04
/u01/app/oradata/Orclsex/users05.dbf               /u01/app/oradata/Orclsex/                          users05
/u01/app/oradata/Orclsex/users06.dbf               /u01/app/oradata/Orclsex/                          users06
/u01/app/oradata/Orclsex/users07.dbf               /u01/app/oradata/Orclsex/                          users07
/u01/app/oradata/Orclsex/users08.dbf               /u01/app/oradata/Orclsex/                          users08
/u01/app/oradata/Orclsex/users09.dbf               /u01/app/oradata/Orclsex/                          users09
/u01/app/oradata/Orclsex/users10.dbf               /u01/app/oradata/Orclsex/                          users10
/u01/app/oradata/Orclsex/users11.dbf               /u01/app/oradata/Orclsex/                          users11
/u01/app/oradata/Orclsex/users12.dbf               /u01/app/oradata/Orclsex/                          users12
/u01/app/oradata/Orclsex/users13.dbf               /u01/app/oradata/Orclsex/                          users13

13 rows selected.

====

SELECT    'set newname for datafile '

       || file#

       || ' to ''/dumps/UGARB/'

       || substr(name,instr(name,'/',-1)+1, instr(substr(name,instr(name,'/',-1)+1),'.')-1 ) ||'.dbf'

       || ''';'

  FROM v$datafile;