Database News You Can Use
A monthly newsletter for Oracle users
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Shared Memory Segments and Semaphores
You’re invited to submit your Oracle-related questions to us at email@example.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only). Hotsos Symposium, March 7-10
This month’s question comes from Helen W. of Santa Monica, California: Sometimes our Oracle database crashes and we are unable to restart the instance. We end up having to reboot the server. This presents a problem since we have several mission-critical databases that reside on the same machine and rebooting takes the instances out of service for a few minutes. Is there some way we can troubleshoot the situation when this happens? What prevents the instance from being able to restart?
Robert Townsend of the Database Specialists team responds: Your problem may be shared memory or semaphores left allocated when the instance died. This is known to happen on several platforms. You are not able to restart the instance because the operating system thinks shared memory segments or semaphores are still in use by the crashed instance. Remember you can’t have two instances with the same SID running on the same server at the same time.
To check to see if this is the case, use the IPCS (inter-process control system) Unix command to evaluate shared memory segments and semaphores. The command works a little differently on different Unix platforms, so view the man pages and get familiar with yours. On Red Hat Linux you will see something like the following:
[oracle@zippy]$ ipcs -t ------ Shared Memory Attach/Detach/Change Times -------- shmid owner attached detached changed 65537 root Jan 3 10:28:10 Not set Jan 3 10:28:10 589837 oracle Jan 22 10:08:35 Jan 22 10:08:46 Jan 22 10:08:26 1769494 oracle Jan 22 10:24:14 Jan 22 10:24:19 Jan 22 10:24:08 ------ Shared Memory Operation/Change Times -------- semid owner last-op last-changed 98304 oracle Thu Jan 22 10:24:52 2004 Thu Jan 22 10:24:52 2004 622593 oracle Thu Jan 22 10:25:12 2004 Thu Jan 22 10:25:12 2004 ------ Message Queues Send/Recv/Change Times -------- msqid owner send recv change
Now you will need to figure out which is which—that is, which shared memory segments and semaphores belong to which Oracle instance. How do we go about this? One approach is to look at the time when an Oracle instance was started and compare this to when a particular shared memory segment was created. Using the ipcs -t command we can tell what time each shared memory segment was created. (On Red Hat Linux this appears in the “changed” column.) In the above example we have two shared memory segments created at different times, corresponding to two Oracle instances started about 15 minutes apart.
Approaching the problem in another way, we could use the ipcs -s command to see the semaphore count in each semaphore set (the “nsems” column below). This should correlate to the “processes” setting in the initSID.ora file or spfile. In the example below, the first instance has the processes parameter set to 100 while the second instance has it set to 150. (Oracle adds four semaphores per instance for its own control.)
[oracle@zippy oracle]$ ipcs -s ------ Semaphore Arrays -------- key semid owner perms nsems 0x4de08350 98304 oracle 640 104 0xc29855dc 622593 oracle 640 154
On some platforms you will also be able to relate the size of a shared memory segment to an instance’s SGA size specified in the initSID.ora file or spfile.
When you identify a shared memory segment or semaphore set that was abandoned by a crashed Oracle instance, you can remove it with the IPCRM command. Use the -m option and a shmid (from the output of the IPCS command) to remove a shared memory segment, or the -s option and a semid to remove a semaphore set. For example:
[oracle@zippy oracle]$ ipcrm -m 589837 [oracle@zippy oracle]$ ipcrm -s 98304
However, there is a caveat to the IPCRM command: Remove the wrong memory segment or semaphore and you will crash another Oracle instance! So be sure of what you are doing and double-check your work. Also, please get familiar with your version of IPCS and IPCRM before you get into a jam. Try starting instances in a test environment and observe the shared memory segments and semaphores using your version of IPCS. Become familiar with this tool and you will be confident when your instance crashes and everybody around you is pressing you for a fix.
Hotsos Symposium is billed as “the only conference in the Americas devoted to Oracle system performance.” It takes place in Dallas, TX on March 7-10, 2004 and is a combination of intense focus, small audience size, and well-known speakers. The event will include presentations by such Oracle experts as Tom Kyte, Jonathan Lewis, Cary Millsap, and Steve Adams. Roger Schrag and Terry Sutton of the Database Specialists team have been invited to speak on the topic of Oracle performance tuning using the wait events interface. Native PL/SQL Compilation in Oracle9i
Don’t miss this outstanding educational opportunity. You’ll find a speaker listing, session abstracts and more on the Hotsos website at http://www.hotsos.com/appearances/sym2004.php.
We’ve updated our popular Native PL/SQL Compilation presentation for the current Oracle9i patch set, and we’ve added a white paper on the subject as well. Starting in Oracle9i, it is possible to compile your PL/SQL stored procedures into native machine code on your database server. This can make your PL/SQL programs run faster. DBAZine Oracle Resources
This white paper provides an overview of the native compilation feature. Then, it describes experiences on a project where a production application including over 35,000 lines of complex PL/SQL code was natively compiled. You’ll learn about actual performance improvements observed, system stability, and the inevitable unexpected issues that popped up along the way. Read more at http://dbspecialists.wpengine.com/presentations.html.
Check out the February issue of DBAZine.com It includes some interesting articles related to Oracle technology, including:
- Disk Management for Oracle by Donald K. Burleson
- Cleaning the Tables by John Weeg
- Partitions in the Real World by Jonathan Lewis