The title of te review says it all. I've looked everywhere for explanations for these settings...

Hi,tom:

You are doing a really good topic for the kernel params around Oracle.But one point i cannot understand.it's this:

What's mean "semaphore" concept? It has any relation between "Shared memory"? It's in ORACLE level or OS level?
Thanks for help.

(a search for "what is a semaphore set" on google) tells me:

I'm sorry I was not clear and made you nervous.
I just wanted to know your opinion about Mr Sing setting
and your way to calculate it.
I appreciate your job.

Mr. Tom,
we are using hp-ux 11,
i can't see the SEMMSL parameter, when i type the following
command as root.

root:/>kmtune | grep sem

it shows

sema
semaem
semmap
semmni
semmns
semmnu
semume
semvmx

what's wrong here.
what could be the equivalent parameter for SEMMSL.

please help.
thanks in advance.

thanks for the help.

Tom,
I am not clear on one thing after reading all the documentation.
If I am running multiple Oracle databases on the same server, then does SHMMAX has to be greater than the sum of all SGAs or do I have to ensure that each SGA is smaller than SHMMAX.

Thanks

Hi Tom,

I have a few questions to ask. (902 on Solaris 8).

(1) You mentioned that these two parameters' values should be good enough. Is it still valid for 902?
shmmni=100
shmseg=10

(2) You didn't mention that how to set SEMMNI, so we still use SEMMNI=100 no matter how many instances and how many processes of each instance we use?

(3) What is the right syntax to set these parameters? I have the sample here, but I am not sure it is right or not. I know it is hard for you to check the syntax. Would you please just give us part of /etc/system setting from your system!

------------------------------------------
set shmsys:shminfo_shmmax=4294967295
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
set semsys:seminfo_semmni=100
set semsys:seminfo_semmsl=256
set semsys:seminfo_semmns=1024
------------------------------------------

Thanks so much for your help.

Hi Tom,

For these settings
SHMMAX 4294967295
SHMMIN 1

You said “They are sufficient for pretty much all systems.”

Then you said “shmmax needs to be set for ALL SGAs, cumulative.”

So, my question is, if my server has 8 GB of RAM and I am running 3 DB’s with a total of 7GB for all SGAs, do I need to set SHMMAX to 7516192768?

Also, what would 'a single shared memory segment' be for an Oracle server? Would each SGA be the answer?

Please let me know your comments.
Thanks,

Orlando

Tom,

One more question if you don’t mind, I’ve seen you always state that one server should run only one DB, I don’t really disagree with that statement, but if we have a lot of power/resources on the server what are some of the major reasons against having multiple production databases running on the same server, besides the business risks/polices.

On the up side, we see big savings on Oracle licenses :-), even though we buy more expensive servers.

Thanks and have a great day.

Orlando Reyes

KERNEL parameters have been set using
set shmsys:shminfo_shmmax=4294967295 on the unix box [sco openserver 5.0.5], and restarted the server, still the parameters remain the same.
Any valid reason for this, and how to set these values ?

Hi Tom,

i have one doubt, why this Oracle architecture is adjusting this kernel parameters, whats teh reason behind this.

Hi Tom,

SHMMAX parameter is obsolete in solaris 10.
We would like to use more than 1.75 GB of sga. We followed metalink note and relocated sgabeg. Now when we try to start with 3GB of SGA it is failing. A TRUSS output of session shows it fails on "shmget()" call.
Since the SHMMAX is depreciated we could not set it.
ALso we are not able to find a way to current value that is used by system for SHMMAX(sysdef is not giving any value).

I have found the same question posted in may SUN forum and Oracle forums , but no where a correct answer is given.

Your help is highly appreciated

Hello Tom,

OS: SLES 9

I am installing Oracle AS Infrastructure 10.1.2 (Rel 2) x86 on SuSE Linux Enetrprise server 9.

I have installed SLES 9 from the scratch and fulfil all requirements include in Oracle® Application Server Installation Guide.10g Release 2 (10.1.2) for Linux x86 B19310-01.

when I starts install Oracle AS 10.1.2 Rel 2.


I got error in Checking security kernel parameters as below:

checking for hardnofiles = 65536 found hardnofiles = 1024 Failed
checking for softnofiles = 4096; softnofiles = 1024

Problem:

The security parameters in the kernel do not meet the minimum requirements.

Recommendation: Perform OS System specific instruction to update kernel parameter, namely BIT_SIZE and NON_EXEC_USER_STACK.


Regards,
Dawar

Yes, I used Oracle® Application Server Installation Guide
10g Release 2 (10.1.2) for Linux x86 B19310-01 guide.

Regards,
Dawar

Yes, they are correct.


Please note: Here is the last part of the error message.

But installer guide or any related documents has no detail about these parameters. " BIT_SIZE and noexec_user_stack"

Recommendation: Perform operating system specific instructions to update the kernel parameters, namely BIT_SIZE and noexec_user_stack.

Regards,
Dawar

Tom,

oracle@abc:/etc> ulimit -Hn
65536

oracle@abc:/etc>ulimit -Sn
65536


I have added hard nofile & soft nofile in the end of /etc/security/limits.conf file per Oracle® Application Server Installation Guide 10g Release 2 (10.1.2) for Linux x86 B19310-01 . Ref: Sections 4.54 & 4.4.1.
Please see below:


oracle@abc:/etc/security> more limits.conf
# /etc/security/limits.conf
#
#Each line describes a limit for a user in the form:
#
#<domain> <type> <item> <value>
#
#Where:
#<domain> can be:
# - an user name
# - a group name, with @group syntax
# - the wildcard *, for default entry
# - the wildcard %, can be also used with %group syntax,
# for maxlogin limit
#
#<type> can have the two values:
# - "soft" for enforcing the soft limits
# - "hard" for enforcing hard limits
#
#<item> can be one of the following:
# - core - limits the core file size (KB)
# - data - max data size (KB)
# - fsize - maximum filesize (KB)
# - memlock - max locked-in-memory address space (KB)
# - nofile - max number of open files
# - rss - max resident set size (KB)
# - stack - max stack size (KB)
# - cpu - max CPU time (MIN)
# - nproc - max number of processes
# - as - address space limit
# - maxlogins - max number of logins for this user
# - priority - the priority to run user process with
# - locks - max number of file locks the user can hold
#
#<domain> <type> <item> <value>
#

#* soft core 0
#* hard rss 10000
#@student hard nproc 20
#@faculty soft nproc 20
#@faculty hard nproc 50
#ftp hard nproc 0
#@student - maxlogins 4
* hard nofile 65536
* soft nproc 2047
* hard nproc 16384
* soft nofile 2048
* hard nofile 65536
# End of file

***********************************************************For your convenince, from the guide see below

4.5 Set Shell Limits for the oracle User
To improve the performance of the software on Linux systems, you must increase the following shell limits for the oracle user, depending on the user's default shell:

Bourne or Bash Shell Limit Korn Shell Limit C or tcsh Shell Limit Hard Limit
nofile nofile descriptors 65536
noproc processes maxproc 16384


To increase the shell limits:

Add the following lines to /etc/security/limits.conf file:

* soft nproc 2047
* hard nproc 16384
* soft nofile 2048
* hard nofile 65536

Add the following line to the /etc/pam.d/login file, if it does not already exist:

session required /lib/security/pam_limits.so

Depending on the oracle user's default shell, make the following changes to the default shell start-up file:

For the Bourne, Bash, or Korn shell, add the following lines to the /etc/profile file:

if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

For the C or tcsh shell, add the following lines to the /etc/csh.login file:

if ( $USER == "oracle" ) then
limit maxproc 16384
limit descriptors 65536
endif

Regards,
Dawar

Yes, I have restrated.

Tom,

OS is Linux (SuSE Linux Enterprise Server 9).
Not a Sun Solaris.

you said:

do you have to touch /etc/system or something like
that to get the rebuild to work?


No

Regards,
Dawar

Tom,

Problem has been solved.


Ref: Article-ID: Note 300819.1 (metalink)

Cause
*****
The mechanism used to set the ulimit depends on PAM limits being used, but the required module(pam_limits.so) is not loaded by default for various applications (ssh, gdm, kde, xdm,su ). The OracleAS 10g installation guide only explains how to configure the "login" mechanism.

Solution
********
In addition to setting the hard and soft values in /etc/security/limits.conf, add the line "session required pam_limits.so" in file used for your session type. Change of the following files may be required

/etc/pam.d/login
/etc/pam.d/su
/etc/pam.d/sshd
/etc/pam.d/xdm
/etc/pam.d/gdm
/etc/pam.d/kde

Some linux installations are using pam system_auth service. Limits for most applications are set changing

/etc/pam.d/system_auth

Regards,
Dawar

Hi Tom,

What is your opinion about the following architecture:

1 Solaris server serving multiple oracle databases.

Each database will have separate Unix user account, separate mount points (2 points each).

Each databse will have a separate ORACLE_HOME running probably same version of Oracle or may be different patch levels or different versions.

I know your recommendation is to have a dedicated server for each database. But since this is a test server we want to be flexible enough to
- failover each database separatelly
- upgrade each database separately etc.

What are the adverse impacts of having this kind of architecture on server resources availability.

Thanks.

Hi Tom,
I am kinda new to Oracle on Solaris and Unix.
Just wantes to know how to find out if there are multiple SIDs and Oracle Homes on a server, say Solaris.
As I heard, the .profile can have only one ORACLE_SID set.
Therefore if there are more than one Oracle Homes and Instances on a server, how to identify them.
Please advice.
Thanks as always

Tom,

You mentioned in one of your responses above :

***
if your server can support N databases each running "an application", then it
could support N+M applications in a single instance on that same server -- M is
a number greater than zero.
***

Can you please elaborate some possible reasons behind this?
Thanks,
Naresh

Tom,

You mentioned the formula in the first response:

ORACLE_SID=A, PROCESSES=100
ORACLE_SID=B, PROCESSES=100
ORACLE_SID=C, PROCESSES=200


The value of SEMMNS is calculated as follows:

SEMMNS = ((A=100) + (B=100)) + ((C=200) * 2) +
((# of instances=3) * 10) = 630


Is it possible to know why this is calculated thus?

Thanks.
Naresh.

Thanks for all the info on the kernel parameters

Tom,

Is it possible that 'low' kernel parameter values which exist on the server when a database is created will permanently 'cripple' that database, even after these 'low' values have been adjusted?

In other words do kernel parameters affect the *creation* of a database in any way?

Thanks,

Robert.

Hi Tom,
Regarding:-
"and from time to time - cpu count can change as cpu's are
added/removed"
In a dynamic CPU swapping env., how cpu count is known to oracle without bouncing the instance?
Regards

Tom,

You explained earlier in this thread that SHMMAX is a cumulative sum of all SGA's on a particular machine. Oracle support (SR# and portion of text below) is explaining that SHMMAX is related to any particular instance running on a machine (not cumulative). From what I understand of the explanation below, we would now have 12gb shared memory area total, which exceeds the 2gb defined to ths OS via SHMMAX. Could you clear up the discrepancy? We are running 10.1.0.4 on Linux ES/3.

Thanks,
Brian
----------------------
SR Number 5207904.992

Explained that SHMMAX is an upper kernel limit defined for what is the maximum single shared memory segment we can allocate upon instance startup.

E.g.
Lets say we set SHMMAX to 2gb.

Any instance on this machine can have its SGA size up to 2gb in size this is the limit & its per instance.

So if you have 6 instances all of 2gb SGA's then you'll have 6 shared memory segments each of 2gb in size so a total usage of 12gb of Memory this does NOT include your PGA as this is seperate private memory not attached to the shared memory segment.

Hi Tom,

If i set the SHMMAX as half of RAM say for example 8GB. Is this 8GB of RAM is used only for Oracle or it can be usable for other application also..

Thanks in advance.
Sachin

Operating system is Solaris 5.9
Oracle is 32-bit and version is 9.2.0.5
sga_max_size is 2.6GB
shmmax is set to 1.7GB
db_cache_size is 1.6GB
Shared pool is 800MB

I am having performance problems in Oracle.

I am thinking of raising shmmax to 4GB so that I can increase db_cache_size to 2.5GB.

[1] Will this increase the performance.
[2] Is increase in shmmax size is directly propotional to db_cache_size in above configuration.

Hi Tom,

We are facing Oracle database performance problem on HPUX(64-bit) server. We have very good server configuration with 16 processor 32GB RAM, however it's giving very worst performance while our QA database running with 4 processor server is giving good performance.

I think there is some problem with Kernel parameters setting on the production server. Let me point out my questions:

i.) In every database startup, in alert log file we get following warning.

-------------------------------------------------------------
WARNING:Oracle instance running on a system with low open file descriptor limit. Tune your system to increase this limit to avoid
severe performance degradation.
----------------------------------------------------------------
on our database db_files set to 1024.

and please find below kernel parameter setting on production HPUX box:

* Tunable parameters

STRMSGSZ 65535
dbc_max_pct 5
dnlc_hash_locks 512
enable_idds 1
ksi_alloc_max 80000
max_thread_proc 256
maxdsiz 4096000000
maxdsiz_64bit 4096000000
maxfiles 1024
maxfiles_lim 2048
maxssiz 401604608
maxssiz_64bit 1073741824
maxswapchunks 16384
maxtsiz 1073741824
maxtsiz_64bit 0X40000000
maxuprc 9995
maxusers 1564
maxvgs 30
msgmni 10000
msgseg 32767
msgtql 10000
ncsize 87168
nfile 152048
nflocks 10000
ninode 82048
nproc 10000
npty 120
nstrpty 60
semmap 1332
semmni 1330
semmns 16384
semmnu 9996
semmsl 4096
semvmx 32768
shmmax 0x8000000
shmmni 512
shmseg 320
swchunk 4096
vps_ceiling 64


I do understand, I can solve this problem by reducing db_files to less than 512 as per formula ( open files=2*db_files+82), but I guess that will solve my error, but as per my understanding if OS is not tuned properly, it might give problem in other way, hence would like to solve this by changing kernel parameter. do you know which kernel parameter do i need to change?( I suspect I need to change maxfiles(curently set to 1024, but not sure).


ii) Query-2: On the same HPUX box, SHMMAX is set to 0x8000000, I am not sure how much memory size it represent. my doubt is, it's not properly set as we are having 32GB RAM and SGA size of the database is 5GB. Could you please let me know "0x8000000" represent how much GB and what is the optimal value for SHMMAX should be for above environment?

Your answer will be highly appreciated.

Thanks & Regards.
Balvant Chhasiya.

Dear Tom,

Thanks for your reply.

For my previous question, I got the reply from metalink for the SR raised. Please find below their feedback:
================================================
RDA OUTPUT
--------------------
Database parameters
-------------------------------
processes 1000

--> this allows only about 1 file descriptor per Oracle process on average (ignoring any other process on the
box).


RESEARCH
========
WebIV Search

Bug 5372330 - Abstract: NUMBER OF FILE DESCRIPTOR INCREASING IN 5 NODE RAC
Status: 92,Closed, Not a Bug

Development explains :

"Each Oracle process (connection) will use about 12 file descriptors from miscellaneous items (eg: stdin/stdout/reserved descriptors for trace + alert,
net connection to/from the process etc..) and then additional descriptors for a
ny datafiles opened, external tables opened, and things like db links, utl_http
etc.. etc.. The main item is usually for datafiles."


CAUSE DETERMINATION
===================
Low value for nofiles(descriptors) for OS 'oracle' user.

CAUSE JUSTIFICATION
===================
The 'processes' parameter is set to 1000, but the nofiles(descriptors) limit for 'oracle' user is only 1024.

PROPOSED SOLUTION(S)
====================
Increase nofiles(descriptors) limit for 'oracle' user to at least 12288 (the best would be 65536).

PROPOSED SOLUTION JUSTIFICATION(S)
==================================
The 'processes' parameter is set to 1000, but the nofiles(descriptors) limit for 'oracle' user is only 1024.
As explained by Development in Bug 5372330 - Abstract: NUMBER OF FILE DESCRIPTOR INCREASING IN 5
NODE RAC (closed as 'Not a Bug') :

"Each Oracle process (connection) will use about 12 file descriptors from miscellaneous items (eg: stdin/stdout/reserved de
scriptors for trace + alert, net connection to/from the process etc..) and then
additional descriptors for any datafiles opened, external tables opened, and thi
ngs like db links, utl_http etc.. etc.. The main item is usually for datafiles."

=============================================

As I mentioned in my previous question, I would like to discuss with you about the query which is giving vary wiered result on my QA(output in 50min) & Prod(output in 5 hours) environment, I tried to post a query on a "SQL Tuning" thread, but I got the message saying "New query will be deleted", could you please let me know in which thread should I post the query.

Appreciate your feedback.

Tom,

Why do we have to set Kernel Parameters for Solaris, HP, Linux servers but not for Windows ??
Why is it necessary for Unix Servers only?
If we are not going with the necessary settings on Windows Servers, then how is it being able to manage itself ??

Hi Tom,

First of all, thanks for your answers in understanding different kernel parameters and their significance.

Let us say, I have my SHMMAX parameter set to 4 GB, then will oracle allow me to set my SGA to be at 7 GB?

If yes, will the SGA be allocated in two different chunks of shared memory? Will it have any performance impact on the database.

Please explain other disadvantages, if there any..

Regards
Naresh

Hi Tom,
Appreciate your responses. I do red al above queries & solutions.
But fail to understand keenly on
1.Why we need to set manually on lunux and not in windows? - if we want to use better performance in windows, should we add anywhere
2.Better documentation / links / books on setting kernel parameter for different OS and with diff. RAM sizes and with multiple dbs
3. I am using 4GB RAM. Pl suggest kernel parameters in linux system with good performance with justifications and how it will impact on SGA
4. examples or comparison of kernel parameters..since flow is missing and have confusion

I am new to this link...sending mail is also appreciatable

Thanks in advance

Hi

Tom this srinivasa,reading your valuables replays since from many years thanks for that.


thanks & regards

J.srinivasa

Hi Tom,
Your informations on the kernel parameters are very clear.
But if these kernel para are not set can i know what kind of errors we will get? or
If we miss any 1 of the kernel para settings whats the error do we get?

Hi ,
Sir i just wanted to ask you is that 10 value fix i didn't get that .........

Thanks

Tom,

I understand that the allocation of shared memory segments goes something like this in order of priority:

1. Allocate a single segment for the entire SGA whenever possible.
2. Allocate contiguous segments when SGA_TARGET > SHMMAX.
3. Allocate non-contiguous segments as a last resort.

Can you tell me whether there is anything internal to Oracle that makes a single segment significantly more desireable than contiguous segments? What is it about contiguous segments that is suboptimal?

Thanks,
Russ

Hai tom;

It was really useful to me. I am using Host/Guest operating System. Allocated ram size was 1GB. Shmmax showing 2gb. sga_target=800m

what's the correlation assigning SHMMAX ?
is it depend sga_target or half physical of RAM ?

Hai tom;

It was really useful to me. I am using Host/Guest operating System. Allocated ram size was 1GB. Shmmax showing 2gb. sga_target=800m

what's the correlation assigning SHMMAX ?
is it depend sga_target or half physical of RAM ?

Hello.
I've found this article very helpful but I'm still not 100% sure on how many semaphores I'll need to host a database.

Please see this message if you want to know more about my query.
( https://community.oracle.com/message/11325503 )

Thanks,
Alex.

if server has 10GB of RAM and i supposed to use 8GB for Oracle db
-How will i set the kernel parameter?
plz help
thanks in advance.