Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

19.12 Linux - PGA MEMORY VERY LARGE DUE TO Q PROCESSES - BACKGROUND Streams EMON Regular Slave

Marcelo MarquesSep 7 2021 — edited Sep 7 2021

------------------------------------------------------------------------------------------------------
--Oracle Enterprise Linux 7.9 x64
--Oracle Database Enterprise Edition 19.12 (19-JULY-2021)
--Oracle Grid Infrastructure 19.12 (19-JULY-2021) for Standalone Server ( NOT RAC - GI HAS/ASM ONLY)
------------------------------------------------------------------------------------------------------
After the 19.12 Patchset was applied the database instance started to show repeated ORA-4036 in the database alert log.
------------------------------------------------------------------------------------------------------
"PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA
memory are not eligible to receive ORA-4036 interrupts. Further occurrences
of this condition will be written to the trace file of the DBRM process."
------------------------------------------------------------------------------------------------------
It got to the point that it consumed all Linux memory and Linux start to swap.
This caused further ORA-4036 errors, and eventually was necessary to stop/start the database instance to free up the Linux memory,
then the database worked fine again, till the same issue occurred.
The workload did not changed, and when 19.11 (19-APRIL-2021) Patchset was installed the ORA-4036 was not occurring.
I opened a SR with Oracle Support and the temporary workaround was to set PGA_AGGREGATE_LIMIT=0.
README_FIRST_adrci_show_problems.txt (4.23 KB)------------------------------------------------------------------------------------------------------
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/PGA_AGGREGATE_LIMIT.html#GUID-E364D0E5-19F2-4081-B55E-131DF09CFDB3
------------------------------------------------------------------------------------------------------
If MEMORY_TARGET is set, then PGA_AGGREGATE_LIMIT defaults to the MEMORY_MAX_TARGET value.
If MEMORY_TARGET is not set, then PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET.
If MEMORY_TARGET is not set, and PGA_AGGREGATE_TARGET is explicitly set to 0, then the value of PGA_AGGREGATE_LIMIT is set to 90% of the physical memory size minus the total SGA size.
In all cases, the default PGA_AGGREGATE_LIMIT is at least 2GB and at least 3MB times the PROCESSES parameter (and at least 5MB times the PROCESSES parameter for an Oracle RAC instance).
For a PDB, the default value is the same as the CDB's default value.
------------------------------------------------------------------------------------------------------
Do not attempt to set PGA_AGGREGATE_LIMIT below its default value, even in a parameter file (pfile), or instance startup will fail.
However, PGA_AGGREGATE_LIMIT can be set to 0 either in a parameter file or dynamically after startup.
If a value of 0 is specified, it means there is no limit to the aggregate PGA memory consumed by the instance.
------------------------------------------------------------------------------------------------------
But after a few days, PGA keeps growing larger and larger. But workload only has 5 users connected.
README_PGA_MEMORY.txt (2.76 KB)I was able to determine that these processes are taking lots of PGA space and not releasing it.
------------------------------------------------------------------------------------------------------
SESSION_ID, SESSION_SERIAL, PROCESS_NAME, PID_THREAD, CURRENT_SIZE_MB, MAXIMUM_SIZE_MB
5645 64179 Q009::mcsdboralnx5.esri.com 22773 6,115.40 6,115.40
4707 62132 Q007::mcsdboralnx5.esri.com 22769 6,147.46 6,147.46
6116 6422 Q00A::mcsdboralnx5.esri.com 22775 6,179.53 6,179.53
3767 25363 Q005::mcsdboralnx5.esri.com 22765 6,211.46 6,211.46
5174 9587 Q008::mcsdboralnx5.esri.com 22771 6,243.46 6,243.46
------------------------------------------------------------------------------------------------------
All these Q processes are related to "BACKGROUND Streams EMON Regular Slave".
The 19.12 patchset has many fixes for PGA memory leaks, and other memory fixes too, maybe those could have introduced this issue.
--Database 19 Release Updates and Revisions Bugs Fixed Lists ( Doc ID 2523220.1 )
------------------------------------------------------------------------------------------------------
27933036 pga memory leak at koh-kghu call / kol vstring for dbms_aq.listen call
32066061 Memory Leak in "kxsclb in kxscf" Component Causing ORA-4030/ORA-4036
32033795 Unreasonable large memory allocations by query processing code
32338476 Memory Leak: auditpqreceive
------------------------------------------------------------------------------------------------------
I have opened a SR with Oracle Support about this problem.
I could rollback the 19.12 Patchset till Oracle has a patch.
But I want to know if anyone has a workaround, such as setting a hidden parameter, that could help resolve the issue.

This post has been answered by Marcelo Marques on Oct 7 2021
Jump to Answer

Comments

You can use 1.18. However, you can also use the latest version (1.20) if you are using a fairly new Java version like 8u271+
Don't forget that you will need to update the Jacob DLL file sized in webutil.cfg to match the version you are using. The file sizes are different for each version.

Youcef Kazar

Thank you good to know. I managed to find and download the 1.18-M2 version.

schubach

Where can I find the 1.18-M2 version? I can't find it anywhere!!!!! I would prefer not to have to upgrade Java at this time.
Thanks,
Bernie

@youcef-kazar
https://github.com/freemansoft/jacob-project/releases
I strongly recommend you patch/upgrade to 12.2.1.4 and the latest Java version.

schubach

We are upgrading our Oracle WAM 1.x installation to the terminal release of that product, WAM 1.9.1.3.1, which according to the release notes, https://docs.oracle.com/cd/F19108_01/19131/PDF/oracle_utilities_wam_release_notes_for_release_1.9.1.3.1.pdf , is compatible with WebLogic 12.2.1.3. I don't know what the risks would be of going to 12.2.1.4 with that product. In the next few years, we will be replacing that product, but for now, we need to to go 1.9.1.3.1 so we can get off of WebLogic 11.2. Going to WAM 2.x is a re-implementation, not an upgrade, and is a multi-year process. So do you know if we can get 1.18-M2 of jacob.jar, or if not, how we can get webutil working in 12.2.1.3 with OLE features?
Thanks,
Bernie

You can use the link I previously shared to get most of the previous stable releases. You will not be able to get the "M" (milestone) builds, but you can get the stable version (e.g. 1.18).

schubach

Hi Michael,
I'm struggling to get webutil configured properly in my 12.2.1.3 environment. webutil_demo is giving error "oracle.forms.webutil.clientinfo.GetClientInfo bean not found. WEBUTIL_CLIENTINFO.GET_SYSTEM_PROPERTY will not work. So I downloaded Webutil Diagnostic Test Form For Checking Webutil Configuration (Doc ID 553849.1) and I get these warnings (see below). My custom form that I call from WAM that worked fine in WebLogic 11.2 environment gives FRM-40734: Internal Error: PL/SQL error occurred. I changed something with it and now I get "Exception in thread "AWT-EventQueue-3" java.lang.NoClassDefFoundError: Could not initialize class oracle.forms.webutil.cApi.CFunc" but either way if the demo and the diagnostic forms aren't working, then the problem is with the webutil config. I have run create_webutil_db against my application schema.
All of this is with the 1.18 release from the web site that you sent, so that's why I thought that I might need specifically the M2 version mentioned as the recommended version. Do you know why M2 is no longer available, if there's a way to get it, or if not, why I'm getting these errors and what might be a workaround?
Thanks,
Bernie
(1) WARNING! The DLL file "C:\FMW\Oracle_Home\forms\webutil\win32\jacob-1.18-M2-x86.dll" does not exist. This DLL is required for OLE integration in Webutil. You will not be able to use OLE functionality... >> FAILED <<
(2) INFO! The DLL file "C:\FMW\Oracle_Home\forms\webutil\win32\ffisamp.dll" does not exist. This DEMO DLL is used to demo the C API in Webutil. You will not be able to see the demo functionality of the C API ...
(3) WARNING! The DLL file "C:\FMW\Oracle_Home\forms\webutil\win64\jacob-1.18-M2-x64.dll" does not exist, this DLL file required for OLE integration in Webutil. You might not be able to use OLE functionality... >> FAILED <<

The M2 release is/was a milestone release (think of it as beta). Your issue has nothing to do with Jacob. The only thing the Jacob libraries are used for is OLE calls. They are needed at startup time to initialize the bean(s) that use it, but beyond that they aren't used. You do need to properly configure things though.
You need to do this:
1] Download and sign jacob.jar and its associated DLLs (don't sign these).
2] Copy the signed jacob.jar to /forms/java
3] Copy the Jacob DLL files to the appropriate directory under /forms/webutil
4] Using Fusion Middleware Control, edit webutil.cfg and replace the references to 1.18-M2 with whichever version you decided to download. You will need to get the file sizes as they are also set in this file. If you chose to use 1.18 these entries need to replace the existing ones:

install.syslib.0.0.7.1=jacob-1.18-x86.dll|167936|1.18|true
install.syslib.0.1.7.1=jacob-1.18-x64.dll|205312|1.18|true

5] Run the provided sql script against a DB schema that will be accessible by all application users. The file is located here:
C:\FMW\Oracle_Home\forms\create_webutil_db.sql
6] Generate webutil.plx from webutil.pll found in ORACLE_HOME\forms
7] Restart the Forms managed server (e.g. WLS_FORMS). This is required any time you make changes to /forms/java
8] Generate your form that is WebUtil enabled. You must perform a compile_all=yes before or during the generation. In the Builder you can do the following:
Ctrl+Shift+K (compile all plsql)
Ctrl+S (save module)
Ctrl+T (generate executable)
On the command line,
frmcmp module=foo userid=scott/tiger@orcl compile_all=yes
9] Ensure that FORMS_PATH (in default.env or your custom env) include the directory where webutil.plx and your module(s) live.
Run...

schubach

Could you please shed some light on the recommended (and easiest) way to sign the jacob.jar file? My application is running behind a firewall on a machine that is not accessible from the outside world, but I'm still having issues from it being unsigned.
webutil error.PNG (136.13 KB)

You need to update your Java version. You are running a desupported version.
Regarding the signing of JARs, this is required for all client side JARs. The signing helps to ensure they can be trusted and ensure that have not be altered.
https://www.google.com/search?q=sign+jar+files

1 - 10

Post Details

Added on Sep 7 2021
29 comments
6,268 views