Oracle Database on NFS : Resolving "ORA-27086: unable to lock file - already in use" Error

Some Context

Oracle database was hosted on ZFS Storage Appliance (NAS). The database files are accessible from the database server node via NFS mounted filesystems. Solaris 10 is the operating system on DB node.

Someone forgets to shutdown the database instance and unmount the remote filesystems before rebooting the database server node. After the system boots up, Oracle RDBMS fails to bring up the database due to locked-out data files.

eg.,

SQL> startup
ORACLE instance started.

Total System Global Area 1.7108E+10 bytes
Fixed Size      2165208 bytes
Variable Size   9965671976 bytes
Database Buffers  6845104128 bytes
Redo Buffers    295329792 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/orclvol4/entDB/system01.dbf'

======================
Extract from alert log:
======================

...
ALTER DATABASE OPEN
Fri Aug 05 21:30:54 2011
Errors in file /oracle112/diag/rdbms/entdb/entDB/trace/entDB_dbw0_7235.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/orclvol4/entDB/system01.dbf'
ORA-27086: unable to lock file - already in use
SVR4 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 21364
Errors in file /oracle112/diag/rdbms/entdb/entDB/trace/entDB_dbw0_7235.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/orclvol4/entDB/sysaux01.dbf'
ORA-27086: unable to lock file - already in use
SVR4 Error: 11: Resource temporarily unavailable
Additional information: 8
Additional information: 21364
...

Reason for the lock failure:

Because of the sudden ungraceful shutdown of the database, file locks on data files were not released by the NFS server (ZFS SA in this case). NFS server held on to the file locks even after the NFS client (DB server node in this example) was restarted. Due to this, Oracle RDBMS is not able to lock those data files residing on NFS server (ZFS SA). As a result, database instance was failed to start up in exclusive mode.

Workaround

Manually clear the NFS locks as outlined below.

On NFS Client (database server node):

1. Shutdown the mounted database
2. Unmount remote (NFS) filesystems

3. Execute: clear_locks -s <nfs_server_host>

   eg.,

   # clear_locks -s sup16
   Clearing locks held for NFS client ipsedb1 on server sup16
   clear of locks held for ipsedb1 on sup16 returned success
   

On NFS Server (ZFS SA):
    (this step may not be necessary but wouldn't hurt to perform)

4. Execute: clear_locks <nfs_client_host>

   eg.,

   sup16# clear_locks 10.129.207.93
   Clearing locks held for NFS client 10.129.207.93 on server sup16
   clear of locks held for 10.129.207.93 on sup16 returned success
   

Again back on NFS Client (database server node):

5. Restart NFS client
       (this step may not be necessary but wouldn't hurt to perform)

   # svcadm -v disable nfs/client
   # svcadm -v enable nfs/client
   

6. Mount remote/NFS filesystems
7. Finally start the database

Also see:
Listing file locks on Solaris 10