Oracle errors - ORA-00604 ORA-04031

[Gino Castoldi_2]

Hi,

Svr: Oracle 8.1.7.0 OVO 7.18 NNM 6.1 MCSG 11.14
HPUX 11.11

We received these Oracle errors in our App log files (opcerror for OVO 7.1).
I checked the Oracle log files - listener.log and alert_openview.log and there are no errors
in those log files, just the application one's.

ERROR: Report /etc/opt/OV/share/conf/OpC/mgmt_sv/reports/C/nodes_node_group.sql failed.
02/28/04 13:35:32 ERROR opcdbmsgmv(12722) [chk_sqlcode.scp:95]: Database: ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","TRIGGER$","sga heap","state objects")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","unknown object","sga heap","state objects")(OpC50-15)
Could not connect to database ov_net.
Please look if the database processes are running. (OpC50-2)

Any ideas as to what is wrong and any suggestions on how to resolve this problem?

10 points to any good answer.
TIA, Gino

[Steven E. Protter · Accepted solution]

Not enough shared memory:

bump up shmmax

That can on 11.11 be done without booting the system.

Do understand that shared memory settings greater than 25% total memory(defined as ram plus active swap swapinfo -tam) will be ignored.

I'm pasting in a link for general oracle performance.

http://www1.itrc.hp.com/service/cki/search.do?category=c0&docType=Security&docType=Patch&docType=EngineerNotes&docType=BugReports&docType=Hardware&docType=ReferenceMaterials&docType=ThirdParty&searchString=UPERFKBAN00000726&search.y=8&search.x=28&mode=id&admit=-682735245+1078158934396+28353475&searchCrit=allwords

If swap is maxed out you need more. If you are already past 2.5 times RAM you need more RAM.

There may be a need to collect data and analyze perrmance issues. Attaching these production scripts:

SEP

[Jean-Luc Oudart]

Got the error a few month ago,
this is the shared pool is either too small (our pb) or fragmented

cf .attachment

Regards,
Jean-Luc

[Jean-Luc Oudart]

Also,
find attached a SQL script to tune your shared memory.
it has been very usefull to us.

Regards,
Jean-Luc

[Indira Aramandla]

Hi Gino,

In addition to the above good answers, as you are using Oracle 8.1.7.0, it could be you are encountering bug 1397603 which effects 8.1.7. release.

The workaround is to set the following init.ora parameter to zero: and then restart the database.

_db_handles_cached = 0
Note the leading underscore in the parameter

If setting the parameter resolves the issue, then you were encountering the bug (1397603). The bug is fixed in releases greater than 8.1.7.2 and in Oracle9i. It you apply the patchset 8.1.7.4 you can (and should) remove the above parameter.


Indira A

[Yogeeraj_1]

hi,

also see the following metalink notes:
Note:146599.1 Diagnosing and Resolving Error ORA-04031
Note:62143.1 Understanding and Tuning the Shared Pool in Oracle7, Oracle8, and Oracle8i.

I would also consider applying patchset 8.1.7.4.

hope this helps too!
regards
Yogeeraj

[Hein van den Heuvel]

SEP wrote: "Not enough shared memory:
bump up shmmax"

I say: non sequitur!

The shared memory complained about is strictly within Oracle. So you have to tell Oracle with it's init params that it is ok to use more shared memory (if you have the physical memory available) or you find ways to have Oracle use less shared memory, probably at the cost of CPU (Like reducing cached cursors, disabling cursor space for time, enabling 'similar' cursor sharing,...

If you choose to up the Oracle Shared pool size, then it may or might not be recommended to increase the Unix parameter shmmax. Notably if this is already set in the 1GB or more space there really is no point to worry about that. It is a seperate decision of minor importance. It is not the first action to take as suggested.
This is an Oracle setup problem, so be sure to keep your focus on Oracle, and yes, check the system parameters every now and than to be sure they are in range.

For further analysis and understanding of this problem you need to run tools like Oracle Statspack and/or utl[be]stat (for old versions). Don't waste your time with Unix level tools like sar, and vgdisplay. Save those for a more deserving occasion.

Cheers,
Hein.

[Gino Castoldi_2]

Hi,

Thank you for your replies.

Yogeeraj:
I do not have access to metalink, can you post those documents to here?

10 points to any good answer.
TIA, Gino

[Jean-Luc Oudart]

If you are an Oracle customer you should have access to Metalink.
You just need to register.

Metalink is 1st channel to log call (tar) with Oracle.

Regards,
Jean-Luc

[Gino Castoldi_2]

Hi,

Jean-Luc:
We are using Oracle because we have OVO and
with OVO you do not get direct Oracle support
or access to Metalink.

10 points to any good answer.
TIA, Gino

[Surya Narayana]

Also, please lookup documentation on the 'chatr' command. It deals with the size of each additional memory that the OS allocates.
I had to use this a while back to resolve the ORA-4031 error.

[Yogeeraj_1]

hi,

here are the notes:
146599.1 - Diagnosing and Resolving Error ORA-04031

regards
Yogeeraj

[Yogeeraj_1]

62143.1 - Understanding and Tuning the Shared Pool

[Hein van den Heuvel]

Gino,

Also be sure to read up carefully the replies in topic:

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=349228

The person there has exactly the same underlying '04031' error, but with a different higher level error.

Actually... it is exactly the same person!

:-)

Hein.