Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Swap, memory, kernal config? - ORA-12500 error

SOLVED
Go to solution
TWBSupport
Regular Advisor

Swap, memory, kernal config? - ORA-12500 error

Hi All -
We have just went live on our new rp2470. Everything seemed fine, then users started getting the ORA-12500 error. Here's the history.. HPUX 11i running Oracle 9i. The application running on Oracle 9i is a 32 bit application & the only thing running on this system right now, the 11i is 64 bit. 2G RAM, SWAP was 4 G, I increased it to 8 G & that allowed approximately 50 more users get on. The server would start rejecting connections at about 127, then after the SWAP increase we got to about 178, but still receiving this error at 178. I need to allow 300 users on this system at once. Attached is my kmtune. Any advice would be greatly appreciated. Thank you!
36 REPLIES
Patrick Wallek
Honored Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

Well, it sounds to me like if you increase your swap and more users get on then it is a memory issue.

2GB of RAM is WAY WAY too small for any server running Oracle. In my opinion that number should be 4GB or even more. The problem is that if your app is only 32-bit you will only use 3.75 GB of RAM. Oracle will be able to use more than 4GB of RAM since it is 64 bit, but that may cause problems with your app.

Steven E. Protter
Exalted Contributor
Solution

Re: Swap, memory, kernal config? - ORA-12500 error

I would look at shmmax

Very few kernel parameters have any effect on the number of possible oracle logins.

shmmax can be safely increased all the way up to 25% of memory. Memory is defined as memory plus swap.

As far as swap settings go, swap should be between 1.0 and 2.0 times physical memory. Anything higher and if you hit swap your machine will be as slow as can be.

Please post up swapinfo -tam

Before you increase swap, you should make sure you are using it.

I'm attaching a performance data collection script in case there are other bottlenecks triggering the problem.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Patrick -
I guess when I read Oracles' recommendations for RAM, (512 for server, 512 for Mgmt) & then I doubled it. I thought we would be good to go...


Steven -
As always you have given me a great tool here. No problems jumped out at me, but I'll take a longer look at it in a few minutes. Here's the swapinfo you requested.
Patrick Wallek
Honored Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

You are actually using some of your swap space which is not good. That could drastically slow down your machine. You also have 3772MB reserved and about 450 MB actually in swap, which is more than your 2GB of RAM. That totals up to more than 4GB.

I would definitely add RAM to this machine to start with. If HP RAM is too expensive for you, you could try Kingston, Crucial or DataRam. I have used Kingston in the past with no problem and they are MUCH MUCH MUCH cheaper than HP and they come with a lifetime warranty.
Steven E. Protter
Exalted Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

I would add HP ram, especially if you have a support contract. It might not seem fair, but HP hardware support doesn't look favorably on a system that has non-HP memory in it. They tend to try and back away from the call.

Your application will perform better if its not swapping most of the time. Your ram to swap ratio is bad and your system is likely to slow down to a crawl when swap gets hit hard.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Thank you both for your replies. I will order the HP RAM. Once I install the new RAM, do I just delete the extra SWAP space I created, by lvreducing?
Patrick Wallek
Honored Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

No, you cannot delete a swap area without rebooting the machine.

If you want, you can leave the swap there "just in case". Also, depending on how much RAM you add, you will either need the extra swap or you will need to turn "swapmem_on" on (set it to 1) so you can actually use all of the RAM in your system.
Steven E. Protter
Exalted Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

That depends on how much RAM you order.

If you add another 2 GB of ram, that will take you to 4GB.

You have two 4 GB swap areas totalling 8 GB.

That would leave you with a ram to swap ratio of 2.0 which would be reasonable.

You can make one swap area priority 1 and the second priority 2 so it doesn't get used so often.

A more optimal setup would be this:

RAM is now 4 GB

primary swap is 2 GB priority 1 or zero.
A secondary swap area of 4 GB set to priority 2.

This would lead to optimal peformance when hitting small amounts of swap.

If you are adding more than 2 GB of RAM than don't remove any sway area at all, just change the priority, maybe optimize as noted in my prior two paragraphs.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Thanks a ton for the advice!
Anand_31
Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Hi there,

I think you need to investigate this further before you add any RAM. It is good that you can add RAM but maybe the problem is elsewhere!

Why is so much swapping happening?!? Who is swapping?!? can you send me the init.ora/SPFILE file?

2GB is a decent amount of RAM to start off with. Oracle needs about 1.5MB per user connection it is just SQL Plus connections and about 5MB per user if it Forms,etc.

Maybe your init.ora is not configured properly and that's why all the memory is used up. For a start, just reduce the SHARED_POOL_SIZE parameter to about 96M or 128M. Most people bumo this up to very high values. You don't need to.

The DB_CACHE_SIZE can be big (but keep it below 1280MB). Start with 256MB for DB_CACHE_SIZE, even if your application is huuuugggge ..as the developers say!

let us know what happens after these changes to the init.ora. I am quite sure your swapping will vanish.

Regards
anand

Anand_31
Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Hi there,

I think you need to investigate this further before you add any RAM. It is good that you can add RAM but maybe the problem is elsewhere!

Why is so much swapping happening?!? Who is swapping?!? can you send me the init.ora/SPFILE file?

2GB is a decent amount of RAM to start off with. Oracle needs about 1.5MB per user connection it is just SQL Plus connections and about 5MB per user if it Forms,etc.

Maybe your init.ora is not configured properly and that's why all the memory is used up. For a start, just reduce the SHARED_POOL_SIZE parameter to about 96M or 128M. Most people bumo this up to very high values. You don't need to.

The DB_CACHE_SIZE can be big (but keep it below 1280MB). Start with 256MB for DB_CACHE_SIZE, even if your application is huuuugggge ..as the developers say!

let us know what happens after these changes to the init.ora. I am quite sure your swapping will vanish.

Regards
anand

Anand_31
Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Hi there,

I think you need to investigate this further before you add any RAM. It is good that you can add RAM but maybe the problem is elsewhere!

Why is so much swapping happening?!? Who is swapping?!? can you send me the init.ora/SPFILE file?

2GB is a decent amount of RAM to start off with. Oracle needs about 1.5MB per user connection it is just SQL Plus connections and about 5MB per user if it Forms,etc.

Maybe your init.ora is not configured properly and that's why all the memory is used up. For a start, just reduce the SHARED_POOL_SIZE parameter to about 96M or 128M. Most people bumo this up to very high values. You don't need to.

The DB_CACHE_SIZE can be big (but keep it below 1280MB). Start with 256MB for DB_CACHE_SIZE, even if your application is huuuugggge ..as the developers say!

let us know what happens after these changes to the init.ora. I am quite sure your swapping will vanish.

Regards
anand

Indira Aramandla
Honored Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

Hi,

In many cases the error ORA-12500 is caused due to lack of resources in the Unix Box, if you are enable to connect to database and randomly you get the error your operating system is reached the maximum values for some resources. Otherwise, if you get the error in first connection the problem may be in the configuration of the system.

Finding the resource which is been reached is difficult, indicates some suggestion to solve the problems. You can look at Shmmax,Semaem
Semaem,semmns

Oracle requires that you have in between two to four times the amount of physical ram that is allocated by default as swap space. In this particular situation, you will need to create auxilary swap space to free up additional ram.

Creating auxilary swap space frees up physical ram allowing the system additional resources for successful connection attempts above five clients.
Never give up, Keep Trying
Volker Borowski
Honored Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

Hi,

check your Oracle-Configuration in addition.
Esp. processes and sessions can limit the number of concurrent connections.

Do you run dedicated or multi-threaded configuration ???

In not security-relevant, please attach listener.ora and init?????.ora.

Volker
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Anand -
Here's our init.ora. I'm not the DBA, I'm the Sys Admin, so if there are any questions as to why he set it up this way, I will have to ask him. Hope this can give you some usable information. thanks
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Here's the listener.ora
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Here's the spfile.. Thanks to all of you.
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Indira -
I do have a question for you. If the HP guys are telling me that hitting the SWAP is bad, but you are basically telling me with Oracle, that's the way it should be. I'm slightly confused. Why is it bad on one hand, but not the other?
Steven E. Protter
Exalted Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

I would suggest that you use tnsping to validate the tnsnames.ora and listener.ora structure.

A subtle error in there might cause problenms.

You might want to consider relinking oracle after making changes to your system.

Lastly, are you up to date on HP patches. March 2003 or June 2003 is pretty stable and Oracle recommends staying current on this stuff.


I'm providing links to other threads based on this error, because frankly, I'm out of ideas:

http://search.hp.com/redirect.html?url=http%3A//forums.itrc.hp.com/cm/QuestionAnswer/1,,0x2a1b8ffa98a2d5118ff10090279cd0f9,00.html&qt=ORA-12500&hit=2

This one has some information posted by me on this issue:
http://search.hp.com/redirect.html?url=http%3A//forums.itrc.hp.com/cm/QuestionAnswer/1,,0xb61d5fe8b250d71190080090279cd0f9,00.html&qt=ORA-12500&hit=1

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Volker Borowski
Honored Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

Hi again,

I have some questions about the init.ora.
Is it still used, since you attached an spfile (which unfortunately my browser was not able to open or to download). May be a spooled output from "select * from V$PARAMETER" suits us more ?

If init.ora is used, could you please attach the included file "/u01/app/oracle/admin/PTWB/pfile/configPTWB.ora" as well (or the above mentioned output from the select statement will be ok).

Best bet still the sessions/processes parameters or may be 24M for PGA_AGGREGATE_TARGET is too low (try 250M) (recommendation for SAP i.e is 10%-20% of phys RAM, but it depends on the application.

Do you run Oracle AND the application (which one) on the same box ?

Volker
Surya Narayana
Occasional Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

I would strongly recommend reading up on the
'chatr' command. That changes the way
memory chunks are allocated.
Oracle Metalink has an article on that too.
This is particularly relevant for 11i/Oracle 9i.
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Here's the other file you requested. Thank you. I did find out that the information about the 32 bit application was wrong. OS, Oracle & the application are all 64 bit. Thanks again for your help..
Indira Aramandla
Honored Contributor

Re: Swap, memory, kernal config? - ORA-12500 error

Hi Belynda,

The reply that I gave was pertaining to ORA-1200. Yes I agree hitting SWAP is poor performance. Increasing swap is only a temporary option for the above error. I am sorry I was not clear enough.

Your server rp2470 is having only 2 GB RAM and your are using oracle 9i RDBMS. And you said by increasing your swap more users can be connected, which clearly says it is a memory issue.

Generally the error ORA-12500 may be an indication that the RAM amount may be reaching its limit. Or is caused as a result of lack of memory resources. In order to prevent one has to:

1. Increase the amount of memory available to the system either by removing unnecessary processes or increasing the amount of physical memory.

2. Increasing the amount of swap space will also temporarily help relieve the symptoms.

Swap space is used when the amount of physical memory (RAM) is full. If the system needs more memory resources and the physical memory is full, inactive pages in memory are moved to the swap space.

While swap space can help machines with a small amount of RAM, it should not be considered a replacement for more RAM. Swap space is located on hard drives, which have a slower access time than physical memory.

Oracle way :
When an Oracle instance is started, the OS is being asked to give some Key resources according to the parameters specified in init.ora. Oracle uses shared memory for efficiency. The OS does not have to load up the same address page(s) into the memory each time a process needs to reference it. Instead the process can just reference the one memory location, read/write to it, and then leave. The number of semaphores allocated is directly dependent on the PROCESSES parameter in the init.ora file. The total amount of semaphores required is derived from the 'processes' parameter inside that Oracle instance init.ora file
For optimal database performance, SGA should fit into real memory, and avoid Swapping.

UNIX way :
One of the most common problems when running large numbers of concurrent users on UNIX machines is lack of memory. A UNIX machine has virtual memory: the total addressable memory range. Virtual memory is composed of RAM, DISK and SWAP space. Generally, you will want to have the available SWAP space equal to 2 to 3 times the RAM. UNIX uses two memory management policies: swapping and paging. Swapping occurs when UNIX transfers an entire process from RAM to a SWAP device. This frees up a large amount of RAM. Paging occurs when UNIX only transfers a "PAGE" of memory to the SWAP device. Only a tiny portion of a process might actually be "paged out" to a SWAP device. While swapping frees up memory - it is slower than paging. Paging generally is more efficient but does not allow for large amounts of memory to be freed simultaneously.

When analysing your UNIX machine, make sure that the machine is not swapping at all and at worst paging lightly. This indicates a system with a healthy amount of memory available

I hope this clears your doubt.
Never give up, Keep Trying
TWBSupport
Regular Advisor

Re: Swap, memory, kernal config? - ORA-12500 error

Hi All!
I'm baaaaackkkk. :o) I upgraded our memory to the max available 8G. Our primary swap is at 4G & priority 1, the secondary swap is 4G & priority 0. No longer getting the user connection errors, but any time a large query is run in the database, CPU goes to 100% & slows the database down to a crawl. The other thing that was done was the large table spaces have been partitioned to separate disks to alleviate any disk i/o contengency, which helped... some... :o)

Here's our swapinfo:
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 4096 0 4096 0% 0 - 1 /dev/vg00/lvol2
localfs 3906 0 3906 0% none 0 0 /swap1/paging
reserve - 4906 -4906
memory 6307 4536 1771 72%
total 14309 9442 4867 66% - 0 -

Got anything else that I can check on the hardware side? I really would like to put this problem to bed, but everytime we fix one thing, another issue pops up. Thanks a ton!