topic Re: What is the cause of these Oracle errors? ORA-03113, etc. in Operating System - HP-UX

What is the cause of these Oracle errors? ORA-03113, etc.

Gino Castoldi_2 — Tue, 06 Jan 2004 07:40:11 GMT

Hi,

HP-UX 11.0 MC/SG 11.09 VPO 6.14 NNM 6.2
4GB memory, Oracle 8.1.7.3.

We recently received these errors that caused
the app OVO to hangup. I had to stop/restart OVO to fix the problem. Here are some errors from the app log file.

01/02/04 05:39:03 opcmsgm (Message Manager)(15589) [chk_sqlcode.scp:95]:
Database: ORA-03113: end-of-file on communication channel
(OpC50-15)
Retry. (OpC51-22)
01/02/04 05:53:32 ERROR opcactm (Action Manager)(15588) [chk_sqlcode.scp:95]:

Database: ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4176 bytes of shared memory ("shared
pool","OPC_HIST_MESSAGES","sga heap","library cache")
(OpC50-15)
Cannot set action status in database. (OpC40-520)
01/02/04 05:53:32 opcragt (OpC remote agent)(6651) [chk_sqlcode.scp:95]:
Database: ORA-03113: end-of-file on communication channel
(OpC50-15)
Retry. (OpC51-22)
Database: ORA-03113: end-of-file on communication channel (OpC50-15)
Could not connect to database ov_net.
Please look if the database processes are running. (OpC50-2)
Database: ORA-01012: not logged on (OpC50-15)
Retry. (OpC51-22)
Database: ORA-01001: invalid cursor (OpC50-15)
Retry. (OpC51-22)

What was the cause of this problem?
Would some OS/Oracle tuning help?

10 points to any good answer.
TIA, Gino

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Gaurav Swarup Sharma — Tue, 06 Jan 2004 07:57:36 GMT

Hi,

You need to upgrade your memory or need to increase SGA parameter

Thanks

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Cheryl Griffin — Tue, 06 Jan 2004 08:02:55 GMT

Gino,
I'd also pursue this with Oracle. It looks like there might be an Oracle patch for this.

http://download-east.oracle.com/otn_hosted_doc/designer/doc_library/des9i_rel2_doc/CDOC72/rep_relnotes/rn_rep_9i.htm

Certification against Oracle 8.1.7 and Oracle9i
Oracle SCM is certified against Oracle 8.1.7 and Oracle9i. For Oracle9i, if patch set 9.0.1.3 is not available, then 9.0.1.2 can be used provided the following one off patches are applied, taking care to download the patch specific to your particular platform:

Patch 2103726. ORA-03113 WITH CONNECT BY QUERY ON VIEW USING A FUNCTION

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Gino Castoldi_2 — Tue, 06 Jan 2004 08:19:14 GMT

Hi,

Gaurav:
The system itself has enough free memory, vmstat shows 440MB of free memory.
Is the SGA parameter in the initsid.ora file?
(shared_pool_size = xxxxxxxx)?

Cheryl:
We have Oracle patch 8.1.7.3 installed. Are you recommending that we install Oracle patch 8.1.7.4? Shouldn't we look at tuning if
we already have patch 8.1.7.3 installed?

10 points to any good answer.
TIA, Gino

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Rita C Workman — Tue, 06 Jan 2004 08:19:38 GMT

The bad thing about a number of Oracle error messages is that they are quite generic. They can be the result of any number of conditions, thus making fixing them a 'real challenge'.

As was mentioned above, check your kernel parms to ensure your settings are solid; check to make sure your properly patched for the versions (O/S,Oracle) you are at. Once you've ruled that these are good, now the fun really begins.
I would next relink my binaries-so that is ruled out as an issue-and retry. If that doesn't work on to the next thing to try.

Do you have any trace files that might give you some indications as to what is causing Oracle to trip and not come up. I had it after an install turn out to be some Oracle file(s) got hosed and a restore fixed it for me. It was a shot in the dark, but it worked. But sometimes you get little tidbits of ideas from those trace files as to what may be something else to check.

Rgrds,
Rita

...with some things in Oracle-it's just a matter of keep digging around the problem till you hit finally hit with your shovel what you couldn't see with your eyes...

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Gino Castoldi_2 — Tue, 06 Jan 2004 08:22:08 GMT

Hi,

Rita:
After I installed Oracle patch 8.1.7.3 I had
to relink all of the binaries to make it work.

10 points to any good answers.
TIA, Gino

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Mark Grant — Tue, 06 Jan 2004 08:31:33 GMT

I would check the maximum shared memory segment size in the kernel. It may not be relevant but it sounds like it.

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Gino Castoldi_2 — Tue, 06 Jan 2004 08:35:18 GMT

Hi,

Mark:
The shmmax is set to 1GB.
(kmtune -q) shmmax 0X40000000
Should this be increased?

10 points to any good answer.
TIA, Gino

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Cheryl Griffin — Tue, 06 Jan 2004 08:38:33 GMT

I would not necessarily rush to upgrade.

Check out the 8.1.7 Release 3 kernel tuning at http://download-west.oracle.com/docs/pdf/A85470_01.pdf
Table 2â 1 UNIX System Configuration Summary

It covers semaphores and shared memory.

The document requires a signup, but signup is fr

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Gino Castoldi_2 — Tue, 06 Jan 2004 08:53:42 GMT

Hi,

Cheryl:
I agree, we are not going to upgrade unless
it can be proven that we need to.
Doing some tuning looks like the best way to proceed. The hard part is that tuning Oracle
is a complicated process.

10 points to any good answer.
TIA, Gino

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Mark Grant — Tue, 06 Jan 2004 08:58:27 GMT

Gino,

I would imagine shmmax would need to be increased if the amount of shared memory being requested exceeds the 1GB. However, I am no oracle expert and this is the values we have set here too.

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Gino Castoldi_2 — Tue, 06 Jan 2004 09:17:54 GMT

Hi,

The SGA is 31.8MB, is this large enough?

10 points to any good answer.
TIA, Gino

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Hein van den Heuvel — Tue, 06 Jan 2004 09:28:54 GMT

> The SGA is 31.8MB, is this large enough?

No, normally not. The only exception is a student type system with 50+ databases on a 1GB box or so.

You are giving Oracle less than 1% of your memory! As a generic guideling give Oracle as much memory (in percents) as work you expect it to do on the box. If the box only does oracle, give oracle all the memory! If Oracle is expected to use 25% of the boc, give is 25% ... as a startng poitns.

Bump block_buffers to 20,000 and shared pool to 300M and try again.

Now activate statspack and run a report over two snaps froma representive period after some stability is reached. PLEASE, Try to interpret at least the first page of the report yourself. Read it 20 times over. Then come back with specific question and quoted details from the report if need be.

Good luck!

Hein.

Re: What is the cause of these Oracle errors? ORA-03113, etc.

John Palmer — Tue, 06 Jan 2004 09:29:12 GMT

The actual error is:-
ORA-04031: unable to allocate 4176 bytes of shared memory ("shared pool","OPC_HIST_MESSAGES","sga heap","library cache")

This is caused by fragmentation of the shared pool (Part of the Oracle SGA).

There are patches for this type of error and you really should consider applying the 8.1.7.4 patchset.

Alternatively, you could increase the size of the shared pool, your SGA at 31.8Mb seems ridiculously small! Look at the shared_pool_size initialisation parameter (specified in bytes).

If you have access to Oracle Metalink, there are several relevant articles which give good advice on how to tune this parameter.

Regards,
John

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Gino Castoldi_2 — Tue, 06 Jan 2004 09:46:40 GMT

Hi,

Hein:
We are running a single instance of Oracle
8.1.7.3 for OpenView Operations.

Our current settings are this:
A) db_block_buffers = 550
B) shared_pool_size = 6000000
So A. should be 20,000 and B. 300,000,000.

John:
Installing patch 8.1.7.4 can be done but it
would have to be scheduled, etc. This is the first time that these types of errors occurred. Increasing the shared_pool_size seems like a good idea from what you and Hein
have said and is easier then applying a patch. (Patching is a good idea but tuning
one thing at a time seems safer and easier to implement at this point).

I make these changes to the initsid.ora file one at a time.

10 points to any good answer.
TIA, Gino

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Hein van den Heuvel — Tue, 06 Jan 2004 09:51:40 GMT

> Our current settings are this:
> A) db_block_buffers = 550
> B) shared_pool_size = 6000000
> So A. should be 20,000 and B. 300,000,000.

Now that I see that you are using Oracle for a relatively small task I would recommend a slightly less aggresive change. 500 buffers, and 6 M shared is just not enough.

Try 2000 buffers and 50 or 100 M buffer pool for this task. Please DO change both in this specific case as they are way low.

The final choice also depend on the overall system contention for memory resources.

Please doublecheck the openview doc for minimal Oracle setup guideance... surely there is some indicator?

Hein.

Re: What is the cause of these Oracle errors? ORA-03113, etc.

Indira Aramandla — Wed, 07 Jan 2004 02:06:46 GMT

Hi TIA,

The problem is reported with the size allocation in shared pool. Please validate the shared pool contenttion and tune it appropriately.

Attached is a document (note from metalink) on how to analyze ORA-04031 ERROR AND SHAREDPOOL FRAGMENTATION.

IA