Run DBV/RMAN validate to check for corrupt block
dbv file=/oradata/ora11gR2/demo01.dbf blocksize=8192
backup validate check logical datafile 7;
$ rman target /
RMAN> backup validate check logical database;
$ sqlplus / as sysdba;
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jun 16 15:58:01 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SHPMLS (DBID=2038560152)
RMAN> backup validate check logical database;
Starting backup at 16-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 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=00007 name=/data/oracle/SHPMLS/data02/SHPMLS_data02.dbf
input datafile file number=00001 name=/data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf
input datafile file number=00006 name=/data/oracle/SHPMLS/data01/SHPMLS_data01.dbf
input datafile file number=00003 name=/data/oracle/SHPMLS/data01/SHPMLS_undo01.dbf
input datafile file number=00004 name=/data/oracle/SHPMLS/data02/SHPMLS_undo02.dbf
input datafile file number=00002 name=/data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf
input datafile file number=00008 name=/data/oracle/SHPMLS/data01/SHPMLS_idx01.dbf
input datafile file number=00009 name=/data/oracle/SHPMLS/data01/SHPMLS_tools01.dbf
input datafile file number=00005 name=/data/oracle/SHPMLS/data02/SHPMLS_audits01.dbf
input datafile file number=00010 name=/data/oracle/SHPMLS/data01/SHPMLS_users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:53
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 143700 262147 287019648
File Name: /data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 106081
Index 0 5821
Other 0 6542
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 29140 131072 287019399
File Name: /data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 44470
Index 0 48246
Other 0 9216
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3 OK 0 1 131200 287019575
File Name: /data/oracle/SHPMLS/data01/SHPMLS_undo01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 131199
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4 OK 0 1 131200 287019648
File Name: /data/oracle/SHPMLS/data02/SHPMLS_undo02.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 131199
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5 OK 0 12523 25603 287017338
File Name: /data/oracle/SHPMLS/data02/SHPMLS_audits01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 2349
Index 0 10257
Other 0 471
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6 OK 0 1 262149 286870924
File Name: /data/oracle/SHPMLS/data01/SHPMLS_data01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 21309
Index 0 75
Other 0 240759
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7 OK 0 929 393222 286870921
File Name: /data/oracle/SHPMLS/data02/SHPMLS_data02.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 20221
Index 0 215
Other 0 371851
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
8 OK 0 1 131073 286870918
File Name: /data/oracle/SHPMLS/data01/SHPMLS_idx01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 6930
Other 0 124141
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
9 OK 0 1 131073 779282
File Name: /data/oracle/SHPMLS/data01/SHPMLS_tools01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 131071
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
10 OK 0 2433 2560 4515814
File Name: /data/oracle/SHPMLS/data01/SHPMLS_users01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 0
Index 0 0
Other 0 127
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 1864
Finished backup at 16-JUN-18
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ sqlplus / as sysdba;
SQL*Plus: Release 11.2.0.3.0 Production on Sat Jun 16 16:02:07 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from V$DATABASE_BLOCK_CORRUPTION;
no rows selected
select FILE_NAME, file_id
from dba_data_files
where TABLESPACE_NAME='SYSAUX';
select TABLESPACE_NAME,BLOCK_SIZE
from dba_tablespaces
where TABLESPACE_NAME='SYSAUX';
set linesize 150
set pagesize 1000
column tablespace_name format a20
column file_name format a70
select t.tablespace_name, t.block_size, f.file_id, f.file_name
from dba_data_files f, dba_tablespaces t
where t.tablespace_name=f.tablespace_name
order by file_id, tablespace_name, file_name;
TABLESPACE_NAME BLOCK_SIZE FILE_ID FILE_NAME
-------------------- ---------- ---------- ----------------------------------------------------------------------
SYSTEM 8192 1 /data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf
SYSAUX 8192 2 /data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf
UNDOTBS1 8192 3 /data/oracle/SHPMLS/data01/SHPMLS_undo01.dbf
UNDOTBS1 8192 4 /data/oracle/SHPMLS/data02/SHPMLS_undo02.dbf
AUDITS 8192 5 /data/oracle/SHPMLS/data02/SHPMLS_audits01.dbf
PMLS_DATA01 8192 6 /data/oracle/SHPMLS/data01/SHPMLS_data01.dbf
PMLS_DATA01 8192 7 /data/oracle/SHPMLS/data02/SHPMLS_data02.dbf
PMLS_IDX01 8192 8 /data/oracle/SHPMLS/data01/SHPMLS_idx01.dbf
TOOLS 8192 9 /data/oracle/SHPMLS/data01/SHPMLS_tools01.dbf
USERS 8192 10 /data/oracle/SHPMLS/data01/SHPMLS_users01.dbf
10 rows selected.
$ rman target /
RMAN> backup validate check logical datafile 1;
RMAN> backup validate check logical datafile 2;
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jun 16 15:43:56 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SHPMLS (DBID=2038560152)
RMAN> backup validate check logical datafile 1;
Starting backup at 16-JUN-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 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=00001 name=/data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1 OK 0 143700 262147 287018506
File Name: /data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 106081
Index 0 5821
Other 0 6542
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Control File and SPFILE
===============================
File Type Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE OK 0 2
Control File OK 0 1864
Finished backup at 16-JUN-18
RMAN> backup validate check logical datafile 2;
Starting backup at 16-JUN-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 OK 0 29140 131072 287018626
File Name: /data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data 0 44470
Index 0 48246
Other 0 9216
Finished backup at 16-JUN-18
set linesize 150
set pagesize 1000
column tablespace_name format a20
column file_name format a70
select t.tablespace_name, t.block_size, f.file_id, f.file_name
from dba_data_files f, dba_tablespaces t
where t.tablespace_name=f.tablespace_name
order by file_id, tablespace_name, file_name;
TABLESPACE_NAME BLOCK_SIZE FILE_ID FILE_NAME
-------------------- ---------- ---------- ----------------------------------------------------------------------
SYSTEM 8192 1 /data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf
SYSAUX 8192 2 /data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf
UNDOTBS1 8192 3 /data/oracle/SHPMLS/data01/SHPMLS_undo01.dbf
UNDOTBS1 8192 4 /data/oracle/SHPMLS/data02/SHPMLS_undo02.dbf
AUDITS 8192 5 /data/oracle/SHPMLS/data02/SHPMLS_audits01.dbf
PMLS_DATA01 8192 6 /data/oracle/SHPMLS/data01/SHPMLS_data01.dbf
PMLS_DATA01 8192 7 /data/oracle/SHPMLS/data02/SHPMLS_data02.dbf
PMLS_IDX01 8192 8 /data/oracle/SHPMLS/data01/SHPMLS_idx01.dbf
TOOLS 8192 9 /data/oracle/SHPMLS/data01/SHPMLS_tools01.dbf
USERS 8192 10 /data/oracle/SHPMLS/data01/SHPMLS_users01.dbf
10 rows selected.
dbv file=/oradata/ora11gR2/demo01_RESTORED.dbf blocksize=8192
set linesize 150
set pagesize 1000
column tablespace_name format a20
column file_name format a70
select t.tablespace_name, t.block_size, f.file_id, f.file_name
from dba_data_files f, dba_tablespaces t
where t.tablespace_name=f.tablespace_name
order by file_id, tablespace_name, file_name;
set linesize 100
set pagesize 10000
select 'dbv file='||f.file_name||' blocksize='||t.block_size||''
from dba_data_files f, dba_tablespaces t
where t.tablespace_name=f.tablespace_name
order by file_id, file_name;
'DBVFILE='||F.FILE_NAME||'BLOCKSIZE='||T.BLOCK_SIZE||''
----------------------------------------------------------------------------------------------------
dbv file=/data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data01/SHPMLS_undo01.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data02/SHPMLS_undo02.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data02/SHPMLS_audits01.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data01/SHPMLS_data01.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data02/SHPMLS_data02.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data01/SHPMLS_idx01.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data01/SHPMLS_tools01.dbf blocksize=8192
dbv file=/data/oracle/SHPMLS/data01/SHPMLS_users01.dbf blocksize=8192
10 rows selected.
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ dbv file=/data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jun 16 15:52:53 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/SHPMLS/data02/SHPMLS_aux01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 131072
Total Pages Processed (Data) : 44470
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 48246
Total Pages Failing (Index): 0
Total Pages Processed (Other): 9216
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 29140
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 287018626 (0.287018626)
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ dbv file=/data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jun 16 15:54:02 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/SHPMLS/data01/SHPMLS_sys01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 262144
Total Pages Processed (Data) : 106081
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 5821
Total Pages Failing (Index): 0
Total Pages Processed (Other): 6542
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 143700
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 287019089 (0.287019089)
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ dbv file=/data/oracle/SHPMLS/data02/SHPMLS_audits01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jun 16 15:55:00 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/SHPMLS/data02/SHPMLS_audits01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 25600
Total Pages Processed (Data) : 2349
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 10257
Total Pages Failing (Index): 0
Total Pages Processed (Other): 471
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 12523
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 287017338 (0.287017338)
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ dbv file=/data/oracle/SHPMLS/data01/SHPMLS_data01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jun 16 15:55:18 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/SHPMLS/data01/SHPMLS_data01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 262144
Total Pages Processed (Data) : 21309
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 75
Total Pages Failing (Index): 0
Total Pages Processed (Other): 240759
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 286870924 (0.286870924)
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ dbv file=/data/oracle/SHPMLS/data01/SHPMLS_idx01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jun 16 15:56:14 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/SHPMLS/data01/SHPMLS_idx01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 131072
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 6930
Total Pages Failing (Index): 0
Total Pages Processed (Other): 124141
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 286870918 (0.286870918)
SHPMLS@oracle@shaahspmldbp1 [PRD ACTIVE]:/home/oracle
$ dbv file=/data/oracle/SHPMLS/data01/SHPMLS_users01.dbf blocksize=8192
DBVERIFY: Release 11.2.0.3.0 - Production on Sat Jun 16 15:56:38 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /data/oracle/SHPMLS/data01/SHPMLS_users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 127
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 2433
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 4515814 (0.4515814)