How to Simulate and Recover Various Oracle File Corruptions

This article explains how to simulate corruption of different Oracle database files and outlines recovery strategies, assuming regular backups are in place.

Password File Corruption

Simulation: Empty the password file with echo '' > $ORACLE_HOME/dbs/orapworcltest.

Symptoms: Login as SYS fails with ORA‑01017.

Recovery: Regenerate the password file using

orapwd file=$ORACLE_HOME/dbs/orapworcltest password=123456 force=y

.

Parameter File (SPFILE) Corruption

Simulation: Empty the SPFILE with echo '' > $ORACLE_HOME/dbs/spfileorcltest.ora.

Symptoms: ALTER SYSTEM commands error with ORA‑01565 and ORA‑27046.

Recovery: List backup with RMAN> list backup of spfile; then restore:

RMAN> restore spfile to '/home/oracle/spfileorcltest.ora' from '/home/oracle/backupdir/c-2750922031-20200317-00';

Move the restored file to the proper location and restart the database. Alternatively, create a new SPFILE from memory: create spfile='/home/oracle/spfileorcltest.ora' from memory;.

Control File Corruption

Simulation: Empty the control file with echo '' > /u01/app/oracle/oradata/orcltest/control01.ctl.

Symptoms: Normal DML works, but switching logfiles or checkpoint causes ORA‑03113 and instance termination; alert log shows ORA‑00202.

Recovery: Use RMAN:

rman target /
RMAN> startup nomount
RMAN> restore controlfile from '/home/oracle/backupdir/c-2750922031-20200317-00';
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;

Note: Restoring to a new path then moving the file avoids ORA‑32011.

Data File Corruption

Various data file types are covered:

Regular tablespace file: Empty /u01/app/oracle/oradata/orcltest/tbs01.dbf. Querying objects raises ORA‑01115/ORA‑01110. Recovery:

alter database datafile 6 offline; RMAN> restore datafile 6; RMAN> recover datafile 6; alter database datafile 6 online;

SYSTEM tablespace: Empty /u01/app/oracle/oradata/orcltest/system01.dbf. Errors on data‑dictionary queries. Recovery: shutdown abort, startup mount, then

RMAN> restore datafile 1; RMAN> recover datafile 1; alter database open;

SYSAUX and UNDO tablespaces: Similar symptoms; recovery follows the same approach as SYSTEM tablespace.

Log File Corruption

Three log groups (inactive, active, current) are examined.

Inactive/Active log file: Empty an inactive log file (e.g., redo03.log). Database continues operating, but log switch triggers errors in the alert log (ORA‑00313, ORA‑00312). Recovery: alter database clear unarchived logfile group 3; then perform a full backup.

Current log file: Empty the current log file. Log switch causes immediate instance crash (ORA‑03113). Recovery involves mounting the database, performing an incomplete recovery, then opening with resetlogs using hidden parameter _allow_resetlogs_corruption=TRUE. Afterward, export data and rebuild the database.

Key Takeaways

Maintain regular full backups and a solid backup strategy.

Configure multiple control file members and log file members for redundancy.

Current log file corruption is the most critical; consider Data Guard for high‑availability.

The article reflects personal experience and invites corrections.

Author: Yang Bao, DBA at Dongcai Fund (original source: https://www.cnblogs.com/ddzj01/p/12526100.html).