Resolving ORA-01122 Error in Oracle Databases

Error Overview

• ORA-01122: “database file failed verification check.”
• Accompanying Errors: Often seen with ORA-01110, ORA-01201, ORA-01202, ORA-01204, and ORA-01205.
• Indicates: A discrepancy between the information in the database file and the control file.

Common Causes

1. File Header Inconsistencies: Mismatch in file sizes or creation times recorded in file headers and control files.
2. Incorrect File Size: The size recorded in the control file doesn’t match the actual file size.
3. Creation Time Discrepancy: Difference in the creation times logged in the file header and the control file.
4. System Change Number (SCN) Issues: Mismatch between the SCN in the file header and the control file.
5. Incorrect File Number: The file number recorded in the file header is incorrect.
6. Wrong File Type: The file type number in the file header doesn’t match a data file type.
7. Database ID Mismatch: The database ID recorded in the file header doesn’t match the one in the control file.

Diagnostic Steps

1. Check File Status: Look at the v$datafile view to determine which files are online or offline.
2. Dump Data File Headers: Use the command alter session set events ‘immediate trace name file_hdrs level 10’; to dump and analyze data file headers.
3. Trace File Analysis: Create and examine trace files to find discrepancies.

Resolution Strategies

1. Offline File Recovery:
   • If the file is offline and contains necessary data, recover the file from a backup.
   • Modify the file state to online after recovery if the database is in mount or cross mode.
   • Keep the file offline during recovery if the database is open, and then change it to online post-recovery.
2. Handling Online Corrupted Files:
   • If the database is up but the corrupted data file is not needed, take the file offline and consider dropping the corresponding tablespace.
   • If the database cannot start because of the corrupted file, put the file offline and repeat the recovery process.
3. Data File Replacement:
   • If the file is irrecoverable, consider replacing it with a backup.
4. Engage Oracle Support:
   • For complex cases, contacting Oracle technical support for guidance is advisable.

Important Considerations

• Backup Verification: Always ensure that backups are up-to-date and reliable.
• Impact Assessment: Understand the importance of the affected data and the impact of recovery or replacement.
• Data Consistency: After recovery, verify the consistency and integrity of the data.

Conclusion

The ORA-01122 error typically signifies discrepancies between data file information and control file records in Oracle databases. Resolving this error involves a series of diagnostic and recovery steps. The approach to resolution depends on the nature of the discrepancy and the condition of the affected files. In cases where internal inconsistencies are complex or data is critical, it is recommended to seek assistance from Oracle’s technical support team.