2009-10-06

How to lock Oracle SGA memory in RAM within zone

A month ago I had a discussion with our DBA about how to make Oracle SGA memory ineligible for swapping/pagging (we had such accident). Conclusion was to use lock_sga parameter. A few days ago the DBA told me that Oracle support told him that the lock_sga parameter is not supported on Solaris. I didn't belive it and checked (you know, Google etc ;-) ). After a while it turned out that locking shared memory in RAM is done by syscall shmctl(id,SHM_LOCK) which can be done only by root ! Once again Oracle support didn't do its homework ;-) But the question remains: can we do it somehow ? There are some solutions where you can write program in C and call shmctl on requested shared memory segments but I don't find it as an elegant solution. At first I had no idea how to approach the problem. After a while my memory started working and recalled that Solaris privileges might help.
Below is example of what one should do to lock Oracle SGA in Solaris zones memory.
We need to fulfill two requirements:
  • give the zone privilege to lock some memory in RAM
  • give Oracle user privilege to lock the same memory as non-root user

The first requiremnt is achieved using zone's feature:

...
set limitpriv=default,proc_lock_memory
...

But using just this doesn't let us lock the SGA:

server:sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Oct 6 15:08:17 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-27126: unable to lock shared memory segment in core
SVR4 Error: 1: Not owner

Oracle user is not able to use it.

The above example - zones privilege - was just the first step. Now time for the second - we need to let user oracle run the shmctl syscall. Solaris RBAC is rescue for us. You need to run the following inside the zone:

usermod -K defaultpriv=basic,proc_lock_memory oracle

Please remember that you need to logout and login into oracle account after usermod command ! Otherwise it won't work !

Let's check if it it saved:

# grep oracle /etc/user_attr
oracle::::type=normal;defaultpriv=basic,proc_lock_memory

Now:

server:sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Oct 6 15:12:56 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1696565248 bytes
Fixed Size 731136 bytes
Variable Size 620756992 bytes
Database Buffers 1073741824 bytes
Redo Buffers 1335296 bytes
Database mounted.
Database opened.
SQL> show parameter lock_sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean TRUE

Easy, isn't it ? :-)

No comments: