Re: Debug ORA-03113 on Oracle XE
From: CarlosAL <miotromailcarlos_at_netscape.net>
Date: Wed, 17 Jun 2009 01:59:53 -0700 (PDT)
Message-ID: <1d4f4ab7-5c24-4591-a6d1-472459a85090_at_r37g2000yqd.googlegroups.com>
On 16 jun, 22:49, joel garry <joel-ga..._at_home.com> wrote:
> 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.
Date: Wed, 17 Jun 2009 01:59:53 -0700 (PDT)
Message-ID: <1d4f4ab7-5c24-4591-a6d1-472459a85090_at_r37g2000yqd.googlegroups.com>
On 16 jun, 22:49, joel garry <joel-ga..._at_home.com> wrote:
> 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.
I seem to remember something related to cursor_sharing=force to cause ORA-07445. I was only asking, not suggesting.
I agree with Joel. Check your memory usage and Oracle parameters related to it (in spfile).
HTH. Cheers.
Carlos. Received on Wed Jun 17 2009 - 03:59:53 CDT