81 积分	0 好友	4 主题

发消息

oracle 启动失败ORA-27300

1^#

发表于 2012-4-17 15:18:46 | 查看: 7046| 回复: 5

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 编辑 ]

分享0

收藏0 回复只看该作者道具举报

myownstars

2^#

发表于 2012-4-17 15:45:24

使用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无法分配信号量进而启动失败

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2012-4-17 19:32:19

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

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-4-17 19:39:53

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

回复只看该作者道具举报

myownstars

5^#

发表于 2012-4-18 09:34:20

$ /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(刘相兵

6^#

发表于 2012-4-18 10:05:08

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

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册