ORA-27154 cannot start database due to semaphore problem, ora-27154 Database

ORA-27154 cannot start database due to semaphore problem, ora-27154 Database

Prompt (11.2.0.1) when the Test Library executes startup ):

Error querying ORA-27154:

Error: ORA-27154Text: post/wait create failed ---------------------------------------------------------------------------Cause: internal error, multiple post/wait creates attempted simultaneously Action: check errno and contact Oracle Support

The error message is an internal error. Multiple post/wait requests at the same time.

DfCheck whether the disk space is full or not..

View the errorDescription of semget:

The prompt "segmet" indicates "get a semaphore set identifier", that is, get a semaphore set identifier. This error may be related to the signal that has not been obtained. No space left on device is not a bucket, but a semaphore resource.

From MOSSee (949468.1), A series of errors appear in the range of 10.1.0.2 to 11.2.0.2. An example is provided:

$ Ipcs-ls

------ Semaphore Limits --------

Max number of arrays = 128

Max semaphores per array = 250

Max semaphores system wide = 32000

Max ops per semop call = 100

Semaphore max value = 32767

The cause is that, in principle, 32000 semaphores are available. A semaphoric identifier can contain a maximum of 250 semaphores. However, the ipcs command shows that each semaphore identifier only allows Oracle to contain a maximum of 156 semaphores.

$ Ipcs <in this example, when no additional instance is started, it contains about 100 semaphore character sets.

..

------ Semaphore Arrays --------

Key semid owner perms nsems

0x0000e15bd 0 root 666 1

0x0000cace 32769 root 666 1

0x358b172c 327683 oracle 660 104

0x9053d038 11075588 oracle 660 156

0x9053d039 11108357 oracle 660 156

0x9053d03a 11141126 oracle 660 156

0x9053d03b 11173895 oracle 660 156

..

The maximum available semaphore is 156*128 = 19968, not 32000.

To solve this problem, add the included semaphores. Here, adjust the settings according to the SEMMNI parameter.

1. query the semaphore parameter value of the current kernel.

#/Sbin/sysctl-a | grep sem

2. Modify the SEMMNI parameter of the/etc/sysctl. conf file.

Change from kernel. sem = 250 32000 100 128 to kernel. sem = 250 32000 100 200

3. Use #/sbin/sysctl-p to make the modification take effect.

Based on my situation, first view the results of ipcs:

After the database is started, the shared memory and semaphore need to be allocated from the operating system. The semaphore is equivalent to the memory lock of the OS, similar to the Oracle latch (Note the differences between Oracle locks and latch), Each process needs to obtain the operating system memory, the first need to obtain the semaphore to apply for memory.

From the above instructions, we can see that the maximum available semaphore is 100, and the maximum semaphore identifier set is 128. Er, here we made a mistake. At that time, we didn't see the actual semaphore identifier set of ipcs. The meanings of the four parameters are as follows:

SEMMSL100 Defines the minimum recommended value, for initial installation only
The maximum number of sempahores that can be in one semaphore set. It shoshould be same size as maximum number of Oracle processes. The maximum number of semaphores allowed in a semaphores set. The number of processes must be the same as that of Oracle.
SEMMNS100 Defines the maximum semaphores on the system.
This setting is a minimum recommended value, for initial installation only. the SEMMNS parameter shocould be set to the sum of the PROCESSES parameterfor each Oracle database, adding the largest one twice, and then adding an additional 10 for each database.
The maximum number of semaphores allowed by the system. The SEMMNS parameter should be set to the PROCESSES parameter of each database, plus a maximum of two times, and then an additional 10 for each database. (Note that this value is the smallest recommended value)
SEMOPM32 Defines the maximum number of operations for each semop call
The maximum operand of each semaphore call.
SEMMNI128 Defines the maximum number of semaphore sets in the entire system
The maximum value of the semaphore set in the system.
We can infer that SEMMNS = SEMMSL * SEMMNI.
However, in the preceding example: 100 <> 100*128, the maximum allowable semaphore (recommended minimum) of SEMMNS is only 100, which obviously cannot meet the number of computing results. In addition, we can see from the official Oracle documents that Recommended Value:

Using ing Kernel Parameters

Verify that the kernel parameters shown in the following table are set to values greater than or equal to the recommended value shown. The procedure following the table describes how to verify and set the values.

Parameter	Value	File
Semmsl Semmns Semopm Semmni	250 32000 100 128	/proc/sys/kernel/sem

SEMMNS is 32000, that is, the result of SEMMSI (250) * SEMMNI (128.

Then we can inferThe error message "sskgpcreates" may be related to the number of processes. The parameters related to this value in the kernel are SEMMNS, which is the same as the above speculation. That is, too many processes, but the maximum number of semaphores allowed is too small, the two do not match, resulting in No space left on device prompting insufficient semaphore Resources.

SolutionModify the semaphore parameter value as instructed by MOS:

This method is only temporary modification and becomes invalid after the machine is restarted. If it needs to take effect permanently, you can modify the parameter value corresponding to/etc/sysctl. conf.

Summary:

1. The error message "No space left on device" does not necessarily indicate insufficient storage space. In this example, it refers to the semaphore resource.

2. kernel. meanings of the four parameters in sem, and SEMMNS (maximum allowed semaphores) = SEMMSL (A semaphores allowed by a semaphores set) * SEMMNI (the maximum semaphores allowed by the System) and SEMMNS Defines the maximum semaphores on the system. this setting is a minimum recommended value, for initial installation only. that is, the maximum semaphore allowed, but this value is the minimum recommended value for initial installation.

3. you may find a solution by using baidu, google, or even MOS, but more importantly, you can know the cause, learn the scenario of the problem, and combine your own problems, after confirming that the class is the same, execute the operation again. One sentence: be cautious.