From ORA-27300, ORA-27301 to ORA-00064, ora-27300ora-00064

From ORA-27300, ORA-27301 to ORA-00064, ora-27300ora-00064
Recently, due to the increase in the number of sessions, you need to adjust the session, that is, to adjust the process parameter. Look at is a relatively simple problem, but encountered a ORA-27300, ORA-27301. This involves modifications to kernel Parameter kernel. sem. The following is a detailed description.

1. Fault
OS Version: SUSE Linux Enterprise Server 10 SP3 (x86_64)-Kernel \ r (\ l)
DB version: SQL * Plus: Release 10.2.0.3.0-Production
Requirement: The Processes parameter is modified from 1000 to 2000.
SQL> startup pfile =/u02/database/MRDB/initMRDB. ora;
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: sskgpcreates

2. Fault Analysis and Solution
# At first, I thought there was not enough space. The following is clearly not a space issue. Second, it is very important to say "semget"
SQL> ho df-h
Filesystem Size Used Avail Use % Mounted on
/Dev/sda3 1.8 T 826G 885G 49%/
Udev 32G 116 K 32G 1%/dev
/Dev/sda1 99 M 9.5 M 85 M 11%/boot

# Background logs are as follows:
Tue Aug 5 18:07:22 2014
Starting ORACLE instance (normal)
Tue Aug 5 18:07:22 2014
Errors in file/u02/database/MRDB/udump/mrdb_ora_30366.trc:
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: sskgpcreates

# Google wrote some articles describing the need to adjust the kernel Parameter kernel. sem, that is, the semaphores.

V2012db02u :~ # Grep kernel. sem/etc/sysctl. conf
Kernel. sem = 1250 32000 100 256 # This is the current value

Semaphore setting example
SEMMSL should be set to the maximum PROCESSES parameter + 10 in the server instance. For example, when the maximum PROCESSES parameter is 5000, SEMMSL should be set to 5010.
SEMMNS parameter should be set to SEMMSL * SEMMNI, SEMMSL is 5010, SEMMNI is generally 128, then SEMMNS parameter should be (5010*128) = 641280.
The SEMOPM parameter should be set to the same as the SEMMSL parameter. In this example, the SEMOPM parameter should be set to 5010.
Therefore, we recommend that you set the semaphore as follows:
Sysctl-w kernel. sem = "5010 641280 5010 128"

# For details about the parameter description and settings, refer to: Linux Kernel Parameter Optimization (for oracle)

# Because the current server has N instances, a relatively large value is given, and the production environment should be modified with caution, as shown below:
V2012db02u :~ # Vi/etc/sysctl. conf
V2012db02u :~ # Sysctl-p
...........
Kernel. sem = 7000 1792000 7000 256
...........

# Start again, sweating ....
SQL> startup pfile =/u02/database/MR/initMR. ora;
ORA-00064: object is too large to allocate on this O/S)

V2012db02u :~ > Oerr ora 00064.
00064,000 00, "object is too large to allocate on this O/S (% s, % s )"
// * Cause: An initialization parameter was set to a value that required
// Allocating more contiguous space than can be allocated on this
// Operating system.
// * Action: Reduce the value of the initialization parameter.

# From the above description, you cannot allocate consecutive space from the OS.
# Description information is provided on Metalink (Doc ID 283980.1). You need to increase the memory allocation granularity to 16 Mb.
# The current memory granularity is 4 MB.
SQL> SELECT x. ksppinm name, y. ksppstvl VALUE, x. ksppdesc descbtion
2 FROM x $ ksppi x, x $ ksppcv y
WHERE x. inst_id = USERENV ('instance ')
3 4 AND y. inst_id = USERENV ('instance ')
5 AND x. indx = y. indx
6 AND x. ksppinm LIKE '% _ ksmg_granule % ';

NAME VALUE DESCBTION
-------------------------------------------------------------------------------------------------------------------
_ Ksmg_granule_size 4194304 granule size in bytes
_ Ksmg_granule_locking_status 1 granule locking status

SQL> show parameter sga # The current sga size is 1 GB
NAME TYPE VALUE
-----------------------------------------------------------------------------
Sga_max_size big integer 1G
Sga_target big integer 1G

# Author: Leshami
# Blog: http://blog.csdn.net/leshami

# Directly adjust to 2 GB
SQL> show parameter sga
NAME TYPE VALUE
-----------------------------------------------------------------------------
Sga_max_size big integer 2G
Sga_target big integer 2G

# The following shows the memory size after modification.
SQL> set linesize 145
Col name format a35
SQL> col value format a30
Col descbtion format a50 wrap
SELECT x. ksppinm name, y. ksppstvl VALUE, x. ksppdesc descbtion
SQL> 2 FROM x $ ksppi x, x $ ksppcv y
3 WHERE x. inst_id = USERENV ('instance ')
4 AND y. inst_id = USERENV ('instance ')
5 AND x. indx = y. indx
6 AND x. ksppinm LIKE '% _ ksmg_granule % ';
NAME VALUE DESCBTION
-------------------------------------------------------------------------------------------------------------------
_ Ksmg_granule_size 16777216 granule size in bytes
_ Ksmg_granule_locking_status 1 granule locking status

# Restart the database again.


3. Doc ID 283980.1

Oracle Server-Enterprise Edition-Version: 9.0.1.0 and later [Release: 9.0.1 and later]
Information in this document applies to any platform.

SYMPTOMS

ORA-00064 error is possibly encountered on database startup when the parameter
OPEN_LINKS_PER_INSTNACE is set to over 400.

CAUSE

The contiguous area of storage to be allocated, exceeds the SGA granule
(Subarea) size. The granule size is calculated based on the SGA size.
SOLUTION

Test case to reproduce the error
======================================
A) add OPEN_LINKS_PER_INSTANCE = 1000 in the initora
B) use sqlplus to try starting up the database

This is NOT a BUG.

To fix the issue, increase the size of the SGA to over 128 Mb by increasing DB_CACHE_SIZE,
SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, etc. This will cause the granule size
Be increased to 16 Mb.


Workaround
============
Set the following hidden parameter

_ Ksmg_granule_size = 16777216

Note: Please set granule size more than or equal to size showing in the error message.

Another Bug related to this error 5959853-ORA-64 during database startup with processes> 6000. DocID: 1457812.1