ORA-27086: Unable to Lock File - Already in Use

ORA-27086: Unable to Lock File - Already in Use

Error Overview

Error Text: ORA-27086: unable to lock file - already in use

ORA-27086 is raised when Oracle tries to acquire an exclusive OS lock on a database file (datafile, controlfile, or log file) and another process has already locked it. This typically means another Oracle instance is using the file, or a stale lock remains from a previous instance crash.

Common Causes

Two Instances Trying to Use Same File

• Standby database opened on same files as primary
• RAC misconfiguration where two instances think they own a file
• Cloned database started without renaming files
• Test instance started on production files

Stale Locks After Instance Crash

• Instance crashed without releasing OS locks
• NFS lock daemon (lockd) holding stale lock
• Operating system did not clean up after process termination
• ASM instance crashed leaving lock files

NFS-Specific Issues

• NFS file locking misconfigured
• NLM (Network Lock Manager) not running
• Different mount options between nodes
• Lock file in /var/lib/nfs/ corrupted

Process Still Running

• Background process from previous startup still alive
• Shadow process holding file open
• Orphaned smon/pmon process

Diagnostic Steps

Identify Holding Process

# Find process holding the file
fuser -fv /u01/oradata/PROD/system01.dbf

# Or use lsof
lsof /u01/oradata/PROD/system01.dbf

# All Oracle processes accessing file
lsof | grep system01.dbf

Check for Other Instances

# All Oracle background processes
ps -ef | grep ora_pmon | grep -v grep

# Check if instance is registered
ps -ef | grep -E "ora_smon|ora_dbw|ora_lgwr" | grep -v grep

# Multiple instances? Check SIDs
cat /etc/oratab

Verify NFS Lock Status (if applicable)

# Check NFS lock daemon
systemctl status nfs-server
systemctl status rpc-statd

# View active NFS locks
showmount -a
cat /var/lib/nfs/sm/*

# On NFS server
showmount -e

Check Alert Log

tail -200 $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log \
    | grep -A5 "ORA-27086"

Typical output:

ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 49152

Resolution Steps

1. Stop Other Instance

-- Identify which instance holds the file
-- On suspect host
SELECT instance_name, status, host_name
FROM v$instance;

-- Shut down properly
SHUTDOWN IMMEDIATE;

-- If hung
SHUTDOWN ABORT;

After shutdown, verify no orphan processes:

ps -ef | grep ora_ | grep -v grep
# Should be empty for that instance

2. Clean Up Stale Processes

# Kill orphan Oracle processes (cautiously)
ps -ef | grep "ora_pmon_$ORACLE_SID" | awk '{print $2}'

# Check shared memory
ipcs -ma | grep oracle

# Remove orphan shared memory after instance fully down
ipcrm -m <shmid>

# Remove orphan semaphores
ipcs -s | grep oracle
ipcrm -s <semid>

WARNING: Only remove IPC resources after confirming no Oracle process owns them.

3. Restart NFS Lock Daemons

# On Linux
systemctl restart rpcbind
systemctl restart nfs-server
systemctl restart rpc-statd

# Clear stale lock state
rm /var/lib/nfs/sm/<hostname>
systemctl restart rpc-statd

4. Mount Database in Correct Mode

-- For physical standby, use STANDBY mode
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

-- Don't open standby in read-write while primary is open
-- Use Active Data Guard for read-only access:
STARTUP;
ALTER DATABASE OPEN READ ONLY;

5. Resolve Cloned Database

-- If clone is using same files as source, recreate with new names
-- Use RMAN DUPLICATE with DB_FILE_NAME_CONVERT
RMAN> DUPLICATE TARGET DATABASE TO clonedb
      DB_FILE_NAME_CONVERT=('/u01/oradata/PROD','/u01/oradata/CLONE')
      LOGFILE '/u01/oradata/CLONE/redo01.log' SIZE 100M;

6. RAC Specific - Check Cluster Lock

# Verify cluster ownership
crsctl stat res -t

# Check OCR for database registration
ocrdump /tmp/ocr.dmp
grep -i "database" /tmp/ocr.dmp

# Restart specific instance
srvctl stop instance -d PROD -i PROD1
srvctl start instance -d PROD -i PROD1

Common Scenarios

Scenario 1: Standby Started in Wrong Mode

ORA-27086: unable to lock file - already in use
File: /u01/oradata/PROD/system01.dbf

Fix: Standby should be MOUNT or OPEN READ ONLY, not OPEN. Primary holds exclusive locks.

Scenario 2: Test on Production Files

DBA cloned ORACLE_HOME, test instance points to production datafiles.

Fix: Verify db_create_file_dest and controlfile points to test paths only.

Scenario 3: After Instance Crash

After OOM-killed Oracle instance, can't restart:
ORA-27086: unable to lock file - already in use

Fix: Clean up IPC resources (ipcrm), kill orphan processes, then restart.

Sample Output

SQL> STARTUP;
ORACLE instance started.
Total System Global Area  4294967296 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode

[oracle@host]$ ps -ef | grep ora_pmon
oracle  12345  1  0 May08 ?  00:00:00 ora_pmon_PROD

# Existing PROD instance still running
SQL> ALTER DATABASE MOUNT;
ALTER DATABASE MOUNT
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/oradata/PROD/system01.dbf'
ORA-01207: file is more recent than control file - old control file
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 11: Resource temporarily unavailable

# Solution: shut down other instance first
[oracle@otherhost]$ . oraenv  # PROD
[oracle@otherhost]$ sqlplus / as sysdba
SQL> SHUTDOWN IMMEDIATE;

# Now retry
SQL> STARTUP;  -- success

Prevention Strategies

Distinct File Locations Per Environment

• Production: /u01/oradata/PROD/
• Test: /u01/oradata/TEST/
• Clone: /u01/oradata/CLONE/
• Never share filesystem paths between environments

Centralized Instance Registry

# /etc/oratab listing all instances on host
PROD:/u01/app/oracle/product/19c/dbhome_1:Y
TEST:/u01/app/oracle/product/19c/dbhome_1:N

# Verify uniqueness
awk -F: '{print $1}' /etc/oratab | sort | uniq -d
# Empty output = no duplicates

NFS Best Practices

# /etc/fstab - Oracle on NFS
nfs-server:/oracle /u01/oradata nfs \
  rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,actimeo=0,vers=3,timeo=600 0 0

Required NFS mount options (Oracle Doc ID 359515.1):

• hard - retry on failure
• nointr - don’t allow signal interruption
• actimeo=0 - disable attribute caching for shared writes

Startup Validation Script

#!/bin/bash
# Pre-startup check
SID=$1

# Check for existing instance
if pgrep -f "ora_pmon_$SID" > /dev/null; then
    echo "ERROR: Instance $SID already running"
    exit 1
fi

# Check for orphan IPC
if ipcs -m | grep oracle | grep -q "^"; then
    echo "WARN: Stale shared memory present"
    ipcs -m | grep oracle
fi

echo "OK: Safe to start $SID"

Standby Mode Discipline

• Use Data Guard Broker for state management
• Document who can open standby read-write (rare)
• Monitor primary/standby role consistency

Related Errors

• ORA-01102: Cannot mount database in EXCLUSIVE mode
• ORA-01122: Database file [N] failed verification check
• ORA-09817: Write to audit file failed
• ORA-27037: Unable to obtain file status
• ORA-27041: Unable to open file

Troubleshooting Checklist

• Identify file holder with fuser or lsof
• Check for other Oracle instances on same host/files
• Verify ora_pmon processes for unexpected SIDs
• Inspect IPC resources for stale entries
• Validate NFS lock daemon if file on NFS
• Restart NFS lockd if stale locks suspected
• Confirm standby in correct mode (MOUNT/READ ONLY)
• Clean up after crash before retry