000036729 - How to troubleshoot Oracle database ORA-04030 errors in RSA Identity Governance & Lifecycle

08/16/2018 02:41:19.451 INFO (Exec Task Consumer#5) [com.aveksa.server.xfw.UnificationExecutor] Failed method=Process subTask=CompleteMergeTasks Default User Population, 67673 
com.aveksa.server.db.PersistenceException: java.sql.SQLException: ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key) 
ORA-06512: at "AVUSER.UNIF_RAWDATA", line 82 
ORA-06512: at "AVUSER.UNIFICATION_PROCESSOR", line 277 
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort subheap,sort key) 
ORA-06512: at line 1

An ORA-4030 error can arise due to the following situations:

• Operating System limit. The Oracle process has grown to a size greater than that allowed by an operating system limit, due to normal (but high) memory usage, or an Oracle process memory leak.
• Over-allocation. The memory allocated (and consumed) by Oracle, plus the memory consumed by non-Oracle processes, exceeds the RAM.
• An Oracle process exceeds the PGA limit.  An Oracle process running PL/SQL code can exceed the PGA_AGGREGATE_TARGET if the code uses PL/SQL local variables to store large amounts of data and/or there is an infinite loop in the code.

1. The alert log from the very first ORA-4030 error, after a database STARTUP.  For example, the database STARTUP looks like this: 

Tue Aug 21 10:24:53 2018
Starting ORACLE instance (normal) (OS id: 89733)

2. For that very first ORA-04030 error after the STARTUP, note the Incident number from within the parentheses at the end of the ORA-4030 trace filename.  For example:

Errors in file /u01/app/oracle/diag/rdbms/acmprd01/ACMPRD01/trace/ACMPRD01_j001_1234.trc (incident=23241):

3. Using the incident number, generate an Oracle Incident Package using the ADRCI following command.  

ips pack incident <incident#> in /tmp

4. To determine the current Patch Set Update (PSU) level, login as the owner of the Oracle Home and issue the following command. 

opatch lsinventory

For further assistance after collecting the above information, please engage RSA Customer Support by logging a new Case for RSA-provided database, or engage your local Oracle DBA for customer-supplied database (also called "Remote DB").

Operating system limit 

On the Linux platform, there is a known issue relating to the max map count setting, located in the file /proc/sys/vm/max_map_count.
The procedure for confirming that the Oracle process has exceeded the value in the max_map_count file is documented in Oracle Support Note 1325100.1 :PLSQL Procedure Causing ORA-4030 Errors.

Simply put, what needs to be confirmed is how many lines are between the following two tag lines in the ORA-04030 trace file:

----- Process Map Dump -----

******************* End of process map dump ***********

1. Login as root.
2. Review the current setting.

# more /proc/sys/vm/max_map_count
65530

3. Increase the max. map count. For example:

# sysctl -w vm.max_map_count=131060
vm.max_map_count = 131060

If the Oracle database is running on a non-Linux platform (for example, IBM AIX), then it is not supplied by RSA, so please contact your local Oracle DBA.
 

Over-allocation

Over-allocation is when the memory allocated to the Oracle database, and the memory consumed by non-Oracle processes, exceeds the memory available to the operating system.  For example (true story):

• The Linux operating system had 16GB of RAM installed.
• The Oracle database MEMORY_TARGET was set to 14GB
• The root user was using 5GB of memory.
• After some time, the Oracle database reported an ORA-04030 error.

1. How much RAM is there?

# free
             total       used      free      shared      buffers      cached
Mem:      18405600   18215380    190220           0       224244     6843656
-/+ buffers/cache:   11147480   7258120
Swap:      8388604       2964   8385640

2. How much memory are the non-Oracle user processes using?

Use the following command to determine the virtual memory size of all the non-Oracle processes.

vsz is the virtual memory size of the process in KiB (1024-byte units).
 

ps -e h -o ruser,vsz |grep -v oracle | awk '{total = total + $2}END{print total}'

ps -e -o ruser,group,pid,vsz,comm | grep -v oracle > virt_mem_size.txt

3.  How much memory is WildFly using?

Use the following command to show the virtual memory size of the Oracle Java/WildFly process.

ps -e -o ruser,group,pid,vsz,comm | grep java

4.  How much memory is WildFly allowed?

The Java -Xmx setting is the maximum Java heap size.  Confirm the -Xmx setting for the Oracle Java/WildFly processes by using the following command:

ps -ef | grep java
oracle    7258     1  1 Aug11 ?        16:58:16 /usr/lib64/jvm/java/jre/bin/java ... -Xmx512m  ...

5.  Check the Oracle database memory settings

• The SGA, or Shared Global Area, is the memory the Oracle database uses to share information between processes.
• The PGA, or Process Global Area, is the memory Oracle database processes use.

SQL> set linesize 128
SQL> show parameter sga_target
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
sga_target                           big integer                      7584M
SQL> show parameter pga_aggregate_target
NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
pga_aggregate_target                 big integer                      4072M

6.  Check actual PGA usage

While still connected to the database, issue the following SQL statement to show the actual PGA being used.

SQL> SELECT SUM(PGA_ALLOC_MEM)/1024/1024 "Total PGA Allocated (Mb)" FROM v$process p, v$session s WHERE p.addr = s.paddr;

7. Check for Memory Segments 

However, it's not only user processes that use memory, there is also something called Shared Memory Segments.  To determine the Shared Memory Segments, please use the following command.

ipcs -m

If there is only one Oracle database, the result may look like this:

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes        nattch      status
0x00000000 20021251   oracle     640        2940928      94
0x00000000 20054020   oracle     640        7046430720   47
0x00000000 20086789   oracle     640        13836288     47
0x85e757a0 20119558   oracle     640        32768        47

8.  Adding it all up

Now we can add up all the pieces of information, and then compare it to the amount of RAM the operating system has available.

The formulas are:

• Non-Oracle memory consumption = Non-Oracle process (step 2) + non-Oracle Memory Segments (step 7)
• Oracle memory allocation = Oracle Java/WildFly process (step 3) + sga_target + pga_aggregate_target (step 5).

If the non-Oracle memory consumption + Oracle memory allocation is GREATER THAN the RAM (step 1), then the memory on the system has been over-subscribed.
To resolve the problem, then the choices are;

• Get rid of some of the non-Oracle processes and/or memory segments.  For this operation, please engage the System Administrator.

• Reduce the Oracle database memory settings.  For more information, please see Article 000031254 - How to change the Oracle SGA/PGA on an RSA Via L&G appliance.

An Oracle process exceeds the PGA limit 

The PGA limit in an Oracle database is set by the Oracle database parameter PGA_AGGREGATE_TARGET.  When an Oracle process exceeds the PGA limit, there are specific memory allocation names reported in the parentheses () of the ORA-04030 error.  However, the RSA Identity Governance & Lifecycle product does not use the PL/SQL language components that can exceed the PGA limit.

• PL/SQL Memory Collections
• PL/SQL Tables
• Local PL/SQL variables/varrays

• Article 000036705 - SUSE 12 SP4 installation causes ORA-00600 and ORA-07445 in the database for RSA Identity Governance & Lifecycle
• Article 000034794 - RSA Identity Governance & Lifecycle Aveksa System Report (ASR) is not generated due to ORA-04030 (qmxlu subheap,qmemNextBuf:alloc)
• Article 000033770 - When starting RSA Via Lifecycle and Governance 7.0 Access Fulfillment Express (AFX) services, some of the connectors are failing due to Out of Memory errors