ORA-27123: unable to attach to shared memory segment

Hi,

WHile startup a database, I got the following error:

ORA-27123: unable to attach to shared memory segment
SVR4 Error: 12: Not enough space

I tried to reduce shared_pool_size in init file but won't work. The top command give a memory of 61MB free; but shared_pool_size of init file is about 28MB.

Can anyone tell me what is wrong? I hope to solve it soon.

Thanks
ebi168Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

paquicubaCommented:
A) You are receiving the above error messages because the SGA size for
   the current instance is too large to fit into the available physical
   memory segments.
   
   Raise SHMMAX in /etc/system or Lower SHARED_POOL_SIZE and/or
   DB_BLOCK_BUFFERS accordingly


   You need to reduce the SGA to a resonable size, so that it can be
   allocated during the startup process.
   
   Approximating size of the SGA
   -----------------------------
 
    8.0.X


      To approximate size of the SGA (Shared Global Area), use following formula:
   
      ((db_block_buffers * block size) +
      (shared_pool_size + large_pool_size + log_buffers) + 1MB


   
    8.1.X


      To approximate size of the SGA (Shared Global Area), use following formula:


      ((db_block_buffers * block size) +
      (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB



   For Example:
      SHARED_POOL_SIZE = 200 MB
      DB_BLOCK_BUFFERS = 200000 # 1600 MB
      DB_BLOCK_SIZE = 8192 #8K
      LOG_BUFFER = 65536 # 64K


   Total SGA size becomes about 1800 MB for this instance.
   
   *NOTE: If you have other instances running on the same server,
           you need to calculate SGA size requirements for the other
           instances also.
 
   Modify DB_BLOCK_BUFFERS from 200000 to approximately 5000 (40 MB)
   in the init<SID>.ora file and restart the database.
       
      SVRMGR> shutdown immediate
      SVRMGR> startup
      Statement processed.




0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ebi168Author Commented:
Thanks for the excellent information. I lowered both the shared pool and db buffer size and the database is startup. But what I don't understand is that when I check use the top command, the memory available is still 61MB. I thought startup the database is going to make the memory left smaller. If there is enough memory, why oracle couldn't allocate and I have to reduce the sga size?
0
paquicubaCommented:
SHMMAX  Available physical memory  Defines the maximum allowable size of one shared memory segment.
The SHMMAX setting should be large enough to hold the entire SGA in "one" shared memory segment. A low setting can cause creation of multiple shared memory segments which may lead to performance degradation.  
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

seazodiacCommented:
can you display your share memory setting by using this command?

$ ipcs -l


if you shared memory is too smaller, this will happen,


if your share memory segment is big enough, then change oracle SGA_MAX_SIZE in the database init.ora file.

most often, this parameter sga_max_size will be default if you don't see one in your init.ora file.

then add this line explicitly in your init.ora file:

SGA_MAX_SIZE=60M

0
seazodiacCommented:
but of course, you have to adjust the other memory pools accordingly so that they will fit into the SGA.
0
ebi168Author Commented:
But what I am wondering is why the free memory size didn't change when I use the "top" unix command after I startup the database. Thanks
0
bytes00Commented:
try this:

ORA-27123: unable to attach to shared memory segment.
I temporarely increased the shmmax setting for the kernel by executing the following command:

$ su - root
# cat /proc/sys/kernel/shmmax
33554432
# echo `expr 1024 \* 1024 \* 1024` > /proc/sys/kernel/shmmax
# cat /proc/sys/kernel/shmmax
1073741824
It is recommended to increase the shmmax setting permanently for Oracle. For more information, see Setting Shared Memory.

For more information on optimizing shared memory settings for Oracle databases on Linux, see Setting Shared Memory. These parameters apply to all Red Hat Linux versions. But note that except for the shmmax parameter, these parameter do not need to be changed for installing Oracle on Linux. But you might want to adjust all shared memory settings later to optimize the server for Oracle.


-john
0
ebi168Author Commented:
My concern is no longer the error. It is that before and after the database startup, the memory size is almost the same:

>top

load averages:  0.37,  0.27,  0.23
226 processes: 224 sleeping, 1 zombie, 1 on cpu
CPU states: 70.2% idle, 21.3% user,  5.9% kernel,  2.7% iowait,  0.0% swap
Memory: 4096M real, 61M free, 475M swap in use, 20G swap free
..........

The free memory is still around 60M after the database startup. Isn't it going to be less than that since the new instance's sga will consume some memory?
0
seazodiacCommented:
ebi:

don't worry about that. UNix work differently than Windows.

Unix have a notiion of System cache. so it will cache the memory segment that ever be requested. i.e., they are no longer listed as free memory as they resided in the cache pool. this is somewhat misleading.

but the system cache pool will provide faster memory allocation when need arises.

so in your case, 60M free memory are "real" free one, they never be used.

0
ebi168Author Commented:
Thanks very much, Seazodiac.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.