Check database, table, index, and datafile corruption in Oracle

In Oracle, You have different methods to check corruption at a different level:

1. RMAN is used the check the existence of physical and logical corruption:

--Check the complete database
RMAN> BACKUP CHECK LOGICAL VALIDATE DATABASE;

--Check the one datafile or specified datafiles:
RMAN> BACKUP CHECK LOGICAL VALIDATE DATAFILE 1;

2. DBVerify utility is used to verify the physical corruption:

dbv file=C:\<path>/\datafilename>.dbf blocksize=8192

3. Use Analyze command to validate the corruption in TABLE or INDEX level. It will detect both logical and physical corruption at the object level. When you run this command it locks the object so try to run in off-peak hours.

-- Used for table
Analyze table <user>.<table_name> validate structure cascade [online];

--Used for index
Analyze index <user>.<index_name/cluster_name> validate structure;

--Used for table partition
Analyze table <user>.<table_name> partition <partition_name> validate structure  cascade;

4. Full EXP backup also helps to detect logical and physical corruption.

--Windows
exp system/<pwd>@<oraclesid> full=y log=check.log file=nul

--Linux
exp system/<system_pwd> full=y log=check.log file=/dev/null