DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01135 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01135: File Accessed for DML/Query is Offline

ORA-01135 occurs when a SQL statement (DML or query) tries to access a datafile that is currently in an offline state. Oracle cannot read from or write to that file, so it immediately raises this error to the calling session. This typically happens due to intentional administrative actions, storage-level failures, or incomplete recovery scenarios.

Top 3 Causes and Fixes

Cause 1: Datafile or Tablespace Manually Taken Offline

A DBA may have explicitly taken a datafile or tablespace offline for maintenance. Any session that touches objects stored in that file will immediately hit ORA-01135.

Diagnosis:

-- Check for offline datafiles
SELECT file#, name, status
FROM v$datafile
WHERE status IN ('OFFLINE', 'RECOVER');

-- Check tablespace status
SELECT tablespace_name, status
FROM dba_tablespaces
WHERE status != 'ONLINE';
Enter fullscreen mode Exit fullscreen mode

Fix (ARCHIVELOG mode):

-- Recover and bring the datafile back online
RECOVER DATAFILE '/u01/oradata/ORCL/users01.dbf';
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' ONLINE;

-- Or recover at tablespace level
ALTER TABLESPACE users ONLINE;
Enter fullscreen mode Exit fullscreen mode

⚠️ In NOARCHIVELOG mode, you cannot recover an offline datafile without a full backup. Always run production databases in ARCHIVELOG mode.

Cause 2: Disk / Filesystem Failure Caused Oracle to Auto-Offline the File

When Oracle encounters an I/O error (disk full, NFS unmounted, storage failure), it automatically marks the affected datafile as offline to protect database consistency. You will see preceding errors like ORA-01115, ORA-01116, or ORA-27072 in the Alert Log before ORA-01135 appears.

Diagnosis:

-- Check files needing recovery
SELECT file#, online, online_status, error, change#
FROM v$recover_file;

-- Confirm physical issue is resolved first, then recover via RMAN
-- Run from RMAN prompt:
-- RMAN> RESTORE DATAFILE 5;
-- RMAN> RECOVER DATAFILE 5;
Enter fullscreen mode Exit fullscreen mode

Fix:

-- After resolving the physical/OS issue and running RMAN recovery:
ALTER DATABASE DATAFILE 5 ONLINE;

-- Verify status
SELECT file#, name, status FROM v$datafile WHERE file# = 5;
Enter fullscreen mode Exit fullscreen mode

Cause 3: Incomplete Recovery / RMAN Point-in-Time Recovery Left Files Offline

After a Point-in-Time Recovery (PITR) or OPEN RESETLOGS, some datafiles may have SCN mismatches and remain offline.

Diagnosis:

-- Check SCN alignment
SELECT file#, status, checkpoint_change#, fuzzy
FROM v$datafile_header
WHERE status != 'ONLINE';

-- Cross-check with control file
SELECT file#, name, status, checkpoint_change#
FROM v$datafile;
Enter fullscreen mode Exit fullscreen mode

Fix:

-- Restore and recover the specific datafile using RMAN
-- RMAN> RESTORE DATAFILE <file#>;
-- RMAN> RECOVER DATAFILE <file#>;

-- Then bring it online
ALTER DATABASE DATAFILE 3 ONLINE;

-- Confirm database health
SELECT name, open_mode, log_mode FROM v$database;
Enter fullscreen mode Exit fullscreen mode

Quick Prevention Tips

1. Monitor datafile status proactively

Schedule a monitoring job that alerts the DBA team whenever any datafile goes offline.

-- Run this regularly via DBMS_SCHEDULER or cron
SELECT d.file#, t.name AS tablespace, d.name AS file_name, d.status
FROM v$datafile d
JOIN v$tablespace t ON d.ts# = t.ts#
WHERE d.status NOT IN ('ONLINE', 'SYSTEM');
Enter fullscreen mode Exit fullscreen mode

2. Always run in ARCHIVELOG mode with a tested RMAN backup strategy

-- Verify archive log mode
SELECT log_mode FROM v$database;

-- Validate recent RMAN backups (run in RMAN)
-- RMAN> VALIDATE BACKUPSET ALL;
-- RMAN> REPORT NEED BACKUP;
Enter fullscreen mode Exit fullscreen mode

Without ARCHIVELOG mode and valid backups, an offline datafile can mean permanent data loss. Run regular recovery drills so your team can restore quickly when it matters most.

Related Errors

Error Code Description
ORA-01116 Error opening database file — often precedes ORA-01135 in Alert Log
ORA-01115 I/O error reading block from file — storage-level failure indicator
ORA-01157 Cannot identify/lock datafile — file missing at OS level
ORA-00376 File cannot be read at this time — similar offline access error

📖 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)