Sometimes You can get ” OGG-00662 ORA-04031: unable to allocate bytes of shared memory (“”,””,””,””) ” in Goldengate during start Extract Process.
OGG-00662 ORA-04031: unable to allocate bytes of shared memory
Details of error are in the Report file as follows. You can review the report file using view report EXTRACT_NAME command.
ORA-04031: unable to allocate nn bytes of shared memory
Cause: More shared memory is needed than was allocated in the shared pool.
Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size". If the large pool is out of memory, increase the INIT.ORA parameter "large_pool_size".
: [/ggateb01/goldengate/product/19.1.0.0.4/extract(IXFormatter::FormatterThread(void*))] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadAr gs*))] : [/lib64/libpthread.so.0()] : [/lib64/libc.so.6(clone)] 2021-01-22 22:16:05 ERROR OGG-00662 OCI Error ORA-04031: unable to allocate bytes of shared memory ("","","","") (status = 4031). Source Context : SourceModule : [er.redo.ora.IXFormatter] SourceID : [er/redo/oracle/IXFormatter.cpp] SourceMethod : [getResult] SourceLine : [761] ThreadBacktrace : [15] elements : [/ggateb01/goldengate/product/19.1.0.0.4/libgglog.so(CMessageContext::AddThreadContext())] : [/ggateb01/goldengate/product/19.1.0.0.4/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))] : [/ggateb01/goldengate/product/19.1.0.0.4/libgglog.so(_MSG_(CSourceContext*, int, CMessageFactory::MessageDisposition))] : [/ggateb01/goldengate/product/19.1.0.0.4/extract()] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(RedoIE::readLCR(ggs::gglib::gglcr::CommonLCR**, long&, bool&))] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(ggs::er::OraTranLogDataSource::readLCR(ggs::gglib::gglcr::CommonLCR**, long&, bool&))] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(ggs::er::ExtractContext::processExtractLoop())] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(ggs::er::ExtractContext::run())] : [/ggateb01/goldengate/product/19.1.0.0.4/extract()] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(ggs::gglib::MultiThreading::MainThread::ExecMain())] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadAr gs*))] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(ggs::gglib::MultiThreading::MainThread::Run(int, char**))] : [/ggateb01/goldengate/product/19.1.0.0.4/extract(main)] : [/lib64/libc.so.6(__libc_start_main)] : [/ggateb01/goldengate/product/19.1.0.0.4/extract()] 2021-01-22 22:16:06 ERROR OGG-02078 Extract encountered a fatal error in a processing thread and is abending.
ORA-04031: unable to allocate bytes of shared memory (“”,””,””,””)
This error is related with the Low setting for streams_pool_size and shared_pool size.
You can flush the shared pool to solve this error.
SQL> alter system flush shared_pool;
To solve this error, Increase the streams_pool_size and shared_pool_size of the database from the current size and restart the database to resolve the issue.
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 137573171
shared_pool_size big integer 2624M
shared_server_sessions integer
shared_servers integer 1
SQL>
SQL>
SQL> show parameter stream
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL>
SQL>
SQL>
SQL> alter system set streams_pool_size=4G scope=spfile sid='*';
System altered.
SQL> alter system set shared_pool_size=8G scope=spfile sid='*';
System altered.
SQL>
Now Restart database to see if the problem is solved or not.
SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;