DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01516 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01516: Nonexistent Log File, Data File, or Temporary File

ORA-01516 is an Oracle error that occurs when a command references a log file, data file, or temporary file that does not exist or cannot be located by the database. This typically happens when an invalid file number is supplied to ALTER DATABASE or ALTER TABLESPACE commands, or when a physical file has been moved or deleted outside of Oracle's file management procedures. Catching and resolving this error quickly is critical to maintaining database availability.

Top 3 Causes

1. Invalid File Number Reference

The most common cause is simply specifying a file number that does not exist in the database's control file. Always verify file numbers before running administrative commands.

-- Check valid datafile numbers
SELECT FILE#, NAME, STATUS
FROM V$DATAFILE
ORDER BY FILE#;

-- Check valid log file groups
SELECT GROUP#, MEMBER, STATUS
FROM V$LOGFILE
ORDER BY GROUP#;

-- Check valid temp file numbers
SELECT FILE#, NAME, STATUS
FROM V$TEMPFILE
ORDER BY FILE#;
Enter fullscreen mode Exit fullscreen mode

2. Physical File Deleted or Moved at the OS Level

When a file is moved or deleted directly from the operating system without using Oracle commands, the control file still holds a reference to the old path, causing ORA-01516 on next access.

-- Rename/relocate a datafile (run in MOUNT state)
STARTUP MOUNT;

ALTER DATABASE RENAME FILE
  '/old/path/datafile01.dbf'
TO
  '/new/path/datafile01.dbf';

ALTER DATABASE OPEN;
Enter fullscreen mode Exit fullscreen mode

3. Missing Temporary File After Restart

Temporary files can disappear after a database restart or crash, especially in RAC environments. Any subsequent operation that references the missing tempfile number will throw ORA-01516.

-- Check current temp file status
SELECT FILE#, NAME, STATUS, BYTES/1024/1024 AS SIZE_MB
FROM V$TEMPFILE;

-- Re-add a missing temporary file
ALTER TABLESPACE TEMP ADD TEMPFILE
  '/u01/app/oracle/oradata/ORCL/temp01.dbf'
SIZE 2048M
REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Restore a missing datafile using RMAN:

-- Connect to RMAN and restore the specific datafile
RMAN TARGET /

RUN {
  RESTORE DATAFILE 5;
  RECOVER DATAFILE 5;
}

-- Bring the file back online in SQL*Plus
ALTER DATABASE DATAFILE 5 ONLINE;
Enter fullscreen mode Exit fullscreen mode

Drop and recreate a missing redo log group:

-- If a log group member is missing, add a new member or drop and recreate
ALTER DATABASE ADD LOGFILE MEMBER
  '/u01/app/oracle/oradata/ORCL/redo03b.log'
TO GROUP 3;

-- If the group itself is invalid and not current/active
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE ADD LOGFILE GROUP 3
  '/u01/app/oracle/oradata/ORCL/redo03.log'
SIZE 200M;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Monitor file status daily with an automated script

Schedule the query below to run every day and alert on any abnormal file status before it becomes a critical incident.

SELECT 'DATAFILE' AS TYPE, FILE#, NAME, STATUS
FROM V$DATAFILE
WHERE STATUS NOT IN ('SYSTEM', 'ONLINE', 'AVAILABLE')
UNION ALL
SELECT 'TEMPFILE', FILE#, NAME, STATUS
FROM V$TEMPFILE
WHERE STATUS != 'ONLINE'
UNION ALL
SELECT 'LOGFILE', F.GROUP#, F.MEMBER, L.STATUS
FROM V$LOGFILE F
JOIN V$LOG L ON F.GROUP# = L.GROUP#
WHERE L.STATUS = 'INVALID' OR F.STATUS = 'INVALID';
Enter fullscreen mode Exit fullscreen mode

2. Enforce a strict change control policy for Oracle file directories

Never allow OS-level file operations (move, delete, rename) on Oracle-managed directories without DBA approval and coordination. All file management must go through Oracle-native commands (ALTER DATABASE, RMAN) to keep the control file consistent with the physical storage layer. This single practice eliminates the majority of ORA-01516 occurrences in production environments.

Related Errors

  • ORA-01157 – Cannot identify/lock data file; often appears alongside ORA-01516 when a file is inaccessible.
  • ORA-01110 – Companion error that provides the actual file name and number involved, making root cause analysis easier.
  • ORA-00313 – Failure to open redo log group, commonly seen when a log file is physically missing.

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