[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 20796
  • Last Modified:

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
0
ebi168
Asked:
ebi168
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
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
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now