ORA-00376: File Cannot Be Read at This Time - Recover Offline Datafiles

ORA-00376: File Cannot Be Read at This Time

Error Overview

Error Text: ORA-00376: file number string cannot be read at this time

The ORA-00376 error occurs when Oracle attempts to read a datafile that is currently offline, in an error state, or otherwise unavailable for I/O operations. This error is always accompanied by a secondary error (typically ORA-01110) that identifies the specific file path. It is a critical error that indicates data is inaccessible and requires immediate DBA attention to restore service.

Common Causes

1. Datafile Taken Offline

• DBA manually took the datafile offline with ALTER DATABASE DATAFILE ... OFFLINE
• A tablespace was taken offline, which offlines all its constituent datafiles
• Datafile went offline automatically due to repeated I/O errors during normal operation

2. Media Failure or Disk Issues

• Underlying disk or storage device failed or was removed
• SAN/NAS path lost connectivity causing Oracle to mark the file offline
• ASM disk group went offline or lost disks, making its datafiles unreadable
• Filesystem corruption preventing reads at the OS level

3. File Permissions or Ownership Changes

• OS-level file permissions changed so the Oracle process can no longer read the file
• File moved or renamed by an OS administrator without Oracle’s knowledge
• NFS export permissions revoked on the storage server side

4. Incomplete or Incorrect Recovery

• Point-in-time recovery left a datafile in a state requiring further recovery
• Datafile restored from backup but not yet recovered to a consistent SCN
• Standby database datafile not properly synchronized

5. Read-Only Tablespace Inconsistencies

• Tablespace switched between read-only and read-write without proper archiving
• Datafile header SCN mismatch following incomplete recovery

Diagnostic Queries

Identify the Affected File and Its Status

-- Find all datafiles that are not online
SELECT
  file#,
  name,
  status,
  enabled,
  checkpoint_change#,
  last_change#,
  recover,
  fuzzy
FROM v$datafile
WHERE status NOT IN ('ONLINE', 'SYSTEM')
ORDER BY file#;

-- Get detailed datafile information including tablespace
SELECT
  df.file#,
  df.name                          AS file_path,
  df.status,
  df.enabled,
  ts.name                          AS tablespace_name,
  df.bytes / 1024 / 1024           AS size_mb,
  df.checkpoint_change#,
  df.last_change#
FROM v$datafile df
JOIN v$tablespace ts ON df.ts# = ts.ts#
ORDER BY df.file#;

Check Tablespace Status

-- Check tablespace status for all tablespaces
SELECT
  tablespace_name,
  status,
  contents,
  logging,
  force_logging
FROM dba_tablespaces
ORDER BY tablespace_name;

-- Find tablespaces with offline datafiles
SELECT
  t.tablespace_name,
  t.status                         AS ts_status,
  d.file_id,
  d.file_name,
  d.status                         AS file_status,
  d.bytes / 1024 / 1024            AS size_mb
FROM dba_tablespaces t
JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
WHERE d.status != 'AVAILABLE'
   OR t.status  != 'ONLINE'
ORDER BY t.tablespace_name;

Check Alert Log for I/O Errors

-- Query alert log for recent file-related errors (12c and above)
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-00376%'
   OR message_text LIKE '%ORA-01110%'
   OR message_text LIKE '%file %offline%'
   OR message_text LIKE '%I/O error%'
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Determine Recovery Requirements

-- Check SCN gaps to understand how much recovery is needed
SELECT
  df.file#,
  df.name,
  df.checkpoint_change#            AS file_scn,
  cf.checkpoint_change#            AS control_file_scn,
  cf.checkpoint_change# - df.checkpoint_change# AS scn_gap,
  df.recover
FROM v$datafile df
CROSS JOIN (SELECT checkpoint_change# FROM v$database) cf
WHERE df.status != 'SYSTEM'
ORDER BY scn_gap DESC;

-- Check if recovery is possible from available archivelogs
SELECT
  sequence#,
  first_change#,
  next_change#,
  first_time,
  next_time,
  archived,
  applied,
  status
FROM v$archived_log
WHERE first_change# >= (
  SELECT MIN(checkpoint_change#) FROM v$datafile WHERE recover = 'YES'
)
ORDER BY sequence#;

ASM Disk Group Health Check

-- If datafiles are on ASM, check disk group status
SELECT
  group_number,
  name,
  state,
  type,
  total_mb,
  free_mb,
  usable_file_mb,
  offline_disks
FROM v$asm_diskgroup
ORDER BY name;

-- Check for offline ASM disks
SELECT
  group_number,
  disk_number,
  name,
  path,
  state,
  mode_status,
  total_mb,
  free_mb,
  reads,
  writes,
  read_errs,
  write_errs
FROM v$asm_disk
WHERE state != 'NORMAL'
   OR mode_status != 'ONLINE'
ORDER BY group_number, disk_number;

Step-by-Step Resolution

1. Identify the Exact File and Error Cause

Review the full error stack in the alert log. ORA-00376 is always accompanied by ORA-01110 which names the file. Establish whether the file is physically present on disk before attempting any Oracle-level recovery.

-- Confirm physical file existence using Oracle's file existence check
-- (Run this as OS command first: ls -lh /path/to/datafile.dbf)

-- Then check Oracle's view of the file
SELECT
  name,
  status,
  recover,
  fuzzy,
  checkpoint_change#
FROM v$datafile
WHERE file# = &file_number;

2. Bring a Simple Offline Datafile Back Online

If the datafile is physically intact and was taken offline without a drop clause, recovery may be straightforward:

-- If the database is in ARCHIVELOG mode and archivelogs are available
RECOVER DATAFILE &file_number;

-- After successful recovery, bring the file online
ALTER DATABASE DATAFILE &file_number ONLINE;

-- Verify the file is now online
SELECT file#, name, status FROM v$datafile WHERE file# = &file_number;

3. Recover Using RMAN (Recommended for Media Failures)

-- Connect to RMAN and restore/recover the datafile
-- Run from RMAN prompt, not SQL*Plus

-- Check what backups are available
LIST BACKUP OF DATAFILE &file_number;

-- Restore and recover the specific datafile
-- (Database can remain open if ARCHIVELOG mode)
RUN {
  SQL 'ALTER DATABASE DATAFILE &file_number OFFLINE';
  RESTORE DATAFILE &file_number;
  RECOVER DATAFILE &file_number;
  SQL 'ALTER DATABASE DATAFILE &file_number ONLINE';
}

4. Recover an Entire Offline Tablespace

-- If an entire tablespace went offline, recover it as a unit
-- (Run from RMAN)
RUN {
  SQL 'ALTER TABLESPACE &tablespace_name OFFLINE IMMEDIATE';
  RESTORE TABLESPACE &tablespace_name;
  RECOVER TABLESPACE &tablespace_name;
  SQL 'ALTER TABLESPACE &tablespace_name ONLINE';
}

5. Resolve Permissions and Path Issues

If the file exists but Oracle cannot read it due to OS permissions:

# Check file ownership and permissions (run as root or oracle OS user)
ls -lh /path/to/datafile.dbf

# Restore correct ownership
chown oracle:oinstall /path/to/datafile.dbf

# Restore correct permissions
chmod 640 /path/to/datafile.dbf

After fixing OS permissions, attempt to recover and bring online:

ALTER DATABASE DATAFILE &file_number ONLINE;

6. Handle a Missing Datafile with No Backup

If the datafile is permanently lost and no backup exists, the tablespace must be dropped and recreated, losing all data it contained:

-- Only if the tablespace is non-critical and data loss is accepted
-- First, take the tablespace offline with the IMMEDIATE clause
ALTER TABLESPACE lost_tablespace OFFLINE IMMEDIATE;

-- Drop the tablespace, including its datafile references
DROP TABLESPACE lost_tablespace INCLUDING CONTENTS AND DATAFILES;

-- Recreate the tablespace
CREATE TABLESPACE lost_tablespace
  DATAFILE '/u01/oradata/lost_tablespace01.dbf' SIZE 1G
  AUTOEXTEND ON MAXSIZE 10G;

7. Open Database with RESETLOGS After Incomplete Recovery

In extreme scenarios where full recovery is impossible:

-- Open with RESETLOGS only after verifying all recoverable files are recovered
-- This discards all redo beyond the recovery point
ALTER DATABASE OPEN RESETLOGS;

Prevention Strategies

1. Proactive Datafile Monitoring

-- Create a scheduled job to alert on non-online datafiles
CREATE OR REPLACE PROCEDURE check_datafile_status AS
  v_count NUMBER;
BEGIN
  SELECT COUNT(*)
  INTO v_count
  FROM v$datafile
  WHERE status NOT IN ('ONLINE', 'SYSTEM');

  IF v_count > 0 THEN
    -- In production, replace with your alerting mechanism
    DBMS_OUTPUT.PUT_LINE('ALERT: ' || v_count || ' datafile(s) are not ONLINE.');
    FOR r IN (SELECT file#, name, status FROM v$datafile WHERE status NOT IN ('ONLINE','SYSTEM')) LOOP
      DBMS_OUTPUT.PUT_LINE('  File ' || r.file# || ': ' || r.name || ' [' || r.status || ']');
    END LOOP;
  END IF;
END;
/

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'CHECK_DATAFILE_STATUS_JOB',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'check_datafile_status',
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
    enabled         => TRUE,
    comments        => 'Alert on offline or error-state datafiles'
  );
END;
/

2. Validate RMAN Backups Regularly

-- From RMAN: validate that all datafile backups are usable
VALIDATE DATABASE;

-- Cross-check the repository against actual backup pieces
CROSSCHECK BACKUP;

-- Report any files needing backup
REPORT NEED BACKUP;

3. Storage and Filesystem Monitoring

• Implement OS-level monitoring on the filesystems hosting datafiles to alert before disks fill completely
• For ASM environments, monitor v$asm_diskgroup for OFFLINE_DISKS > 0 and USABLE_FILE_MB nearing zero
• Use Oracle OEM or third-party tools to alert on I/O error rates at the ASM disk level

4. Configuration Best Practices

• Always run production databases in ARCHIVELOG mode to enable online datafile recovery
• Ensure CONTROL_FILE_RECORD_KEEP_TIME is set long enough to retain backup metadata
• Keep at least two copies of the control file on separate disks or ASM disk groups
• Test the full restore-and-recover procedure in a non-production environment at least quarterly

Diagnostic Scripts

These Oracle Day by Day scripts can help diagnose storage and file-related issues:

• gvsess.sql — Identify sessions blocked due to unavailable objects

Related Errors

• ORA-01110 - Data file number string (accompanies ORA-00376)
• ORA-01578 - Oracle data block corrupted
• ORA-01652 - Unable to extend temp segment
• ORA-15001 - Diskgroup does not exist or not mounted
• ORA-15040 - Diskgroup is incomplete

Emergency Response

Quick Assessment

-- Run immediately to understand the scope of the problem
SELECT
  df.file#,
  df.name,
  df.status,
  ts.name AS tablespace_name,
  df.recover
FROM v$datafile df
JOIN v$tablespace ts ON df.ts# = ts.ts#
WHERE df.status NOT IN ('ONLINE', 'SYSTEM')
ORDER BY df.file#;

Immediate Actions

1. Do not shut down the database unless absolutely necessary — a running database preserves more recovery options
2. Check the alert log immediately for the ORA-01110 companion error naming the exact file path
3. Verify physical file presence at the OS level before attempting Oracle recovery commands
4. Engage your backup/recovery runbook — RMAN is the preferred recovery tool in all cases

Post-Resolution Verification

-- Confirm all datafiles are online after recovery
SELECT file#, name, status, checkpoint_change#
FROM v$datafile
ORDER BY file#;

-- Confirm tablespaces are online
SELECT tablespace_name, status FROM dba_tablespaces ORDER BY tablespace_name;

-- Run a checkpoint to flush all dirty buffers and update headers
ALTER SYSTEM CHECKPOINT;