loading
hello dummmy text
ORACLE DATABASE CONFIGURATION PARAMETERS

Installing Oracle Database is a very common activity to every DBA. In this process, DBA would try to configure all the pre-requisites that Oracle installation document will guide, respective to the version and OS architecture. In which the very common configuration on UNIX platforms is setting up LIMITS.CONF file from /etc/security directory. But why should we configure this? If we have not configured or wrongly configured this and try to install or run Oracle database software what will be the consequences?

If you have never thought of this, we are going to get this answered in this blog. We are going to understand the syntax and the purpose of this configuration and then verify its effects on running Oracle Database Software as we did in “Why are Kernel parameters critical for Oracle Database?” article in the past. These configurations will have direct impact on Oracle Database Performance, understanding these configurations will give you a clear picture to address any “Performance Tuning” issues going forward.

limits.conf:

I have taken the below example from the installation documentation of Oracle 12cr1.

oracle   soft   nofile    1024

oracle   hard   nofile    65536

oracle   soft   nproc    16384

oracle   hard   nproc    16384

oracle   soft   stack    10240

oracle   hard   stack    32768

There are 4 columns in this syntax, let us understand these columns wise first and then read it as a line.

Column 1: It specifies to which user on OS, the limits are applicable. As Oracle guides us to create user oracle on UNIX, it guides to use oracle in the first column. If you are using any different user to install Oracle software, then please do include that username. For example, include user grid for Oracle Grid installation.

Column 2: It accepts two options “soft” and “hard”. “soft” will be the maximum number that a user can set. “hard” tells the maximum number that a user(oracle) can re-configure. Confusing? We will pick an example later to understand the same.

Column 3: On what resource the limits are applied. In the configuration we see three resource names: “nofile”,”nproc”,”stack”(New in 12c). “nofile” specifies maximum number of file handlers that oracle user can have at any given point in time. “nproc” specifies maximum number of processes that oracle user can run on the server. “stack” specifies maximum stack size in KB for each thread that oracle user process creates on the server.

Column 4: Specifies the maximum number associated to its resource parameters explained in column 3 above.

Why to configure limits.conf for #Oralce #Database? #Orskl Click To Tweet

Let us read the lines now.

Line 1:

oracle   soft   nofile    1024

Specifies the maximum number of 1024 file handlers are allowed to the user oracle on the server.

Line 2:

oracle   hard   nofile    65536

If oracle user reaches the bottleneck of 1024 as specified in line 1 and would wish to extend his limit, he can resize it to maximum of 65536. Beyond which he will need root access to extend. Which means that you can extend you soft limit as oracle user only until 65536 without root login.

Line 3:

oracle   soft   nproc    16384

Specifies the maximum number of 16384 processes are allowed to run by the user oracle on the server.

Line 4:

oracle   hard   nproc    16384

As hard limit value and soft limit value for resource nproc is same, user oracle cannot extend this value beyond 16384 through his login. Resizing this will need root login.

Line 5:

oracle   soft   stack    10240

This specifies the maximum stack size of 10MB to each process thread that oracle user handles.

Line 6:

oracle   hard   stack    32768

Specifies that if user oracle wants to extend the size of this resource, he can resize it to maximum of 32 MB.

Well, hope it is fine so far in understanding what these configurations mean to Oracle Database software installation.

Case study:

Let us test the same by changing one of the resources “nproc” on Oracle 12c Database running on Oracle Enterprise Linux.

1. Connected to the server as oracle and checked my resource limits.

cat /etc/security/limits.conf

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

oracle soft stack 10240

oracle hard stack 32768

So, the nproc soft limit is 2047 and hard limit is 16384

2. Started oracle database to check how many maximum processes it is creating to bring up the instance.

. oraenv

[oracle@ORASRV1 ~]$ . oraenv

ORACLE_SID = [oracle] ? ORADB1

The Oracle base has been set to /oracle/app/oracle

[oracle@ORASRV1 ~]$ sqlplus / as sysdba

SQL> startup;

ORACLE instance started.

 

Total System Global Area  834666496 bytes

Fixed Size                  2929888 bytes

Variable Size             599788320 bytes

Database Buffers          226492416 bytes

Redo Buffers                5455872 bytes

Database mounted.

Database opened.

SQL> exit

3. The number of process in my case found to be 61(It includes database software processes and shell processes as well).

[oracle@ORASRV1 ~]$ ps -ef | grep oracle | wc -l

61

4. Brought down Oracle database instance now.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>exit

5. Now changed soft limit of nproc for oracle user to just 30.

vi /etc/security/limits.conf

oracle soft nproc 30

…

6. Restarted server – Not mandatory, we can even reconnect in another putty session to apply changes.
7. Now connected as oracle user and started the database.

[oracle@ORASRV1 ~]$ sqlplus / as sysdba

SQL> startup;

ORACLE instance started.

Total System Global Area  834666496 bytes

Fixed Size                  2929888 bytes

Variable Size             599788320 bytes

Database Buffers          226492416 bytes

Redo Buffers                5455872 bytes

Database mounted.

Database opened.

SQL> exit

8. Do you think everything is working fine? NO, check that your shell is now broken. You will not be able to run anything in your terminal.

[oracle@ORASRV1 ~]$ ps -ef | grep oracle | wc -l

-bash: fork: retry: No child processes

-bash: fork: retry: No child processes

-bash: fork: retry: No child processes

-bash: fork: retry: No child processes

-bash: fork: Resource temporarily unavailable

9. Re-check the same by connecting a new putty session as different user. In my case I logged in as “root” user.

[root@ORASRV1 ~]# ps -ef | grep oracle | wc -l

29

10. There are only 29 processes that Linux kernel allowed to oracle user to create as we have set soft limit to 30. This has intense effect on the software. It will not even allow you to establish a new terminal on putty as the maximum process count reached.

a

Affects of bad configuration of limits.conf for #Oralce #Database? #Orskl Click To Tweet

Take-aways:

  • limits.conf is very important configuration which directly impacts the software behavior on the OS.
  • You should consider this configuration while dealing with any “Performance Tuning” issues on the server level.
  • If you are increasing “processes” parameter in the Oracle Database parameter file, please do check if this limit (‘nproc’ soft limit) can be handled by server.
  • The same will be applicable to other resource limitations you configure.
  • You can increase the nproc limit to higher number less than hard limit value as oracle user and everything will work fine.
  • As an exercise, you can try and test for other resource limitations we configured.

I love to see your comments!

Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.

16 Comment

  1. nabil azougagh

    tanks a lot, very useful article

    1. Pawan Kumar Yaddanapudi

      Cheers 🙂

  2. Samrat

    How is OS level nproc related to DB init parameter processes and sessions? sessions is roughly 1.5-2 times processes if I am not wrong. Would like to know the relation between nproc and the init parameter. If I want to install 5 Oracle databases on a server then before installing them, if I run the now available 12c/11g pre-install rpm, it will set the ulimits to a certain value as per Oracle’s standards. Is there a way to forsee how much I might need when I install more databases? Ideally, I suppose Oracle comes up with a big enough number. However, just wanted to se sure if there is a way to foresee it.

    1. Pawan Kumar Yaddanapudi

      Here you go. For every database session, there is a server process created on the server, isn’t it? So sessions/processes parameter is directly dependent on nproc. Sadly, there is no option to forecast the maximum number that you would need on the server. DBA’s intervention is much needed in this regards.

  3. SHARIF CHOWDHURY

    Thanks, Very nice article. Please keep it up

    1. Pawan Kumar Yaddanapudi

      Thank you !!!

  4. Samir Pophalkar

    As you have mentioned “These configurations will have direct impact on Oracle Database Performance, understanding these configurations will give you a clear picture to address any “Performance Tuning” issues going forward.”
    Can you please give any example/case in which performance of query/overall database has improved after changing default setting given in oracle documentation.
    If we don’t configure these setting in /etc/security/limits.conf, what are default values in unix/linux?

    1. Pawan Kumar Yaddanapudi

      Nice point infact. Thank you.
      One of the examples that i could think about at the moment related to this blog is “stack” where the max size per thread is 10MB, if a server process tries to access block size of more than 10MB size, then you would see performance issue in the database. You can relate the same to the block size in the database.
      If we do not configure any resource limits, then no user on the server have any limitations. You will be free to use any amount of resources.

  5. ORSKL Follower - Vizag

    Awesome post! Looking for more posts 🙂

  6. Md Shamim

    Very nice post.
    I have confusion about file handler. Could you explain it with one example.

    1. Pawan Kumar Yaddanapudi

      Here you go !!! When a process starts running on the server, it might try accessing files on the disk. Can be once or many times based on the program that process runs. The resource limit says the maximum number of file handles that ORACLE user on the OS can have at any given point of time. Irrespective of no of processes.

      Hope this helped you.

  7. Shravan

    It was very nice and use full information.
    Except new more use information excepting from you. Hope we will get it.

  8. How Oracle database does instance recovery after failures? |

  9. Tamil

    Hi Pavan,

    Thank you so much for your blog,

    I have one question about the below hard and soft.
    oracle soft nproc 16384
    oracle hard nproc 16384
    I have seen your video, after the changing the soft value to 30, why can’t it is altered the maximum value of hard process.

    1. Pawan Kumar Yaddanapudi

      Can you please elaborate your query?

  10. Nucadmin

    Hi Pawan,
    Wishing you a great day.
    Your article emphasis on why need to configure limit.conf for Oracle database. But gives a vague information on what is the purpose of limit.conf file, it would actually add more value to this article if you add those information.
    “limits.conf is very important configuration which directly impacts the software behavior on the OS.” -> which is not correct it is actually the configuration file for ulimits (user limits). This is the file which tells or defines the limit of resources for an user in an Operating System.
    The configuration files varies in OL5,6 and 7. Since OL6 onwards configuration file is changed to /etc/security/limits.d/-oracle.conf and also has direct relationship to PAM modules. User limits also can be set within shell profile of an user. Oracle universal installer verifies resource parameters from environment variables in shell during installation or checks the configuration for ulimits of Oracle user.

    Since 2012 Oracle no longer calls it’s Linux OS as Oracle Enterprise Linux it’s just Oracle Linux.

    I hope these info add value to your articles.
    Regards
    Nucadmin,
    http://www.nuccloudlabs.com

Write a Reply or Comment

Your email address will not be published.

Knowledge That Can Always Keep Your Inbox Informed