As part of normal operations, Oracle database processes constantly coordinate and communicate with system server processes. In Linux or Solaris, this type of process-to-process coordination is referred to as interprocess communication (IPC). OSs typically support three types of interprocess communication mechanisms: semaphores, shared memory, and message queues. Oracle database applications typically require significant amounts of semaphore and shared memory resources to function properly.

A semaphore is a construct used by the OS to control which processes can have access to other server resources such as shared memory. The number of OS resources that a database application can consume is governed by several kernel parameters. Maintaining these values is described in detail in this chapter.

Nowadays, almost any medium-to-large database can quickly exceed the default kernel settings for system resource limits. Therefore, most database vendors (Oracle, MySQL, DB2, PostgreSQL, and so on) have their own set of recommended values for the kernel parameters that govern the various required OS resources.

Before you can install a database on a server, it prudent to first ensure that that the kernel parameters are configured correctly. An incorrectly configured kernel usually results in either a nonfunctioning or poorly performing database. For example, if enough shared memory can’t be allocated when Oracle starts, an error message like this will be thrown:

ORA-27123:  unable  to  attach to  shared  memory  segment

To avoid these problems, a knowledgeable DBA must know how to view and set kernel parameters that affect database availability and performance. This chapter begins with a high-level overview of how to examine and modify kernel parameters, and then dives into specific details for settings most critical for database applications.

One last note before you start: many of the recipes in this section have slightly different solutions, depending on whether you’re using Linux or Solaris. The differences are noted where applicable.

9-1. Displaying Server Hardware and the Operating System

Problem

You have the task of installing the Oracle database software (binaries). Before you download and install the software, you need to verify the system architecture and OS version of the target host.

Solution

This “Solution” section contains two sections: one for Linux and the other for Solaris. First, let’s discuss viewing server information on Linux.

Linux

Use the uname (print system information) utility with the -a option to display system details. Here’s the output for a typical Linux system:

$ uname  -a
Linux dv3.rmug.org 2.6.18-308.4.1.0.1.el5xen #1 SMP
Tue Apr 17 16:41:30 EDT 2012 x86_64 x86_64 x86_64 GNU/Linux

You can also produce the previous output piecemeal via the following options: -s (kernel name), -n (nodename), -r (kernel release), -v (kernel version), -m (machine), -p (processor), -i (hardware platform), and -o (operating system). For example, to print the hardware platform, use the following:

$ uname -p
x86_64

Use uname with the --help parameter to display all choices available in your environment.

Solaris

For Solaris systems, you can use the uname command to print out hardware details:

$ uname -a
SunOS devz1 5.11 11.1 sun4v sparc sun4v

This output indicates that the OS release is 5.11, the platform is sun4v, and the hardware class is sparc. You can also print out the server details in a columnar format using the -X option:

$ uname -X
System = SunOS
Node = devz1
Release = 5.11
KernelID = 11.1
Machine = sun4v
BusType = <unknown>
Serial = <unknown>
Users = <unknown>
OEM# = 0
Origin# = 1
NumCPU = 16

Here’s how to confirm the release level:

$ cat /etc/release
Oracle Solaris 11.1 SPARC
...

You can show the architecture via the isainfo command:

$ isainfo -kv
64-bit sparcv9 kernel modules

How It Works

In today’s global environment, you’ll often connect remotely to database servers located in dispersed data centers. In these situations, you’ll frequently use the uname command with the -a option to verify which machine you’re logged on to.

In a Linux environment, you can also view server information by querying the virtual files in the /proc directory. For example, you can view the current version of the server by viewing the /proc/version file:

$ cat /proc/version
Linux version 2.6.18-308.4.1.0.1.el5xen ([email protected])
(gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)) #1 SMP Tue Apr 17 16:41:30 EDT 2012

The Linux /proc virtual filesystem acts as an interface for viewing and configuring kernel parameters. The /proc directory is a hierarchy of files and subdirectories that contain the current settings of kernel values. It is appropriately named /proc because this virtual filesystem sends information to other system processes. This filesystem is virtual because its files don’t actually reside on disk. Note that most files beneath the /proc directory have a 0-byte size. The /proc virtual files are created dynamically in memory from kernel data when you access them.

For your convenience, the /proc filesystem is subdivided into directories that contain related parameters. For example, the /proc/sys subdirectory contains many of the parameters used to configure the kernel. Use the man proc command to view documentation on the /proc virtual filesystem for your server.

Some utilities, such as top and free, extract information from the /proc virtual files and present it in a human-readable formatted fashion.

Table 9-1 describes some of the virtual files in the /proc directory. Use your favorite file-viewing utility (cat, more, less, view, grep, and so on) to inspect these virtual files.

9-2. Listing CPUs

Problem

The Oracle installation documentation recommends installing the binaries on a server with CPUs that meet certain requirements. You want to display the CPU characteristics on your server.

Solution

This “Solution” section contains two sections: one for Linux and the other for Solaris. First let’s cover viewing CPU information on Linux.

Linux

You can quickly obtain in-depth information about the physical characteristics of the CPU(s) on a Linux server by viewing the virtual /proc/cpuinfo file:

$ cat /proc/cpuinfo

For multiple processor boxes, there is a section in the output for each CPU. The first CPU on the box is identified as 0, the next one as 1, and so on. Here’s a partial listing of some typical output from /proc/cpuinfo:

processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 45
model name      : Intel(R) Xeon(R) CPU E5-2690 0 @ 2.90GHz
stepping        : 7
cpu MHz         : 2893.100
cache size      : 20480 KB
physical id     : 0
siblings        : 1
core id         : 0
cpu cores       : 1
...

Solaris

For Solaris systems, use the prtdiag utility to display CPU information:

$ prtdiag

Here’s a partial listing of the output:

================================ Virtual CPUs ==========

CPU ID Frequency Implementation         Status
------ --------- ---------------------- -------
0      2848 MHz  SPARC-T4               on-line
1      2848 MHz  SPARC-T4               on-line
2      2848 MHz  SPARC-T4               on-line
3      2848 MHz  SPARC-T4               on-line
...

How It Works

Sometimes you need to know whether the CPUs on your box are powerful enough to handle the database software being installed. If you have multiple CPUs on your server, you’ll see a listing for each CPU. On Linux systems, use the information in the /proc/cpuinfo file to determine whether your server meets Oracle’s prerequisites for CPU minimum megahertz speed, which is usually the cpu MHz line in the /proc/cpuinfo file. On Solaris systems, use the prtdiag utility to view CPU information.

9-3. Displaying Physical Memory

Problem

Oracle’s installation documentation recommends that you have a certain minimal amount of memory installed on the server. You want to verify that you have enough memory on a box before you do a database installation.

Solution

This “Solution” section contains two sections: one for Linux and the other for Solaris. First up is Linux.

Linux

With Linux, you can view the contents of the /proc/meminfo file with grep to check the total physical memory amount:

$ grep MemTotal /proc/meminfo
MemTotal:     15622144 kB

On Linux systems, issue the following grep command to view the total amount of swap memory:

$ grep SwapTotal /proc/meminfo
SwapTotal:    10288440 kB

Solaris

On Solaris systems, use the prtconf utility to display installed memory:

$ prtconf | grep "Memory size"

Here’s a snippet of the output:

Memory size: 32768 Megabytes

On Solaris systems, run the following command to view the swap size:

$ swap -l
swapfile             dev    swaplo   blocks     free
/dev/zvol/dsk/rpool/swap 196,2        16 67108848 67108848

How It Works

When dealing with database servers, you have to be aware of two types of memory: physical RAM and virtual (swap). Depending on the software you install on a box, you are sometimes required to check to see whether there is sufficient RAM and swap memory on the target server. If you don’t have enough physical or swap memory, usually the Oracle Universal Installer will alert you to any inadequacies when you attempt to perform the installation.

You can also view physical and swap memory by issuing the free command with no options. This command gives you a view of the currently free and consumed memory on the box:

$ free
         total        used        free        shared        buffers        cached
Mem:   2074904     2050512       24392             0          84704       1759792
-/+ buffers/cache:  206016     1868888
Swap:      4184924   74652     4110272

On Linux systems, if you want to view per–process memory consumption, use the following cat commands:

$ cat  /proc/<PID>/maps
$ cat  /proc/<PID>/status

On Solaris systems, use the pmap command to see process-to-memory mappings. First use the ps command to identify the process of interest:

$ ps -ef | grep smon | grep -v grep
oracle  7625     1   0   Apr 10 ?           9:29 ora_smon_DWREP

Then use the PID as input to the pmap command:

$ pmap -x 7625

Here’s a partial listing of the output:

7625:   ora_smon_DWREP
         Address     Kbytes        RSS       Anon     Locked Mode   Mapped File
0000000100000000     214912     197232          -          - r-x--  oracle
000000010D2DE000       1584       1144        320          - rwx--  oracle
000000010D46A000         24          -          -          - rwx--  oracle
...

9-4. Viewing Kernel Parameters

Problem

You’re installing database binaries on a new server and need to modify kernel parameters per the installation documentation. Before you make the change, you first want to view all kernel parameters.

Solution

The solution varies by OS. First let’s view Linux kernel parameters:

Linux

Run the following grep command as root to view the current kernel settings in the /proc/sys/kernel directory:

# grep . /proc/sys/kernel/*

The previous command instructs grep to print all strings contained in files located in the /proc/sys/kernel directory. Here is a partial listing of the output:

/proc/sys/kernel/sem:250        32000   100     128
/proc/sys/kernel/shmall:2097152
/proc/sys/kernel/shmmax:1073741824
/proc/sys/kernel/shmmni:4096

You can also use grep to filter for a particular setting. The example searches for the string sem in any files in the /proc/sys/kernel directory:

# grep . /proc/sys/kernel/* | grep  sem
/proc/sys/kernel/sem:250      32000 100    128

If you want to save all the current kernel values in a file, pipe the output of the grep command to a file:

# grep  . /proc/sys/kernel/* >jul11_kernel_parms.txt

Solaris

Solaris 10 and above no longer use the /etc/system file to manage shared memory. Solaris uses the resource control facility for memory management. The /etc/project file is where project settings are stored. To verify the project settings, first use the id command to determine the project identifier:

$ id -p
uid=2000(oracle) gid=200(dba) projid=200(group.dba)

Now use the prctl command to view the maximum shared memory allowed for a project:

$ prctl -n project.max-shm-memory -i project group.dba
project: 200: group.dba
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      24.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

And you can also view the maximum number of semaphore IDs for a project:

$ prctl -n project.max-sem-ids -i project group.dba
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-sem-ids
        privileged        128       -   deny                                 -
        system          16.8M     max   deny

If you’re logged on as the oracle user, you can also verify parameters for the current process as follows:

$ prctl -n project.max-shm-memory -i process $$
process: 20506: bash
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      24.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -

How It Works

Occasionally, you’ll need to verify kernel parameter settings when you’re troubleshooting issues. On Linux systems, you can view settings in the /proc/sys/kernel directory. On Solaris systems, use the prctl utility to view kernel parameters.

Using sysctl

On Linux systems, another way to display all kernel parameters is via the sysctl utility, which allows you to view and modify the kernel files found in the /proc/sys directory. Use the -a option of sysctl to view all kernel parameters:

# sysctl -a

Here is a small snippet of the large output of the previous command:

kernel.msgmnb = 16384
kernel.msgmni = 16
kernel.msgmax = 8192
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.shmmax = 1073741824

You can save the output of the sysctl -a command to a text file, as shown here:

# sysctl -a  > /root/kernelsysctl.txt

Inspect the output of the sysctl -a command. Note that the kernel values are either a single value or an array of values. When just a single value is involved, it is fairly easy to determine the parameter setting. For example, from the output of sysctl -a, the maximum setting for shared memory (shmmax) is 1,073,741,824 bytes:

kernel.shmmax  = 1073741824

When the kernel settings are stored as an array, it becomes a bit more difficult to determine a particular value. For example, this array of four values shows the current kernel settings for sem (semaphores):

kernel.sem  = 250       32000     100      128

When you work with array values, it is important to know which element of the array maps to which kernel setting. In the previous output, the values of the semaphore array 250 32000 100 128 map to the following kernel parameters: semmsl, semmns, semopm, and semmni, respectively. For example, semmsl is set to 250 and defines the maximum number of semaphores in a semaphore array. The meanings of these semaphore parameters are discussed in detail in recipe 9-6.

Viewing Shared Memory

On Linux systems, all the shared memory parameters appropriately begin with the string shm. Use the ls (list) command with the -1 (that’s a number one) option to display which shared memory parameters are available on your Linux system:

$ ls -1 /proc/sys/kernel/shm*

You should see output similar to this:

/proc/sys/kernel/shmall
/proc/sys/kernel/shmmax
/proc/sys/kernel/shmmni

Next, use the cat command to view individual shared memory settings. This example shows how to view the maximum size (in bytes) of a shared memory segment (shmmax) that can be created:

$ cat  /proc/sys/kernel/shmmax
2147483648

Table 9-2 describes the shared memory parameters. Some of these parameters may not be updatable on your particular Linux system.

9-5. Modifying Kernel Parameters

Problem

You’re performing a database installation. The Oracle documentation specifies the recommended settings for several kernel parameters, but you want to modify these kernel parameters.

Solution

This solution is split into two sections: one for Linux and one for Solaris. Let’s look at Linux first.

Linux

On Linux systems, there are several valid techniques for changing kernel parameters: running sysctl, editing sysctl.conf, adding entries with echo, and adding entries with cat. This section focuses on using sysctl and editing the sysctl.conf file directly. See the “How It Works” section for details on using echo and cat to modify kernel parameters.

Use the sysctl command with the -w option to dynamically modify kernel parameters. The following command changes the kernel semaphore settings in the /proc/sys/kernel/sem virtual file:

# sysctl  -w kernel.sem="250   32000 100 128"

Notice that there are no spaces around the = sign. If you attempt to run the sysctl command with spaces around the = sign, you will receive an error like the following:

error: ’kernel.sem’  must  be of  the  form  name=value
error: Malformed setting ’=’
error: ’250  32000 100 128’  must  be of  the  form  name=value

To make changes persist across system reboots, use your favorite editor (such as vi) to add the parameters to the /etc/sysctl.conf file.

You can also directly modify the /etc/sysctl.conf file and then use the sysctl -p command to make desired kernel parameter changes. This example uses vi to first edit the /etc/ sysctl.conf file:

# vi /etc/sysctl.conf
# Add changes and then exit...

After you modify the /etc/sysctl.conf file, you can use the sysctl -p command to make the entries in the /etc/sysctl.conf file instantiated as the current values used by the Linux kernel:

# sysctl -p

The previous command loads into memory the values found in the /etc/sysctl.conf file. You can verify that the values were changed by using cat to view the corresponding virtual file.

When you edit the sysctl.conf file, we recommend that you first make a copy of the file with the date embedded into the file name. For example, before making any changes, create a copy of the file, as shown here:

# cp /etc/sysctl.conf    /etc/sysctl.conf.01_jan_08

Making a copy serves two purposes. First, it provides you with a copy of the parameters as they were before the change, which comes in handy if you want to revert to the previous settings for any reason. Second, this also gives you an audit trail of all kernel changes made via this file. You can then use commands such as diff to display differences in file versions (see recipe 5-14 for details).

Solaris

To modify kernel parameters on Solaris systems, use the prctl command. The following modifies the max-shm-memory parameter to 8GB:

# prctl -n project.max-shm-memory -v 8gb -r -i project group.dba

The following command modifies the max-sem-ids parameter to 256:

# prctl -n project.max-sem-ids -v 256 -r -i project group.dba

When you use the prctl command, you don’t have to reboot the system to instantiate the parameters. However, you do need to use the projmod command to ensure that the parameters are added to the /etc/project file, so that the parameters persist across system reboots. This example updates the /etc/project for the max-shm-memory parameter:

# projmod -sK "project.max-shm-memory=(privileged,8G,deny)" group.dba

How It Works

One advantageous feature of both Linux and Solaris is that you can dynamically change many of the kernel settings while the system is running. The parameters take effect as soon as you change them, and you are not required to reboot the system. This is different from many other OSs that require a server reboot for kernel changes to become instantiated.

On Linux systems, the /proc/sys directory contains virtual files that correspond to kernel settings. You can change the /proc/sys files to dynamically configure kernel values. Hundreds of parameters exist that you can modify. Run the following find command to give you a rough idea of how many kernel files there are with your version of Linux:

# find /proc/sys -type f | wc -l
598

Not all virtual files in the /proc/sys directory can be modified. One quick way to determine whether a /proc/sys file can be altered is to check the permissions. Any file that shows the writable permission can be changed. This example uses the ls -altr command to view the /proc/sys/kernel file permissions:

# ls -altr /proc/sys/kernel

Here is a partial listing of the output. Notice that the first two files are not modifiable, but the last three can be modified (signified by the w write permission):

-r--r--r--      1 root  root  0 Sep      4 16:32  version
-r--r--r--      1 root  root  0 Sep      4 16:32  tainted
-rw-r--r--      1 root  root  0 Sep      4 16:32  shmmni
-rw-r--r--      1 root  root  0 Sep      4 16:32  shmmax
-rw-r--r--      1 root  root  0 Sep      4 16:32  shmall

The shmmax parameter can have a significant impact on database performance. With Oracle databases, this parameter should be set to a value higher than the SGA size. If the shmmax value is too small, you may not be able to start your Oracle instance. Correctly sizing and configuring shared memory are important DBA tasks when building a new database server.

Databases use shared memory as a holding area for data read from disk. Database processes read and modify the data held in shared memory. The shared memory area uses semaphores to control exclusive access to memory segments. A database will fail to start if there isn’t enough shared memory available to be allocated. Therefore, it is paramount that DBAs know how to manage shared memory because it has a direct impact on database availability and performance.

Adding sysctl.conf Entries with echo

You can use the echo command to modify kernel parameters by writing the desired output to the specified virtual file. This example writes the values 250 32000 100 128 to the virtual /proc/sys/kernel/sem file using the echo command:

# echo 250 32000 100 128 > /proc/sys/kernel/sem

This command immediately changes the kernel settings for the sem (semaphores) parameter. If you want the change to persist across system reboots, you also need to add an entry to the /etc/sysctl.conf file. This file is read when the system boots to determine the settings for kernel parameters. You can edit the /etc/sysctl.conf file directly (with an editor such as vi) and add the following line:

kernel.sem  = 250 32000 100 128

Alternatively, you can use the echo command to add the desired parameters to the end of the /etc/sysctl.conf file, as shown here:

# echo "kernel.sem  = 250 32000 100 128"  >> /etc/sysctl.conf

Notice that the previous command uses >> to concatenate the desired entry to the bottom of the /etc/sysctl.conf file. Don’t use just a single right arrow > because it would overwrite the contents of /etc/sysctl.conf.

When you use echo and >> to write to the contents of the /etc/sysctl.conf file, no checks are performed to determine whether the kernel parameters you are writing to the file already exist. The echo and >> techniques simply add the values to the bottom of the file.

If two entries in the /etc/sysctl.conf file configure the same kernel parameter, the value that appears nearest to the bottom of the file will be the one that gets set because the parameters are processed from top to bottom. For example, suppose that you have the following two lines in the /etc/sysctl.conf file:

kernel.sem  = 500 64000 200 500
kernel.sem  = 250 32000 100 128

The bottom line in the previous listing will be set last, so it will dictate the kernel setting for the kernel.sem value.

After you use echo to write to the /etc/sysctl.conf file, you can use the sysctl -p command to make the entries in the /etc/sysctl.conf file instantiated as the current values used by the Linux kernel:

# sysctl  -p

Adding sysctl.conf entries with cat

The technique shown here is handy for adding several entries to the /etc/sysctl.conf file at the same time. First, use the cat command to add entries to the /etc/sysctl.conf file. This example shows how to use cat to write typical kernel parameter settings for an Oracle database:

# cat  >> /etc/sysctl.conf  <<EOF
kernel.shmall  = 2097152
kernel.shmmax  = 536870912
kernel.shmmni  = 4096
kernel.sem  = 250 32000 100 128
fs.file-max  = 65536
net.ipv4.ip_local_port_range  =  1024 65000
net.core.rmem_default  = 262144
net.core.rmem_max  = 262144
net.core.wmem_default  = 262144
net.core.wmem_max  = 262144
EOF

This command uses cat to write all the values encapsulated between the two EOF markers to the /etc/sysctl.conf file, which allows you to add several parameters simultaneously to the /etc/sysctl.conf file. When using cat and >> to write parameters to the /etc/sysctl.conf file, there is no automatic checking to determine whether the parameters already exist in the file. Using cat and >> will simply write to the bottom of the file.

After the desired changes are made, use the sysctl -p command to make the entries in the /etc/sysctl.conf file the current values used by the Linux kernel, as shown here:

# sysctl -p

9-6. Displaying Semaphores

Problem

The Oracle installation documentation recommends configuring the system semaphores to certain minimal values. Before you modify the settings, you want to view the semaphore parameters.

Solution

This “Solution” section contains two sections: one for Linux and the other for Solaris. First, let’s discuss displaying semaphores on Linux.

Linux

On Linux systems, you can view semaphore information by displaying the contents of the /proc/sys/kernel/sem file. This example uses the cat command to view semaphore data (you don’t have to be root to view these values):

$ cat /proc/sys/kernel/sem
250         32000     100         128

Notice that there are four values listed for semaphores in the previous output. The numbers represent the value for the following semaphore kernel parameters: semmsl, semmns, semopm, and semmni, respectively. For example, the semmsl value is currently 250, semmns is 32000, and so forth.

Solaris

On Solaris, first view the project for the current user:

$ id -p
uid=2000(oracle) gid=200(dba) projid=200(group.dba)

Use the prctl command to show various semaphore settings for the currently logged-on user. For example, the following shows the maximum number of semaphores per set:

$ prctl -n process.max-sem-nsems $$

Here is some sample output:

process: 10033: -ksh
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
process.max-sem-nsems
        privileged        512       -   deny                                 -
        system          32.8K     max   deny                                 -

The line of code shows the maximum number of semaphore IDs allowed for the project:

$ prctl -n  project.max-sem-ids $$

This shows the maximum number of semaphore operations allowed per semop call:

$ prctl -n process.max-sem-ops $$

How It Works

Before making changes to any kernel information, prudent to first view the current values. Table 9-3 details the meanings of the relevant Linux semaphore variables. Notice that all semaphore variable names aptly begin with the letters sem. These semaphore names may vary slightly, depending on which version of the OS you’re using.

Semaphores are locking mechanisms that coordinate mutually exclusive access to sharable system resources. Semaphores act as gatekeepers to ensure that particular shared system resources are not accessed by multiple processes at the same time. Databases use semaphores to manage access to OS resources such as shared memory.

Database background processes require semaphores to manage mutually exclusive access to shared resources. If there aren’t enough semaphores available for all database processes, the database might not start or a runtime failure might occur. For semaphore-related problems, it is critical that DBAs know how to view and configure these kernel parameters.

9-7. Configuring Semaphores

Problem

You’re installing Oracle software on a database server and need to modify the semaphore settings.

Solution

This “Solution” section contains two sections: one for Linux and the other for Solaris. First, let’s discuss configuring semaphores on Linux.

Linux

On Linux systems, first check the current values in case they need to be referenced later. To view the current semaphore settings, use the cat command:

# cat /proc/sys/kernel/sem
250     32000     128    128

The values in the previous output represent the settings for the following semaphore parameters in this order: semmsl, semmns, semopm, and semmni.

This example uses the echo command to increase the maximum number of semaphore arrays (semmni) from 128 to 256:

# echo 250 32000 100 256 > /proc/sys/kernel/sem

Solaris

To modify kernel parameters on Solaris systems, use the prctl command. The following command modifies the max-sem-ids parameter to 256:

# prctl -n project.max-sem-ids -v 256 -r -i project group.dba

When using the prctl command, you don’t need to reboot the system to instantiate the parameters, but you do need to use the projmod command to ensure that the parameters are added to the /etc/project file. This process ensures that the parameters persist across system reboots. Here are some examples of using projmod to make settings permanent across system reboots:

# projmod -sK "project.max-sem-ids=(privileged,256,deny)" group.dba
# projmod -sK "project.max-shm-ids=(privileged,100,deny)" group.dba
# projmod -sK "project.max-sem-nsems=(privileged,256,deny)" group.db

How It Works

After you change the semaphore settings, it is a good idea to use the cat command to verify that the changes took place:

# cat  /proc/sys/kernel/sem
250     32000    100    256

If you want the changes to persist across a system reboot, ensure that you modify the /etc/sysctl.conf file appropriately. In this example, an editor (such as vi) is used to add the following entry to the /etc/sysctl.conf file:

kernel.sem  = 250 32000 100 256

9-8. Viewing Memory Structures

Problem

Your database has experienced a hard. You want to see whether any database–related memory structures are still physically allocated on the server.

Solution

For both Linux and Solaris, use the ipcs (interprocess communication status) command without any options to view the current allocated physical memory, semaphores, and message queues:

$ ipcs

Here is some typical output:

------ Shared Memory  Segments  --------
Key         shmid     owner       perms     bytes       nattch      status
0xb3e36378  131072     oracle     640       421527552   17
------ Semaphore  Arrays  --------
Key         semid       owner   perms   nsems
0x288e2800 1146880      oracle  640     126
0x288e2801 1179649      oracle  640     126
0x288e2802 1212418      oracle  640     126
0x288e2803 1245187      oracle  640     126
0x288e2804 1277956      oracle  640     126
------ Message  Queues  --------
Key     msqid    owner   perms   used-bytes    messages

The prior output has three sections. The oracle user has 421,527,552 bytes of shared memory allocated. There are five semaphore arrays allocated with 126 semaphores per array. There are no message queues allocated.

How It Works

The “Solution” section of this recipe demonstrates how to view in-memory structures that are currently allocated. On Linux systems, to view the system limits imposed on memory and semaphores, use the -lms options of the ipcs command:

$ ipcs -lms
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 1048576
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

------ 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

Compare the maximum memory values to the settings in the /proc/sys/kernel directory:

$ cat /proc/sys/kernel/shmall
2097152
$ cat /proc/sys/kernel/shmmax
1073741824
$ cat /proc/sys/kernel/shmmni
4096

To view all the options available with the ipcs command, use the -h (help) option.

9-9. Removing In-Memory Structures

Problem

Your database unexpectedly crashed, and for some reason the semaphores and shared memory have not been released. Other databases are running on the server, so you can’t reboot the system to release the shared memory objects. You want to manually remove these orphaned memory structures.

Solution

On both Linux and Solaris systems, first view the structures to be removed with the ipcs -sm command:

$ ipcs -sm

On this server, there are two instances of Oracle running, each with one allocated shared memory segment and five sets of semaphore arrays:

------ Shared Memory  Segments  --------
Key        shmid     owner  perms        bytes  nattch    status
0xb3e36378 32768    oracle    640    421527552      16
0x34525e84 65537    oracle    640    421527552      11

------ Semaphore  Arrays  --------
key         semid     owner   perms  nsems
0x288e2800 360448    oracle     640    126
0x288e2801 393217    oracle     640    126
0x288e2802 425986    oracle     640    126
0x288e2803 458755    oracle     640    126
0x288e2804 491524    oracle     640    126
0x3239d0e4 622597    oracle     640    126
0x3239d0e5 655366    oracle     640    126
0x3239d0e6 688135    oracle     640    126
0x3239d0e7 720904    oracle     640    126
0x3239d0e8 753673    oracle     640    126

If you have multiple databases on one server, first verify which memory structures belong to the orphaned instance by running the Oracle sysresv utility (located in the ORACLE_HOME/bin directory). This command reports on memory structures that correspond to your current instance setting of ORACLE_SID. Run this command as the owner of the Oracle binaries (usually oracle):

$ sysresv

Here is the pertinent output:

IPC  Resources  for ORACLE_SID  "RMDB2"  :
Shared  Memory:
ID       KEY
65537    0x34525e84
Semaphores:
ID        KEY
622597    0x3239d0e4
655366    0x3239d0e5
688135    0x3239d0e6
720904    0x3239d0e7
753673    0x3239d0e8

Total /dev/shm size: 14168080384 bytes, used: 1828995072 bytes
Shared Memory:
ID              KEY
1397653516      0x00000000
1397686285      0x00000000
1397719054      0x00000000
1397620747      0x00000000
1397751823      0x6ecd05d4
Semaphores:
ID              KEY
4718634         0xaee7d96c

You can remove memory objects either by key or ID. This example uses the -m option to remove a shared memory segment by its ID:

$ ipcrm -m 622597

This example uses the -s option to remove a semaphore array using an ID:

$ ipcrm  -s  4718634

You can verify that the memory structures have been removed by running sysresv again.

How It Works

The ipcrm command uses either the key or the ID as its input for identifying which IPC object to remove. The basic syntax for using ipcrm is as follows:

$ ipcrm  [ -M key | -m  id  | -Q key | -q  id | -S  key | -s  id ]

In the previous syntax description, -M is used with a shared memory key, -m is used with a shared memory ID, -S is used with a semaphore key, and -s is used with a semaphore ID.

Occasionally, you might have a database crash and for some reason the database semaphores or shared memory structures haven’t been released properly by the OS. In these rare situations, if you don’t have the luxury of rebooting the server, you have to first identify the unreleased memory object with the ipcs and sysresv commands and then remove it with the appropriate ipcrm command.

9-10. Viewing Network Configuration Settings

Problem

The Oracle documentation recommends setting some network parameters on Linux systems to minimal values. You first want to inspect the current network settings.

Solution

The virtual /proc network files are usually located either in /proc/sys/net/core or in /proc/ sys/net/ipv4. By using the ls -altr command, you can see that most of the virtual network files in /proc/sys/net/core are updatable:

# ls -altr /proc/sys/net/core

Here’s a partial listing of the output:

total 0
-rw-r--r-- 1 root root 0 Jun 12 22:02 xfrm_larval_drop
-rw-r--r-- 1 root root 0 Jun 12 22:02 xfrm_aevent_rseqth
-rw-r--r-- 1 root root 0 Jun 12 22:02 xfrm_aevent_etime
-rw-r--r-- 1 root root 0 Jun 12 22:02 xfrm_acq_expires
-rw-r--r-- 1 root root 0 Jun 12 22:02 wmem_max
-rw-r--r-- 1 root root 0 Jun 12 22:02 wmem_default
...

Use the cat command to view a particular virtual network file. This example uses cat to display the current setting for the rmem_default kernel parameter:

# cat /proc/sys/net/core/rmem_default
524288

How It Works

To view a complete listing of network settings, use the sysctl command and grep for the string net:

# sysctl -a | grep -i net

You’ll be presented with a great deal of output. Table 9-4 lists some of the network kernel parameters that you may have to modify for database servers.

9-11. Configuring Network Settings

Problem

You’re installing database software on a Linux server, and the Oracle documentation indicates that you need to configure some network parameters.

Solution

Use the echo command to update the /proc/sys/net/ipv4/ip_local_port_range file. This example uses the echo command to change the first local port allowed for TCP and UPD traffic to 1024 and the last local port to 65000:

# echo 1024 65000 > /proc/sys/net/ipv4/ip_local_port_range

You can verify the changes with the cat command:

# cat /proc/sys/net/ipv4/ip_local_port_range
1024   65000

You need to add these entries to the /etc/sysctl.conf file to have the changes persist across system reboots. Here’s a sample entry in the /etc/sysctl.conf file:

net.ipv4.ip_local_port_range=1024 65000

How It Works

Before changing any network kernel parameters, make sure that you first save a copy of the original values somewhere so that you can change back to the old values if the new values cause undesirable results. In the example, the value of the ip_local_port_range is first viewed with the cat command:

$ cat /proc/sys/net/ipv4/ip_local_port_range
32768    61000

9-12. Modifying System Open File Limits

Problem

The Oracle installation documentation for Linux recommends setting the system-wide open file limit for the server. You want to enable this restriction.

Solution

Use the echo command or the sysctl command to dynamically modify the /proc/sys/fs/file-max value. This example uses the echo command to change the file-max value on the fly to 65536:

# echo 65536 > /proc/sys/fs/file-max

Use the cat command to verify that the change took place:

# cat /proc/sys/fs/file-max
65536

Here’s an example of using sysctl -w to modify the maximum open file limit:

# sysctl -w fs.file-max=65536

Remember to add an entry to the /etc/sysctl.conf file to make the changes persist across system reboots.

How It Works

Linux imposes a limit on the overall number of files that can simultaneously be open on the server. Servers that host database applications tend to have many simultaneously open files. If the default value for the maximum number of open files is too low, you most likely will have to increase it. You’ll know that you’ve hit the maximum limit on the number of open files if you start seeing errors pertaining to “running out of file handles.”

This maximum open file limit is governed by the Linux kernel /proc/sys/fs/file-max virtual file. You can also view the maximum number of file handles by viewing the contents of the /proc/sys/fs/file-nr virtual file:

# cat /proc/sys/fs/file-nr
885 0 65536

This output shows the current number of allocated file handles, the number of free file handles, and the maximum number of file handles, respectively.

9-13. Showing Shell Limits

Problem

You want to view system resource limits instantiated by your logon shell.

Solution

This solution applies to both Linux and Solaris systems. Use the -a (all) option of the ulimit command to print the current soft limits for a process:

$ ulimit -a
core  file size          (blocks, -c) 0
data  seg size           (kbytes, -d) unlimited
file size                (blocks, -f) unlimited
pending  signals                 (-i) 1024
max  locked  memory     (kbytes,  -l) 32
max  memory  size       (kbytes,  -m) unlimited
open files                       (-n) 10000
pipe  size           (512  bytes, -p) 8
POSIX  message  queues    (bytes, -q) 819200
stack  size             (kbytes,  -s) 10240
cpu time                (seconds, -t) unlimited
max  user  processes             (-u) 16375
virtual memory          (kbytes,  -v) unlimited
file  locks                      (-x) unlimited

This output displays the parameter, units of measurement, option used to manipulate the parameter, and its current setting. To view the hard limit resources, use the ulimit -aH command. If you want to view a soft or hard limit for a particular setting, specify its option. For example, to display the hard limit for the number of open files, use the -Hn option:

$ ulimit -Hn
20000

How It Works

Your logon shell will impose default maximum limits on various resources that a process can use, such as the number of open files, processes per user, amount of memory allocated, and so on. These shell limits are defined by the ulimit command. Each resource has a soft limit setting and a hard limit setting. The soft limit setting establishes the default resource limit when a user logs on to the system. If the user process exceeds the soft limit setting for a resource, an error will be thrown. The user can manually increase the setting of the soft limit setting for a resource up to (but not exceeding) the value defined by the hard limit.

A user can modify the hard limit down, but cannot modify the hard limit up. So if you set a hard limit to a lower value, you can’t reset it to its original value. Only the root user can modify a hard limit to a higher value.

On Linux systems, the default values for soft and hard limits on the server are established by adding entries into the /etc/security/limits.conf file. On Solaris systems, use the prctl and projmod commands if you need to modify resource limits. Database processes tend to consume more resources than the default shell limits allow. Therefore, it’s important that you’re familiar with viewing and modifying shell resource limits.

9-14. Changing Shell Limits

Problem

You’re performing an Oracle installation on a new server and need to modify the shell limits per the installation instructions.

Solution

To alter the default shell limits for a user on Linux systems, edit the /etc/security/limits.conf file as root. This example shows how to change the number of processes and number of open files defined for the oracle user:

oracle  soft  nproc  2047
oracle  hard  nproc  16384
oracle  soft  nofile 1024
oracle  hard  nofile 65536

In this output, the first line defines the soft limit for the number of processes for the oracle user to 2047, and the second line sets the hard limit for the number of processes to 16384. The third and fourth lines set the soft and hard limits for the number of open files for the oracle user, respectively. These limits will be imposed on the oracle user when logging on to the server.

How It Works

The ulimit command provides a way to view and limit resources used by a shell and the resources of subprocesses started by a shell. As a non-root user, you can change your soft limits up to the value defined by the hard limit. As the root user, you can modify hard limits to higher values.

For example, to adjust the soft limit of the number of open files to 15000, issue the following:

$ ulimit -Sn 15000

To view the change, issue the command without a value:

$ ulimit -Sn
15000

If you exceed the value defined by the hard limit, you’ll receive an error such as the following:

-bash:  ulimit:  open files:  cannot  modify  limit: Invalid  argument

When you add entries to the /etc/security/limits.conf file on Linux systems, the default shell limit is set for the user process when users log on to the system. Table 9-5 shows the values and their meaning when changing the /etc/security/limits.conf file.

When working with database applications, you might not ever want to have a database process constrained by a soft limit. Instead, you might want only the hard limit to govern the amount of resources a database process uses. In this case, you can set the soft limit equal to the hard limit in the database user login profile file. For example, if the oracle user on your Linux box uses the Bash shell, you would modify the .bash_profile logon file (see recipe 2-5 for further details).

The following entries are added to the oracle user’s .bash_profile file to establish the soft limits equal to the hard limits:

ulimit -u  16384
ulimit -n  65536

When you use the ulimit command, if you don’t denote a -S (soft) or -H (hard) option, both the soft and hard limits are set to the value specified. Whenever the oracle user logs on, the soft and hard limits for the number of processes will be 16384, and the soft and hard limits for the number of open files will be 65536.

If you write an entry into the logon file that exceeds the hard limit, the user will receive an error such as this at logon time:

-bash: ulimit:  open files: cannot modify limit: Operation not permitted