ORA-01187: File Failed Verification - Fix Temp Files

ORA-01187: Cannot Read from File Because It Failed Verification

Error Overview

Error Text: ORA-01187: cannot read from file N because it failed verification tests

ORA-01187 occurs when Oracle attempts to read from a datafile or tempfile and the file fails its internal verification checks. The verification process confirms that the file header matches what Oracle expects in terms of DBID, file number, creation timestamp, and checkpoint information. A mismatch on any of these fields causes Oracle to reject the file outright.

This error is most frequently seen with temporary tablespace tempfiles after a database recovery or point-in-time restore, because tempfiles are not backed up by RMAN and are not always recreated automatically. It can also occur with regular datafiles when a backup from the wrong database is mistakenly restored in place of the correct file.

Common Causes

1. Tempfile Mismatch After Recovery

• RMAN restore from a backup does not include tempfiles (they are excluded by design)
• After a recovery, the tempfiles recorded in the control file do not match the actual files on disk
• Database was cloned and old tempfiles from the source still exist on the target filesystem

2. Wrong File Restored

• A datafile from a different database (different DBID) was placed in the correct path
• A backup piece from an older backup was restored over a newer file inadvertently
• A tablespace from another database was plugged in without proper conversion

3. Control File Recreated With Stale Tempfile Entries

• CREATE CONTROLFILE script included tempfile entries from an old state
• After OPEN RESETLOGS, the control file was restored from a pre-resetlogs backup
• Standby control file applied to the primary database by mistake

4. Filesystem or Storage Substitution

• New LUN or filesystem presented at the same mount point but containing different (or empty) files
• Storage team restored a volume snapshot from a different point in time than expected
• NFS server restored from a different snapshot than the Oracle host expected

5. File Header Corruption

• First few blocks of the datafile corrupted, making the header unreadable
• Partial write during a crash damaged the file header block
• Filesystem-level corruption affecting block zero of the file

Diagnostic Queries

Identify the Failing File

-- Run in MOUNT state or while the error is active:
SELECT
  f.file#,
  f.name                                         AS file_path,
  f.status,
  h.status                                       AS header_status,
  h.error,
  h.recover,
  h.fuzzy,
  h.checkpoint_change#,
  TO_CHAR(h.checkpoint_time, 'YYYY-MM-DD HH24:MI:SS') AS chkpt_time,
  t.name                                         AS tablespace_name
FROM v$datafile f
JOIN v$datafile_header h ON f.file# = h.file#
JOIN v$tablespace t ON f.ts# = t.ts#
WHERE h.error IS NOT NULL OR h.status = 'UNKNOWN'
ORDER BY f.file#;

-- Check tempfiles specifically:
SELECT
  tf.file#,
  tf.name,
  tf.status,
  th.status   AS header_status,
  th.error
FROM v$tempfile tf
JOIN v$tempfile_header th ON tf.file# = th.file#
ORDER BY tf.file#;

Compare Control File vs Disk Headers

-- What does the control file expect for the file?
SELECT
  file#,
  name,
  creation_change#,
  creation_time,
  resetlogs_change#,
  resetlogs_time,
  checkpoint_change#,
  checkpoint_time,
  bytes / 1024 / 1024 AS expected_size_mb
FROM v$datafile
WHERE file# = &problem_file_number;

-- What does the file header actually contain?
SELECT
  file#,
  name,
  status,
  checkpoint_change#,
  TO_CHAR(checkpoint_time, 'YYYY-MM-DD HH24:MI:SS') AS checkpoint_time,
  error
FROM v$datafile_header
WHERE file# = &problem_file_number;
-- If checkpoint_change# differs between the two views, the file is wrong.

Check Temp Tablespace Configuration

-- All temp tablespaces and their files:
SELECT
  ts.tablespace_name,
  ts.status,
  tf.file_name,
  ROUND(tf.bytes / 1024 / 1024, 2)      AS size_mb,
  tf.status                             AS file_status,
  tf.autoextensible
FROM dba_tablespaces ts
JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
WHERE ts.contents = 'TEMPORARY'
ORDER BY ts.tablespace_name;

-- Which users have which temp tablespace assigned?
SELECT username, temporary_tablespace
FROM dba_users
WHERE account_status = 'OPEN'
ORDER BY temporary_tablespace, username;

-- Default temporary tablespace for the database:
SELECT property_name, property_value
FROM database_properties
WHERE property_name IN ('DEFAULT_TEMP_TABLESPACE', 'DEFAULT_PERMANENT_TABLESPACE');

Assess Recovery State

-- Is the database in a post-recovery state needing resetlogs?
SELECT
  name,
  open_mode,
  resetlogs_change#,
  TO_CHAR(resetlogs_time, 'YYYY-MM-DD HH24:MI:SS') AS resetlogs_time,
  log_mode,
  db_unique_name,
  dbid
FROM v$database;

-- Check archived log sequence continuity:
SELECT
  sequence#,
  applied,
  deleted,
  status,
  TO_CHAR(first_time, 'YYYY-MM-DD HH24:MI:SS') AS first_time,
  TO_CHAR(next_time, 'YYYY-MM-DD HH24:MI:SS')  AS next_time
FROM v$archived_log
WHERE standby_dest = 'NO'
ORDER BY sequence# DESC
FETCH FIRST 20 ROWS ONLY;

Step-by-Step Resolution

1. Determine Whether the Failing File Is a Tempfile or Datafile

ORA-01187 behaviour differs significantly depending on file type:

• Tempfile: Drop and recreate — no data is lost (temp tablespace data is always transient)
• Datafile: Must restore the correct file from RMAN backup

-- Is the file# a tempfile or datafile?
SELECT 'DATAFILE' AS type, file#, name FROM v$datafile WHERE file# = &N
UNION ALL
SELECT 'TEMPFILE' AS type, file#, name FROM v$tempfile WHERE file# = &N;

2. Fix a Failing Tempfile (Most Common Case)

-- Step 1: Drop the bad tempfile from the tablespace
ALTER TABLESPACE temp DROP TEMPFILE '/path/to/failing_temp01.dbf';

-- Step 2: Add a new tempfile
ALTER TABLESPACE temp
  ADD TEMPFILE '/u01/oradata/temp01.dbf'
  SIZE 2G AUTOEXTEND ON MAXSIZE 32G;

-- Step 3: Verify the new tempfile is online
SELECT file#, name, status FROM v$tempfile_header;

If the database is in MOUNT mode and won’t open due to this error:

-- Remove the tempfile entry from the control file:
ALTER DATABASE TEMPFILE '/path/to/bad_temp.dbf' DROP;

-- Then open:
ALTER DATABASE OPEN;

-- Then add a new tempfile:
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/temp01.dbf' SIZE 2G;

3. Fix a Failing Datafile

-- Step 1: Take the datafile offline (non-SYSTEM tablespace only)
ALTER DATABASE DATAFILE &N OFFLINE;

-- Step 2: Restore the correct file using RMAN
-- (In RMAN)
-- RESTORE DATAFILE &N;
-- RECOVER DATAFILE &N;

-- Step 3: Bring online
ALTER DATABASE DATAFILE &N ONLINE;

If the file was from the wrong database (different DBID), do not attempt to use it — restore from RMAN:

-- In RMAN:
RMAN> RESTORE DATAFILE &N;
RMAN> RECOVER DATAFILE &N;

4. After Database Clone — Remove Old Tempfiles

-- On a cloned database, old tempfiles from the source may cause ORA-01187:

-- Drop all existing tempfiles:
ALTER TABLESPACE temp DROP TEMPFILE '/source_host_path/temp01.dbf';

-- Add fresh tempfiles appropriate for the new host:
ALTER TABLESPACE temp ADD TEMPFILE '/new_host_path/temp01.dbf' SIZE 4G AUTOEXTEND ON;

-- Repeat for each temp tablespace.

5. Open After Incomplete Recovery

-- If the database requires RESETLOGS after incomplete recovery,
-- tempfiles are always stale. After OPEN RESETLOGS, recreate all tempfiles:
ALTER DATABASE OPEN RESETLOGS;

-- Then immediately:
ALTER TABLESPACE temp DROP TEMPFILE '/path/temp01.dbf';
ALTER TABLESPACE temp ADD TEMPFILE '/path/temp01.dbf' SIZE 2G AUTOEXTEND ON;

6. Recreate the Temp Tablespace from Scratch (Severe Cases)

-- Create a replacement temp tablespace:
CREATE TEMPORARY TABLESPACE temp2
  TEMPFILE '/u01/oradata/temp02_01.dbf'
  SIZE 4G AUTOEXTEND ON MAXSIZE 32G;

-- Make it the default:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Drop the old broken one:
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

-- Optionally rename temp2 to temp by creating a new one:
CREATE TEMPORARY TABLESPACE temp
  TEMPFILE '/u01/oradata/temp01.dbf' SIZE 4G AUTOEXTEND ON;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Prevention Strategies

1. Script Tempfile Recreation Into Every Recovery Runbook

Since RMAN does not back up tempfiles, every recovery runbook should include a step to drop and recreate tempfiles after ALTER DATABASE OPEN or ALTER DATABASE OPEN RESETLOGS:

-- Post-recovery tempfile recreation script:
-- 1. Query existing temp tablespaces
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';

-- 2. For each temp tablespace, drop old tempfiles and add new ones:
ALTER TABLESPACE temp DROP TEMPFILE '/old/path/temp01.dbf';
ALTER TABLESPACE temp ADD TEMPFILE '/new/path/temp01.dbf' SIZE 4G AUTOEXTEND ON;

2. Include Tempfile Steps in Clone Procedures

-- In any database duplication or cloning runbook, after duplication completes:
-- Verify all tempfiles are accessible and correctly sized.
SELECT ts.tablespace_name, tf.file_name, tf.status
FROM dba_tablespaces ts
JOIN dba_temp_files tf ON ts.tablespace_name = tf.tablespace_name
WHERE ts.contents = 'TEMPORARY';

3. Monitor Temp Tablespace File Status

-- Include in daily monitoring checks:
SELECT
  f.file#,
  f.name,
  h.status,
  h.error
FROM v$tempfile f
JOIN v$tempfile_header h ON f.file# = h.file#
WHERE h.status != 'ONLINE' OR h.error IS NOT NULL;
-- Expect no rows.

4. Size Temp Tablespace Appropriately

-- Avoid ORA-01187 caused by autogrown files conflicting with storage limits.
-- Set sensible MAXSIZE values:
ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/temp01.dbf'
  SIZE 4G AUTOEXTEND ON MAXSIZE 32G;

Related Errors

• ORA-01110 - Data file identification (accompanies ORA-01187)
• ORA-01157 - Cannot identify/lock data file
• ORA-25153 - Temporary tablespace is empty
• ORA-01652 - Unable to extend temp segment

Emergency Response

Quick Tempfile Fix

-- If database is open and a temp operation is failing:
ALTER TABLESPACE temp DROP TEMPFILE '/path/to/bad_temp.dbf';
ALTER TABLESPACE temp ADD TEMPFILE '/path/to/new_temp.dbf' SIZE 2G;

Quick Fix When Database Won’t Open

STARTUP MOUNT;
ALTER DATABASE TEMPFILE '/path/to/bad_temp.dbf' DROP;
ALTER DATABASE OPEN;
ALTER TABLESPACE temp ADD TEMPFILE '/path/new_temp.dbf' SIZE 2G;

Verification After Fix

-- Confirm all temp files are healthy:
SELECT file#, name, status, error FROM v$tempfile_header;

-- Run a sort-intensive query to exercise the temp tablespace:
SELECT *
FROM dba_objects
ORDER BY object_name, object_type, object_id
FETCH FIRST 1 ROW ONLY;