oracle 启动失败ORA-27300

myownstars

Server:  sun OS
Db: 9.2.0.8
$ uname -a
SunOS hsun90 5.8 Generic_117350-62 sun4u sparc SUNW,Sun-Fire-V240
$ more /etc/system
……………..
set shmsys:shminfo_shmmax=8589934590
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmsl=1500
set semsys:seminfo_semmns=10000
set semsys:seminfo_semopm=100
set semsys:seminfo_semvmx=32767
……….
$ ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         unlimited
stack(kbytes)        8192
coredump(blocks)     unlimited
nofiles(descriptors) 256
vmemory(kbytes)      unlimited
该服务器上有两个db，但是只能启动其中一个，尝试启动第2个的时候会报告如下错误
Connected to an idle instance.
SQL> startup nomount;
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpbitsper
$  ipcs -a
IPC status from <running system> as of Tue Apr 17 09:12:34 MEST 2012
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP CBYTES  QNUM QBYTES LSPID LRPID   STIME    RTIME    CTIME
Message Queues:
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NATTCH      SEGSZ  CPID  LPID   ATIME    DTIME    CTIME
Shared Memory:
m       3100   0xb911e7e8 --rw-r-----   oracle      dba   oracle      dba      8  222298112 14823 17807  9:07:51  9:08:00  8:28:19
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NSEMS   OTIME    CTIME
Semaphores:
s    4128768   0x607f4428 --ra-r-----   oracle      dba   oracle      dba    24  9:08:29  8:28:20
s      65539   0x7c4d     --ra-ra-ra-     root    other     root    other     1  6:43:45  6:23:30

如果没有db运行，随便启动任意一个db都可以成功，但就是不能两个都启动
使用truss跟踪sqlplus
$ truss -aefo sqlplus.log /opt/oracle/u01/app/oracle/product/9.2.0/bin/sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Tue Apr 17 04:53:32 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpbitsper
SQL> exit
日志摘录如下
28814:  stat("/opt/oracle/u01/app/oracle/product/9.2.0/dbs/spfilePROD025.ora", 0xFFFFFFFF7FFFB368) Err#2 ENOENT
28814:  stat("/opt/oracle/u01/app/oracle/product/9.2.0/dbs/spfile.ora", 0xFFFFFFFF7FFFB368) Err#2 ENOENT
28814:  brk(0x103511020)                                = 0
28814:  brk(0x103531020)                                = 0
28814:  open("/opt/oracle/u01/app/oracle/product/9.2.0/oracore/mesg/lrmus.msb", O_RDONLY) = 13
28814:  fcntl(13, F_SETFD, 0x00000001)                  = 0
28814:  lseek(13, 0, SEEK_SET)                          = 0
28814:  read(13, "1513 "011303\t\t\0\0\0\0".., 256)     = 256
28814:  open("/opt/oracle/u01/app/oracle/product/9.2.0/dbs/initPROD025.ora", O_RDONLY) = 14
28814:  stat("/opt/oracle/u01/app/oracle/product/9.2.0/dbs/initPROD025.ora", 0xFFFFFFFF7FFFAAF8) = 0—挨个搜查pfile，然后lseek读取每个参数
28814:  fstat(14, 0xFFFFFFFF7FFFAAB0)                   = 0
28814:  brk(0x103531020)                                = 0
28814:  brk(0x103535020)                                = 0
28814:  ioctl(14, TCGETA, 0xFFFFFFFF7FFFA9EC)           Err#25 ENOTTY
28814:  read(14, " # # # # # # # # # # # #".., 8192)    = 2830
28814:  lseek(14, 0, SEEK_CUR)                          = 2830
28814:  close(14)                                       = 0
28814:  sysconfig(_CONFIG_NPROC_CONF)                   = 2
28814:  pset_bind(-2, P_PID, -1, 0xFFFFFFFF7FFFB0A4)    = 0
………………………….
28814:  write(7, " [ 1 7 - A P R - 2 0 1 2".., 45)      = 45
28814:  lseek(9, 92160, SEEK_SET)                       = 92160
28814:  read(9, "\0\r13A0\0\0\0 V13A6\0\0".., 512)      = 512
28814:  lseek(9, 13312, SEEK_SET)                       = 13312
28814:  read(9, "\01903A2\0\0\09E03A3\0\0".., 512)      = 512
28814:  lseek(7, 0, SEEK_CUR)                           = 92045
28814:  lseek(7, 0, SEEK_CUR)                           = 92045
28814:  write(7, " [ 1 7 - A P R - 2 0 1 2".., 40)      = 40
28814:  shmget(-1190008856, 0, 0)                       Err#2 ENOENT
28814:  shmget(-1190008855, 0, 0)                       Err#2 ENOENT
28814:  shmget(-1190008854, 0, 0)                       Err#2 ENOENT
28814:  shmget(-1190008853, 0, 0)                       Err#2 ENOENT
28814:  close(6)                                        = 0
28814:  open("/opt/oracle/u01/app/oracle/admin/PROD025/bdump/alert_PROD025.log", O_WRONLY|O_APPEND|O_CREAT|O_LARGEFILE, 0664) = 6
………………………….
28814:  getuid()                                        = 1025 [1025]
28814:  getgid()                                        = 101 [101]
28814:  semget(0, 1, 0600|IPC_CREAT|IPC_EXCL)           Err#28 ENOSPC
28814:  stat("/opt/oracle/u01/app/oracle/admin/PROD025/udump", 0xFFFFFFFF7FFFA040) = 0
28814:  getpid()                                        = 28814 [28813]
28814:  getpid()                                        = 28814 [28813]
28814:  close(5)                                        = 0
28814:  lstat("/opt/oracle/u01/app/oracle/admin/PROD025/udump/prod025_ora_28814.trc", 0xFFFFFFFF7FFFA260) Err#2 ENOENT

[ 本帖最后由 myownstars 于 2012-4-17 15:27 编辑 ]

Maclean Liu(刘相兵

28814:  semget(0, 1, 0600|IPC_CREAT|IPC_EXCL)           Err#28 ENOSPC








ORA-7306, ORA-7336, ORA-7329
  Oracle received a system error on a shmget() call.  The system error
  should be reported.  There are a few possibilities:

  1) There is insufficient shared memory available.  This is
     indicated by the operating system error ENOSPC.  Most likely, SHMMNI
     is too small. Alternatively, there may shared memory already
     allocated; if it is not attached, perhaps it can be freed.  Maybe
     shared memory isn't configured in the kernel.

     
ORA-7250, ORA-7279, ORA-7252, ORA-27146
  Oracle received a system error on a semget() call.  The system error should be
  reported.  There should be only one system error ever returned with
  this, ENOSPC.  This can mean one of two things.  Either the system
  limit on sempahore sets has been reached or the system limit on the
  total number of semaphores has been reached.  Raise SEMMNI or SEMMNS,
  as is appropriate, or perhaps there are some semaphore sets which can
  be released.  In the case of ORA-7250, ORANSEMS may be set too high
  (>SEMMSL).  If it is, raise SEMMSL or decrease ORANSEMS.
  
  

System errors
=============

ENOENT - No such file or directory, system error 2
ENOMEM - Not enough core, system error 12
EACCES - Permission denied, system error number 13
EINVAL - Invalid argument, system error number 22
EMFILE - Too many open files, system error number 24
ENOSPC - No space left on device, system error number 28

myownstars

$ /usr/sbin/sysdef -i | grep -i sem
sys/semsys
sys/sparcv9/semsys
* IPC Semaphores
     10  semaphore identifiers (SEMMNI)
10000  semaphores in system (SEMMNS)--系统可以使用10000个信号量
     30  undo structures in system (SEMMNU)
   1500  max semaphores per id (SEMMSL)
    100  max operations per semop call (SEMOPM)
     10  max undo entries per process (SEMUME)
32767  semaphore maximum value (SEMVMX)
16384  adjust on exit max value (SEMAEM)

两个db的processes参数起初都设置为500，后来被我降低为50，通过ipcs可以看到oracle启动后只用了24个信号量，按说还有足够的剩余才对，可怎么还会在startup时候报告错误
Semaphores:
s    4128768   0x607f4428 --ra-r-----   oracle      dba   oracle      dba    24  9:08:29  8:28:20
s      65539   0x7c4d     --ra-ra-ra-     root    other     root    other     1  6:43:45  6:23:30

再来看一下另外一个server，同样的sun os
* IPC Semaphores
*
   100  semaphore identifiers (SEMMNI) --100，出错的server设置为100
12800  semaphores in system (SEMMNS) --出错的server设置为10000
    30  undo structures in system (SEMMNU)
  1500  max semaphores per id (SEMMSL)
   100  max operations per semop call (SEMOPM)
    10  max undo entries per process (SEMUME)
32767  semaphore maximum value (SEMVMX)
16384  adjust on exit max value (SEMAEM)
*
* IPC Shared Memory
*
4294967295      max shared memory segment size (SHMMAX)
   100  shared memory identifiers (SHMMNI)
*
* Time Sharing Scheduler Tunables
*
60      maximum time sharing user priority (TSMAXUPRI)
SYS     system class name (SYS_NAME)
$ ipcs -a
IPC status from <running system> as of Wed Apr 18 03:16:41 MEST 2012
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP CBYTES  QNUM QBYTES LSPID LRPID   STIME    RTIME    CTIME
Message Queues:
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NATTCH      SEGSZ  CPID  LPID   ATIME    DTIME    CTIME
Shared Memory:
m        100   0x921ec7ec --rw-r-----   oracle      dba   oracle      dba     19 2634031104   976  9932  3:00:22  3:00:22  1:03:33
m       2801   0xcef74ef4 --rw-r-----   oracle      dba   oracle      dba     20  629153792 19688 14875  3:12:57  3:13:37 11:58:50
m        102   0xd68aa7d4 --rw-r-----   oracle      dba   oracle      dba     18  536879104  1578  9734  3:00:05  3:01:00  1:04:24
m        103   0x51ab3140 --rw-r-----   oracle      dba   oracle      dba     69  536879104  1752 15683  3:15:05  3:15:05  1:04:40
m        204   0x18a7ce70 --rw-r-----   oracle      dba   oracle      dba     13  557842432 17633 15668  3:15:04  3:15:07  2:10:11
m        105   0x8ce282ec --rw-r-----   oracle      dba   oracle      dba      9  557842432 22008 14895  3:13:02  3:13:02  2:12:16
m          6   0xb5ed94bc --rw-r-----   oracle      dba   oracle      dba      8  132415488 23667  4581 23:10:30 23:10:30  2:13:02
m        107   0x73fbe6bc --rw-r-----   oracle      dba   oracle      dba      9  490733568 26051 24589  0:05:02  1:21:12  2:13:50
m        108   0xa474dc94 --rw-r-----   oracle      dba   oracle      dba     10  507510784 28026 27427  0:05:02  0:05:03  2:14:25
T         ID      KEY        MODE        OWNER    GROUP  CREATOR   CGROUP NSEMS   OTIME    CTIME
Semaphores:
s          0   0x187cf    --ra-ra-ra-     root     root     root     root     1  1:02:11  1:02:10
s     196609   0x62b295d8 --ra-r-----   oracle      dba   oracle      dba   504  3:16:14  1:03:36
s    3604482   0xe5f330c8 --ra-r-----   oracle      dba   oracle      dba   504  3:15:23 11:58:51
s     196611   0x7e3a6954 --ra-r-----   oracle      dba   oracle      dba   504  3:16:19  1:04:24
s     196612   0x25fe120  --ra-r-----   oracle      dba   oracle      dba   504  3:16:38  1:04:41
s     196613   0x44f770ac --ra-r-----   oracle      dba   oracle      dba   504  3:16:14  2:10:11
s     196614   0x9159e94c --ra-r-----   oracle      dba   oracle      dba   504  3:13:02  2:12:16
s     196615   0x4bad890  --ra-r-----   oracle      dba   oracle      dba    54  2:36:38  2:13:03
s     196616   0xe3f163c0 --ra-r-----   oracle      dba   oracle      dba   504  3:15:13  2:13:51
s     196617   0xf9443568 --ra-r-----   oracle      dba   oracle      dba   204  3:16:15  2:14:26
--同时成功启动了多个db，使用的信号量总数504 * n + 204 + 54

问题：第2个server只有两个参数设置的同第一个不一样，但是可以启动多个db，是否因SEMMNI设置过小导致？

Maclean Liu(刘相兵

SEMMNI   1024 Maximum number of semaphore sets in the entire system.
SEMMSL  100   Minimum recommended value. SEMMSL should be 10 plus the largest PROCESSES  parameter of any Oracle database on the system.

SEMMNS  Maximum semaphores on the system. This setting is a minimum recommended value. SEMMNS should be set to the sum of the PROCESSES parameter for each Oracle database, add the largest one twice, plus add an
additional 10 for each database.


SEMOPM 100 Maximum number of operations per semop call.
SEMVMX 32767 Maximum value of a semaphore.
(swap space) 750 MB Two to four times your system's physical
memory size.


请贴出 spfile的内容


strings spfile$SID.ora

Maclean Liu(刘相兵

ODM DATA:

The Database Can Not Be Started. Getting ORA-27154 and sskgpbitsper

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.
***Checked for relevance on 02-Nov-2011***
Symptoms
After creating a new database, startup fails with the following errors :

SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpbitsper

Changes
Created a new database.

Cause
A few bugs were logged for this issue and the reason behind these errors was identified to be the semaphores, they were not enough to process the startup.

Solution

Please ask the system admin to bump up the semaphores on this server.

If the above doesn't fix your problem or if further clarification is needed, please log a ticket with your OS Vendor.
Reference:
Note 153961.1 Semaphores and Shared Memory - An Overview
Note 15566.1   TECH: Unix Semaphores and Shared Memory Explained

myownstars

使用sysdef -i查看更详细的信号量设置，
$ /usr/sbin/sysdef -i | grep -i sem
sys/semsys
sys/sparcv9/semsys
* IPC Semaphores
    10  semaphore identifiers (SEMMNI)
10000  semaphores in system (SEMMNS)
    30  undo structures in system (SEMMNU)
  1500  max semaphores per id (SEMMSL)
   100  max operations per semop call (SEMOPM)
    10  max undo entries per process (SEMUME)
32767  semaphore maximum value (SEMVMX)
16384  adjust on exit max value (SEMAEM)

其中SEMMNI仅设置为10，是不是该参数过小导致第2个启动的oracle无法分配信号量进而启动失败