Shell limits, Oracle Single Instance, and systemd in Oracle Linux 8.4

The more blog posts I write the harder it becomes to come up with a good title. So what is this post all about? In short, it’s about the implications of using systemd to start a single instance Oracle database in Oracle Linux and associated shell limits. In theory this should apply to Single Instance Oracle only – Oracle Restart/Real Application Clusters use different means to control the start of databases via their respective registries.

A short introduction to systemd

As of Oracle Linux 7 systemd replaced upstart in the same way upstart replaced SysV init earlier. systemd is a system and service manager for Linux Operating systems and does a lot more than either of its predecessors. It’s also extremely well documented in man pages.

An Oracle administrator might use systemd to start a database automatically when a system boots up. This is done via a so-called unit file, containing the necessary instructions to start the Oracle database.

Which leads me back to the topic of this post. You can read at oracle-base.com and a few other places that systemd does not respect shell limits set by pam_limits(8). Which is considered a feature; feel free to check the bugzilla discussion for more details. Remember that pam_limits(8) and its associated limits.conf file is used to configure shell limits for the oracle user, as per the Database Installation Guide.

To demonstrate the effect I’m going to use my most recent Oracle Linux 8.4 lab VM. The oracle account has been created and configured via the 19c preinstall RPM. I’m using my Vagrant box, updated to Oracle Linux 8.4 including all patches up to June 14th.

NOTE: the settings you’ll see later in the unit files are for Oracle Linux 8.4. Although Oracle Linux 7 uses systemd as well it might not support the same directives and/or syntax.

Let’s have a look at systemd unit files.

Creating a oneshot systemd unit file

I picked /etc/systemd/system as the location for my new unit file. At least in Oracle Linux 8.4 that seems to be the preferred location for custom unit files.

WARNING: This is totally lab VM/playground territory, you should never do this in a real (live) environment!

If you find the examples confusing because you aren’t familiar with systemd please head over to the documentation for more information.

# cat /etc/systemd/system/limitstest.service 
[Unit]
Description=A oneshot service to test whether systemd respects PAM limits

[Service]
User=oracle
Group=oinstall
Type=oneshot
ExecStart="/home/oracle/test.sh"

This unit file merely fires off /home/oracle/test.sh (once) as oracle:oinstall. The little shell script in /home/oracle/test.sh couldn’t be simpler:

$ cat /home/oracle/test.sh 
#!/usr/bin/env bash

echo "-----------------------------------------------------------"
echo "test run starts at $(/usr/bin/date):"
echo "I am $(/usr/bin/whoami)"

echo ""
echo "my own PID is: $$"
echo ""
/usr/bin/ps -ef| /usr/bin/grep $$ | /usr/bin/grep -v grep

echo ""
echo "hard limits according to ulimit:"
echo ""
ulimit -Ha

echo ""
echo "soft limits according to ulimit:"
echo ""
ulimit -Sa

echo ""
echo "actual limits as per /proc":
echo ""
/usr/bin/cat /proc/$$/limits

echo -----------------------------------------------------------

It prints a few PIDs and shell limits (both hard and soft limits). Finally, it looks into the /proc file system to get the shell limits of itself (${$}).

Unless you restart your (virtual) server, systemd won’t know about the new unit file. Alternatively you can run systemctl daemon-reload to reload the daemon.

Running the unit file

So let’s go ahead and see what the output of the unit file is. I started it using systemctl start limitstest.service.

# journalctl -u limitstest.service

...

Jun 15 19:44:07 server1 systemd[1]: Starting A oneshot service to test whether systemd respects PAM limits...
Jun 15 19:44:07 server1 test.sh[13046]: -----------------------------------------------------------
Jun 15 19:44:07 server1 test.sh[13046]: test run starts at Tue Jun 15 19:44:07 UTC 2021:
Jun 15 19:44:07 server1 test.sh[13046]: I am oracle
Jun 15 19:44:07 server1 test.sh[13046]: my own PID is: 13046
Jun 15 19:44:07 server1 test.sh[13046]: oracle     13046       1  0 19:44 ?        00:00:00 bash /home/oracle/test.sh
Jun 15 19:44:07 server1 test.sh[13046]: oracle     13049   13046  0 19:44 ?        00:00:00 /usr/bin/ps -ef
Jun 15 19:44:07 server1 test.sh[13046]: hard limits according to ulimit:
Jun 15 19:44:07 server1 test.sh[13046]: core file size          (blocks, -c) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: data seg size           (kbytes, -d) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: scheduling priority             (-e) 0
Jun 15 19:44:07 server1 test.sh[13046]: file size               (blocks, -f) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: pending signals                 (-i) 30554
Jun 15 19:44:07 server1 test.sh[13046]: max locked memory       (kbytes, -l) 64
Jun 15 19:44:07 server1 test.sh[13046]: max memory size         (kbytes, -m) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: open files                      (-n) 262144
Jun 15 19:44:07 server1 test.sh[13046]: pipe size            (512 bytes, -p) 8
Jun 15 19:44:07 server1 test.sh[13046]: POSIX message queues     (bytes, -q) 819200
Jun 15 19:44:07 server1 test.sh[13046]: real-time priority              (-r) 0
Jun 15 19:44:07 server1 test.sh[13046]: stack size              (kbytes, -s) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: cpu time               (seconds, -t) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: max user processes              (-u) 30554
Jun 15 19:44:07 server1 test.sh[13046]: virtual memory          (kbytes, -v) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: file locks                      (-x) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: soft limits according to ulimit:
Jun 15 19:44:07 server1 test.sh[13046]: core file size          (blocks, -c) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: data seg size           (kbytes, -d) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: scheduling priority             (-e) 0
Jun 15 19:44:07 server1 test.sh[13046]: file size               (blocks, -f) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: pending signals                 (-i) 30554
Jun 15 19:44:07 server1 test.sh[13046]: max locked memory       (kbytes, -l) 64
Jun 15 19:44:07 server1 test.sh[13046]: max memory size         (kbytes, -m) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: open files                      (-n) 1024
Jun 15 19:44:07 server1 test.sh[13046]: pipe size            (512 bytes, -p) 8
Jun 15 19:44:07 server1 test.sh[13046]: POSIX message queues     (bytes, -q) 819200
Jun 15 19:44:07 server1 test.sh[13046]: real-time priority              (-r) 0
Jun 15 19:44:07 server1 test.sh[13046]: stack size              (kbytes, -s) 8192
Jun 15 19:44:07 server1 test.sh[13046]: cpu time               (seconds, -t) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: max user processes              (-u) 30554
Jun 15 19:44:07 server1 test.sh[13046]: virtual memory          (kbytes, -v) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: file locks                      (-x) unlimited
Jun 15 19:44:07 server1 test.sh[13046]: actual limits as per /proc:
Jun 15 19:44:07 server1 test.sh[13046]: Limit                     Soft Limit           Hard Limit           Units
Jun 15 19:44:07 server1 test.sh[13046]: Max cpu time              unlimited            unlimited            seconds
Jun 15 19:44:07 server1 test.sh[13046]: Max file size             unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max data size             unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max stack size            8388608              unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max core file size        unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max resident set          unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max processes             30554                30554                processes
Jun 15 19:44:07 server1 test.sh[13046]: Max open files            1024                 262144               files
Jun 15 19:44:07 server1 test.sh[13046]: Max locked memory         65536                65536                bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max address space         unlimited            unlimited            bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max file locks            unlimited            unlimited            locks
Jun 15 19:44:07 server1 test.sh[13046]: Max pending signals       30554                30554                signals
Jun 15 19:44:07 server1 test.sh[13046]: Max msgqueue size         819200               819200               bytes
Jun 15 19:44:07 server1 test.sh[13046]: Max nice priority         0                    0
Jun 15 19:44:07 server1 test.sh[13046]: Max realtime priority     0                    0
Jun 15 19:44:07 server1 test.sh[13046]: Max realtime timeout      unlimited            unlimited            us
Jun 15 19:44:07 server1 test.sh[13046]: -----------------------------------------------------------
Jun 15 19:44:07 server1 systemd[1]: limitstest.service: Succeeded.
Jun 15 19:44:07 server1 systemd[1]: Started A oneshot service to test whether systemd respects PAM limits.

...

All right, that worked and we are off to a good start. Those of us who installed Oracle a few times might already spot a few details in the above output. It’s not quite what I had in mind.

Executing test.sh in an interactive shell

For comparison, this is the output when logged in as oracle (in an interactive shell)

$ ./test.sh 
-----------------------------------------------------------
test run starts at Tue Jun 15 20:13:41 UTC 2021:
I am oracle

my own PID is: 13173

oracle     13173   13150  0 20:13 pts/0    00:00:00 bash ./test.sh
oracle     13176   13173  0 20:13 pts/0    00:00:00 /usr/bin/ps -ef

hard limits according to ulimit:

core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 30554
max locked memory       (kbytes, -l) 134217728
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 32768
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

soft limits according to ulimit:

core file size          (blocks, -c) unlimited
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 30554
max locked memory       (kbytes, -l) 134217728
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 16384
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

actual limits as per /proc:

Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            10485760             33554432             bytes     
Max core file size        unlimited            unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             16384                16384                processes 
Max open files            1024                 65536                files     
Max locked memory         137438953472         137438953472         bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       30554                30554                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
-----------------------------------------------------------

As you can see, there are quite some differences making it necessary to set limits via systemd-specific syntax in the unit file itself. I can only conclude that systemd – as documented – does not pay attention to the configuration set by pam_limits(8).

OK, so shell limits aren’t respected, what next?

So if systemd doesn’t make use of pam_limits(8) there needs to be a different solution. There is a whole raft of options documented in systemd.directives(7) for Oracle Linux 8. Again, this might be different for Oracle Linux 7. The database needs the following shell limits to be set:

  • open file descriptors (“nofile”)
  • number of processes available to a single user (“nproc”)
  • Size of the stack segment per process (“stack”)
  • Maximum locked memory limit (“memlock”)

These map to the following systemd.directives(7):

  • LimitNOFILE
  • LimitNPROC
  • LimitSTACK
  • LimitMEMLOCK

You should really go ahead and read the man pages for systemd, they are great!

Amending the unit file

The next step is to add the above directives to the unit file, reload systemd, start the service and see what happens. Note that most sources I found only set LimitMEMLOCK and LimitNOFILE for Oracle. I am using the values from the Oracle documentation – your system might require different settings.

[Unit]
Description=A oneshot service to test whether systemd respects PAM limits

[Service]
LimitNOFILE=1024:65536
LimitNPROC=2047:16384
LimitSTACK=10485760:33554432
LimitMEMLOCK=infinity
User=oracle
Group=oinstall
Type=oneshot
ExecStart="/home/oracle/test.sh"

Apart from the additional Limit.* directives, it’s the same file. The syntax in Oracle Linux 8 is LimitDIRECTIVE=soft:hard limit.

With the unit file changed, the following limits have been recorded:

Jun 16 20:30:57 server1 systemd[1]: Starting A oneshot service to test whether systemd respects PAM limits...
Jun 16 20:30:57 server1 test.sh[13821]: -----------------------------------------------------------
Jun 16 20:30:57 server1 test.sh[13821]: test run starts at Wed Jun 16 20:30:57 UTC 2021:
Jun 16 20:30:57 server1 test.sh[13821]: I am oracle
Jun 16 20:30:57 server1 test.sh[13821]: my own PID is: 13821
Jun 16 20:30:57 server1 test.sh[13821]: oracle     13821       1  0 20:30 ?        00:00:00 bash /home/oracle/test.sh
Jun 16 20:30:57 server1 test.sh[13821]: oracle     13824   13821  0 20:30 ?        00:00:00 /usr/bin/ps -ef
Jun 16 20:30:57 server1 test.sh[13821]: hard limits according to ulimit:
Jun 16 20:30:57 server1 test.sh[13821]: core file size          (blocks, -c) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: data seg size           (kbytes, -d) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: scheduling priority             (-e) 0
Jun 16 20:30:57 server1 test.sh[13821]: file size               (blocks, -f) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: pending signals                 (-i) 30554
Jun 16 20:30:57 server1 test.sh[13821]: max locked memory       (kbytes, -l) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: max memory size         (kbytes, -m) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: open files                      (-n) 65536
Jun 16 20:30:57 server1 test.sh[13821]: pipe size            (512 bytes, -p) 8
Jun 16 20:30:57 server1 test.sh[13821]: POSIX message queues     (bytes, -q) 819200
Jun 16 20:30:57 server1 test.sh[13821]: real-time priority              (-r) 0
Jun 16 20:30:57 server1 test.sh[13821]: stack size              (kbytes, -s) 32768
Jun 16 20:30:57 server1 test.sh[13821]: cpu time               (seconds, -t) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: max user processes              (-u) 16384
Jun 16 20:30:57 server1 test.sh[13821]: virtual memory          (kbytes, -v) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: file locks                      (-x) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: soft limits according to ulimit:
Jun 16 20:30:57 server1 test.sh[13821]: core file size          (blocks, -c) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: data seg size           (kbytes, -d) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: scheduling priority             (-e) 0
Jun 16 20:30:57 server1 test.sh[13821]: file size               (blocks, -f) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: pending signals                 (-i) 30554
Jun 16 20:30:57 server1 test.sh[13821]: max locked memory       (kbytes, -l) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: max memory size         (kbytes, -m) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: open files                      (-n) 1024
Jun 16 20:30:57 server1 test.sh[13821]: pipe size            (512 bytes, -p) 8
Jun 16 20:30:57 server1 test.sh[13821]: POSIX message queues     (bytes, -q) 819200
Jun 16 20:30:57 server1 test.sh[13821]: real-time priority              (-r) 0
Jun 16 20:30:57 server1 test.sh[13821]: stack size              (kbytes, -s) 10240
Jun 16 20:30:57 server1 test.sh[13821]: cpu time               (seconds, -t) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: max user processes              (-u) 2047
Jun 16 20:30:57 server1 test.sh[13821]: virtual memory          (kbytes, -v) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: file locks                      (-x) unlimited
Jun 16 20:30:57 server1 test.sh[13821]: actual limits as per /proc:
Jun 16 20:30:57 server1 test.sh[13821]: Limit                     Soft Limit           Hard Limit           Units
Jun 16 20:30:57 server1 test.sh[13821]: Max cpu time              unlimited            unlimited            seconds
Jun 16 20:30:57 server1 test.sh[13821]: Max file size             unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max data size             unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max stack size            10485760             33554432             bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max core file size        unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max resident set          unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max processes             2047                 16384                processes
Jun 16 20:30:57 server1 test.sh[13821]: Max open files            1024                 65536                files
Jun 16 20:30:57 server1 test.sh[13821]: Max locked memory         unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max address space         unlimited            unlimited            bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max file locks            unlimited            unlimited            locks
Jun 16 20:30:57 server1 test.sh[13821]: Max pending signals       30554                30554                signals
Jun 16 20:30:57 server1 test.sh[13821]: Max msgqueue size         819200               819200               bytes
Jun 16 20:30:57 server1 test.sh[13821]: Max nice priority         0                    0
Jun 16 20:30:57 server1 test.sh[13821]: Max realtime priority     0                    0
Jun 16 20:30:57 server1 test.sh[13821]: Max realtime timeout      unlimited            unlimited            us
Jun 16 20:30:57 server1 systemd[1]: limitstest.service: Succeeded.
Jun 16 20:30:57 server1 test.sh[13821]: -----------------------------------------------------------
Jun 16 20:30:57 server1 systemd[1]: Started A oneshot service to test whether systemd respects PAM limits.

Thankfully this works, and the settings as documented by Oracle are implemented.

Summary

I always found it hard to come up with a working systemd unit file to start a single instance Oracle database. There is a wide range of articles covering SysV init, upstart, and systemd. It certainly can be confusing for a DBA to pick the right one. I have never been a great fan of using /etc/init.d for startup scripts in modern Linux distributions, even though Oracle Linux has a compatibility wrapper to deal with legacy startup scripts. After spending time with systemd I’m now comfortable writing my own start/stop script for RDBMS and listener. Maybe I’ll publish the results in a different post.

I hope you found the article useful for writing your own systemd unit files in Oracle Linux 7 and 8.