oracle_12c_expdp_hang_enqueue_blocker

During routine operation, we noticed that the export took 44 minutes instead of 7 minutes. After I tried to start the expdp manually, I found out that indeed it is slow and it took several minutes to even connect to the DB and start processing data. After further investigation of the sessions, I found the following waiting event:

Waiting event

oracle     34417    SYS           22     8626 Streams AQ: enqueue blocked on low memory Tue 27 Apr 13:13:09  WAITING    ACTIVE  

After internet investigation I have found the following solution and workaround.

The solution is to apply the following patches:

Patch 27634991 may be applied to prevent this problem from occurring.

Bug:17365043 Session hangs on “Streams AQ: enqueue blocked on low memory”

Bug:24560906 High CPU usage for Background q0 processes with fix for

bug 21286665

I didn't do any of these :D

The workaround applies if you have the following flag on:

Flag check

SQL> select shrink_phase_knlasg from X$KNLASG;

SHRINK_PHASE_KNLASG
-------------------
                  1

If that is on, force oracle to finish the job by using the following command:

Force

SQL> Alter system set events 'immediate trace name mman_create_def_request level 6';

System altered.

SQL> select shrink_phase_knlasg from X$KNLASG;

SHRINK_PHASE_KNLASG
-------------------
                  0

That solved the slow expdp for me.

  • oracle_12c_expdp_hang_enqueue_blocker.txt
  • Last modified: 2021/04/27 19:09
  • by andonovj