How to find all the corrupted objects in your Database
                                                 Last update (2014-11-19 00:03:58)
                                                                                                                   Date added (2008-02-13 17:04:57)

Summary
It is very common to have some corrupted objects. To diagnose the problem you can follow the steps below

How to
1. First populate the v$database_block_corruption view with information on all the corrupted blocks by executing the following command from rman.
rman target / catalog rman/*****@rman
Recovery Manager: Release 10.2.0.2.0 - Production on Wed May 6 10:24:44 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: MISPROD (DBID=266844531)
connected to recovery catalog database

RMAN>BACKUP VALIDATE CHECK LOGICAL DATABASE;

Starting backup at 06-MAY-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=3562 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile fno=00083 name=/ora3/MISPROD/datafiles/presper_fact_tbl_02.dbf
input datafile fno=00080 name=/ora5/MISPROD/datafiles/stage_cust_tbl_01.dbf
input datafile fno=00067 name=/ora5/MISPROD/datafiles/shadow_idx_01.dbf
input datafile fno=00013 name=/ora5/MISPROD/datafiles/stage_stage_tbl_01.dbf
input datafile fno=00074 name=/ora2/MISPROD/datafiles/presper_fact_tbl_01.dbf
input datafile fno=00018 name=/ora1/MISPROD/datafiles/undotbs_05.dbf
input datafile fno=00075 name=/ora3/MISPROD/datafiles/presper_fact_idx_01.dbf
input datafile fno=00016 name=/ora3/MISPROD/datafiles/ods_ods_idx_01.dbf
input datafile fno=00023 name=/ora3/MISPROD/datafiles/mtr_fact_tbl_04.dbf
channel ch02: backup set complete, elapsed time: 00:05:35
channel ch02: starting full datafile backupset
channel ch02: specifying datafile(s) in backupset
input datafile fno=00008 name=/ora4/MISPROD/datafiles/owb102_01.dbf
input datafile fno=00064 name=/ora3/MISPROD/datafiles/stage_tbl_01.dbf
input datafile fno=00033 name=/ora5/MISPROD/datafiles/mtr_fact_idx_01.dbf
input datafile fno=00007 name=/ora5/MISPROD/datafiles/disco_olap_01.dbf
input datafile fno=00069 name=/ora5/MISPROD/datafiles/ods_idx_01.dbf
input datafile fno=00002 name=/ora2/MISPROD/datafiles/indexes_01.dbf
input datafile fno=00014 name=/ora2/MISPROD/datafiles/mtr_fact_tbl_02.dbf
input datafile fno=00026 name=/ora2/MISPROD/datafiles/mtr_fact_idx_03.dbf
input datafile fno=00003 name=/ora4/MISPROD/datafiles/sysaux01.dbf
input datafile fno=00004 name=/ora4/MISPROD/datafiles/users01.dbf
input datafile fno=00066 name=/ora4/MISPROD/datafiles/shadow_tbl_01.dbf
input datafile fno=00032 name=/ora4/MISPROD/datafiles/mtr_fact_tbl_01.dbf
input datafile fno=00021 name=/ora4/MISPROD/datafiles/fin_fact_tbl_01.dbf
input datafile fno=00001 name=/ora4/MISPROD/datafiles/system01.dbf
input datafile fno=00006 name=/ora4/MISPROD/datafiles/disco_01.dbf
input datafile fno=00034 name=/ora4/MISPROD/datafiles/msdm_01.dbf
input datafile fno=00070 name=/ora4/MISPROD/datafiles/presper_dims_tbl_01.dbf
input datafile fno=00022 name=/ora4/MISPROD/datafiles/fin_fact_idx_01.dbf
input datafile fno=00071 name=/ora4/MISPROD/datafiles/presper_dims_idx_01.dbf
input datafile fno=00045 name=/ora4/MISPROD/datafiles/hr_fact_idx_01.dbf
input datafile fno=00005 name=/ora4/MISPROD/datafiles/applexpr_01.dbf
input datafile fno=00052 name=/ora4/MISPROD/datafiles/con_dims_tbl_01.dbf
input datafile fno=00081 name=/ora4/MISPROD/datafiles/stage_cust_idx_01.dbf
input datafile fno=00028 name=/ora4/MISPROD/datafiles/mtr_dims_tbl_01.dbf
input datafile fno=00044 name=/ora4/MISPROD/datafiles/hr_fact_tbl_01.dbf
input datafile fno=00097 name=/ora4/MISPROD/datafiles/presmsdm_fact_idx_01.dbf
input datafile fno=00061 name=/ora4/MISPROD/datafiles/boxirepo_01.dbf
input datafile fno=00068 name=/ora4/MISPROD/datafiles/ods_tbl_01.dbf
input datafile fno=00054 name=/ora4/MISPROD/datafiles/con_dimb_tbl_01.dbf
input datafile fno=00060 name=/ora4/MISPROD/datafiles/dwsrc_01.dbf
channel ch03: backup set complete, elapsed time: 00:06:56
channel ch03: starting full datafile backupset
channel ch03: specifying datafile(s) in backupset
input datafile fno=00082 name=/ora4/MISPROD/datafiles/stage_cust_tbl_02.dbf
input datafile fno=00103 name=/ora3/MISPROD/datafiles/undotbs_03.dbf
input datafile fno=00104 name=/ora5/MISPROD/datafiles/undotbs_04.dbf
input datafile fno=00038 name=/ora5/MISPROD/datafiles/mtr_exp_tbl_01.dbf
input datafile fno=00096 name=/ora5/MISPROD/datafiles/presmsdm_fact_tbl_01.dbf
input datafile fno=00102 name=/ora2/MISPROD/datafiles/undotbs_02.dbf
input datafile fno=00029 name=/ora1/MISPROD/datafiles/users02.dbf
input datafile fno=00027 name=/ora1/MISPROD/datafiles/fin_fact_tbl_02.dbf
input datafile fno=00020 name=/ora1/MISPROD/datafiles/mtr_fact_tbl_03.dbf
channel ch01: backup set complete, elapsed time: 00:03:35
channel ch01: starting full datafile backupset
channel ch01: specifying datafile(s) in backupset
input datafile fno=00101 name=/ora4/MISPROD/datafiles/undotbs_01.dbf
input datafile fno=00015 name=/ora3/MISPROD/datafiles/ods_ods_tbl_01.dbf
input datafile fno=00017 name=/ora3/MISPROD/datafiles/indexes_02.dbf
input datafile fno=00062 name=/ora5/MISPROD/datafiles/prestage_tbl_01.dbf
input datafile fno=00024 name=/ora5/MISPROD/datafiles/mtr_fact_tbl_05.dbf
input datafile fno=00040 name=/ora5/MISPROD/datafiles/hr_dims_tbl_01.dbf
input datafile fno=00011 name=/ora2/MISPROD/datafiles/stage_source_tbl_01.dbf
input datafile fno=00019 name=/ora2/MISPROD/datafiles/prestage_tbl_02.dbf
input datafile fno=00025 name=/ora1/MISPROD/datafiles/mtr_fact_idx_02.dbf
input datafile fno=00030 name=/ora1/MISPROD/datafiles/stage_cust_idx_02.dbf
channel ch02: backup set complete, elapsed time: 00:04:05
channel ch02: starting full datafile backupset
channel ch02: specifying datafile(s) in backupset
input datafile fno=00009 name=/ora5/MISPROD/datafiles/owb102_idx_01.dbf
input datafile fno=00010 name=/ora5/MISPROD/datafiles/owf_01.dbf
input datafile fno=00089 name=/ora5/MISPROD/datafiles/ods_msdm_tbl_01.dbf
channel ch03: backup set complete, elapsed time: 00:00:25
channel ch03: starting full datafile backupset
channel ch03: specifying datafile(s) in backupset
input datafile fno=00065 name=/ora5/MISPROD/datafiles/stage_idx_01.dbf
input datafile fno=00012 name=/ora5/MISPROD/datafiles/stage_source_idx_01.dbf
input datafile fno=00099 name=/ora5/MISPROD/datafiles/helpdesk_01.dbf
input datafile fno=00050 name=/ora5/MISPROD/datafiles/mis_tbl_01.dbf
input datafile fno=00098 name=/ora5/MISPROD/datafiles/fc_tbs_01.dbf
channel ch01: backup set complete, elapsed time: 00:00:15
channel ch01: starting full datafile backupset
channel ch01: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ch02: backup set complete, elapsed time: 00:00:02
Finished backup at 06-MAY-09

All the corrupted block will be listed in the view v$database_block_corruption
SQL> SELECT * FROM v$database_block_corruption;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION TYPE
---------- ---------- ---------- ------------------ ---------
      2001     416757          1         6.0555E+12 LOGICAL
      2001     421106          1         6.0555E+12 LOGICAL
      1794    1135369         28         6.0555E+12 LOGICAL
      2001     420726          1         6.0555E+12 LOGICAL
      1744      53364          1         6.0555E+12 LOGICAL
      2001     417012          1         6.0555E+12 LOGICAL
      2001     420920          1         6.0555E+12 LOGICAL
      1794    1104580          1         6.0555E+12 LOGICAL
      1794    1135413         16         6.0555E+12 LOGICAL
      2001     420853          1         6.0555E+12 LOGICAL
      2017     581347          2         6.0556E+12 LOGICAL
      2001     421172          1         6.0555E+12 LOGICAL
      2017     588707          2         6.0556E+12 LOGICAL
2. Login as SYS and create a temporary table called corrupted_objects which will help us populate the corrupted objects with the procedure Find_Corrupted. To have faster results create a table helpdesk.all_extents which holds all the database extents and an index for fast quering it.
1. CREATE TABLE sys.corrupted_objects
CREATE TABLE sys.CORRUPTED_OBJECTS
(
  OWNER           VARCHAR2(30 BYTE),
  SEGMENT_NAME    VARCHAR2(81 BYTE),
  PARTITION_NAME  VARCHAR2(30 BYTE),
  SEGMENT_TYPE    VARCHAR2(18 BYTE),
  FILE#           NUMBER,
  BLOCK#          NUMBER
)
2. CREATE TABLE helpdesk.all_extents as select * from dba_extents; (it will take some time...) 3. CREATE INDEX HELPDESK.ALL_EXTENTS_IDX ON HELPDESK.ALL_EXTENTS (FILE_ID, BLOCK_ID); 4.Create procedure Find_Corrupted CREATE OR REPLACE PROCEDURE Find_Corrupted IS CURSOR C IS SELECT FILE# file_id, BLOCK# block_id FROM v$database_block_corruption; BEGIN FOR c1 IN C LOOP INSERT INTO sys.corrupted_objects (owner, segment_name, segment_type, partition_name, FILE#, BLOCK#) SELECT /*+ index(A) */ owner, segment_name, segment_type, partition_name, c1.file_id, c1.block_id FROM helpdesk.all_extents A WHERE file_id=c1.file_id AND c1.block_id BETWEEN block_id AND block_id+blocks-1; COMMIT; END LOOP; EXCEPTION WHEN OTHERS THEN NULL; END Find_Corrupted; /
3. As SYS execute procedure Find_Corrupted and select all the objects populated in the table corrupted_objects. Executing the procedure Find_Corrupted will take some time!
SELECT DISTINCT(segment_name), segment_type, owner, partition_name FROM sys.corrupted_objects;

SEGMENT_NAME                  SEGMENT_TYPE       OWNER                          PARTITION_NAME
----------------------------- ------------------ ------------------------------ --------------------
USAGE_CUSTOMER_DAILY_AGG      TABLE SUBPARTITION TARGET_DW                      P20100827_S3
USAGE_CUSTOMER_DAILY_AGG_IF6  INDEX SUBPARTITION TARGET_DW                      P20090426_S3
USAGE_CUSTOMER_DAILY_AGG_IF2  INDEX SUBPARTITION TARGET_DW                      P20090227_S1
FAULT_FCT_PRM_UIDX            INDEX PARTITION    PRESENT_PERIF                  Y2010_Q3_M08
USAGE_CUSTOMER_DAILY_AGG      TABLE SUBPARTITION TARGET_DW                      P20100827_S1
USAGE_CUSTOMER_DAILY_AGG_IF1  INDEX SUBPARTITION TARGET_DW                      P20090228_S8
USAGE_CUSTOMER_DAILY_AGG      TABLE SUBPARTITION TARGET_DW                      P20100828_S7
USAGE_CUSTOMER_DAILY_AGG_IF6  INDEX SUBPARTITION TARGET_DW                      P20090227_S1
USAGE_CUSTOMER_DAILY_AGG      TABLE SUBPARTITION TARGET_DW                      P20100828_S5
USAGE_CUSTOMER_DAILY_AGG_IF1  INDEX SUBPARTITION TARGET_DW                      P20090228_S4
KPIMIS_MONTHLY_AGG_UIDX       INDEX PARTITION    PRESENT_PERIF                  Y2009_Q3_M07
For more details Note:28814.1 Metalink

To solve the problem with LOGICAL corruption type you have to move or rebuild the segments.

After moving tables and rebuilding indexes you have to re-check individualy the datafiles
rman target / catalog rman/****@rman
Recovery Manager: Release 10.2.0.2.0 - Production on Wed May 6 10:24:44 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: MISPROD (DBID=266844531)
connected to recovery catalog database

RMAN>RUN { 
allocate channel ch01 TYPE 'SBT_TAPE';
allocate channel ch02 TYPE 'SBT_TAPE';
allocate channel ch03 TYPE 'SBT_TAPE';
send 'NB_ORA_CLIENT=dwhprod1';
BACKUP VALIDATE CHECK LOGICAL DATAFILE 2001;
BACKUP VALIDATE CHECK LOGICAL DATAFILE 1794;
BACKUP VALIDATE CHECK LOGICAL DATAFILE 1744;
BACKUP VALIDATE CHECK LOGICAL DATAFILE 2017;
}

    
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search