Oracle DBA

Database block corruption is one of the most dreaded issues for any DBA. Whether caused by storage faults, memory glitches, or human error, corruption can lead to data loss or application downtime if not handled carefully. Oracle provides multiple tools and techniques to detect, validate, and repair corruptions, both in non-CDB and multitenant (CDB/PDB) environments.

1. RMAN Validation

The Recovery Manager (RMAN) provides built-in commands to check datafile blocks for corruption, without performing actual backup or restore.

Basic Validation

RMAN> VALIDATE DATABASE;

This scans the entire database for corrupt blocks.

You can also validate a specific tablespace or datafile:

RMAN> VALIDATE TABLESPACE users;
RMAN> VALIDATE DATAFILE 5;

Backup Validate

Instead of performing a backup, you can simulate one to check for corruption:

RMAN> BACKUP VALIDATE DATABASE;

Restore Validate

Similarly, RESTORE VALIDATE checks if a restore would succeed:

RMAN> RESTORE DATABASE VALIDATE;

Logical Check

For deeper checks, include the CHECK LOGICAL clause:

RMAN> VALIDATE DATABASE CHECK LOGICAL;

This adds logical structure validation in addition to physical checks.

Reporting Corrupt Blocks

Any corruption found is logged in:

SELECT * FROM v$database_block_corruption;

---

2. Multitenant (CDB/PDB) RMAN Validation

In multitenant architecture, validation can be targeted to specific containers.

Validate only the root:

RMAN> VALIDATE DATABASE ROOT;

Validate a particular PDB:

RMAN> VALIDATE PLUGGABLE DATABASE pdb1;

Or connect directly to a PDB and run validation within it.

Logical checks are supported here too:

RMAN> VALIDATE PLUGGABLE DATABASE pdb1 CHECK LOGICAL;

---

3. DBVerify (DBV)

Oracle’s DBVerify utility (dbv) is an external command-line tool that scans datafiles for corruption.

Example:

dbv file=/u01/oradata/ORCL/users01.dbf blocksize=8192

• Works both on offline and online datafiles.
• Does not work on control files or redo logs.

This is particularly useful for checking individual datafiles outside of RMAN.

---

4. ANALYZE Command

The SQL ANALYZE command allows validation of object structures.

Example for a table:

ANALYZE TABLE employees VALIDATE STRUCTURE CASCADE;

• Corrupted rows are recorded in the INVALID_ROWS table.
• Can be used for indexes as well.

This method is object-level and helps detect structural issues.

---

5. DB_BLOCK_CHECKING Parameter

You can enable in-memory block consistency checks with the DB_BLOCK_CHECKING parameter.

ALTER SYSTEM SET db_block_checking=FULL SCOPE=BOTH;

• Options include OFF, LOW, MEDIUM, FULL.
• FULL provides the highest protection but adds overhead.
• Recommended for critical systems if performance impact is acceptable.

---

6. Block Media Recovery (BMR)

When specific blocks are corrupted, Block Media Recovery (BMR) allows recovery without restoring the entire datafile.

Example:

RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 100;

Or recover multiple blocks from the corruption list:

SELECT * FROM v$database_block_corruption;

Then:

RMAN> BLOCKRECOVER CORRUPTION LIST;

This saves time and minimizes downtime compared to full file restores.

---

7. DBMS_REPAIR Package

Oracle’s DBMS_REPAIR package provides PL/SQL procedures to detect and handle corruption.

Setup

First, create repair tables:

BEGIN
  DBMS_REPAIR.admin_tables (
    table_name  => 'REPAIR_TABLE',
    table_type  => dbms_repair.repair_table,
    action      => dbms_repair.create_action,
    tablespace  => 'USERS'
  );
END;
/

Detect Corruption

BEGIN
  DBMS_REPAIR.check_object (
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    repair_table_name => 'REPAIR_TABLE'
  );
END;
/

Skip Corrupt Blocks

BEGIN
  DBMS_REPAIR.skip_corrupt_blocks (
    schema_name => 'HR',
    object_name => 'EMPLOYEES',
    flags       => DBMS_REPAIR.SKIP_FLAG
  );
END;
/

This allows queries to bypass bad blocks.

Orphan Key Handling

Create an orphan key table and clean up orphaned rows if indexes are affected.

---

Conclusion

Corruption is a serious concern, but Oracle equips DBAs with powerful tools:

• RMAN Validation: database-wide or PDB-specific checks.
• DBVerify: standalone file verification.
• ANALYZE: object-level structure validation.
• DB_BLOCK_CHECKING: proactive protection at runtime.
• Block Media Recovery: fast, targeted block recovery.
• DBMS_REPAIR: programmatic detection and handling.

By combining these methods, you can quickly detect corruption, minimize downtime, and ensure data integrity.