| Hi All,
I’m using Oracle 10g on Win Xp system SP2. I have the following
entries in spfile.ora file:
processes = 100
sessions = 1105
__shared_pool_size = 117440512
shared_pool_size = 33554432
__large_pool_size = 20971520
large_pool_size = 4194304
__java_pool_size = 20971520
java_pool_size = 20971520
sga_target = 167772160
control_files =
D:\ORACLE\PRODUCT\10.1.0\DB_1\FISDB10G\CONTROL01.CTL,
D:\ORACLE\PRODUCT\10.1.0\DB_1\FISDB10G\CONTROL02.CTL,
D:\ORACLE\PRODUCT\10.1.0\DB_1\FISDB10G\CONTROL03.CTL
db_block_size = 8192
__db_cache_size = 4194304
compatible = 10.1.0.2.0
db_file_multiblock_read_count= 16
db_recovery_file_dest =
D:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size= 2147483648
undo_management = AUTO
undo_tablespace = UNDOTBS1
remote_login_passwordfile= EXCLUSIVE
db_domain =
global_names = FALSE
dispatchers = (protocol=TCP)
utl_file_dir = d:\temp
job_queue_processes = 10
background_dump_dest =
D:\ORACLE\PRODUCT\10.1.0\ADMIN\FISDB10G\BDUMP
user_dump_dest =
D:\ORACLE\PRODUCT\10.1.0\ADMIN\FISDB10G\UDUMP
core_dump_dest =
D:\ORACLE\PRODUCT\10.1.0\ADMIN\FISDB10G\CDUMP
sort_area_size = 65536
db_name = fisdb10g
open_cursors = 300
pga_aggregate_target = 25165824
I’m getting the following error frequently. And around 35 programmers
will connect daily.
ORA-00604: error occurred at recursive SQL level 1
ORA-12414: internal LBAC error: zllcsi:OCIStmtExecute
Error: ORA-04031: unable to allocate 64088 bytes of shared memory
(“large pool”,”unknown object”,”session heap”,”kksfal:1:frame
segment”)
ORA-06512: at “LBACSYS.LBAC_CACHE”, line 99
ORA-06512: at “LBACSYS.LBAC_EVENTS”, line 74
ORA-06512: at line 2
Can anybody help me why this error is coming? |
|
|
|
Yes, it’s what the error message explicitly says. You’ve run out of space in
the large pool, which is apparently just 4MB big. Give it some more memory. |
|
|
|
Your large pool is only 4 meg; increase its size and you should
eliminate the problem. Also, why are you using an 8k block size and a
db_file_multiblock_read_count of 16 on Windows? It should be 8. |
|
|
|
Actually, on Windows, a 16K block size is my preference. Point is, he can
use any block size he likes on Windows, because NTFS does direct I/O out of
the box.
Or are you saying the multiblock read count should be 8Kx8=64K, hence
16Kx4=64K etc etc etc?
Besides which, I just tried this:
SQL> show parameter db_file_multi
NAME TYPE VALUE
———————————— ———–
db_file_multiblock_read_count integer 16
SQL> alter system set db_file_multiblock_read_count=512 scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> show parameter db_file_multi
NAME TYPE VALUE
———————————— ———–
db_file_multiblock_read_count integer 128
Which indicates that 128x8k (happens to be an 8k system) is do-able: 1MB
seems perfectly feasible to me.
I’m a bit at a loss therefore to understand the “It should be 8” comment.
Regards
HJR |
|
|
|
|
|
I don’t quite understand your comment, and although David seems to agree,
could you please elaborate a bit more:
SGA_TARGET is set, so the pool should be dynamically managed and the 4MB figure
is just the minimum. Oracle realised already that it needed 5 times the number:
__large_pool_size = 20971520
I haven’t researched Automatic Shared Memory Tuning sofar, but I’d expect that
the, as the New Features Guide puts them,’self tuning algorithms’ would prevent
such a situation from happing.
What am I missing? |
|
|
|
Care to explain second half of your post a bit more? |
|
|
|
> I don’t quite understand your comment, and although David seems to agree,
> could you please elaborate a bit more:
>
> SGA_TARGET is set, so the pool should be dynamically managed and the 4MB
> figure is just the minimum.And that minimum is clearly insufficient…
> Oracle realised already that it needed 5 times
> the number:
> __large_pool_size = 20971520
…..because Oracle’s had to increase it… but that still clearly isn’t
enough, given the error message. (One presumes that it cannot dynamically
add more, because there is insufficient target).
OK. Maybe I should have posted to increase the target. But I would also
suggest increasing the minimum. 4MB is about as small as it can go, after
all.
> I haven’t researched Automatic Shared Memory Tuning sofar, but I’d expect
> that the, as the New Features Guide puts them,’self tuning algorithms’
> would prevent such a situation from happing.
I wouldn’t bank on that in any Oracle first release.
> What am I missing?
Probably not a lot. He’s just running out of memory, I guess. You can only
dynamically shuffle so far… once all the other pools have pinched their
bit, and it’s in use, you can’t start spooning out extra dollops to the
large pool. |
|
|
|
In 10g on Windows, unless specifically instructed otherwise, DBCA makes a
block size of 8 and a db_file_multiblock_read_count of 16. He probably used
DBCA. |
|
|
|
Apparently my previous employer was using less than optimal hardware
with their Windows NT/2000/XP solutions and I found, by experience,
that 64K was the best I could expect (8k block size * 8k blocks read).
If that is incorrect then disregard the rest of my post and I shall
stand corrected. |
|
|
|
What is in your alert log?
Is it XP Pro or Home?
Have you checked metalink? |
|
|
|
Hi,
Thanks for response,
Yes i have changed the large_pool_size to larger size.
java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=80971520
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=100
*.remote_login_passwordfile=’EXCLUSIVE’
*.sessions=1105
*.sga_target=167772160
*.shared_pool_size=41457280
*.sort_area_size=65536
But, After changing, if restarte the database. Now, it is sayiing…
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Env varaibles, ORA_HOME and ORACLE_SID are set.
And one more thing is it is createing log file in
\oracle\product\10.1.0\db_1\database directory. The file contents are
like …
An Unhandled Exception Occurred,
Exception Number : 0xc0000005
Exception Address : 0x6130975c |
|
|
|
Hi
If i connect from sqlplus prompt, it is working fine. but if Java is
trying to connect, then it is saying following error…
(java.sql.SQLException: ORA-00604: error occurred at recursive SQL
level 3
ORA-04031: unable to allocate 4096 bytes of shared memory (“shared
pool”,”select
/*+ rule */ bucket_cn…”,”Typecheck heap”,”kgghteInit”)
What might be the problem? |
|
|
|
Your shared pool is ridiculously small. Also, by setting explicitly
the shared pool, you won’t be using automatic pool management anymore.
You need to set db_cache_size, and remove large_pool_size,
shared_pool_size, java_pool_size and db_block_buffers. |
|
|
|
Unfortunately, that advice is incorrect on a couple of levels.
For a start, if you manually set pool sizes, then automatic pool management
will still happen, but the manual settings are taken as minimum sizes.
Above those minima, however, dynamic and automatic re-allocation can still
happen.
Secondly, the advice to go totally manually or totally automatic is not
actually bad, but to remove the manual settings for large_pool,
shared_pool, java_pool and db_block_buffers, only then to manually set
db_cache_size is not the way to do it.
DB_cache_size is the manual mechanism to set the size (or the minimum size)
of the buffer cache in 9i and above.
What you should actually set is a realistic SGA_MAX_SIZE, and SGA_TARGET.
Those are the parameters which say how big your SGA as a whole should be,
within which automatic allocation of pools can take place. There is an
interesting interaction between the two parameters, and that was discussed
in a thread here just three or four days ago.
So: I second Sybrand’s advice to remove the daft minimum sizes you have
currently set. You SGA_TARGET is currently about 160MB, I think. That is
not hopelessly inadequate, and it very much depends on what you are doing
with this database, but I would certainly bump that up to over 200MB.
HJR |
|
|
|
Personally … and I can’t remember why … I’d use oradim to drop the
Oracle service and recreate it. Every time I’ve had this problem the
issue has been one of Window’s services and this fixes it. |
|
