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.
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.
