ORA-01119: Error Creating Database File - Fix Permissions

ORA-01119: Error in Creating Database File

Error Overview

Error Text: ORA-01119: error in creating database file '/path/to/file.dbf'

ORA-01119 occurs when Oracle cannot create a new datafile, tempfile, or control file at the specified path. It always appears alongside a secondary OS error (such as ORA-27040 or ORA-27044) that provides the underlying reason. Common scenarios include adding a datafile to an existing tablespace, creating a new tablespace, or running a CREATE DATABASE script where the target directory is inaccessible to the Oracle process.

The error prevents any structural change that requires Oracle to create a new file on disk. It does not corrupt existing files and does not require database recovery — it requires fixing the OS-level or ASM-level condition that blocked file creation.

Common Causes

1. Directory Does Not Exist or Is Not Accessible

• The directory portion of the specified path does not exist on the server
• Oracle OS user (oracle) does not have write permission on the target directory
• The path points to a directory owned by root or another user without group write access

2. Insufficient Disk Space

• The filesystem hosting the target directory is full or nearly full
• The SIZE clause of the ADD DATAFILE statement exceeds available space
• Thin-provisioned storage is over-committed and has no free capacity

3. OS-Level File Descriptor or Limit Constraints

• MAXDATAFILES in the control file has been reached
• OS-level open file limit (ulimit -n) too low for the Oracle process
• Operating system limit on files per directory has been reached

4. ASM Diskgroup Issues

• The ASM diskgroup referenced in the +DG path does not exist or is not mounted
• ASM diskgroup has insufficient free space for the requested file
• Oracle instance is not connected to the correct ASM instance

5. OMF (Oracle Managed Files) Misconfiguration

• DB_CREATE_FILE_DEST points to a directory that does not exist or lacks permissions
• OMF is enabled but the underlying ASM diskgroup is offline
• Conflict between OMF settings and an explicit file path in the DDL

Diagnostic Queries

Check Filesystem Space and Current File Inventory

-- How many datafiles are currently open against the limit?
SELECT
  COUNT(*)                         AS current_datafiles,
  (SELECT value FROM v$parameter WHERE name = 'db_files') AS db_files_limit,
  (SELECT COUNT(*) FROM v$controlfile) AS control_file_count
FROM v$datafile;

-- Control file MAXDATAFILES limit (from creation parameters)
-- Cannot query directly — check via:
-- RMAN> REPORT SCHEMA;

-- All existing datafile paths — check which directories are in use
SELECT
  tablespace_name,
  file_name,
  ROUND(bytes / 1024 / 1024, 2)    AS size_mb,
  status,
  autoextensible
FROM dba_data_files
ORDER BY tablespace_name, file_id;

-- Tempfile paths
SELECT
  tablespace_name,
  file_name,
  ROUND(bytes / 1024 / 1024, 2)    AS size_mb,
  status,
  autoextensible
FROM dba_temp_files
ORDER BY tablespace_name;

Check OMF and File Destination Parameters

-- OMF and creation destination parameters
SELECT name, value
FROM v$parameter
WHERE name IN (
  'db_create_file_dest',
  'db_create_online_log_dest_1',
  'db_create_online_log_dest_2',
  'db_recovery_file_dest',
  'db_files',
  'maxdatafiles'
)
ORDER BY name;

-- ASM diskgroup status and free space
SELECT
  group_number,
  name,
  state,
  type,
  ROUND(total_mb / 1024, 2)   AS total_gb,
  ROUND(free_mb / 1024, 2)    AS free_gb,
  ROUND(free_mb * 100.0 / NULLIF(total_mb, 0), 1) AS pct_free
FROM v$asm_diskgroup
ORDER BY name;

Validate the Target Directory at the OS Level

-- Use UTL_FILE to test write access to a directory
-- First create a directory object pointing to the target path:
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/oradata/newts';

-- Then test write access:
DECLARE
  fh UTL_FILE.FILE_TYPE;
BEGIN
  fh := UTL_FILE.FOPEN('TEST_DIR', 'write_test.tmp', 'W');
  UTL_FILE.FCLOSE(fh);
  UTL_FILE.FREMOVE('TEST_DIR', 'write_test.tmp');
  DBMS_OUTPUT.PUT_LINE('Directory is writable by Oracle process.');
EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Directory does not exist or is not accessible.');
  WHEN UTL_FILE.ACCESS_DENIED THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Write permission denied.');
END;
/

Check for Near-Limit Conditions

-- Are any tablespaces approaching their maximum size?
SELECT
  df.tablespace_name,
  COUNT(df.file_id)                                        AS file_count,
  ROUND(SUM(df.bytes) / 1024 / 1024 / 1024, 2)            AS total_gb,
  ROUND(SUM(df.maxbytes) / 1024 / 1024 / 1024, 2)         AS max_gb,
  ROUND(SUM(fs.bytes) / 1024 / 1024 / 1024, 2)            AS free_gb
FROM dba_data_files df
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) AS bytes
  FROM dba_free_space
  GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
GROUP BY df.tablespace_name
ORDER BY free_gb ASC NULLS FIRST;

-- db_files parameter vs current usage
SELECT
  (SELECT COUNT(*) FROM v$datafile) AS datafiles_in_use,
  TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'db_files')) AS db_files_param,
  TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'db_files'))
    - (SELECT COUNT(*) FROM v$datafile) AS slots_remaining;

Step-by-Step Resolution

1. Read the Full Error Stack

ORA-01119 is never the only error. The line immediately after it in the alert log or SQL*Plus output provides the root cause:

ORA-01119: error in creating database file '/u02/oradata/users02.dbf'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 13) Permission denied

Always resolve the OS error (OS 13, OS 28, OS 17, etc.) first.

2. Create the Target Directory with Correct Ownership

# From root or sudo:
mkdir -p /u02/oradata/newts
chown oracle:dba /u02/oradata/newts
chmod 750 /u02/oradata/newts

# Verify:
ls -ld /u02/oradata/newts
# drwxr-x--- 2 oracle dba 4096 Mar 24 10:00 /u02/oradata/newts

3. Fix Permissions on an Existing Directory

# Check current permissions:
ls -ld /u01/oradata/

# Fix ownership if needed:
chown oracle:dba /u01/oradata/
chmod 750 /u01/oradata/

# If using NFS, verify the NFS mount options include rw for the oracle UID:
mount | grep /u01/oradata

4. Free Disk Space or Choose a Different Filesystem

# Check filesystem usage:
df -h /u01/oradata/

# If full, clean up old trace files, audit files, or archived logs:
find /u01/app/oracle/diag -name "*.trc" -mtime +30 -delete
find /u01/app/oracle/product/*/dbs -name "snap*.trc" -delete

5. Retry the DDL with a Valid Path

-- Add datafile to an existing tablespace:
ALTER TABLESPACE users
  ADD DATAFILE '/u02/oradata/users02.dbf'
  SIZE 2G AUTOEXTEND ON MAXSIZE 10G;

-- Or create a new tablespace:
CREATE TABLESPACE new_ts
  DATAFILE '/u02/oradata/newts01.dbf'
  SIZE 1G AUTOEXTEND ON MAXSIZE 32G;

6. Increase db_files If the Limit Is Reached

-- Check current limit and usage:
SELECT COUNT(*) FROM v$datafile;
SHOW PARAMETER db_files;

-- Increase the parameter (requires restart):
ALTER SYSTEM SET db_files = 500 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

-- Note: MAXDATAFILES in the control file is a separate hard limit.
-- To increase it, recreate the control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/cf_trace.sql' RESETLOGS;
-- Edit the trace, update MAXDATAFILES, then run as CREATE CONTROLFILE.

7. Fix ASM Diskgroup Issues

-- Check ASM diskgroup state:
SELECT name, state, free_mb FROM v$asm_diskgroup;

-- Mount a dismounted diskgroup (run as SYSASM from ASM instance):
ALTER DISKGROUP data MOUNT;

-- Add disk to expand a full diskgroup:
ALTER DISKGROUP data ADD DISK '/dev/sde' NAME data05;

-- Then retry the datafile creation with the ASM path:
ALTER TABLESPACE users ADD DATAFILE '+DATA' SIZE 2G;

Prevention Strategies

1. Use OMF to Avoid Path Errors

-- Set OMF destination — Oracle manages paths automatically:
ALTER SYSTEM SET db_create_file_dest = '/u01/oradata' SCOPE=BOTH;
-- Or for ASM:
ALTER SYSTEM SET db_create_file_dest = '+DATA' SCOPE=BOTH;

-- With OMF, simply run:
ALTER TABLESPACE users ADD DATAFILE SIZE 2G;

2. Monitor Disk Space Proactively

-- Schedule a daily check of tablespace fullness:
SELECT
  tablespace_name,
  ROUND((1 - NVL(f.free_bytes, 0) / t.total_bytes) * 100, 1) AS pct_used
FROM (
  SELECT tablespace_name, SUM(bytes) AS total_bytes FROM dba_data_files GROUP BY tablespace_name
) t
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) AS free_bytes FROM dba_free_space GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
WHERE (1 - NVL(f.free_bytes, 0) / t.total_bytes) * 100 > 85
ORDER BY pct_used DESC;

3. Standardize Directory Structure and Permissions

• Maintain a documented standard path layout (e.g., /u01/oradata/<SID>/) created during installation
• Include directory creation and ownership in all provisioning runbooks
• Audit permissions monthly: find /u01/oradata -not -user oracle -not -type l

4. Track db_files Headroom

-- Alert when fewer than 50 file slots remain:
SELECT
  TO_NUMBER((SELECT value FROM v$parameter WHERE name = 'db_files'))
    - (SELECT COUNT(*) FROM v$datafile) AS slots_remaining
FROM dual;

Related Errors

• ORA-01110 - Data file identification (companion error)
• ORA-01157 - Cannot identify/lock data file
• ORA-01653 - Unable to extend table in tablespace
• ORA-01658 - Unable to create INITIAL extent for segment

Emergency Response

Quick Fixes

# 1. Create missing directory immediately:
mkdir -p /path/to/dir && chown oracle:dba /path/to/dir && chmod 750 /path/to/dir

# 2. Check available space:
df -h /path/to/dir

# 3. Find and clean large files (careful — confirm before deleting):
find /u01 -name "*.arc" -size +1G -mtime +7 | head -20

-- 4. Retry DDL after fixing OS issue:
ALTER TABLESPACE users ADD DATAFILE '/path/to/dir/users02.dbf' SIZE 1G;

Post-Resolution Checks

-- Confirm new file is online:
SELECT file_id, file_name, status, bytes/1024/1024 AS mb
FROM dba_data_files
ORDER BY file_id DESC
FETCH FIRST 5 ROWS ONLY;

-- Confirm tablespace is healthy:
SELECT tablespace_name, status FROM dba_tablespaces WHERE status != 'ONLINE';