Hi everyone,
Hope you’re doing good.
I’m working for a client which has Oracle DB for SAP applications.
Those DB’s are not using RAC, yes, I know 🙂
SAP does not support use RAC in its fullness, as cannot use services with load balancing. If you would like to use RAC for SAP, service must connect only in 1 node, another nodes can be used only for failover, but only one node can remain active.
OK, as I was saying, this client use Veritas Cluster as “high avaialibility” solution. They have two-node clusters where the DB is stored in dNFS (NetApp), NFS mount points are mounted in all nodes of this cluster. Veritas will move listener and DB to another node in case of issues.
Last night they had some issues in 1st node and Veritas tried to bring up the services on 2nd node, but it failed.
Let’s try then to bring up the services manually.
Then we tried to startup (first execute the mount) manually:
DBL01:db01 53> sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 17 05:12:05 2023 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to an idle instance. SQL> SQL> SQL> startup mount ORACLE instance started. Total System Global Area 1.9931E+10 bytes Fixed Size 8906744 bytes Variable Size 7381975040 bytes Database Buffers 1.2482E+10 bytes Redo Buffers 58200064 bytes ORA-00205: error in identifying control file, check alert log for more info
As you can see, it’s complaining about control file. This message can make it you worried! As it appears, control file is not acessible.
Let’s check into alert.log (output truncated to make it easier to read it):
2023-08-17T05:12:15.105485+00:00 ALTER DATABASE MOUNT 2023-08-17T05:12:15.205125+00:00 ORA-00210: cannot open the specified control file ORA-00202: control file: '/oracle/DBL01/origlogB/ctrl/control03.ctl' ORA-27086: unable to lock file - already in use Linux-x86_64 Error: 11: Resource temporarily unavailable Additional information: 8 Additional information: 4294967294
Ok, main message is:
ORA-27086: unable to lock file – already in use
Sometimes, if DB is not closed using the right way (clean shutdown), a “lock” file can be held into $ORACLE_HOME/dbs. Let’s check:
DBL01:db01 54> ls -ltr total 12168 -rw-r----- 1 db01 dba 2048 Oct 20 2021 orapwDBL01 drwx------ 3 db01 dba 4096 Oct 20 2021 wallet -rw-r----- 1 db01 dba 6656 Aug 17 00:13 spfileDBL01.ora -rw-r----- 1 db01 dba 12288 Aug 17 04:28 dr2DBL01.dat -rw-r----- 1 db01 dba 12288 Aug 17 04:29 dr1DBL01.dat -rw-rw---- 1 db01 dba 24 Aug 17 05:12 lkDBL01
As we can see, file lkDBL01 is there. Let’s remove it:
DBL01:orap01 55> rm lkDBL01
Even after remove we still continue to get the message “ORA-27086: unable to lock file – already in use”.
What else we can do?
Well, at DB side, nothing more!
So, what is the reason?
This is because as opened database files that were opened on node 1 has acquired locks in the storage layer, those locks were not released after the crash. The only solution here is asking storage team to release (break) the locks.
The team from client doesn’t know how to do it, I’m glad that I was able to help them.
First, from NetApp admin console/prompt (output is truncated to make easier to read it):
CA-NAS-CL01::*> vserver locks show -vserver PROD-SVM -volume *DBL01* 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. Vserver: PROD-SVM Volume Object Path LIF Protocol Lock Type Client -------- ------------------------- ----------- --------- ----------- ---------- DBL01_db01_origlog_t110_vol /vol/DBL01_db01_origlog_t110_vol/DBL01_db01-origlogb-qt/ctrl/control01.ctl PROD-SVM-NFS_LIF3 nlm byte-range 10.222.133.141 Bytelock Offset(Length): 0 (18446744073709551615) /vol/DBL01_db01_origlog_t110_vol/DBL01_db01-origloga-qt/ctrl/control02.ctl PROD-SVM-NFS_LIF3 nlm byte-range 10.222.133.141 DBL01_db01_sapdataa_t110_vol /vol/DBL01_db01_sapdataa_t110_vol/DBL01_db01-data1-qt/system01.dbf PROD-SVM-NFS_LIF4 nlm byte-range 10.222.133.141 Bytelock Offset(Length): 0 (18446744073709551615) /vol/DBL01_db01_sapdataa_t110_vol/DBL01_db01-data3-qt/undotbs01.dbf PROD-SVM-NFS_LIF4 nlm byte-range 10.222.133.141 . . . 19 entries were displayed.
So, the command “vserver locks show” will displays information about locks.
OK, now, we are ready to break (release) the locks from the storage layer.
Please, make sure you really don’t have the files opened in any server. Break the locks of opened files can lead to data corruption!
CA-NAS-CL01::*> vserver locks break -vserver PROD-SVM -volume *DBL01* -path *
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.
Warning: Breaking file locks can cause applications to become unsynchronized and may lead to data corruption.
Do you want to continue? {y|n}: y
19 entries were acted on.
Great, the locks were breaked.
Let’s check again if we have locks:
CA-NAS-CL01::*> vserver locks show -vserver PROD-SVM -volume *DBL01* 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. CA-NAS-CL01::*>
Perfect! We don’t have locks anymore!
Let’s try to mount (and open) the DB again:
DBL01:db01 59> sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Aug 17 05:26:13 2023 Version 19.17.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 1.9931E+10 bytes Fixed Size 8906744 bytes Variable size 7381975040 bytes Database Buffers 1.2482E+10 bytes Redo Buffers 58200064 bytes Database mounted. SQL> alter database open; Database altered.
Great! That’s it!
Hope this helps!
Peace,
Vinicius
Reference notes:
This My Oracle Support notes has the old NetApp commands which didn’t work in the most (?) recent environments (the commands has been changed a few years ago!)
