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

[#1]

Article Number

000036729

Applies To

RSA Product Set: Identity Governance & Lifecycle
RSA Version/Condition: 6.9.1+
 

Issue

From time to time, the RSA Identity Governance & Lifecycle product may report an ORA-04030 error from the Oracle database.  For example:

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.

 

Task

In order to troubleshoot an ORA-04030 error, the following information will be needed from the Oracle database:

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

Resolution

Below are some possible solutions for the ORA-04030 error.  However, as the solutions are implemented at the Operating System or Oracle database level, please engage RSA Customer Support for RSA-provided database if you need help with the troubleshooting steps.  For customer-supplied database, please engage your local Oracle DBA team.

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

This count then needs to be compared to the value in the file: /proc/sys/vm/max_map_count.
Once it has been confirmed that the number of lines in the Oracle Process Map Dump exceeds the value in the max_map_count file, then the setting can be reviewed and changed with the following commands.

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?

First, we need to know how much RAM the operating system has available, using the free command.

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

In this example, the system the system has 18,405,600 bytes, or 16GB of RAM.
 

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}'

However, if you wish to capture the results into a file to examine usage per user, then use this command.

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

This can then be compared to the results from step 3.  If the WildFly memory usage is significantly greater than the maximum Java heap size, then there may be a Java leak.
 

5.  Check the Oracle database memory settings

Next, determine how much memory has been allocated to the Oracle database. 

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

Connect to the database as SYSDBA, and issue the following commands.

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

If there are Shared Memory Segments where the owner column has a user other than oracle" then take note of how many bytes the Shared Memory Segments are using.

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.

 or

• 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

Therefore, the RSA Identity Governance & Lifecycle product will not report an ORA-04030 error because it has exceeded the PGA_AGGREGATE_TARGET setting of the Oracle database.

Notes

The following articles deal with specific occurrences of the ORA-04030 error with the RSA Identity Governance & Lifecycle product.  Please review these articles in case a specific remediation is available for the applicable scenario.

• 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