Re: Debug ORA-03113 on Oracle XE

From: joel garry <joel-garry_at_home.com>
Date: Tue, 16 Jun 2009 13:49:16 -0700 (PDT)
Message-ID: <7e99eda7-c730-4640-9351-3e5d4de0e171_at_z20g2000prh.googlegroups.com>

On Jun 16, 9:33 am, "Álvaro G. Vicario"
<alvaro.NOSPAMTH..._at_demogracia.com> wrote:
> Álvaro G. Vicario escribió:
>
>
>
> > CarlosAL escribió:
> >> On 16 jun, 15:00, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> >>> Álvaro G. Vicario schrieb:
>
> >>>> Maxim Demenko escribió:
> >>>>> Álvaro G. Vicario schrieb:
> >>>>>> I have a web application written in PHP that's failing on one
> >>>>>> computer (and only that computer): a laptop that runs the Oracle 10g
> >>>>>> Express Edition. It fails when it executes a specific SELECT query: I
> >>>>>> get a "ORA-03113 end-of-file on communication channel" error and the
> >>>>>> connection drops.
> >>>>>> My main concern is that I can't figure out what to do next. It seems
> >>>>>> that ORA-03113 is a very generic error that can happen for lots of
> >>>>>> reasons.
> >>>>>> The query returns no rows with the current data set but it's pretty
> >>>>>> complex (lots of joins and subqueries). However, I only get the
> >>>>>> ORA-03113 error when I run it through PHP and Apache. When I run it
> >>>>>> from SQL Developer I get no error and the results come almost
> >>>>>> instantly. The PHP backtrace doesn't provide additional info. The PHP
> >>>>>> and Apache versions do not seem to matter. There're neither antivirus
> >>>>>> nor firewall in the laptop. If Oracle XE generates further info I
> >>>>>> don't know where I can find it.
> >>>>> The first place where i would look up - alert.log, 3113 should be
> >>>>> recorded there as well as the preceding error ( which is very often an
> >>>>> ORA-00600 or ORA-07445, an additional trace file should be generated
> >>>>> as well ),
> >>>> I found "alert_xe.log". It doesn't mention 3113 but there're several
> >>>> lines like:
> >>>> Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_3928.trc:
> >>>> ORA-07445: se ha encontrado una excepción: volcado de memoria
> >>>> [ACCESS_VIOLATION] [_lxptmutf8+30] [PC:0x60AD606E] [ADDR:0xA98FD33]
> >>>> [UNABLE_TO_READ] []
> >>>> ... and there's a 6 MB dump at "xe_ora_3928.trc". At least now I have
> >>>> something to look at, thank you.
> >>>>> when you figured out, from which error is the origin of 3113, next
> >>>>> step should be Metalink.
> >>>> I presume Metalink is only for pay customers, isn't it?
> >>> Sorry, have overlooked you are on XE.
> >>> In that case you can not get any support from Oracle ( you'll have to
> >>> upgrade your Oracle version - in that case your error may be not
> >>> reproducible at all). You can though try to ask on the OTN Oracle XE
> >>> forum, maybe somebody already experienced similar error and has a
> >>> workaround.
>
> >> Álvaro:
>
> >> Revisa tu 'shared pool'.
>
> >> ¿Tienes cursor_sharing=force?
>
> > Tengo lo que venga de fábrica con el XE. En Google me salen toneladas de
> > documentación. ¿Qué miro?
>
> I've edited this file:
>
> C:\oraclexe\app\oracle\product\10.2.0\server\config\scripts\init.ora

Maybe I've been confused because this is XE, but I don't think that is the correct file. There should be an spfile named C:\oraclexe\app \oracle\product\10.2.0\server\dbs\SPFILEXE.ORA that your db is using, which is not editable. You either have to use alter commands to change stuff, or create a pfile from spfile and edit that, then specify it during the startup, then create spfile from pfile to make it persistent if it works.

>
> After checking they didn't already exist, I added these directives:
>
> cursor_sharing=force
> shared_pool_size=52428800
>
> Then I restarted the server. Now I can no longer log in into the
> database so I assume I did something wrong...

I suspect you have memory problems. What else is running on the laptop, and how much physical memory does it have? Since sqlplus was working, I'm guessing something besides Oracle was sucking up your memory. There are some mysterious bugs fixed in later versions that may also be involved, perhaps keeping it in sqlplus with no actual results returned just barely doesn't blow memory.

Also, if you get it running again, from the sqlplus as / command line:

show parameter pool

You may need to export your user data and reinstall XE.

jg

--
_at_home.com is bogus.
“Mommy had her 'mad face' on.” - 8 year old testifying about being
shot by an off-duty police officer when his drug-addled mother tried
to run the officer down during a road-rage incident.

Received on Tue Jun 16 2009 - 15:49:16 CDT