ORA-01157: Cannot Identify Data File - Recovery Steps

ORA-01157: Cannot Identify/Lock Data File - See DBWR Trace

Error Overview

Error Text: ORA-01157: cannot identify/lock data file N - see DBWR trace file

ORA-01157 occurs when Oracle’s Database Writer (DBWR) background process cannot open or lock a datafile during database startup or normal operation. The error is always paired with ORA-01110, which names the specific file. DBWR requires exclusive lock access to every online datafile; if even one file is inaccessible, Oracle aborts the open sequence.

This error most commonly surfaces when trying to ALTER DATABASE OPEN in mount mode after a file was deleted, renamed, moved, or made inaccessible since the last successful open. It is one of the most serious startup errors because it prevents the database from opening until the file situation is resolved.

Common Causes

1. Datafile Deleted or Moved at the OS Level

• File removed by an OS administrator or automated cleanup script
• mv command used instead of Oracle’s ALTER DATABASE RENAME FILE
• Backup script copied files to tape/cloud and then deleted originals

2. Storage Failure

• NFS mount point went offline between shutdown and startup
• SAN LUN failed or was unmapped from the host
• ASM diskgroup dismounted, making ASM-resident files invisible

3. Incorrect Database Mount

• Database opened against a wrong parameter file pointing to a different file layout
• Standby database files in a different location from production
• ORACLE_SID set to wrong instance

4. File Permission Changes

• Oracle OS user lost read/write access after a security change
• Filesystem remounted read-only (e.g., after a crash)
• ACL or SELinux policy changed

5. Partial Restore Without Renaming

• RMAN restored files to a non-default location but no SWITCH was performed
• Control file was recreated with old paths that no longer reflect current file locations

Diagnostic Queries

Identify the Problem File and Its Tablespace

-- Run immediately after the error — must be in MOUNT state:
SELECT
  f.file#,
  f.name                                      AS file_path,
  f.status,
  f.enabled,
  h.error,
  h.recover,
  h.fuzzy,
  h.checkpoint_change#,
  TO_CHAR(h.checkpoint_time, 'YYYY-MM-DD HH24:MI:SS') AS checkpoint_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#
ORDER BY f.file#;

-- Which files have errors specifically:
SELECT
  f.file#,
  f.name,
  h.status,
  h.error,
  h.recover
FROM v$datafile f
JOIN v$datafile_header h ON f.file# = h.file#
WHERE h.status = 'UNKNOWN'
   OR h.error IS NOT NULL
   OR f.status = 'OFFLINE'
ORDER BY f.file#;

Check DBWR Trace for Root Cause

-- Find the DBWR trace file location:
SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';

-- Find DBWR process info:
SELECT
  name,
  description,
  error
FROM v$bgprocess
WHERE name LIKE 'DBW%'
ORDER BY name;

-- Recent errors in the alert log:
SELECT
  originating_timestamp,
  message_text
FROM x$dbgalertext
WHERE message_text LIKE '%ORA-01157%'
   OR message_text LIKE '%ORA-01110%'
   OR message_text LIKE '%DBWR%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

Assess RMAN Backup Availability

-- Is the problem file covered by a recent backup?
SELECT
  bf.file#,
  MAX(bs.completion_time)                       AS last_backup,
  MAX(bf.checkpoint_change#)                    AS backup_scn,
  (SELECT current_scn FROM v$database)          AS current_scn,
  (SELECT current_scn FROM v$database)
    - MAX(bf.checkpoint_change#)                AS scn_gap
FROM v$backup_datafile bf
JOIN v$backup_set bs ON bf.set_stamp = bs.stamp AND bf.set_count = bs.recid
WHERE bf.file# = &problem_file_number
GROUP BY bf.file#;

-- List all available backups for the problem file:
SELECT
  bs.recid,
  bs.backup_type,
  bs.status,
  bs.completion_time,
  bf.blocks,
  bf.block_size,
  bf.checkpoint_change#
FROM v$backup_datafile bf
JOIN v$backup_set bs ON bf.set_stamp = bs.stamp AND bf.set_count = bs.recid
WHERE bf.file# = &problem_file_number
ORDER BY bs.completion_time DESC;

Determine If Tablespace Can Be Taken Offline

-- Is the affected tablespace SYSTEM, SYSAUX, active UNDO, or active TEMP?
-- These cannot be taken offline and require full database recovery.
SELECT
  t.name                  AS tablespace_name,
  t.ts#,
  ts.contents,
  ts.status,
  (SELECT value FROM v$parameter WHERE name = 'undo_tablespace') AS active_undo_ts
FROM v$tablespace t
JOIN dba_tablespaces ts ON t.name = ts.tablespace_name
WHERE t.ts# = (
  SELECT ts# FROM v$datafile WHERE file# = &problem_file_number
);

Step-by-Step Resolution

1. Start in Mount Mode and Identify the File

STARTUP MOUNT;

-- Run the diagnostic query above.
-- Note the file number N, path, and tablespace name.

2. Check the OS for the File

# Substitute the path from ORA-01110:
ls -lh /path/to/datafile.dbf

# If on ASM:
asmcmd ls -l +DATADG/ORCL/DATAFILE/tablespace_name.dbf

Decision point:

• File exists → Go to Step 3 (permission or path issue)
• File missing → Go to Step 4 (RMAN restore)
• File on failed NFS/SAN → Restore the mount first, then go to Step 3

3. Fix File Access Without Restore

# Fix permissions:
chmod 640 /path/to/datafile.dbf
chown oracle:dba /path/to/datafile.dbf

# Remount NFS if it dropped:
mount /u01/oradata

-- If file was moved/renamed at OS level, update the control file:
ALTER DATABASE RENAME FILE '/old/path/file.dbf' TO '/new/path/file.dbf';

-- Then open:
ALTER DATABASE OPEN;

4. Take the File Offline and Open (Non-SYSTEM Tablespace Only)

-- This works only when the tablespace is NOT SYSTEM, active UNDO, or active TEMP:
ALTER DATABASE DATAFILE &N OFFLINE;
ALTER DATABASE OPEN;

-- After opening, restore and recover in the background:
-- (In RMAN)
-- RESTORE DATAFILE &N;
-- RECOVER DATAFILE &N;
-- ALTER DATABASE DATAFILE &N ONLINE;

5. RMAN Restore and Recovery (Full Procedure)

-- In RMAN (database in MOUNT state):
RMAN TARGET /

RESTORE DATAFILE &N;
RECOVER DATAFILE &N;

-- If recovery requires archived logs not available locally:
RECOVER DATAFILE &N FROM TAG 'BACKUP_TAG';

-- Bring online and open:
ALTER DATABASE DATAFILE &N ONLINE;
ALTER DATABASE OPEN;

6. Incomplete Recovery (If Archived Logs Are Missing)

-- In RMAN, recover to the last available SCN:
RMAN> RUN {
  SET UNTIL SCN = &last_available_scn;
  RESTORE DATABASE;
  RECOVER DATABASE;
}
ALTER DATABASE OPEN RESETLOGS;

7. SYSTEM Tablespace Recovery

-- SYSTEM file is always file# 1. Database cannot open with SYSTEM offline.
-- Must restore in mount mode:
RMAN> RESTORE DATAFILE 1;
RMAN> RECOVER DATAFILE 1;
ALTER DATABASE OPEN;

8. Recreate the File for Temporary Tablespaces

-- If the file belongs to a TEMP tablespace:
-- Take the tablespace offline
ALTER TABLESPACE temp OFFLINE;

-- Drop and recreate the temp file:
ALTER TABLESPACE temp DROP TEMPFILE '/path/to/missing_temp.dbf';
ALTER TABLESPACE temp ADD TEMPFILE '/path/to/new_temp.dbf' SIZE 2G AUTOEXTEND ON;
ALTER DATABASE OPEN;

Prevention Strategies

1. Never Use OS Commands to Move or Delete Datafiles

-- ALWAYS use Oracle-aware methods:

-- Online move (12c+):
ALTER DATABASE MOVE DATAFILE '/old/path/file.dbf' TO '/new/path/file.dbf';

-- RMAN copy + switch:
RMAN> COPY DATAFILE '/old/path/file.dbf' TO '/new/path/file.dbf';
RMAN> SWITCH DATAFILE '/old/path/file.dbf' TO COPY;

2. Monitor Datafile Accessibility Proactively

-- Daily check — alert if any file reports an error:
SELECT file#, name, status, error
FROM v$datafile_header
WHERE status != 'ONLINE' OR error IS NOT NULL OR recover = 'YES';

-- Automate with DBMS_SCHEDULER:
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'CHECK_DATAFILES',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
                          FOR r IN (SELECT file#, name FROM v$datafile_header
                                    WHERE status != ''ONLINE'' OR error IS NOT NULL)
                          LOOP
                            -- Send alert (UTL_MAIL or custom proc)
                            DBMS_OUTPUT.PUT_LINE(''ALERT: File '' || r.file# || '' '' || r.name);
                          END LOOP;
                        END;',
    repeat_interval => 'FREQ=DAILY;BYHOUR=6',
    enabled         => TRUE
  );
END;
/

3. Validate Backups and Archived Log Retention

-- In RMAN — weekly validation:
RMAN> VALIDATE DATABASE;
RMAN> CROSSCHECK BACKUP;
RMAN> CROSSCHECK ARCHIVELOG ALL;

-- Confirm no backups are expired:
SELECT status, COUNT(*) FROM v$backup_set GROUP BY status;

4. Document Mount Points and Storage Layout

• Keep an up-to-date runbook listing all datafile paths and their underlying storage
• Include mount point recovery steps for NFS/SAN in the DBA runbook
• Test mount recovery procedures in DR drills annually

Related Errors

• ORA-01110 - Data file identification (always accompanies ORA-01157)
• ORA-01119 - Error creating database file
• ORA-01187 - Cannot read from file because it failed verification
• ORA-01578 - Oracle data block corrupted

Emergency Response

Startup Decision Flowchart

STARTUP MOUNT → ORA-01157 fires
    ↓
Is affected tablespace SYSTEM/SYSAUX/active UNDO?
    YES → RMAN restore in mount mode, then OPEN
    NO  →
        Does file exist on disk?
            YES → Fix permissions/path → RENAME FILE → OPEN
            NO  → OFFLINE file → OPEN → restore/recover online

Critical Commands

-- 1. Always start here:
STARTUP MOUNT;
SELECT file#, name, status FROM v$datafile_header WHERE status = 'UNKNOWN' OR error IS NOT NULL;

-- 2. Non-critical tablespace offline + open:
ALTER DATABASE DATAFILE &N OFFLINE;
ALTER DATABASE OPEN;

-- 3. Rename if path moved:
ALTER DATABASE RENAME FILE '/old/path' TO '/new/path';
ALTER DATABASE OPEN;

Post-Recovery Checklist

-- Verify all files are online:
SELECT COUNT(*) FROM v$datafile_header
WHERE status != 'ONLINE' OR error IS NOT NULL OR recover = 'YES';
-- Expect 0.

-- Take a fresh RMAN backup immediately:
-- RMAN> BACKUP DATABASE PLUS ARCHIVELOG;