Database consistency is the most important aspect in order to smoothen and fasten various database operations. Being a competent database system, Oracle is widely used in organizations; however, it is equally dangerous when its database meets corruption. The post brings you a number of ways that will help you identify and fix Oracle database corruption.
1. DBVerify
DBVerify is an external tool that checks the integrity of offline as well as online datafiles and allows you to validate these files. However, you can use the utility to validate the cache-managed blocks and not redo logs or control files. You can use the following command to validate disk blocks of one datafile:
C:\>dbv file=C:\Oracle\oradata\TSH1\system01.dbf feedback=1000 blocksize=5125
With the command, you will verify the file named ‘C:\Oracle\oradata\TSH1\system01.dbf’. It shows one period after processing a set of 1000 pages of the file. If the block size of the file is not 2 KB, it is required for you to specify ‘blocksize’ parameter.
Besides, if you need to validate a Segment, the following command will perform the task:
“C:\>DBV USERID=FGC/ORACLE SEGMENT_ID=6.5.11601 “
Here, USERID is used for username and password, and SEGMENT_ID specifies the segment that has to be verified.
2. ANALYZE …. VALIDATE Structure
This command allows you to verify the structure of objects within your Oracle database, such as table, table partition, index, index partition, object reference, cluster, and index-organized table. If it detects corruption, the corresponding rows move to the INVALID_ROWS table.
To create INVALID_ROWS table, run the following:
SQL> @C:\Oracle\901\rdbms\admin\UTLVALID.SQL
You can validate the structure of a table by running the following:
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE;
Run the following command to validate the structure of a table named ‘scott.emp’ with all the indexes:
SQL> ANALYZE TABLE scott.emp VALIDATE STRUCTURE CASCADE;
You can validate the structure of an index with the following command:
SQL> ANALYZE INDEX scott.pk_emp VALIDATE STRUCTURE;
3. DB_BLOCK_CHECKING Parameter
You can use the DB_BLOCK_CHECKING parameter to check the internal consistency of the database blocks after each DML operation is performed. When you set the DB_BLOCK_CHECKING parameter to [TRUE|HIGH], Oracle walks through the data of the block to check if it is self-consistent. However, know that the parameter causes overhead to the server; therefore, it is recommended to use the parameter as [TRUE|HIGH], if the server is able to accept the overhead. Moreover, if you set the parameter as ‘FULL’, all block headers are checked.
4. Oracle Recovery Manager (RMAN)
The Oracle Recovery Manager validates data using VALIDATE, BACKUP VALIDATE, and RESTORE VALIDATE commands.
You can run the following BACKUP VALIDATE command to validate the database:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
The output given by the above command is the same that you received during backup; however, it does not create any backup. If there is corruption in a block, it is shown in V$DATABASE_BLOCK_CORRUPTION view and in the RMAN output.
Note that the command performs a check for physical corruption. If you want it to check logical corruption, add the clause CHECK LOGICAL. For checking logical corruption, you need to run the following command:
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
Besides, the Oracle Recovery Manager (RMAN) uses RESTORE VALIDATE command to validate the data of backup files. The command can be used as follows:
RMAN> RESTORE DATABASE VALIDATE;
RMAN> RESTORE ARCHIVELOG ALL VALIDATE;
5. DBMS_REPAIR
The DBMS_REPAIR package is a part of database installation to help detect and repair corruption in an Oracle database. The procedure includes two administration tables and detects corrupt blocks, repairs them, and rebuilds freelists.
These are the most common corruption detection and repair methods. You can also use other methods like full database recovery, individual datafile recovery, etc.
