UNIX for Advanced & Expert Users

I have a problem with Oracle Database due to block size it said : "Oracle's SGA to big to fit contiguosly into one segment" Reconfigure the UNIX kernel to have bigger segments.

How can I reconfigure Kernel in order to get bigger "segments". Does it works on pure UNIX system with no Solstice install?

I'm not an Oracle expert, but I would guess that we're talking about shared memory segments. There is usually a kernel parameter called shmmax that is the max size of a shared memory segment. Oracle should have some docs about how big they want this to be.

Perderabo is correct - increase shmmax.

And you can control the size of your SGA by changing certain settings in the init<SID>.ora file. A big one is db_block_buffers. If this is set to 10000, and you have 16K block size, this will contribute about 160MB toward the total size of the SGA. Another big one (as regards SGA size) is shared_pool_size.

Oracle uses shared memory and semaphores to communicate between processes and
the SGA (System Global Area). There are certain requirements for shared
memory and the semaphores. When the Oracle instance comes up, it allocates a
certain portion of the main memory to create the SGA. If the shared memory or
the semaphores are not set properly, then it gives an error related to shared
memory or semaphores.

The following are the recommended values for shared memory and semaphores for
running a SMALL size Oracle database. These values are set at the Unix kernel
level.


SCOPE & APPLICATION
This entry lists shared memory requirements for Unix systems.


Shared Memory Requirements on Unix:
===================================

The shared memory feature of the UNIX operating system is required by Oracle.
The System Global Area (SGA) resides in shared memory; therefore, shared
memory must be available to each Oracle process to address the entire SGA.


Definitions of Shared Memory and Semaphore Parameters

SHMMAX = The maximum size(in bytes) of a single shared memory segment.
SHMMIN = The minimum size(in bytes) of a single shared memory segment.
SHMMNI = The number of shared memory identifiers.
SHMSEG = The maximum number of shared memory segments that can be attached by
a process.
SEMMNS = The number of semaphores in the system.
SEMMNI = The number of semaphore set identifiers in the system; determines the
number of semaphore sets that can be created at any one time.
SEMMSL = The maximum number of sempahores that can be in one semaphore set.
It should be same size as maximum number of Oracle processes
(The PROCESSES parameter in the init.ora file).


Recommended Semaphore and Shared Memory Parameters

Operating System Shared Memory Parameters Semaphore
---------------- ------------------------ ---------
Sun OS SHMSIZE = 32768 SEMMNS = 200
SHMMNI = 50 SEMMNI = 50


Solaris SHMMAX = 8388608(or larger than max SGA) SEMMNS = 200
SHMSEG = 20 SEMMSL = 50
SHMMNI = 100 SEMMNI = 70


HP/UX SHMMAX = 0x4000000 (64 Mb) SEMMNS = 128
SHMSEG = 12 SEMMNI = 10


Digital Unix SHMMAX = 4194304 SEMMNS = 60
(DEC Alpha OSF/1) SHMSEG = 32 SEMMSL = 25


Ultrix Use system default SEMMNS = 60
SEMMSL = 5

AT&T Unix SHMMAX = RAM-Dependant SEMMNS = 200
8 or 16 Mb RAM SHMMAX = 5 Mb for all RAM
32 Mb RAM SHMMAX = 8 Mb values
64 Mb RAM SHMMAX = 16 Mb
128 Mb RAM SHMMAX = 32 Mb
256 Mb RAM SHMMAX = 64 Mb
512 Mb RAM SHMMAX = 128 Mb
1024 Mb RAM SHMMAX = 256 Mb
2048 Mb RAM SHMMAX = 512 Mb
SHMSEG = 6 for all RAM values
SHMMIN = 1 for all RAM values


Dynix/PTX SHMMAX = 11010048 SEMMNS = 200
SHMSEG = 20 SEMMSL = 85
Other parameter: NOFILES = 128


DG/UX SHMMAX = 4194304 SEMMNS = 200
SHMSEG = 15



Setting the Shared Memory and Semaphore Parameters

The values of the shared memory and semaphore parameters must be set in the
kernel configuration file of your operating system (OS). The location of that
file is port-specific. See [NOTE:1010913.6] for its name and location on your
system.

To check your current shared memory and semaphore configuration you can use
the command:

% sysdef | more

Example on HP-UX (relevant sections only):
------------------------------------------
Semaphore Related Parameters
maximum value for semaphores(semaem)= 16384
Semaphore map(semmap)= 4098
number of semaphore identifiers(semmni) = 4096
total number of semaphores in the system(semmns) = 8192
number of semaphore undo structures(semmnu) = 1536
semaphore undo entries per process(semume) = 512
semaphore maximum value(semvmx) = 32767

Shared Memory Related Parameters
maximum shared memory segment size in bytes(shmmax) = 536870912
minimum shared memory segment size in bytes(shmmin) = 1
maximum shared memory segments in system (shmmni) = 512
maximum shared memory segments per process(shmseg) = 512

NOTE: The SHMMAX is quite large on this system as there are 8 instances
running on this system.


To make changes in shared memory or semaphore parameters:

1. Shut down any running Oracle instances
2. Locate the kernel configuration file for your OS
3. Make the necessary changes using the system utilities or your
favorite editor. System utilities include:

OS Utility
--- -------
HP-UX SAMS
SCO SYSADMSH
AIX SMIT
Solaris ADMINTOOL

4. Reconfigure the kernel.
5. Reboot your machine.
6. Restart your Oracle instances


Example, using Solaris 2.3/2.4 parameters and commands:
--------------------------------------------------------
1. Log into SQLDBA and type:
SQLDBA> shutdown
SQLDBA> exit

2. Log in as the superuser (root) and:
# cd /etc/system

3. Add the following lines to the /etc/system file:

set shmsys:shminfo_shmmax=8388608
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=20
set semsys:seminfo_semmns=200
set semsys:seminfo_semmni=70

4. Reconfigure the kernel:
# touch /reconfigure

5. Reboot the machine:
#init 6

6. Log into SQLDBA and type:
SQLDBA> startup
SQLDBA> exit



Oracle, Shared Memory, and SGA Size

There are Oracle init<SID>.ora parameters which can be modified to influence
the size of the SGA. These settings of these parameters, in conjunction with
OS shared memory and semaphore parameters, can influence both system and
Oracle performance.