Troubleshooting Oracle GoldenGate Extract Process Abending Due to ORA-04031

Issue Overview

Oracle GoldenGate (OGG) Extract process encountered an error and abended due to an ORA-04031 error. The error message indicates an issue with shared memory allocation in the Streams Pool:

2024-06-11 17:36:48  ERROR   OGG-00662  OCI Error ORA-04031: unable to allocate 8216 bytes of shared memory ("streams pool","unknown object","Logminer LCR c","krvurtla
:l2")
 (status = 4031).

Source Context :
  SourceModule            : [er.redo.ora.IXFormatter]
  SourceID                : [er/redo/oracle/IXFormatter.cpp]
  SourceMethod            : [getResult]
  SourceLine              : [761]
  ThreadBacktrace         : [15] elements
                          : [/u01/app/oracle/product/19c/ogg/libgglog.so(CMessageContext::AddThreadContext())]
                          : [/u01/app/oracle/product/19c/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
                          : [/u01/app/oracle/product/19c/ogg/libgglog.so(_MSG_(CSourceContext*, int, CMessageFactory::MessageDisposition))]
                          : [/u01/app/oracle/product/19c/ogg/extract()]
                          : [/u01/app/oracle/product/19c/ogg/extract(RedoIE::readLCR(ggs::gglib::gglcr::CommonLCR**, long&, bool&))]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::er::OraTranLogDataSource::readLCR(ggs::gglib::gglcr::CommonLCR**, long&, bool&))]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::er::ExtractContext::processExtractLoop())]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::er::ExtractContext::run())]
                          : [/u01/app/oracle/product/19c/ogg/extract()]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::gglib::MultiThreading::MainThread::ExecMain())]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
                          : [/u01/app/oracle/product/19c/ogg/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
                          : [/u01/app/oracle/product/19c/ogg/extract(main)]
                          : [/lib64/libc.so.6(__libc_start_main)]
                          : [/u01/app/oracle/product/19c/ogg/extract()]

2024-06-11 17:36:48  ERROR   OGG-02078  Extract encountered a fatal error in a processing thread and is abending.

Root Cause

The ORA-04031 error occurs when the Oracle database is unable to allocate memory from the shared memory area. In this case, the streams pool size was set to 0, which prevented LogMiner from obtaining sufficient memory for processing redo logs.

GGSCI (ggate1 as c##ggadmin@ESTCDB/CDB$ROOT) 33> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     ABENDED     EXTTEST     00:07:45      00:01:20    
REPLICAT    RUNNING     REPT        00:00:00      00:00:05    


GGSCI (ggate1 as c##ggadmin@ESTCDB/CDB$ROOT) 34

Error Logs Analysis

• The GoldenGate Extract process (EXTTEST) failed due to an issue in redo log processing.
• LogMiner could not allocate the required memory from the Streams Pool.
• The show parameter sga command revealed that streams_pool_size was set to 0.

Solution: Increase Streams Pool Size

To resolve the issue, the Streams Pool Size needs to be allocated with sufficient memory.

Steps to Fix ORA-04031 for OGG Extract Process

1. Check Current SGA Parameters:

SQL> show parameter sga;

1. Ensure that SGA_TARGET and SGA_MAX_SIZE are appropriately sized.
2. Check Streams Pool Size: SQL> show parameter streams;If the value is 0, LogMiner will struggle to allocate memory.

1. Increase Streams Pool Size:
   • SQL> alter system set streams_pool_size=2g;This command dynamically allocates 2GB for the Streams Pool.
2. Verify the Change:
   • SQL> show parameter streams;Ensure that the streams_pool_size now reflects the updated value.
3. Restart the Extract Process in GoldenGate:
   • GGSCI> start EXTTESTAfter allocating memory, restart the Extract process.
4. Check Extract Process Status:
   • GGSCI> info allEnsure that the Extract process is now running without issues.

SQL> show parameter sga

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga	     boolean	 FALSE
lock_sga			     boolean	 FALSE
pre_page_sga			     boolean	 TRUE
sga_max_size			     big integer 3008M
sga_min_size			     big integer 0
sga_target			     big integer 2912M
unified_audit_sga_queue_size	     integer	 1048576
SQL> show parameter streams

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size		     big integer 0
SQL> alter system set streams_pool_size=2g;

System altered.

SQL> show parameter streams

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size		     big integer 2G
SQL> 

Best Practices to Prevent ORA-04031 in Oracle GoldenGate

• Allocate Sufficient Streams Pool Memory: Keep at least 1-2GB allocated, depending on workload

Conclusion

The ORA-04031 error was caused due to insufficient Streams Pool memory allocation, preventing LogMiner from processing redo logs efficiently. By increasing the streams_pool_size, the issue was resolved, and the GoldenGate Extract process resumed normal operation.