ORA-01157 Deep Dive: Cannot Identify/Lock Data File — Diagnosis and Recovery

1. Introduction to ORA-01157

The ORA-01157 error is one of the most dreaded messages an Oracle DBA can encounter. This error indicates that the Database Writer (DBWR) background process cannot find or lock a datafile, potentially preventing the database from opening or causing critical application failures.

1.1 Associated Error Codes

ORA-01157 rarely appears in isolation. It is typically accompanied by additional diagnostic errors:

1.2 Impact Assessment Matrix

The severity of ORA-01157 depends entirely on which tablespace's datafile is affected:

2. Root Cause Analysis

2.1 Category 1: Physical File Issues

2.1.1 Accidental Deletion or Corruption

The most common cause is the physical removal or corruption of datafiles:

• Human error — Manual file deletion by operators or administrators
• Automated scripts — Cleanup scripts with incorrect logic targeting database files
• Disk failures — Bad sectors or complete drive failure
• Ransomware/malware — Encryption or deletion of database files
• Storage array failures — LUN corruption or mapping issues

-- Check file existence and status from Oracle
SELECT file#, name, status, enabled 
FROM v$datafile 
WHERE file# = &affected_file_number;

-- Verify from OS level (Linux/Unix)
ls -la /path/to/datafile.dbf

-- Check filesystem integrity
df -h | grep /oradata

2.1.2 File Rename or Relocation

When datafiles are renamed or moved at the OS level without updating Oracle's control file:

-- Check control file's record of datafile location
SELECT name FROM v$datafile;

-- If file was moved, update Oracle (database must be mounted)
ALTER DATABASE RENAME FILE '/old/path/file.dbf' TO '/new/path/file.dbf';

2.2 Category 2: Locking Conflicts

2.2.1 Multiple Oracle Instances

Another process or instance may have locked the datafile:

# Check for duplicate processes accessing the file (Linux)
fuser /path/to/datafile.dbf
lsof /path/to/datafile.dbf

# Identify conflicting processes
ps -ef | grep ora_ | grep 

2.2.2 Backup Software Conflicts

Third-party backup tools sometimes hold file locks during hot backups:

# Common backup process patterns to check
ps -ef | grep -E "(backup|veeam|netbackup|commvault|rman)"

# Check file descriptor locks
lsof +D /oradata | grep -v oracle

2.3 Category 3: ASM-Specific Issues

2.3.1 Diskgroup Not Mounted

-- Check ASM diskgroup status
SELECT name, state, type FROM v$asm_diskgroup;

-- Mount missing diskgroup
ALTER DISKGROUP data_dg MOUNT;

2.3.2 Permission Issues in ASM

-- Verify ASM file permissions
SELECT file_number, permissions FROM v$asm_file 
WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name = 'DATA');

-- Connect with proper privilege for ASM operations
sqlplus / as sysasm

2.3.3 RAC-Specific ASM Issues

In RAC environments, diskgroups must be visible to all nodes:

-- Check diskgroup visibility across nodes
SELECT inst_id, name, state FROM gv$asm_diskgroup;

-- Verify CSS/CRS communication
crsctl check crs

2.4 Category 4: Storage Configuration Issues

2.4.1 NFS Configuration Problems

# Verify NFS mount options
mount | grep nfs

# Required mount options for Oracle on NFS
# rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600

2.4.2 SAN/Storage Array Issues

• LUN mapping inconsistencies
• Multipathing failures
• Storage-side timeout issues

# Check multipath status (Linux)
multipath -ll

# Verify disk accessibility
dd if=/dev/sdb of=/dev/null bs=512 count=1

3. Diagnostic Methodology

3.1 Initial Assessment

Step 1: Examine the Alert Log

# Location varies by version and configuration
tail -500 $ORACLE_BASE/diag/rdbms/<db_name>/<SID>/trace/alert_<SID>.log

# Key patterns to search
grep -E "(ORA-01157|ORA-01110|DBWR)" alert_<SID>.log

Step 2: Check DBWR Trace File

The error message explicitly mentions "see DBWR trace file":

# Find recent DBWR traces
ls -lt $ORACLE_BASE/diag/rdbms/<db_name>/<SID>/trace/*dbw*.trc | head -5

# Examine the trace
cat <latest_dbwr_trace>.trc

3.2 Database-Level Diagnostics

-- From a connected session (if database is at least mounted)
-- Check datafile status
SELECT file#, status, name, 
       bytes/1024/1024 as size_mb,
       checkpoint_change#
FROM v$datafile;

-- Check recovery status
SELECT * FROM v$recover_file;

-- Verify file header information
SELECT file#, status, error, recover, 
       fuzzy, checkpoint_change#
FROM v$datafile_header;

-- For tablespace-level view
SELECT tablespace_name, file_id, file_name, status
FROM dba_data_files
ORDER BY tablespace_name, file_id;

3.3 Comprehensive OS-Level Diagnostic Script

#!/bin/bash
# Comprehensive OS-level datafile diagnostic script
DATAFILE=$1

echo "=== File Existence Check ==="
ls -la $DATAFILE

echo "=== File Ownership and Permissions ==="
stat $DATAFILE

echo "=== Process Locks ==="
fuser -v $DATAFILE

echo "=== Open File Descriptors ==="
lsof $DATAFILE

echo "=== Filesystem Status ==="
df -h $(dirname $DATAFILE)

echo "=== Recent System Errors ==="
dmesg | tail -50 | grep -i -E "(error|fail|io)"

4. Recovery Strategies by Tablespace Type

4.1 Scenario 1: Temporary Tablespace Datafile

This is the simplest recovery scenario since temporary tablespaces contain no persistent data.

-- Step 1: Startup mount (if database is down)
STARTUP MOUNT;

-- Step 2: Take the datafile offline with DROP option
ALTER DATABASE DATAFILE '/path/to/temp01.dbf' OFFLINE DROP;

-- Step 3: Open the database
ALTER DATABASE OPEN;

-- Step 4: Drop and recreate the temporary tablespace
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

CREATE TEMPORARY TABLESPACE temp 
TEMPFILE '/path/to/new_temp01.dbf' SIZE 2G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Step 5: Set as default temporary tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

4.2 Scenario 2: Read-Only Tablespace

Read-only tablespaces require only backup restoration without media recovery:

-- Step 1: Verify the tablespace was read-only
SELECT tablespace_name, status FROM dba_tablespaces
WHERE tablespace_name = 'READONLY_TS';

-- Step 2: Restore from backup
RMAN> RESTORE DATAFILE <file#>;

-- Step 3: No recovery needed if backup was taken after tablespace went read-only
-- If backup was from read-write period:
RMAN> RECOVER DATAFILE <file#>;

4.3 Scenario 3: User/Application Tablespace (with Backup)

4.3.1 RMAN Recovery Method

-- Database can remain open if the tablespace is not critical

-- Step 1: Take the datafile offline
ALTER DATABASE DATAFILE <file#> OFFLINE;

-- Step 2: Restore the datafile
RMAN> RESTORE DATAFILE <file#>;

-- Step 3: Recover the datafile
RMAN> RECOVER DATAFILE <file#>;

-- Step 4: Bring datafile online
SQL> ALTER DATABASE DATAFILE <file#> ONLINE;

4.3.2 Manual Recovery (Using Backup Copies)

-- Step 1: Copy backup file to the datafile location
-- (OS Level): cp /backup/users01.dbf /oradata/users01.dbf

-- Step 2: Set correct ownership
-- chown oracle:oinstall /oradata/users01.dbf

-- Step 3: Recover from SQL*Plus
RECOVER DATAFILE '/oradata/users01.dbf';
-- Apply redo logs as prompted

-- Step 4: Bring online
ALTER DATABASE DATAFILE '/oradata/users01.dbf' ONLINE;

4.4 Scenario 4: UNDO Tablespace

UNDO tablespace recovery requires careful handling to preserve transaction integrity.

4.4.1 After Clean Shutdown

-- Step 1: Modify init.ora/spfile
ALTER SYSTEM SET undo_management=MANUAL SCOPE=SPFILE;

-- Step 2: Restart to mount
STARTUP MOUNT;

-- Step 3: Take the UNDO datafile offline
ALTER DATABASE DATAFILE <undo_file#> OFFLINE DROP;

-- Step 4: Open the database
ALTER DATABASE OPEN;

-- Step 5: Create new UNDO tablespace
CREATE UNDO TABLESPACE undotbs2 
DATAFILE '/oradata/undotbs02.dbf' SIZE 500M;

-- Step 6: Switch to new UNDO tablespace
ALTER SYSTEM SET undo_tablespace=UNDOTBS2;

-- Step 7: Revert undo_management
ALTER SYSTEM SET undo_management=AUTO SCOPE=SPFILE;

4.4.2 After Crash or SHUTDOWN ABORT

If the database crashed with active transactions in the lost UNDO segment:

-- This requires recovery from backup
RMAN> RESTORE DATAFILE <undo_file#>;
RMAN> RECOVER DATAFILE <undo_file#>;

ALTER SYSTEM SET "_allow_resetlogs_corruption"=TRUE SCOPE=SPFILE;
ALTER SYSTEM SET "_corrupted_rollback_segments"='<segment_list>' SCOPE=SPFILE;

4.5 Scenario 5: SYSTEM/SYSAUX Tablespace

This is the most critical scenario requiring complete database recovery.

4.5.1 Standard RMAN Recovery

-- Step 1: Startup mount
STARTUP MOUNT;

-- Step 2: Restore SYSTEM datafile
RMAN> RESTORE DATAFILE 1;  -- SYSTEM is typically file# 1

-- Step 3: Recover with all available logs
RMAN> RECOVER DATAFILE 1;

-- Step 4: Open the database
ALTER DATABASE OPEN;

4.5.2 Complete Database Restore

If multiple critical datafiles are affected:

RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;

5. No-Backup Recovery: When Standard Methods Fail

5.1 Understanding the Challenge

When ORA-01157 occurs without available backups, organizations face potential catastrophic data loss. Traditional Oracle recovery methods cannot help if:

• No RMAN backups exist
• All archive logs are unavailable
• The SYSTEM tablespace is damaged beyond standard recovery
• Multiple datafiles are corrupted simultaneously

5.2 Oracle's Internal Tool: DUL

Oracle Support provides an internal tool called DUL (Data Unloader) for last-resort recovery. However:

• Availability: Only through Oracle Support engagement
• Expertise Required: Deep Oracle internals knowledge
• License Restrictions: Cannot be retained after engagement
• Platform Limitations: Not available for all platforms

5.3 DBRECOVER: Professional Data Extraction Solution

For organizations requiring independent recovery capabilities, DBRECOVER represents the most comprehensive third-party Oracle data recovery solution available. It provides direct datafile access capabilities when the database cannot be opened.

5.3.1 Key Capabilities

5.3.2 Technical Architecture

DBRECOVER operates on the principle of "dirty reads" — it assumes all transactions are committed and reads data directly from the datafile blocks:

5.3.3 Using DBRECOVER for ORA-01157 Recovery

Scenario: SYSTEM tablespace intact, user tablespace datafile lost without backup.

# Step 1: Launch DBRECOVER GUI
cd /opt/dbrecover
sh prm.sh   # Linux/Unix
# or
prm.bat     # Windows

# Step 2: Configure data source
# - Select "Add Datafile" from File menu
# - Browse to SYSTEM tablespace datafile
# - Add all available datafiles

# Step 3: Configure database parameters
# - Database block size: 8192 (or your db_block_size)
# - Character set: AL32UTF8 (or your database charset)
# - Endian format: Little Endian for Linux x86

# Step 4: Load data dictionary
# - Click "Load Dict" button
# - DBRECOVER parses SYSTEM tablespace to reconstruct dictionary

# Step 5: Browse and select objects
# - Navigate through schema tree
# - Select tables to recover

# Step 6: Export or DataBridge
# Option A: Export to flat files, then use SQL*Loader
# Option B: Use DataBridge for direct database-to-database transfer

5.3.4 DBRECOVER vs Traditional Recovery

5.3.5 When to Use DBRECOVER

DBRECOVER is appropriate when:

1. No backups exist for the affected datafiles
2. Archive logs are missing or corrupted
3. SYSTEM tablespace is damaged beyond Oracle recovery
4. Truncate/Drop recovery is needed without flashback
5. Time is critical and standard recovery will take too long
6. Ransomware/malware has encrypted datafile headers
7. ASM diskgroup is damaged but disks are accessible

6. Prevention Strategies

6.1 Backup Best Practices

-- Implement comprehensive RMAN backup strategy
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

-- Daily incremental backup script
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;
RMAN> BACKUP CURRENT CONTROLFILE;
RMAN> DELETE NOPROMPT OBSOLETE;

6.2 Monitoring and Alerting

-- Create monitoring procedure for datafile accessibility
CREATE OR REPLACE PROCEDURE check_datafile_health AS
  v_status VARCHAR2(20);
  v_error  VARCHAR2(200);
BEGIN
  FOR df IN (SELECT file#, name FROM v$datafile) LOOP
    BEGIN
      SELECT status INTO v_status 
      FROM v$datafile_header 
      WHERE file# = df.file#;
      
      IF v_status != 'ONLINE' THEN
        DBMS_OUTPUT.PUT_LINE('ALERT: Datafile ' || df.name || 
                             ' status: ' || v_status);
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('CRITICAL: Cannot access ' || df.name);
    END;
  END LOOP;
END;
/

6.3 Infrastructure Recommendations

• Use ASM for automatic storage management and mirroring
• Implement proper RAID levels (RAID 10 for datafiles)
• Configure multipathing for SAN storage
• Test recovery procedures regularly
• Maintain standby databases using Data Guard
• Use Oracle Flashback technologies for logical errors
• Implement file system snapshots where possible

6.4 Operational Safeguards

# Protect datafile directories (Linux)
chmod 750 /oradata
chattr +i /oradata/*.dbf  # Immutable attribute (use carefully)

# Implement sudo restrictions for Oracle files
# /etc/sudoers
oracle ALL=(root) !/bin/rm /oradata/*

# Regular backup validation
rman target / << EOF
RESTORE DATABASE VALIDATE;
EOF

7. Special Cases: RAC, Data Guard, and PDB

7.1 RAC Environment Considerations

-- In RAC, verify all instances see the datafile
SELECT inst_id, file#, status, name 
FROM gv$datafile 
WHERE status != 'ONLINE';

-- Check if ASM diskgroup is mounted on all nodes
SELECT inst_id, name, state FROM gv$asm_diskgroup;

-- Force diskgroup mount on specific instance
ALTER DISKGROUP data_dg MOUNT FORCE;

7.2 Data Guard Standby Issues

-- Check standby datafile status
SELECT file#, status, name FROM v$datafile;

-- If standby datafile is missing, create it
ALTER DATABASE CREATE DATAFILE '/standby/path/file.dbf' 
AS '/primary/path/file.dbf';

-- Resume recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

7.3 Pluggable Database (PDB) Scenarios

-- Check PDB datafile status
ALTER SESSION SET CONTAINER = <pdb_name>;
SELECT file#, name, status FROM v$datafile;

-- Recovery in CDB context
ALTER SESSION SET CONTAINER = CDB$ROOT;
RMAN> RESTORE PLUGGABLE DATABASE <pdb_name>;
RMAN> RECOVER PLUGGABLE DATABASE <pdb_name>;

8. Quick Reference Commands

Diagnostic Queries

-- Essential diagnostic queries
SELECT file#, name, status FROM v$datafile;
SELECT * FROM v$recover_file;
SELECT file#, error FROM v$datafile_header WHERE error IS NOT NULL;

Recovery Commands

-- SQL*Plus recovery commands
ALTER DATABASE DATAFILE <n> OFFLINE;
ALTER DATABASE DATAFILE <n> OFFLINE DROP;
RECOVER DATAFILE <n>;
ALTER DATABASE DATAFILE <n> ONLINE;

RMAN Commands

-- RMAN recovery commands
RMAN> RESTORE DATAFILE <n>;
RMAN> RECOVER DATAFILE <n>;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;

Conclusion

ORA-01157 represents a serious database availability issue that demands immediate attention. The key to successful resolution lies in:

• Rapid diagnosis — Quickly identify whether the issue is physical file loss, locking conflict, or permission-related
• Appropriate recovery strategy — Match the recovery approach to the tablespace type and backup availability
• Fallback options — Understand that tools like DBRECOVER exist for no-backup scenarios
• Prevention focus — Implement comprehensive backup, monitoring, and protection strategies

While Oracle's built-in recovery mechanisms handle most scenarios effectively, the existence of third-party tools like DBRECOVER provides a crucial safety net for catastrophic situations where standard recovery fails. Every Oracle DBA should be aware of these options and have recovery procedures documented and tested before they're needed.

Expert Assistance

For complex ORA-01157 recovery scenarios, especially when backups are unavailable or data must be extracted from corrupted datafiles, our experts are available to help. Contact us at [email protected]