Wednesday, 28 July 2010

Understanding Shared Memory and Semaphores

Shared memory and semaphores are two important resources for an Oracle instance on Unix. An instance cannot start if it is unable to allocate what it needs.


Shared memory is exactly that - a memory region that can shared between different processes. Oracle uses shared memory for implementing the SGA, which needs to be visible to all database sessions.

Semaphores can be thought of as flags (hence their name, semaphores). They are either on or off. A process can turn on the flag or turn it off. If the flag is already on, processes who try to turn on the flag will sleep until the flag is off. Upon awakening, the process will reattempt to turn the flag on, possibly suceeding or possibly sleeping again. Such behaviour allows semaphores to be used in implementing a post-wait driver - a system where processes can wait for events (i.e. wait on turning on a semphore) and post events (i.e. turning of a semaphore). This mechanism is used by Oracle to maintain concurrency control over the SGA, since it is writeable by all processes attached.


Shared memory required by the Oracle Instance : On instance startup, the first things that the instance does is: -Read the "init.ora" -Start the background processes -Allocate the shared memory and semphores required The size of the SGA will be calculated from various "init.ora" parameters. This will be the amount of shared memory required. The SGA is broken into 4 sections - the fixed portion, which is constant in size, the variable portion, which varies in size depending on "init.ora" parameters, the redo block buffer, which has its size controlled by log_buffers, and the db block buffer, which has its size controlled by db_block_buffers. The size of the SGA is the sum of the sizes of the 4 portions. There is unfortunately no simple ormula for determining the size of the variable portion.

Generally, the shared pool dominates all other parts of the variable portion, so as a rule of thumb, one can estimate the size as the value of shared_pool_size.

The number of semphores required is much simpler to determine.
Oracle will need exactly as many semaphores as the value of the processes "init.ora" parameter.


1. One-segment

2. Contigous multi-segment

3. Non-contigous multi-segment

When attempting to allocate and attach shared memory for the SGA, it will attempt each one, in the above order, until one succeeds or raises an ORA error. On other, non-fatal, errors, Oracle simply cleans up and tries again using the next memory model. The entire SGA must fit into shared memory, so the total amount of shared memory allocated under any model will be equal of the size of the SGA(SGASIZE).

1. One-segment:- The one-segment model is the simplest and first model tried. In this model, the SGA resides in only one shared memory segment. Oracle attempts to allocate and attach one shared memory segement of size equal to total size of the SGA. However, if the SGASIZE is larger than the configured SHMMAX, this will obviously fail. In this case, the SGA will need to be placed in multiple shared memory segments, and Oracle proceeds to the next memory model for the SGA.

With multiple segments there are two possibilities. The segments can be attached contiguously, so that it appears to be one large shared memory segment, or non-contiguously, with gaps between the segments.

2. Contigous multi-segment - In the contiguous segment model, Oracle simply divides the SGA into SGASIZE/SHMMAX (rounded down) segments of size SHMMAX plus another segment of size SGASIZE modulo SHMMAX

3. Non- contigous multi-segment : Once the number of segments and their sizes is determined, Oracle then allocates and attaches the segments one at a time; first the fixed and variable portion segment(s), then the redo block buffer segment(s), then the db block buffer segment(s). They will be attached non-contiguously,
At this point, we have either attached the entire SGA or returned an ORA error. The total size of segments attached is exactly SGASIZE; no space is wasted. Once Oracle has the shared memory attached, Oracle proceeds to allocating the semaphores it requires.

Recommended values of kernel parameters for Shared memory in
Oracle 8i

SHMMAX= max value of shared memory segment = .5 * size of
physical memory

SHMMIN= min size of shared memory segment=1

SHMMNI= max number of shared memory identifiers on system = 100

SHMSEG= max number of shared memory segments per process = 10

max Sga that can be created by the one segment model is SHMMAX*SHMSEG

You can display the current kernel parameters by doing a "sysdef -i"


Oracle just needs to allocate a number of semaphores equal to the processes parameter in "init.ora".

SEMMSL= # of semaphores in a semaphore set

SEMMNI= the maximum # of semaphores sets in the system

SEMMNS= the number of semaphores in the system.

SEMOPM= max number of operations per semop call = 100

SEMVMX = semaphore max value = 32767

When an Oracle instance is started, all required semaphores will be allocated. Semaphores are allocated in sets.

Since each oracle process* requires a semaphore, the number that is allocated is equal to the value of the init.ora parameter PROCESSES. The total # of semaphores required is the sum of all your instance's PROCESSES.

You can allocate all of your semaphores in one or more semaphore sets. If SEMMSL=PROCESSES, then only one semaphore set is required.

The maximum # of semaphores that can be allocated will be the lesser of (SEMMSL*SEMMNI) or SEMMNS.

If SEMMSL is not equal to PROCESSES, be sure that the total # of semaphores required (sum of PROCESSES) does not exceed the maximum (SEMMSL*SEMMNI, SEMMNS).

For example, if SEMMSL=25 and SEMMNI=10, total # of semaphores required (sum of PROCESSES) must not exceed 250 (10 semaphore sets * 25 semaphores/set).

Note: some Operating Systems have a maximum # of semaphore sets in the system.

If you have more than one instance and the values of PROCESSES are different, you may want to make SEMMSL equal to the lowest PROCESSES so that you don't allocate semaphores that will not be used. Otherwise, this could prevent you from being able to allocate all of your requirements.

For example:
Instance PROD has PROCESSES=100
Instance DEV has PROCESSES=50

If SEMMSL = 50, 3 semaphore sets will be allocated, 2 for PROD and 1 for DEV.

If SEMMSL = 100, 2 semaphore sets will be allocated, 1 for PROD
and 1 for DEV.In this case, 100 semaphores will be allocated for DEV when it will only use 50. These unused 50 semaphores cannot be allocated for any other databases.

To see what semaphores have been allocated, use the Unix command 'ipcs -b'.

For example:



s 0 0 --ra-r----- osupport dba 25

s 1 0 --ra-r----- osupport dba 25

s 18 0 --ra-r----- osupport dba 25

s 19 0 --ra-r----- osupport dba 25

s 4 0 --ra-r----- osupport dba 25

s 5 0 --ra-r----- osupport dba 25

NSEMS=the number of semaphores in each semaphores set.

Perform these steps for each instance that is up and running:

$ svrmgrl

SVRMGR>connect internal

SVRMGR>oradebug ipc

This will show the shared memory segment and semaphore that each instance has attached/in use.

Example output from oradebug ipc command:

-------------- Shared memory --------------

Seg Id Address Size

10250 c1eaf000 4591616

Total: # of segments = 1, size = 4591616

-------------- Semaphores ----------------

Total number of semaphores = 50

Number of semaphores per set = 50

Number of semaphore sets = 1

Semaphore identifiers:


The Seg Id shows 10250 for the shared memory which is attacehed to the RUNNING instance. DO NOT REMOVE THAT ONE.

The Semaphore identifiers shows 188434 for the semaphore which is attacehed to the RUNNING instance. DO NOT REMOVE THAT ONE.

Once you have noted ALL of the identifiers for ALL of the instances which are up and running, compare these id numbers to those in the "ipcs -b" listing.

The entry that does not have a running instance to match is the orphaned entry. THAT ONE SHOULD BE REMOVED.

The command used to remove these entries is: ipcrm

NOTE: The option differs for shared memory and semaphores.

ipcrm -m <== Use for the Shared Memory entry

ipcrm -s <== Use for the Semaphore entry

also refer semaphores here.

Monday, 19 July 2010

Date Arithmetic

Interval Time
Execute daily'SYSDATE + 1'
Execute every 4 hours'SYSDATE + 4/24'
Execute every 10 minutes'SYSDATE + 10/1440'
Execute every 30 seconds'SYSDATE + 30/86400'
Execute every 7 days'SYSDATE + 7'
Do no re-execute and remove jobNULL
NOTE: Remember that job intervals expressed as shown in the previous table do not guarantee that the next execution will happen at a specific day or time, only that the spacing between executions will be at least that specified. For instance, if a job is first executed at 12:00 p.m. with in interval of 'SYSTEM + 1', it will be scheduled to execute the next day at 12:00 p.m. However, if a user executes the job manually at 4:00 p.m. the next day using DBMS_JOB.RUN, then it will be rescheduled for execution at 4:00 p.m. the next day. Another possibility is that the database is down or the job queue so busy that the job cannot be executed exactly at the time scheduled. In this case, the job will run as soon as it can, but the execution time will have migrated away from the original submission time due to the later execution. This "drift" in next execution times is characteristic of jobs with simple interval expressions.

Jobs with type 2 execution requirements involve more complex interval date expressions, as see in the following table.
ActionInterval Time
Every day at 12:00 midnightTRUNC(SYSDATE + 1)
Every day at 8:00 p.m.TRUNC(SYSDATE + 1) + 20/24
Every Tuesday at 12:00 noonNEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 12/24
First day of the month at midnightTRUNC(LAST_DAY(SYSDATE) + 1)
Last day of the quarter at 11:00 p.m.TRUNC(ADD_MONTH(SYSDATE + 2/24,3),'Q') - 1/24
Every Monday, Wednesday and Friday at 9:00 p.m.TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY"))) + 21/24

WHERE RD.DATE_CREATED >= (TRUNC(ADD_MONTHS(SYSDATE,-1),'MM') + 19) AND RD.DATE_CREATED <= (TRUNC(SYSDATE ,'MM') + 19) -- Between 20th of current and previous month

FROM DUAL; --- between last to Mondays

Thursday, 15 July 2010


Friday JOB ::

backup device type disk tag '%tag' database;

backup device type disk tag '%tag' archivelog all not backed up delete all input;

allocate channel for maintenance device type disk;

crosscheck backup;

delete noprompt expired backup;

report obsolete;

delete noprompt obsolete device type disk;

crosscheck archivelog all;

delete noprompt expired archivelog all;

restore database validate check logical;

release channel;

============== OR ==========================================

backup incremental level 0 device type disk tag '%TAG' database;

backup device type disk tag '%TAG' archivelog all not backed up delete all input;

crosscheck backup;

delete noprompt expired backup;

report obsolete;

delete noprompt obsolete device type disk;

restore database validate check logical;

Mon –Thu Job::

backup incremental level 1 device type disk tag '%TAG' database;

backup device type disk tag '%TAG' archivelog all not backed up;

restore database validate check logical;

Friday, 9 July 2010

Oracle 11Gr2 Bash_Profile & /etc/sysctl.conf

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc


# User specific environment and startup programs

umask 022




export PATH

set -o vi;export ORACLE_HOSTNAME


ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1;export ORACLE_HOME

#OMS_HOME=/u01/app/oracle/oracle/product/10.2.0/oms10g;export OMS_HOME

#AGENT_HOME=/u01/app/oracle/oracle/product/10.2.0/agent10g;export AGENT_HOME


##PATH=$PATH:$ORACLE_HOME/bin:/oracle/admin/dba/bin;export PATH




#NLS_LANG=American_America.WE8ISO8859P9;export NLS_LANG

#NLS_DATE_FORMAT='yyyy-mon-dd';export NLS_DATE_FORMAT

##NLS_DATE_FORMAT='yyyy-mon-dd:hh24:mi:ss';export NLS_DATE_FORMAT


#unset TWO_TASK

. /home/oracle/.aliases

set EDITOR=vi

export PATH


#Oracle Requirements

#Hemesh Patel 7th July 2010

fs.aio-max-nr = 1048576

fs.file-max = 6815744

kernel.shmmni = 4096

kernel.sem = 250 32000 100 128

net.ipv4.ip_local_port_range = 9000 65500

net.core.rmem_default = 4194304

net.core.rmem_max = 4194304

net.core.wmem_default = 262144

net.core.wmem_max = 1048586

net.ipv4.tcp_wmem = 262144 262144 262144

net.ipv4.tcp_rmem = 4194304 4194304 4194304

Wednesday, 7 July 2010

Oracle EM 11g Grid Library



Hardware Requirements

Operating System Requirements --My Oracle Support note 412431.1

Package Requirements

Operating System Groups and Users Requirements

Unique Host Name and Static IP Address Requirements-- You must use only a static IP address. If you use a dynamic IP address, the installation will fail.

Temporary Directory Space Requirements--Ensure that you allocate 400 MB of space for a temporary directory where the executables can be copied. For example, /tmp on UNIX hosts andc:\Temp on Microsoft Windows hosts.

Central Inventory Requirements--Ensure that you allocate 100 MB of space for the central inventory directory.Also ensure that the central inventory directory is not on a shared file system.

If it is already on a shared file system, then switch over to a non-shared file system by following the instructions outlined in My Oracle Support note 1092645.1.

UMASK Value Requirements--Ensure that you set the default file mode creation mask (umask) to 022 in the shell startup file.

File Descriptor Requirements--Ensure that you set the file descriptor to a minimum of 4096.

To verify the current value set to the file descriptors, run the following command:

/bin/sh -c "ulimit -n"

If the current value is not 4096, then as a root user, update the /etc/security/limits.conf file with the following entries:

<UID> soft nofile 4096

<UID> hard nofile 4096

JDK Requirements--Ensure that you have SUN JDK 1.6_18 version installed on the host where you are planning to install Enterprise Manager Grid control. This is required for all UNIX 64 bit platforms and Microsoft Windows 64 bit platforms.

If you have a JDK installed, then verify its version by navigating to the <jdk>/bin directory and running the following command. The output of this query should give you the complete version number.

java -fullversion

You can also download the JDK from the following location, and you can also refer to My Oracle Support note 1063587.1 for an example on installing JDK 1.6_18 on Linux x86_64 platform.

Note: JROCKET is not supported.