ORA-27086: skgfglk: unable to lock file - already in use -> Instance "BOCHUM", status BLOCKED

From: A. Farber <Alexander.Farber_at_gmail.com>
Date: Fri, 27 Jun 2008 02:36:36 -0700 (PDT)
Message-ID: <eab82d4d-8b58-48dd-8481-9820a12be619@a70g2000hsh.googlegroups.com>


Hello DBAs,

I'm running Oracle 9.2.0.8 on CentOS Linux 4.6 (which is same as RHEL 4.6).

Since yesterday I'm unable to startup a "BOCHUM" database:

oracle_at_ablora01:~> export ORACLE_SID=BOCHUM

oracle_at_ablora01:~> lsnrctl status

LSNRCTL for Linux: Version 9.2.0.8.0 - Production on 27-JUN-2008 10:46:14

Copyright (c) 1991, 2006, Oracle Corporation. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(PORT=1521)) STATUS of the LISTENER


Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.8.0 -
Production
Start Date                26-JUN-2008 18:24:18
Uptime                    0 days 16 hr. 21 min. 56 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Log File         /abl/apps/oracle/9.2/network/log/
listener.log
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ablora01.XXX.XXX.com) (PORT=1521)))
Services Summary...
Service "BOCHUM" has 1 instance(s).
 Instance "BOCHUM", status BLOCKED, has 1 handler(s) for this service...
Service "test1" has 1 instance(s).
 Instance "test1", status READY, has 1 handler(s) for this service... Service "test1XDB" has 1 instance(s).
 Instance "test1", status READY, has 1 handler(s) for this service... The command completed successfully

oracle_at_ablora01:~> sqlplus /nolog

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Jun 27 10:49:11 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect / as sysdba;
Connected.
SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 135336712 bytes

Fixed Size                   451336 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

SQL> exit
Disconnected from Oracle9i Release 9.2.0.8.0 - Production JServer Release 9.2.0.8.0 - Production

So I'm checking the alert log - the
/abl/apps/oracle/admin/BOCHUM/bdump/alert_BOCHUM.log and there I see:

Restarting dead background process QMN0
QMN0 started with pid=9, OS id=27924
Fri Jun 27 10:40:29 2008
Restarting dead background process QMN0
QMN0 started with pid=9, OS id=28049
Fri Jun 27 10:46:32 2008
Restarting dead background process QMN0
QMN0 started with pid=9, OS id=28179
Fri Jun 27 10:49:23 2008
Starting ORACLE instance (normal)
Shutting down instance: further logons disabled Shutting down instance (immediate)
License high water mark = 3
Fri Jun 27 10:49:30 2008
ALTER DATABASE CLOSE NORMAL
ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL... ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active Fri Jun 27 10:49:37 2008
Starting ORACLE instance (normal)
Fri Jun 27 10:49:37 2008
Running with 1 strand for Non-Enterprise Edition LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value Running with 1 strand for Non-Enterprise Edition LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.8.0. System parameters with non-default values:

  processes                = 150
  timed_statistics         = TRUE
  shared_pool_size         = 83886080
  large_pool_size          = 8388608
  java_pool_size           = 0
  control_files            = /abl/apps/oracle/oradata/BOCHUM/
control01.ctl, /abl/apps/oracle/oradata/BOCHUM/control02.ctl, /abl/ apps/oracle/oradata/BOCHUM/control03.ctl
  db_block_size            = 8192
  db_cache_size            = 25165824
  compatible               = 9.2.0.0.0
  db_file_multiblock_read_count= 16
  cluster_database         = FALSE
  fast_start_mttr_target   = 0
  undo_management          = AUTO
  undo_tablespace          = UNDOTBS1
  undo_retention           = 10800
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  instance_name            = BOCHUM
  job_queue_processes      = 10
  hash_join_enabled        = TRUE
  background_dump_dest     = /abl/apps/oracle/admin/BOCHUM/bdump
  user_dump_dest           = /abl/apps/oracle/admin/BOCHUM/udump
  core_dump_dest           = /abl/apps/oracle/admin/BOCHUM/cdump
  sort_area_size           = 524288
  db_name                  = BOCHUM
  open_cursors             = 300

  star_transformation_enabled= FALSE
  query_rewrite_enabled    = FALSE
  pga_aggregate_target     = 25165824
  aq_tm_processes          = 1
PMON started with pid=2, OS id=28247
DBW0 started with pid=3, OS id=28249
LGWR started with pid=4, OS id=28251
CKPT started with pid=5, OS id=28253
SMON started with pid=6, OS id=28255
RECO started with pid=7, OS id=28257

CJQ0 started with pid=8, OS id=28259
QMN0 started with pid=9, OS id=28261
Fri Jun 27 10:49:37 2008
Oracle Data Guard is not available in this edition of Oracle. Fri Jun 27 10:49:37 2008
ALTER DATABASE MOUNT
Fri Jun 27 10:49:38 2008
ORA-00202: controlfile: '/abl/apps/oracle/oradata/BOCHUM/ control01.ctl'
ORA-27086: skgfglk: unable to lock file - already in use Linux Error: 11: Resource temporarily unavailable Additional information: 8
Fri Jun 27 10:49:38 2008
ORA-205 signalled during: ALTER DATABASE MOUNT... Fri Jun 27 10:55:40 2008
Restarting dead background process QMN0
QMN0 started with pid=9, OS id=28403

I look at the controlfile referenced above, but don't see anything special there:

oracle_at_ablora01:rdbms> ll /abl/apps/oracle/oradata/BOCHUM/

        total 17055228
drwxr-xr-x  2 oracle oinstall       4096 Jun 26 17:13 .
drwxr-xr-x  4 oracle oinstall       4096 May 20 15:55 ..

-rw-r----- 1 oracle oinstall 1613824 Jun 26 17:18 control01.ctl
-rw-r----- 1 oracle oinstall 1613824 Jun 26 17:18 control02.ctl
-rw-r----- 1 oracle oinstall 1613824 Jun 26 17:18 control03.ctl
-rw-r----- 1 oracle oinstall 20979712 Jun 26 16:51 drsys01.dbf
-rw-r----- 1 oracle oinstall 26222592 Jun 24 07:03 indx01.dbf
-rw-r----- 1 oracle oinstall 104858112 Jun 24 06:34 redo01.log
-rw-r----- 1 oracle oinstall 104858112 Jun 26 17:18 redo02.log
-rw-r----- 1 oracle oinstall 104858112 Jun 26 17:18 redo03.log
-rw-r----- 1 oracle oinstall 262152192 Jun 26 17:16 system01.dbf
-rw-r----- 1 oracle oinstall 5242888192 Jun 26 17:18
td_db_storage_01.dbf
-rw-r----- 1 oracle oinstall 5242888192 Jun 26 17:18
td_db_storage_02.dbf
-rw-r----- 1 oracle oinstall 5242888192 Jun 26 17:14
td_db_storage_03.dbf
-rw-r----- 1 oracle oinstall 4194312192 Jun 26 17:18 temp01.dbf
-rw-r----- 1 oracle oinstall 10493952 Jun 24 07:03 tools01.dbf
-rw-r----- 1 oracle oinstall 1011884032 Jun 26 17:17 undotbs01.dbf
-rw-r----- 1 oracle oinstall 26222592 Jun 24 07:03 users01.dbf

oracle_at_ablora01:rdbms> /usr/sbin/lsof | fgrep control01.ctl

        oracle 4831 oracle 13u REG 0,25 2023424  5013540 /abl/apps/oracle/oradata/test1/control01.ctl (ablnas02:/vol/ablapp01/oracle)
oracle 4833 oracle 13u REG 0,25 2023424 5013540 /abl/apps/oracle/oradata/test1/control01.ctl (ablnas02:/vol/ablapp01/oracle)
oracle 4835 oracle 13uW REG 0,25 2023424 5013540 /abl/apps/oracle/oradata/test1/control01.ctl (ablnas02:/vol/ablapp01/oracle)

Does anybody please have a hint for me?

Also, I had to change the hostname few days ago, it had been incorrect before. I have rebooted after that, the Oracle (and HP Quality Center using it) worked ok.

Thank you
Alex

PS: Few more infos here (do you need anything else?):

oracle_at_ablora01:rdbms> ipcs

  • Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x1aaa70c8 196608 oracle 640 140509184 8 0xdff8f304 65537 oracle 640 253755392 10
  • Semaphore Arrays -------- key semid owner perms nsems 0xe408a284 884736 oracle 640 154 0x4621179c 360449 oracle 640 154
  • Message Queues -------- key msqid owner perms used-bytes messages

oracle_at_ablora01:dbs> ll /abl/apps/oracle/9.2/dbs/ total 2082200

drwxr-xr-x   2 oracle oinstall       4096 Jun 27  2008 .
drwxr-xr-x  52 oracle dba            4096 Jun 25 11:45 ..

-rw-r----- 1 oracle oinstall 2126323712 Jun 5 19:16 04ji6oiv_1_1
-rw-r--r-- 1 oracle oinstall 12920 Mar 8 2002 initdw.ora
-rw-r--r-- 1 oracle oinstall 8385 Mar 9 2002 init.ora
-rw-rw---- 1 oracle oinstall 24 Jun 27 2008 lkBOCHUM
-rw-rw---- 1 oracle oinstall 24 Jun 26 18:37 lkTEST1
-rwSr----- 1 oracle oinstall 1536 Jun 26 17:18 orapwBOCHUM
-rwSr----- 1 oracle oinstall 1536 May 17 15:05 orapwtest1
-rw-r----- 1 oracle oinstall 1613824 Jun 5 19:13 snapcf_BOCHUM.f
-rw-r----- 1 oracle oinstall 2560 Jun 5 09:57
spfileBOCHUM.ora
-rw-r----- 1 oracle oinstall 3584 Jun 5 10:11 spfiletest1.ora
Received on Fri Jun 27 2008 - 04:36:36 CDT

Original text of this message