Home » RDBMS Server » Server Utilities » ORA-04031 error with EXPDP (11.2.0.4, Oracle Linux 7)
ORA-04031 error with EXPDP [message #676629] Tue, 25 June 2019 13:26 Go to next message
rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON
Member
Hi all, I am getting an error ORA-04031 when doing an EXPDP:

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT job_id FROM v$datapum...","SQLA","tmp")

It is just a straight-forward export of the full Db, no fancy SQL, etc. Here are some relevant parameters:

[oracle@proddb trace]$ free
total used free shared buff/cache available
Mem: 65712740 2862632 37481512 8279164 25368596 54251229
Swap: 37748728 0 377487280

memory_max_target big integer 20G
memory_target big integer 0
streams_pool_size big integer 10G
shared_pool_reserved_size big integer 10066329
shared_pool_size big integer 192M
large_pool_size big integer 0
use_large_pages string TRUE


When I asked Oracle support about increasing shared_pool_size, he told me "192M is the minimum allocation, it will still be managed by automatic memory management (AMM) above that value. But if you set it to zero, AMM has more flexibility controlling it."

Is that correct? They also asked me to flush the shared pool and ideally restart the instance, but that is just a band-aid, not a solution. I recently converted this Db to use AMM by setting memory_max_target and resetting the SGA parameters, but I did not touch any shared_* or large_* parameters. Any help would be greatly appreciated, thanks.
Re: ORA-04031 error with EXPDP [message #676630 is a reply to message #676629] Tue, 25 June 2019 14:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As far as I remember there is a bug in 11gR2 using at the same time large pages and memory target.

Re: ORA-04031 error with EXPDP [message #676631 is a reply to message #676630] Tue, 25 June 2019 15:03 Go to previous messageGo to next message
rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON
Member
Thanks Michel. so for hugepages, I see that Oracle recommends automatic shared memory management (ASMM). But that's a 10g feature, whereas AMM is a 11g feature? Sorry I cant quite make out the difference between the two, database-parameter-wise. Should I disable hugepages?
Re: ORA-04031 error with EXPDP [message #676632 is a reply to message #676631] Wed, 26 June 2019 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have the choice between (roughly):
- use_large_pages = false, memory_max_target/memory_target != 0, pga_aggregate_target = 0 , sga_target/sga_max_size = 0
- use_large_pages = true, memory_max_target/memory_target = 0, pga_aggregate_target != 0, sga_target/sga_max_size != 0

Re: ORA-04031 error with EXPDP [message #676634 is a reply to message #676629] Wed, 26 June 2019 02:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Apart from the large pages issue, you are severely restricting the memory available for the shared pool by allocating half of your memory target to the streams pool. Why would you not set this to zero and let ASMM manage it? I cannot imagine why 10GB would be needed, and there is no advantage in making it bigger than necessary.
Re: ORA-04031 error with EXPDP [message #676640 is a reply to message #676634] Wed, 26 June 2019 08:19 Go to previous message
rcct
Messages: 36
Registered: May 2013
Location: Ottawa, ON
Member
Thanks Michel and John. I set the streams pool to that large value on Oracle support recommendations, but I don't have much confidence in this particular technician. He recommended that based on AWR report which showed the streams pool getting full. My server is also not using hugepages, based on the following:

[oracle@proddb trace]$ grep Huge /proc/meminfo
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB


I will proceed with option 1 of Michel's reply, which I believe enables ASMM. What value would you recommend for shared_pool_reserved_size and shared_pool_size for ASMM? Currently, they are set as:

shared_pool_reserved_size big integer 10066329
shared_pool_size big integer 192M
Previous Topic: Error_Stack...ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
Next Topic: datapump Export taking long time(HUNG)
Goto Forum:
  


Current Time: Thu Mar 28 14:57:18 CDT 2024