DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01122 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01122: Database File Failed Verification Check

ORA-01122 occurs when Oracle cannot verify the consistency of a datafile header during database startup or recovery. This typically means the SCN (System Change Number) recorded in the datafile header does not match what the control file expects. It almost always appears alongside ORA-01110, which identifies the specific problematic file.

Top 3 Causes and Fixes

Cause 1: Datafile Restored Without Recovery

The most common cause. After restoring a datafile from backup, the file's checkpoint SCN is older than what the control file expects, so Oracle rejects it.

Diagnosis:

-- Check datafile header status
SELECT file#, error, recover, fuzzy, checkpoint_change#
FROM v$datafile_header;

-- Compare with control file SCN
SELECT file#, checkpoint_change#, name
FROM v$datafile;
Enter fullscreen mode Exit fullscreen mode

Fix:

-- Start in MOUNT mode and recover the specific file
STARTUP MOUNT;

RECOVER DATAFILE 5;
-- Apply archive logs as prompted, then type AUTO or specify log path

ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Cause 2: Control File Mismatch (Old or Recreated Control File)

When a control file is recreated or an older backup of the control file is used, its internal SCN reference becomes out of sync with existing datafiles.

Diagnosis:

-- Check if control file is older than datafiles
SELECT controlfile_change#, controlfile_time
FROM v$database;

SELECT file#, checkpoint_change#
FROM v$datafile_header
WHERE checkpoint_change# > (SELECT controlfile_change# FROM v$database);
Enter fullscreen mode Exit fullscreen mode

Fix:

-- Mount and perform full database recovery
STARTUP MOUNT;

RECOVER DATABASE USING BACKUP CONTROLFILE;
-- Supply archive logs when prompted

-- If complete recovery is not possible, open with RESETLOGS
-- WARNING: This may result in data loss
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Cause 3: Datafile Left in Hot Backup Mode

If a tablespace was placed in BEGIN BACKUP mode and the database crashed or END BACKUP was never issued, the datafile header SCN is frozen and will fail verification on the next startup.

Diagnosis:

-- Identify files still in backup mode
SELECT file#, status, change#, time
FROM v$backup
WHERE status = 'ACTIVE';
Enter fullscreen mode Exit fullscreen mode

Fix:

-- End backup mode for all affected tablespaces
ALTER TABLESPACE users END BACKUP;

-- Or end backup mode for ALL tablespaces at once
BEGIN
  FOR ts IN (SELECT tablespace_name FROM dba_tablespaces) LOOP
    EXECUTE IMMEDIATE
      'ALTER TABLESPACE ' || ts.tablespace_name || ' END BACKUP';
  END LOOP;
END;
/

-- Then recover and open
RECOVER DATABASE;
ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Using RMAN

RMAN is the recommended tool for handling ORA-01122 in modern Oracle environments.

-- Connect to RMAN
-- $ rman target /

-- Restore and recover a specific datafile
RESTORE DATAFILE 5;
RECOVER DATAFILE 5;
ALTER DATABASE OPEN;

-- Full database restore and recovery
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

-- If incomplete recovery is necessary
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Validate Backups Regularly

Never assume a backup is valid — test it. Use RMAN's VALIDATE command to confirm recoverability without actually restoring files.

-- Validate all backups and check for block corruption
VALIDATE DATABASE;
SELECT * FROM v$database_block_corruption;
Enter fullscreen mode Exit fullscreen mode

2. Never Manipulate Datafiles at the OS Level

Avoid using OS commands (cp, mv) to copy or move datafiles. Always use Oracle-provided tools to prevent header mismatches.

-- Oracle 12c+: Move datafile online safely
ALTER DATABASE MOVE DATAFILE '/old/path/file01.dbf'
  TO '/new/path/file01.dbf';

-- Monitor backup mode status regularly
SELECT file#, status, change#, time
FROM v$backup
ORDER BY status DESC;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-01110 Always accompanies ORA-01122; identifies the problematic datafile path and number
ORA-01113 Media recovery required for the datafile
ORA-01194 Datafile needs more recovery to become consistent
ORA-00376 File cannot be read at this time (often triggered after ORA-01122)
ORA-01578 Physical block corruption in datafile

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)