ORA-01110: Data File - Identify & Recover Problem Datafiles

ORA-01110: Data File N

Error Overview

Error Text: ORA-01110: data file N: '/path/to/datafile.dbf'

ORA-01110 is a secondary error that always accompanies a primary error such as ORA-01578, ORA-01157, ORA-27091, or ORA-01116. It identifies the specific datafile involved in the primary error by number and full path. On its own it conveys no action; its job is to tell you exactly which file Oracle cannot access, read, or write. Treating the primary error without noting the file number and path from ORA-01110 is a common diagnostic mistake.

This error appears in alert logs, trace files, OEM incidents, and SQL output. Capturing both the primary error code and the ORA-01110 detail together is the first step in every datafile recovery procedure.

Common Causes

1. Underlying Storage or OS Failure

• The datafile path reported by ORA-01110 no longer exists on disk
• NFS mount or SAN volume went offline while Oracle was running
• File was accidentally deleted at the OS level by a non-DBA process
• Disk or LUN failure causing the file to become inaccessible

2. Datafile Moved Without Oracle Being Informed

• OS mv or cp used instead of RMAN COPY or ALTER DATABASE RENAME FILE
• ASM rebalance moved an extent group but control file still holds old path
• Cold backup restore placed files in a different directory than the original

3. Permissions or Ownership Change

• Oracle OS user lost read/write permission on the datafile
• File ownership changed after an OS upgrade or security hardening script
• SELinux or AppArmor policy blocking Oracle from opening the file

4. Block Corruption in the Identified File

• When paired with ORA-01578, the file named in ORA-01110 contains a corrupt block
• Partial write during a crash left a block in an invalid state
• Hardware error wrote bad data to the file

5. Incomplete Recovery or Restore

• RMAN restore completed for some files but not the one in ORA-01110
• Point-in-time recovery left a file at a lower SCN than the rest of the database
• Tablespace point-in-time recovery (TSPITR) produced an inconsistent file set

Diagnostic Queries

Identify the File and Its Current Status

-- Full file details for the file number reported in ORA-01110
SELECT
  file#,
  name,
  status,
  enabled,
  checkpoint_change#,
  checkpoint_time,
  last_change#,
  fuzzy,
  error
FROM v$datafile_header
WHERE file# = &file_number;

-- Control file view — confirm path and online status
SELECT
  file#,
  name,
  status,
  checkpoint_change#,
  checkpoint_time,
  bytes / 1024 / 1024           AS size_mb,
  blocks,
  block_size
FROM v$datafile
WHERE file# = &file_number;

-- Which tablespace owns this file?
SELECT
  df.file_id,
  df.file_name,
  df.tablespace_name,
  df.status,
  df.autoextensible,
  ROUND(df.bytes / 1024 / 1024, 2)     AS size_mb,
  ROUND(df.maxbytes / 1024 / 1024, 2)  AS max_mb,
  ts.status                             AS tablespace_status,
  ts.contents
FROM dba_data_files df
JOIN dba_tablespaces ts ON df.tablespace_name = ts.tablespace_name
WHERE df.file_id = &file_number;

Verify the File Exists on Disk

-- Use UTL_FILE or an external table to check OS-level accessibility
-- Quick check via RMAN validation
-- (Run in RMAN, not SQL*Plus)
-- VALIDATE DATAFILE &file_number;

-- Check for files needing recovery
SELECT
  file#,
  error,
  recover,
  fuzzy,
  checkpoint_change#
FROM v$datafile_header
WHERE (error IS NOT NULL OR recover = 'YES')
ORDER BY file#;

-- Recent ORA-01110 occurrences in the alert log via external table
-- (assumes alert log external table is configured)
SELECT originating_timestamp, message_text
FROM x$dbgalertext
WHERE message_text LIKE '%ORA-01110%'
  AND originating_timestamp > SYSTIMESTAMP - INTERVAL '24' HOUR
ORDER BY originating_timestamp DESC;

Check RMAN Backup Coverage for This File

-- Is the file covered by a current RMAN backup?
SELECT
  bs.recid                            AS backup_set,
  bf.file#,
  bf.name                             AS backed_up_name,
  bs.completion_time,
  bs.status,
  bs.backup_type
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# = &file_number
ORDER BY bs.completion_time DESC
FETCH FIRST 10 ROWS ONLY;

-- What is the current SCN and how far back does backup coverage go?
SELECT
  bf.file#,
  MIN(bf.checkpoint_change#) AS oldest_backup_scn,
  MAX(bf.checkpoint_change#) AS newest_backup_scn,
  MAX(bs.completion_time)    AS last_backup_time
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# = &file_number
GROUP BY bf.file#;

Identify All Affected Files in a Multi-File Incident

-- Find every file currently in error state across the database
SELECT
  f.file#,
  f.name,
  f.status,
  h.error,
  h.recover,
  f.ts#,
  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 f.status != 'ONLINE'
   OR h.error IS NOT NULL
   OR h.recover = 'YES'
ORDER BY f.file#;

Step-by-Step Resolution

1. Record the Primary Error and File Details

Before taking any action, capture the complete error stack from the alert log. ORA-01110 always has a companion error above it. Common pairings:

Primary Error	Meaning
ORA-01157	DBWR cannot lock/identify the file
ORA-01578	Corrupt block in the identified file
ORA-27091	Unable to queue I/O (OS layer)
ORA-01116	Error opening database file

-- Note the file number N from ORA-01110 then run:
SELECT file#, name, status FROM v$datafile WHERE file# = &N;

2. Check Whether the File Exists at the OS Level

# From the Oracle OS account:
ls -lh /path/to/datafile.dbf

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

If the file is missing, proceed to the RMAN restore step. If it exists, check permissions:

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

3. Take the File Offline (If Database Is Open)

-- If the tablespace is not SYSTEM or active UNDO/TEMP:
ALTER DATABASE DATAFILE &file_number OFFLINE;

-- Then open the database if it is in MOUNT state:
ALTER DATABASE OPEN;

4. Restore and Recover with RMAN

-- In RMAN:
RMAN> RESTORE DATAFILE &file_number;
RMAN> RECOVER DATAFILE &file_number;
RMAN> ALTER DATABASE DATAFILE &file_number ONLINE;

-- If the file path has changed, use:
RMAN> RESTORE DATAFILE &file_number TO '/new/path/datafile.dbf';
RMAN> SWITCH DATAFILE &file_number TO COPY;
RMAN> RECOVER DATAFILE &file_number;

5. Rename File in Control File If Path Changed

-- If the physical file was moved outside of Oracle:
ALTER DATABASE RENAME FILE '/old/path/datafile.dbf'
  TO '/new/path/datafile.dbf';

-- For ASM to filesystem move, use RMAN COPY then SWITCH:
RMAN> COPY DATAFILE &file_number TO '/new/path/datafile.dbf';
RMAN> SWITCH DATAFILE &file_number TO COPY;

6. Handle SYSTEM or UNDO Tablespace Files

-- SYSTEM datafile issues require database-level recovery:
STARTUP MOUNT;
RMAN> RESTORE DATAFILE 1;  -- SYSTEM is always file# 1
RMAN> RECOVER DATAFILE 1;
ALTER DATABASE OPEN;

-- UNDO datafile — if cannot restore, create new undo tablespace:
CREATE UNDO TABLESPACE undotbs2
  DATAFILE '/u01/oradata/undotbs2.dbf' SIZE 10G AUTOEXTEND ON;
ALTER SYSTEM SET undo_tablespace = UNDOTBS2 SCOPE=BOTH;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

7. Bring the File Back Online and Verify

-- After recovery:
ALTER DATABASE DATAFILE &file_number ONLINE;

-- Verify no error remains:
SELECT file#, name, status, error FROM v$datafile_header WHERE file# = &file_number;

-- Open resetlogs only if incomplete recovery was performed:
ALTER DATABASE OPEN RESETLOGS;

Prevention Strategies

1. Never Move Datafiles at the OS Level

Always use Oracle-aware methods to relocate datafiles:

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

-- Or RMAN offline method:
RMAN> COPY DATAFILE '/old/path/file.dbf' TO '/new/path/file.dbf';
RMAN> SWITCH DATAFILE '/old/path/file.dbf' TO COPY;

2. Monitor Datafile Status Proactively

-- Schedule this as a daily job:
SELECT file#, name, status, error
FROM v$datafile_header
WHERE status != 'ONLINE' OR error IS NOT NULL;

3. Validate Backups Regularly

-- In RMAN — run weekly:
RMAN> VALIDATE DATABASE CHECK LOGICAL;

-- Or validate specific file:
RMAN> VALIDATE DATAFILE &file_number CHECK LOGICAL;

4. Configure Multiplexed Control Files

SHOW PARAMETER control_files;
-- Maintain at least 2 control files on separate disks to preserve accurate datafile tracking.

Related Errors

• ORA-01157 - Cannot identify/lock data file (most common companion)
• ORA-01578 - Oracle data block corrupted (block-level companion)
• ORA-01116 - Error opening database file
• ORA-01187 - Cannot read from file because it failed verification

Emergency Response

Quick Decision Tree

1. Is the file on SYSTEM tablespace? → Mount-mode recovery required, see Step 6 above
2. Does the file exist on disk? → Check permissions, rename in control file if path changed
3. File missing entirely? → RMAN restore from backup immediately
4. File exists but corrupt? → See ORA-01578 for block-level recovery

Critical Commands

-- 1. Check file status immediately
SELECT file#, name, status, error FROM v$datafile_header WHERE file# = &N;

-- 2. Take offline to open database while recovering
ALTER DATABASE DATAFILE &N OFFLINE;
ALTER DATABASE OPEN;

-- 3. Restore in RMAN (background)
-- RESTORE DATAFILE &N;
-- RECOVER DATAFILE &N;
-- ALTER DATABASE DATAFILE &N ONLINE;

Post-Recovery Validation

-- Confirm all files are healthy
SELECT COUNT(*) AS problem_files
FROM v$datafile_header
WHERE status != 'ONLINE' OR error IS NOT NULL OR recover = 'YES';

-- Should return 0. Take a fresh RMAN backup immediately after recovery.