Why should we configure limits.conf for Oracle database?

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.

Related Posts

About The Author

11 Comments

  1. nabil.azougagh@gmail.com'
    nabil azougagh
    June 20, 2017
    • Pawan Kumar Yaddanapudi
      June 21, 2017
  2. samrat.banik@fmr.com'
    Samrat
    June 20, 2017
    • Pawan Kumar Yaddanapudi
      June 21, 2017
  3. sharif.dba@gmail.com'
    SHARIF CHOWDHURY
    June 21, 2017
    • Pawan Kumar Yaddanapudi
      June 21, 2017
  4. samirpophalkar@gmail.com'
    Samir Pophalkar
    June 21, 2017
    • Pawan Kumar Yaddanapudi
      June 21, 2017
  5. vnairaj@gmail.com'
    ORSKL Follower - Vizag
    June 21, 2017
  6. shamimjsr7@gmail.com'
    Md Shamim
    June 24, 2017
    • Pawan Kumar Yaddanapudi
      June 24, 2017

Add Comment