Shaun's Wiki

While working through database log files looking for issues I came across numerous instances of the following error:

2019-11-07T03:57:00.623733+02:00
Errors in file /u01/app/oracle/admin/diag/rdbms/dev/dev1/trace/dev1_ora_8142.trc  (incident=1313939):
ORA-00603: ORACLE server session terminated by fatal error
ORA-27515: inadequate memlock limit or driver settings
Incident details in: /u01/app/oracle/admin/diag/rdbms/dev/dev1/incident/incdir_1313939/dev1_ora_8142_i1313939.trc

This seems to indicate some sort of memory limit (memlock limit) is being reached by a process in the database. Following the Exacheck recommendation the grid user memlock value was already set to unlimited, and oracle user memlock to 237GB (90% of physical memory). It seemed unlikely this memory lock limit was being reached, but the source of the error was unclear.

This was identified as the query for the failing session from the trc log:

----- Current SQL Statement for this session (sql_id=gngtvs38t0060) -----
SELECT /*+ CONNECT_BY_FILTERING */ s.privilege# FROM sys.sysauth$ s        CONNECT BY s.grantee# = PRIOR s.privilege#
                              AND (s.privilege# > 0 OR s.privilege# = -352)                              START WITH (s.p
rivilege# > 0 OR s.privilege# = -352) AND s.grantee# IN     (SELECT c1.privilege# FROM sys.codeauth$ c1 WHERE c1.obj# =
:1)            UNION                                                                      SELECT c2.privilege# FROM sys.
codeauth$ c2 WHERE c2.obj# = :2              ORDER BY 1 ASC

And the process resource limits show a 64K memlock limit, which makes no sense as it is the Oracle user.

----- Process Resource Limits -----
***************** Dumping Resource Limits(s/h) *****************
core file size                         0 KB/UNLIMITED
data seg size                     UNLIMITED/UNLIMITED
scheduling priority                    0 KB/0 KB
file size                         UNLIMITED/UNLIMITED
pending signals                     1005 KB/1005 KB
max locked memory                     64 KB/64 KB
max memory size                   UNLIMITED/UNLIMITED
open files                             4 KB/4 KB
POSIX message queues                 800 KB/800 KB
real-time priority                     0 KB/0 KB
stack size                            32 MB/UNLIMITED
cpu time                          UNLIMITED/UNLIMITED
max user processes                  1005 KB/1005 KB
virtual memory                    UNLIMITED/UNLIMITED
file locks                        UNLIMITED/UNLIMITED
***************** End of Resource Limits Dump ******************

Process Group: DEFAULT, pseudo proc: 0x4c0aa17f8
O/S info: user: oracle, term: UNKNOWN, ospid: 8142

Working back through the Exawatcher Ps archives, I was able to track pid 8142 to its parent process:

0 R oracle     8142   8141 17  21   9  10    - 394308 4972401 -              03:56 ?        00:00:03 oracleeiwdev1 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
0 S oracle     8141   8140  0  10   9  10    - 20888 28639 pipe_wait         03:56 ?        00:00:00 /u01/app/oracle/product/12.2.0.1/dbhome_1/bin/sqlplus -S -L
4 S oracle     8140  90932  0  26   9  10    -  2488 26587 do_wait           03:56 ?        00:00:00 /bin/sh /var/tmp/mgsscript1567727100
4 D root      90932  90927  8   8   9  10    - 40120 38740 lock_page_killabl 03:13 ?        00:04:40 /opt/managesoft/libexec/ndtrack -o InventoryType=Machine -o UserInteractionLevel=Quiet
0 S root      90927  33457  1   7  19   0    -  6172 29375 hrtimer_nanosleep 03:13 ?        00:00:00 /opt/managesoft/libexec/ndschedag -o ScheduleType=Machine -r {de9cddcd-53ac-4796-b02c-f00764f00a1f}
1 S root      33457      1  0  22  19   0    -  4096  4546 poll_schedule_tim Nov06 ?        00:00:00 /opt/managesoft/libexec/ndtask

I then looked at the process limits for the ndtask process:

[root@db01 Ps.ExaWatcher]# cat /proc/33457/limits
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            8388608              unlimited            bytes
Max core file size        0                    unlimited            bytes
Max resident set          unlimited            unlimited            bytes
Max processes             1029339              1029339              processes
Max open files            1024                 4096                 files
Max locked memory         65536                65536                bytes

At the same time, checking on My Oracle Support I was able to find the exact same issue being experienced and logged as a bug. https://support.oracle.com/epmos/faces/DocumentDisplay?id=2605233.1&displayIndex=1#CAUSE

With a Solution of: 3rd party softwares connect to database with a hard code memlock setting should have the sufficient value.

In other words, flexera has a hardcoded memlock-related bug that causes the process to crash every few hours and dump trace logs, which is a problem to maintain.

I therefore have disabled Flexera on all Warehouse database servers.