I have a powerful machine with 70 GB RAM. How many Oracle instances with 20 GB can be created

oracleoracle-11g-r2

I have powerful machine with 70 GB RAM. I created one Oracle instance with 20 GB as sga_target. I am not able to create another Oracle instance with sga_target > 10G even when I keep the first database down. If I set sga_target >=10G it gives below error on startup:

ORA-27104: system-defined limits for shared memory was misconfigured

While free -m shows that there is enough memeory available though:

             total       used       free     shared    buffers     cached
Mem:         72419      34490      37928          0        618      28159
-/+ buffers/cache:       5711      66707
Swap:         2047          0       2047

Do I need to increase swap space? Any pointer in this regard is highly appreciated.

Also for 70 GB memory, for creating many instances, what would be best value for swap space – is there some way to caluculate this? My objective is to have at least two instances each with sga_target=20G and will keep only one instance up at a time. If I am missing any concept here?

Output of ipcs -im is as below

------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 4194303
max total shared memory (kbytes) = 8388608
min seg size (bytes) = 1

Best Answer

Your kernel parameters need modifying.

Edit /etc/sysctl.conf and ensure the following lines are present:

kernel.shmall = 18350080
kernel.shmmax = 75161927680
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128

Then reboot the machine.

Related Solutions

Oracle extents – How to have so many with this small size

The data you're getting from Toad appears to be incorrect or, at least, misleading. If you are using a locally managed tablespace with automatic extent allocation, Oracle will determine your initial and next extent sizes automatically. In 11.2, the first 16 extents are going to be 64k in size (for a total of 1 MB). The next 63 extents are going to be 1 MB in size. So if you have 26 extents, that implies that the table occupies 11 MB of space on disk. An initial extent of 1.44 GB makes no sense and a size of 4.18 MB seems rather low if you're saying it hasn't shrunk.

What does

SELECT tablespace_name,
       extent_id, 
       bytes/1024 kb
  FROM user_extents
 WHERE segment_name = <<name of table>>

show you? If you have 26 extents, you should see that extents 0-15 are 64 kb in size and extents 16-25 are 1 MB in size.

SELECT SUM(bytes)/1024/1024 size_in_mb
  FROM user_extents
 WHERE segment_name = <<name of table>>

will show you the total size of the table's extents on disk.

Oracle extents – How to have so few with this large size

You may believe that this is a different question but my answer is going to be essentially identical assuming the additional information from the prior question is the same-- you're using automatic extent allocation and you're not sure how Toad is determining these numbers.

The data you're getting from Toad appears to be incorrect or, at least, misleading. If you are using a locally managed tablespace with automatic extent allocation, Oracle will determine your initial and next extent sizes automatically. In 11.2, the first 16 extents are going to be 64k in size (for a total of 1 MB). The next 63 extents are going to be 1 MB in size. So if you have 12 extents, that implies that the table occupies 768 kb of space on disk. An initial extent of 118 MB makes no sense and a size of 376 MB makes no sense.

What does

SELECT tablespace_name,
       extent_id, 
       bytes/1024 kb
  FROM user_extents
 WHERE segment_name = <<name of index>>

show you? If you have 12 extents, you should see that extents 0-11 are 64 kb in size.

SELECT SUM(bytes)/1024/1024 size_in_mb
  FROM user_extents
 WHERE segment_name = <<name of index>>

will show you the total size of the table's extents on disk.

Related Question