-
ORA-04030 while running jobs through dba_jobs
Hello All,
I am getting ORA-04030 errors daily with jobs that are scheduled to run through dba_jobs using dbms_sql. The same jobs complete without errors when they are run manually.
I have tried to work with Oracle but I have not got any success , even I tried lots of things ……(using Oracle notes and my own experience) but without success, I hope you guys can help me to resolve this error….
Here are the details of my server .
OS AIX5L Based Systems (64-bit / Memory 16GB
Oracle - Oracle Server - Enterprise Edition 10.1.0.5
Oracle user has unlimited ulimit
/optware/oracle$ ulimit -Ha
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) unlimited
Some of the important parameters from init.ora
resource_limit = TRUE
sga_max_size = 4194304000
__shared_pool_size = 1895825408
shared_pool_size = 1895825408
__large_pool_size = 318767104
large_pool_size = 318767104
__java_pool_size = 419430400
java_pool_size = 419430400
shared_pool_reserved_size= 50677721
sga_target = 4194304000
db_block_size = 8192
__db_cache_size = 1543503872
db_cache_size = 838860800
compatible = 10.1.0.4.0
job_queue_processes = 25
cursor_sharing = SIMILAR
parallel_min_servers = 0
parallel_max_servers = 5
hash_area_size = 1310720
sort_area_retained_size = 0
open_cursors = 500
pga_aggregate_target = 3145728000
Please let me know if you need any more details.
Santo
-
1- You probably already did it but just to be sure, don't forget to check "Note 233869.1 Diagnosing and Resolving ORA-4030 errors".
2- Be sure you set ALL unlimited values for both -ha and -sa.
3- If your offending code relies in bulk collect, try the code without it. In a 10g environment -even after working a TAR for two month- our only practical solution was to get rid of bulk collect to avoid the error.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Originally Posted by PAVB
1- You probably already did it but just to be sure, don't forget to check "Note 233869.1 Diagnosing and Resolving ORA-4030 errors".
2- Be sure you set ALL unlimited values for both -ha and -sa.
3- If your offending code relies in bulk collect, try the code without it. In a 10g environment -even after working a TAR for two month- our only practical solution was to get rid of bulk collect to avoid the error.
PAVB
Thanks for your prompt reply.
1- Yes I went through 233869.1 Note id, Made some changes also but no success, “There can be issues with other OS parameter settings such as maxuproc” , I am not sure how oracle link 4030 with Maxuproc process, Oracle not able to justified this….anyway my current setting for this parameter is 2048
2- Hard ulimit and soft ulimit shows different values but our SA say that this is maximum and we cant go beyond this, Everything is set unlimited? Any suggestion on this from your side?
abc:/optware/oracle$ ulimit -Ha
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
coredump(blocks) unlimited
nofiles(descriptors) unlimited
abc:/optware/oracle$ ulimit -sa
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) 16777216
stack(kbytes) 4194304
memory(kbytes) 16777216
coredump(blocks) 2097151
nofiles(descriptors) unlimited
3) I had open STR number of times, every time I got different answer....but finally I got only one answer.....apply latest patches....I did that also.......but still problem...is same....
-
1- In our case we set all hard and soft values to unlimited, which solved the issue on AIX/9i... it didn't on Linux/10g
2- To work around the issue on Linux/10g we made changes to the application getting rid of bulk collect operations.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
what is the value of WORKAREA_SIZE_POLICY?..
also PS the min values assigned sounds too high and you also have a sga_max/sga_target specified.. so memory left for PGA is practically too low though you have set it to ~3G.
Suggestion: You any way have turned automatic sizing of different pools by setting SGA_TARGET.. i suggest you lower the values for Shared_Pool to about 400M, db_cache_size to 400M, Large_Pool to about 200M..
Rgds
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|