Operating System - HP-UX
1748000 Members
4415 Online
108756 Solutions
New Discussion юеВ

Oracle errors - ORA-00604 ORA-04031

 
SOLVED
Go to solution
Gino Castoldi_2
Honored Contributor

Oracle errors - ORA-00604 ORA-04031

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
13 REPLIES 13
Steven E. Protter
Exalted Contributor
Solution

Re: Oracle errors - ORA-00604 ORA-04031

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Jean-Luc Oudart
Honored Contributor

Re: Oracle errors - ORA-00604 ORA-04031

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
fiat lux
Jean-Luc Oudart
Honored Contributor

Re: Oracle errors - ORA-00604 ORA-04031

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

Regards,
Jean-Luc
fiat lux
Indira Aramandla
Honored Contributor

Re: Oracle errors - ORA-00604 ORA-04031

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
Never give up, Keep Trying
Yogeeraj_1
Honored Contributor

Re: Oracle errors - ORA-00604 ORA-04031

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Hein van den Heuvel
Honored Contributor

Re: Oracle errors - ORA-00604 ORA-04031


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
Honored Contributor

Re: Oracle errors - ORA-00604 ORA-04031

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
Honored Contributor

Re: Oracle errors - ORA-00604 ORA-04031

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
fiat lux
Gino Castoldi_2
Honored Contributor

Re: Oracle errors - ORA-00604 ORA-04031

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