karthik_reddy215
asked on
ORA-00604: ORA-04031
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4112 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","library cache")
NAME TYPE VALUE
--------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 64M
large_pool_size big integer 800M
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 27682406
shared_pool_size big integer 512M
streams_pool_size big integer 48M
ORA-04031: unable to allocate 4112 bytes of shared memory ("shared pool","unknown object","sga heap(2,0)","library cache")
NAME TYPE VALUE
--------------------------
buffer_pool_keep string
buffer_pool_recycle string
global_context_pool_size string
java_pool_size big integer 64M
large_pool_size big integer 800M
olap_page_pool_size big integer 0
shared_pool_reserved_size big integer 27682406
shared_pool_size big integer 512M
streams_pool_size big integer 48M
512M isn't a lot of memory for Oracle to work with.
If the above link doesn't help, give Oracle more memory or tune the statement that caused the error.
If the above link doesn't help, give Oracle more memory or tune the statement that caused the error.
Hi,
Here's my grain of sand...
Looks to me l you have changed SGA setup... Have you checked the total size you have tried to allocate ? Do you have enough memory available on your machine ?...
If you have manually allocated your memory 'segments' in the SGA, switch to automatic management. Just set a sga_max_size and a sga_target to the max you can give to oracle on your system and let it do its job.
P
Here's my grain of sand...
Looks to me l you have changed SGA setup... Have you checked the total size you have tried to allocate ? Do you have enough memory available on your machine ?...
If you have manually allocated your memory 'segments' in the SGA, switch to automatic management. Just set a sga_max_size and a sga_target to the max you can give to oracle on your system and let it do its job.
P
Check this:
http://thedailyreviewer.com/dbsoftware/view/ora-00604-ora-04031-102377285
I hope can help you!
http://thedailyreviewer.com/dbsoftware/view/ora-00604-ora-04031-102377285
I hope can help you!
ASKER
Piloute: here are my parameters
NAME TYPE VALUE
--------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4000M
sga_target big integer 4000M
this is auto right..
NAME TYPE VALUE
--------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 4000M
sga_target big integer 4000M
this is auto right..
>>this is auto right..
Correct. To fully use Automatic Memory Management (AMM) you should set all pool related parameters to 0. This allows Oracle to reallocate memory as needed.
That said: I've never confirmed this and just going by what Oracle told me but setting the pools along with AMM is setting the 'minimum' values for those pools.
So the 512M for shared pool is supposed to be the minimum value.
Since you ran out of shared pool, AMM must not have been able to reallocate enough from another pool. With 4000M allowed, something was using it all.
Are you using dbConsole or Grid Control? I would look at the memory stats to see how it was allocated around that time to see what might have caused the error.
It might have been a rogue 'bad' query of some type. If you continue to receive this error you'll probably need to increase SGA_MAX_SIZE.
Correct. To fully use Automatic Memory Management (AMM) you should set all pool related parameters to 0. This allows Oracle to reallocate memory as needed.
That said: I've never confirmed this and just going by what Oracle told me but setting the pools along with AMM is setting the 'minimum' values for those pools.
So the 512M for shared pool is supposed to be the minimum value.
Since you ran out of shared pool, AMM must not have been able to reallocate enough from another pool. With 4000M allowed, something was using it all.
Are you using dbConsole or Grid Control? I would look at the memory stats to see how it was allocated around that time to see what might have caused the error.
It might have been a rogue 'bad' query of some type. If you continue to receive this error you'll probably need to increase SGA_MAX_SIZE.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>How much memory (RAM) have you on your VM ?
Did I miss something? Where did you get this was a VM?
Did I miss something? Where did you get this was a VM?
ASKER
flushing and restart the data has worked.
Thanks every one.
Thanks every one.
flushing may eliminate the current symptom but will likely not 'solve' the problem.
@slightwv
My fault. I wans working on another issue related to a VM at the same moment...
@karthik
slightwv is right. Your solution is really temporary if you need a 24/24 database... You should investigate, now you got time....
Cheers,
P
My fault. I wans working on another issue related to a VM at the same moment...
@karthik
slightwv is right. Your solution is really temporary if you need a 24/24 database... You should investigate, now you got time....
Cheers,
P
http://www.orafaq.com/forum/t/56722/2/