DevOps, CI/CD and Automation

- When did problem occur & detected?
Aug/08/2021 fist time and every day since.
- Did the database/Application Crashed with this issue?
yes, sessions were disconnected due to ORA-4036.
now, after setting PGA_AGGREGATE_LIMIT=0 is stable but PGA memory keeps taking more and more memory space due to the Q processes "BACKGROUND Streams EMON Regular Slave".
- Is this a PROD, QA, DEV, TEST Environment
development
- Is this a RAC environment or single instance?
no rac - orace grid infrastructure for standalone server - HAS/ASM only with single db instance
- Is this a Standby DG Instance?
No
- Is this an engineered system (Exadata, Database Appliance)?
No

I am seeing the same issue in 19.12 on Windows and 19.12 on Linux. The PGA memory Q processes "BACKGROUND Streams EMON Regular Slave" keeps getting more and more memory space and not releasing it, got to the point to be consuming 40GB of memory on my server, hence I had no choice, I had to shutdown the database instance and start up it again to clean the large Q processes, but I know it will be just a matter of a few days for those Q processes to grow larger and larger again. : (

Note: What are the Q Processes???
https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/background-processes.html#GUID-86184690-5531-405F-AA05-BB935F57B76D
Qnnn
AQ Server Class Process
Per AQ Master Class server process
Each server class process acts on behalf of an AQ master class process. This relationship is maintained until the master requires services of a particular service process. Once released, the server class processes are moved to a free server pool.

Database instances Advanced Queueing

----------------------------------------------------------------------------------------------------------------------
Oracle Support Replied and proposed a workaround. I have implemented and I am monitoring the database instances to see if the Q processes continue to consume large amounts of memory and not releasing it. Let's see if this workaround works.
----------------------------------------------------------------------------------------------------------------------
My Oracle Support Reply:
There is a known issue with Emon
Bug 9735536 [[https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=9735536]](https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=9735536]) Enhancement request which allows ability to selectively remove slow clients from Emon Notification mechanism ( Doc ID 9735536.8 )
Event Monitor (EMON) Slave Process Constantly Consuming CPU ( Doc ID 1603844.1 )
To permanently resolve the issue if the release is pre-11.2.0.3 apply Patch 9735536.
In 11.2.0.4 onwards do the following
connect / as sysdba
alter system set "_client_enable_auto_unregister"=true scope=spfile;
shutdown immediate;
startup;
----------------------------------------------------------------------------------------------------------------------
The links above are related to "11.2.0.4 onwards", well quite old workaround and it might not work for 19.12, but let's see what happens.
----------------------------------------------------------------------------------------------------------------------

The workaround above suggested by Oracle Support did not work. The Q processes continue to take more and more memory and not releasing it.
My 2 cents.
I asked Oracle Support to log a new bug for this issue.
When 19.11 Patch was installed this issue was not occurring.
Only after the 19.12 Patch was applied the issue started to happen.
19.12 Patch has pga memory leak fixes and other memory fixes as well and some of these memory leak fixes might have introduced this new issue in 19.12.
--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 am experiencing the issue with 19.12 on Windows and 19.12 on Linux.
I asked Oracle Support to request Oracle Development to look on both platforms.

Hello Marcelo,
Thanks for sharing this information
Best regards
Mohamed Houri

If anyone is experiencing the same issue then please log a SR with Oracle Support. I'm still working with Oracle Support but we could not find a workaround and I don't think there will be one. I am still trying to make Oracle Support to log a new bug for this issue to have Oracle Development to look into it and provide an One-Off patch.

Hello,
When you have set your pga_aggregate_limit >0, you have to know that processes launched by SYS user are not capped by this limit and can go beyond without being stopped by MMON (only insufficient OS memory will stop them). This is why Oracle has shown the following information
"PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA
memory are not eligible to receive ORA-4036 interrupts”.
If you don’t have changed the _pga_limit_dump_summary parameter, then any ORA-04036 error will have a corresponding trace file dumped into the trace file directory. Check the alert_log to find the corresponding trace file name. If you can find this trace file then you can clearly identify what process has consumed the most of pga, in what heap and for what allocation reason. Supply this information to Oracle support.
After you have set the pga_aggregate_limit to 0, processes launched by non-SYS users are not anymore subject to the ORA-04036 and, of course, this will increase the total PGA consumption since both SYS and  non-SYS process are not concerned by a PGA limit value.
You have a pga_aggregate_target of 24GB. You have a small number(0.04) of over allocation count. What is SGA memory value? What is the RAM value of this machine? Are you using huge page? If so what is the % of their usage

sar -H

Are the sql tuning advisor and space advisor automatic jobs enabled?                     
Best Regards
Mohamed Houri

Mohamed, I have identified the cause, it is the Q processes that keep taking more and more server memory and not releasing the memory, after I set PGA_AGGREGATE_LIMIT=0 the PGA can grow unlimited and ORA-4036 is not happening anymore, but the Q processes continue to take more and more server memory till all the memory is depleted, the only alternative is to shutdown and startup the database instance to clear the Q processes.
I just got out of the phone with Oracle Support and Oracle Development is investigating the issue.
Thanks,
Marcelo Marques | Principal Product Engineer, Esri | OCP - Oracle Certified Professional

Hello,
If anyone applied the 19.12 Pathset (19-JUL-2021) and now is experiencing the same issue with PGA Memory getting very large, but the workload has not changed, that's because the Q processes have a memory leak issue and keep taking more and more memory and this makes the PGA to grow large and large.
If PGA_AGGREGATE_LIMIT is "unset" then Oracle Instance determines the PGA Limit value at instance startup, if the PGA grows and reaches the limit then ORA-4036 error will happen, and user sessions might get disconnected, in this case will be necessary to shutdown / startup the oracle instance to clear the problem, but the Q processes memory leak will continue to happen, now as a temporary workaround set PGA_AGGREGATE_LIMIT=0 this allows the PGA to grow unlimited, this means the Q processes memory leak will take more and more memory till they consume all server memory then ORA-4036 can occur again because there is no more free server memory for the PGA to grow, so you will need to monitor the PGA size and the Q processes size.
I have a 19.12 CDB with 30 PDBs on Linux and Windows and I am "not" using Advanced Queueing, nor Streams and the issue with Q Processes Memory Leak happens.
I am experiencing the Q Processes Memory Leak problem with 19.12 on Linux and 19.12 on Windows. I am still working with Oracle Support on this issue, there is no workaround, the issue does not happen on 19.11 Patchset (19-Apr-2021), so an alternative is to rollback 19.12 Pathset till an One-Off Patch for 19.12 is available, and eventually the fix will get into 19.13 Patchset (19-OCT-2021).
If you encounter the same problem then create a SR with Oracle Support.
You will need to provide the logs described in this Oracle Support article.
SRDC - Unidentified Memory Issues Checklist of Evidence to Supply (1909842.1)
And, also attach to the SR, see below, 3 days of Q processes heapdump logs.
heapdumps.txt (5.79 KB)Thanks,
Marcelo Marques | Principal Product Engineer, Esri | OCP - Oracle Certified Professional

Hello Marcelo
Thanks for this detailed feedback
But this PGA memory leak will kick in only for those applications using Advanced Queueing? right?
Best regards
Mohamed Houri

@mohamed-houri2 - I have a 19.12 CDB with 30 PDBs on Linux and Windows and I am "not" using Advanced Queueing, nor Streams and the issue with Q Processes Memory Leak still happens.

Below is the temporary workaround I am using so I don't need to shutdown/startup the db instance.
q_processes_memory_leask_temporary_workaround.txt (3.49 KB)I am still working with Oracle Support on the SR that I have opened on Aug/03 and till now Oracle Support has not found a definitive viable workaround and has not logged a bug for the issue yet. : (

Someone else was experiencing the same issue in their production environment.
https://community.oracle.com/mosc/discussion/comment/16841951#Comment_16841951
here's the steps provided from Oracle:
Check if AQ_SRVNTFN_TABLE_Q_1 dequeue is enabled or disabled:
select owner, name, queue_table, ENQUEUE_ENABLED, DEQUEUE_ENABLED from dba_queues where name = 'AQ_SRVNTFN_TABLE_Q_1' and owner='SYS';
If the dequeue is disabled, re-enable it with the following:
EXECUTE DBMS_AQADM.START_QUEUE (queue_name => 'SYS.AQ_SRVNTFN_TABLE_Q_1');
check again to confirm the change. This stopped the Q processes from growing in their case.
-----------------------------------------------------------------------------------------------
I applied the workaround described above on my databases 19.12 Linux and 19.12 Windows but the Q processes memory leak still happens and the Q processes continue to take more and more memory and do not release the memory. : (
workaround_2021_10_05_dequeue.txt (3.65 KB)

Oracle Support replied to my SR this morning Oct/05/2021 and Oracle Support has finally agreed to file a bug for this issue. I will post more details once I have an update from Oracle Support.

With regard to workaround, I would prefer killing the q processes when they grow instead of setting pga limit to 0. Killing a q process won't crash the database:
pargs -e 28979 | grep -i bg
envp[44]: SKGP_HIDDEN_ARGS=<BG...

I am using a temporary workaround till Oracle has an One-Off Patch.
I posted the workaround on Sep/30, please see above the post with the script "q_processes_memory_leak_temporary_workaround.txt", with this workaround I don't need to stop/start the oracle instance and I don't need to kill the q processes manually in the OS, which in Windows is difficult to do.

Update. I opened the Oracle Support SR on Aug/03/2021, more than 30 days ago, and I had 2 Spin-Off SRs, each time I had to provide more and more logs, and at each Spin-Off SR I had to engage with another support engineer and explain the problem all over again, the first parent SR owner was not following up directly with the Spin-Off SR owners to coordinate and explain the issue, hence we ended up wasting a lot of time, only when the first parent SR owner aggreged to file the bug that another Oracle support engineer took ownership of the parent SR and with his help we finally found a workaround that worked. I wish that person had been involved in the SR from the beginning, we would probably had encountered the workaround a lot sooner.

Marcelo,
Thank you very much for this benevolent effort to share with us your case of of pga overconsumption following the upgrade to 19.12. I really appreciated it.
Just today I shared your issue with my colleague who is going to upgrade one of his bases from 19.8 to 19.12 to solve an unreported bug of LOB TEMPORARY not released by the call to DBMS_LOB.FreeTemporary from the JDBC pool of connection as it used to be in their 12.2 previous release.
I suppose that the final workaround is the one you have already shared in one of your previous interventions.
Best regards
Mohamed Houri

Oracle Support authored the article about this issue, note it is still not visible as it has to go thru Oracle Support internal reviews/QA prior publishing:
Doc ID 2813205.1 - QMON (q00) Pga Memory Growing Very Large After Applied DBRU 19.12 Leading To ORA-04036 And ORA-25226 Errors.

MOS - My Oracle Support Article is now visible.
Doc ID 2813205.1 - QMON (q00) Pga Memory Growing Very Large After Applied DBRU 19.12 Leading To ORA-04036 And ORA-25226 Errors.
Note: I have asked Oracle Support to update the article, it is missing the fact that the workaround must be applied on the CDB and "ALL" PDBs as well, and it is also missing the workaround I posted above to reset the Q processes to free the PGA memory without the need to shutdown/start the db instance.
Nov/02/2021 - Oracle Support removed the Doc ID above, see this other article.
Oracle Support Document 2807234.1 ( AQ PL/SQL Callbacks Are Not Invoked After Applying the 19C DB July RU) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2807234.1

Hello Marcelo.
Thank you so much for publishing this.
We have exactly the same issue, but on 19.10 version. We have open SR with oracle about 7+ month old and without any progress (

Marcelo
MOS - My Oracle Support Article is now visible.
Thanks for the fellow-up
Best regards
Mohamed Houri

Oracle Database 19c Release Update & Release Update Revision July 2021 Known Issues (Doc ID 19202107.9)
AQ PL/SQL Callbacks Are Not Invoked After Applying the 19C DB July RU (DOC ID 2807234.1)

Hi all.
As I mentioned, we having the same issue. I've tried to apply workaround from above notes, but it does not help.
PGA leakage is still exists.
We have 19.10 version and having this issues with only one database. This one is fresh db created in 19c home.
All other were upgraded to 19c from 18c and they are not affected.

The workaround above works fine if you have 19.12 Patchset installed, but if you are experiencing the same issue on another version, 19.10 Patchset, then it might be a different cause, thus requires a different workaround or patch to be installed, you need to open an SR with Oracle Support to investigate further. Oracle Support will eventually recommend that you apply the latest 19.13 Pathset (19-Oct-2021), there are many memory leak fixes in 19.11 / 19.12 / 19.13 , so it is recommended to install the latest Patchset.
If your Oracle Support ticket is idle and there was no satisfactory activity and no progress then you need to call Oracle Support by phone and ask to speak with an Oracle Support Manager to escalate your support ticket. I know this pain, In my case, I had to call support almost every day for about 30 days to get Oracle Support to pay attention to my ticket, I spoke with 3 different Oracle Support Managers, and every time the primary SR Owner created a Spin-Off SR for someone else in Support to investigate the issue I had to attach the same logs to the Spin-Off SR and explain the issue all over again to the Spin-Off SR Owner, we wasted a lot of time with this because Oracle Support Engineers were not taking to each other internally to coordinate, really frustrating and I complained about this to the Oracle Support Managers but little was done to remediate this and progress was quite slow, then finally the Primary SR was assigned to another Support Engineer, his name Rodrigo Martins, and in less than 24 hours Rodrigo found the workaround.
Rodrigo Martins - Principal Technical Support Engineer - Oracle Global Customer Support - Information Integration.
I hope this helps. -Marcelo

>I know this pain
Yeah, that is the pain.
Some piece of my pain :) : We have ticket opened for this problem. It was opened in Feb-2021 ). I'we already have escalation and the escalation of escalation :). No luck for now. Also, I've added information about this thread and documents were mentioned there to the SR.
The response was on Monday this week: "yes looks like thats the same issue .. can you try the solution listed there?". I provided the comments that it does not help. The silence again.

yes, frustrating, but the only way out of it is to call Oracle Support daily and leave a message to the SR Owner and if the SR Owner does not reply in 24 hours and the ticket is severity 2 then call again and ask to talk to a Support Manager, then ask the ticket to be set at priority 1 but to contact you only during business hours, this was what they did for my ticket and eventually my primary SR was assigned to Rodrigo Martins, you can ask your ticket to be reassigned to a different person, call the Support Manager and they will arrange that, try to ask specifically for Rodrigo Martins. You will need to be persistent and call Oracle Support daily and leave messages to the SR Owner and ask to speak with the Oracle Support Manager, you can also request the Support Manager on duty to call you back so you can try to escalate your ticket even further. That was what I did. -Marcelo