Re: Debug ORA-03113 on Oracle XE

From: Álvaro G. Vicario <alvaro.NOSPAMTHANX_at_demogracia.com>
Date: Wed, 17 Jun 2009 12:00:56 +0200
Message-ID: <h1aesr$jsq$1_at_news.eternal-september.org>

joel garry escribió:
>>>>>>>> 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.

There it is:

xe.__java_pool_size=4194304
xe.__large_pool_size=8388608
xe.__shared_pool_size=104857600
xe.__streams_pool_size=8388608
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
*.background_dump_dest='C:\oraclexe\app\oracle\admin\XE\bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oraclexe\oradata\XE\control.dbf'
*.core_dump_dest='C:\oraclexe\app\oracle\admin\XE\cdump'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\flash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=80M
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.sga_target=240M
*.shared_servers=4
*.undo_management='AUTO'

*.undo_tablespace='UNDO'
*.user_dump_dest='C:\oraclexe\app\oracle\admin\XE\udump'

I've played around with ALTER SYSTEM (actually, it's a faster way to test stuff) but changing the *_pool_size parameters didn't have any effect on the issue.

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

The laptop has 3GB of RAM and most of it is free. However, the XE edition claims to allow a limited amount of memory (1GB) so it's easy that I'm hitting a memory limit somewhere.

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

Well, the free edition is seldom updated so I'll have to live with 10g R2 for a while.

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

SQL> show parameter pool;

NAME                                 TYPE        VALUE
------------------------------------ ----------- 
------------------------------
buffer_pool_keep                     string
buffer_pool_recycle                  string
global_context_pool_size             string
java_pool_size                       big integer 0
large_pool_size                      big integer 0
olap_page_pool_size                  big integer 0
shared_pool_reserved_size            big integer 5033164
shared_pool_size                     big integer 0
streams_pool_size                    big integer 0

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

Data is disposable (I regenerate it all from SQL scripts) and I've reinstalled XE two or three times but the problem is not specific to the laptop: I've learnt that it happens on any 10g XE installation when I happen to generate certain data from the application, but it won't fail in Oracle 9.

The "alert_xe.log" file shows this:

Errors in file c:\oraclexe\app\oracle\admin\xe\udump\xe_ora_5500.trc: ORA-07445: se ha encontrado una excepción: volcado de memoria [ACCESS_VIOLATION] [_lxptmutf8+30] [PC:0x60AD606E] [ADDR:0xB40DEDF] [UNABLE_TO_READ] [] Having an access violation makes me think that I've just hit a bug in the Oracle engine. Unless I'm able to find an obvious misconfiguration issue, I guess it'll be easier to just rewrite my query.

Thanks a lot for your tips.

-- 
-- 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 Wed Jun 17 2009 - 05:00:56 CDT