Re: Debug ORA-03113 on Oracle XE

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com>
Date: Tue, 16 Jun 2009 18:33:56 +0200
Message-ID: <h18hhn$265$1_at_news.eternal-september.org>



Á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

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...

-- 
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
Received on Tue Jun 16 2009 - 11:33:56 CDT

Original text of this message