Description

Various server and ue_nn daemon logs have errors such as this:

Oracle error: update_cursor13 z30
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool"

over a period of 20 minutes.

Looking at the /exlibris/app/oracle/diag/rdbms/aleph22/aleph22/trace/alert_aleph22.log file, we see an occurrence of the "ORA-04031 heap dump being written to trace file" error message, followed by a 20-minute period in which about 100 "unable to allocate 32 bytes of shared memory" messages are written. 

These are some excerpts from the log:
Sat Nov 29 15:16:34 2014
ORA-04031 heap dump being written to trace file /exlibris/app/oracle/diag/rdbms/aleph22/aleph22/trace/aleph22_ora_9052.trc
Errors in file /exlibris/app/oracle/diag/rdbms/aleph22/aleph22/trace/aleph22_ora_9052.trc (incident=150473):
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select /*+ index(z410 z410_i...","SQLA","tmp")
...
[about 100 occurrences of "unable to allocate 32 bytes"]
...
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select f.file#, f.block#, f....","SQLA","tmp")
Sat Nov 29 15:35:10 2014

<that's the last ORA-04031 error message>

Resolution

As shown in the analysis in Additional Information below, it seems that the updates which showed errors in the batch job logs and the ue_nn daemons *were* eventually successful.

This is a known Oracle bug.  It's described at https://support.oracle.com/epmos/fac...ylh6jwz_73#REF .

The workaround is setting "_enable_shared_pool_durations = false" in the init file and restarting Oracle.

Additional Information

Looking at the odn01 run_e_21.29644 log, we see that all of the error messages occurred during the processing of two doc records: 
HANDLING DOC NO. - SYS01.007587357 2017-03-14 14:59:01 
... 
HANDLING DOC NO. - SYS01.007861825 2017-03-14 15:02:41 
The above are the only HANDLING occurrences for these bib records on 2017-03-14. 

Checking the sys00 z00p with util f/4 I see this: 
Doc Number : 007587357 
Timestamp : 201703141959074 
Status : UPDATED 
and 
Doc Number : 007861825 
Timestamp : 201703142002425 
Status : UPDATED 
This indicates to me that these updates of the z00p by ue_21 *were* eventually successful. 
 

• Article last edited: 12-Mar-2017