After I cycled the VM, I could not start Oracle database because control files and data files (on NFS) cannot be identified.
This morning, one of our database servers had extremely high load (>252 compared to normal <4). I could neither log in to the server nor connect to the database. After power cycling the VM, I couldn’t bring ODSI up.
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 22 09:31:00 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 3.4360E+10 bytes Fixed Size 4517816 bytes Variable Size 7113539656 bytes Database Buffers 2.7179E+10 bytes Redo Buffers 62590976 bytes ORA-00205: error in identifying control file, check alert log for more info
Database alert log showed the following error:
Mon Oct 22 09:34:26 2018 ORA-00210: cannot open the specified control file ORA-00202: control file: '/nfs/odsi/redoA/control02.ctl' ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8 ORA-00210: cannot open the specified control file ORA-00202: control file: '/nfs/odsi/oradata/control01.ctl' ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8
A quick search pointed me to a post with same issue. So I tried the trick, which bypassed the control file error. However, I got “file already in use” error on the data files now.
$ cd /nfs/odsi/oradata $ mv control01.ctl control01.SAV $ cp -p control01.SAV control01.ctl $ cd ../redoA $ mv control02.ctl control02.SAV $ cp -p control02.SAV control02.ctl $ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 22 09:52:38 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 3.4360E+10 bytes Fixed Size 4517816 bytes Variable Size 7113539656 bytes Database Buffers 2.7179E+10 bytes Redo Buffers 62590976 bytes Database mounted. ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/nfs/odsi/oradata/system01.dbf'
Database alert log now showed all 225 database files were already in use. I did not have the space or time to use the previous trick. My memory served me well. I remembered we had similar issue more than five years ago when we had the database on physical AIX server and had to clear the locks on our NetApp storage appliance.
Mon Oct 22 09:53:10 2018 Errors in file /oradba/diag/rdbms/odsi/ODSI/trace/ODSI_dbw0_6679.trc: ORA-01157: cannot identify/lock data file 1 - see DBWR trace file ORA-01110: data file 1: '/nfs/odsi/oradata/system01.dbf' ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8 Mon Oct 22 09:53:10 2018 Errors in file /oradba/diag/rdbms/odsi/ODSI/trace/ODSI_dbw0_6679.trc: ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/nfs/odsi/oradata/sysaux01.dbf' ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8 ... Mon Oct 22 09:53:13 2018 Errors in file /oradba/diag/rdbms/odsi/ODSI/trace/ODSI_dbw0_6679.trc: ORA-01157: cannot identify/lock data file 225 - see DBWR trace file ORA-01110: data file 225: '/nfs/odsi/oradata/odsmgr_hr_data_03.dbf' ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8
Please read these two documents: “ORA-01157 ORA-01110 ORA-27086 after crash prevents database from opening (Doc ID 429912.1)” and “How to clear NFS locks during network crash or outage for Oracle datafiles”.
This is how we broke the locks in our environment (cluster ONTAP 9):
Ccfs::> set advanced
Warning: These advanced commands are potentially dangerous; use them only when directed to do so by NetApp personnel.
Do you want to continue? {y|n}: y
Ccfs::*> vserver locks show -vserver snap5 -volume VOdsi*
Notice: Using this command can impact system performance. It is recommended
that you specify both the vserver and the volume when issuing this command to ...
Vserver: snap5
Volume Object Path LIF Protocol Lock Type Client
-------- ------------------------- ----------- --------- ----------- ----------
VOdsi_Data
/Odsi_Data/control01.SAV ttdfs5 nlm byte-range 10.53.42.21
Bytelock Offset(Length): 0 (18446744073709551615)
/Odsi_Data/system01.dbf ttdfs5 nlm byte-range 10.53.42.21
Bytelock Offset(Length): 0 (18446744073709551615)
/Odsi_Data/sysaux01.dbf ttdfs5 nlm byte-range 10.53.42.21
Bytelock Offset(Length): 0 (18446744073709551615)
...
Ccfs::*> vserver locks break -vserver snap5 -volume VOdsi_Data -lif ttdfs5 -path /Odsi_Data/*
Warning: Breaking file locks can cause applications to become unsynchronized and may lead to data corruption.
Do you want to continue? {y|n}: y
226 entries were acted on.
Ccfs::*> vserver locks show -vserver snap5 -volume VOdsi*
Notice: Using this command can impact system performance. It is recommended ...
Vserver: snap5
Volume Object Path LIF Protocol Lock Type Client
-------- ------------------------- ----------- --------- ----------- ----------
VOdsi_Dtmp
/Odsi_Dtmp/temp_01.dbf ttdfs5 nlm byte-range 10.53.42.21
Bytelock Offset(Length): 0 (18446744073709551615)
/Odsi_Dtmp/temp_02.dbf ttdfs5 nlm byte-range 10.53.42.21
Bytelock Offset(Length): 0 (18446744073709551615)
/Odsi_Dtmp/temp_03.dbf ttdfs5 nlm byte-range 10.53.42.21
Bytelock Offset(Length): 0 (18446744073709551615)
VOdsi_RedoA
/Odsi_RedoA/control02.SAV ttdfs5 nlm byte-range 10.53.42.21
Bytelock Offset(Length): 0 (18446744073709551615)
4 entries were displayed.
Ccfs::*> vserver locks break -vserver snap5 -volume VOdsi_Dtmp -path /Odsi_Dtmp/* -lif ttdfs5
Warning: Breaking file locks can cause applications to become unsynchronized and may lead to data corruption.
Do you want to continue? {y|n}: y
3 entries were acted on.
Ccfs::*> vserver locks break -vserver snap5 -volume VOdsi_RedoA -path /Odsi_RedoA/* -lif ttdfs5
Warning: Breaking file locks can cause applications to become unsynchronized and may lead to data corruption.
Do you want to continue? {y|n}: y
1 entry was acted on.
Ccfs::*> vserver locks show -vserver snap5 -volume VOdsi*
Notice: Using this command can impact system performance. It is recommended
that you specify both the vserver and the volume when issuing this command to
minimize the scope of the command's operation. To abort the command, press Ctrl-C.
There are no entries matching your query.
Ccfs::*> set -privilege admin
Ccfs::> exit
Once the locks were cleared, I was able to start this Oracle database.