ORA-03113: end-of-file on communication channel sql error

ORA-03113 is one of the worst Oracle errors to try to debug, since you cannot build in exception-handling for it.  The problem could be almost anything.

Do you have an earlier version of the report that you can try?  Or does this only happen on some work stations but not others?

It could be one of two things:
- a bug
- your server process terminates
Look for trace files in your bdump or udump directory. It could be a bug that shows up in certain conditions, like a combinations SQL statements.
If it's not a bug, then it could be your network instability, you may find some simptoms in your listener.log. It could also be some timeout or i/o limits that you exceeded.
If you find none of the above, and you can recreate the problem, then contact Oracle support.

Hi all

In my Oracle instalation this problem is usually caused by two reasons

1) Instalation of new rules to our firewall which is used for communication from client to demilitarized zone where db servers are settled.

2) We have set sqlnet.ora parameter SQLNET.EXPIRE_TIME to wrong value and server detects some connections as dead connections and clear them out.


Try to se these topics. Maybe they will help you.


Starous

hi
maybe u are using some T/P monitor like tuxedo from BEA
i have seen in the past that sometimes server processes are killed in a tuxedo env.
if so try to check with the vendor.
maybe a simple restart of the T/P software is enough

Are you still having this trouble?  If so please give us so more details, like:
1. does this happen every time or only sometimes?
2. does this happen on multiple machines or only on one?
3. did you change or recompile the report recently?
4. were any system, database or network changes made recently?
5. do other reports work fine, but this one fails?

Yes, I?m still working on the Ora-03113 sql error.
-It happens every time when I try to run a costing utilities report.  It scans all the tables in the database.  It stops on the same work order number.
-It happens on all the machines.
-I didn?t change the report recently.  But someone has access to change the report.  I?m not sure.
-First, the user tried to run this report and got ?ora-01555: snapshot too old: rollback segment number 13 with name ?RB13? too small? error message.  So, I dropped the RB13 and recreated it.  Then, I got ora-03113 error message.
-Other reports work fine.

I look at the alert.log and sqlnet.ora.  I don?t see there are any error messages.  But, I found some error on one big table.  Here is the output.
 
SQL>ANALYZE TABLE WIP_RECEIPT_DIST VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE WIP_RECEIPT_DIST VALIDATE STRUCTURE CASCADE;
ERROR at line 1:
ORA-01499: table/index cross reference failure ? see trace file

I am thinking to rebuild some indexes!?  Have any ideas?

Thank you so much for your input.

WHat you said indicates a problem similar to the one I had. It happened only on a certain query. This turned out to be a bug. There was a trace file in bdump or udump directory every time I had this error. In your case it seems to be a corrupt index. So maybe dropping and creating it could resolve it. Still, look for a trace file.

Hi experts,

Yes, I still have ora-3113 sql error.  I run the performance check list.  Here are some results.

Cache Hit Ratio .798132
Redo buffer allocation retries 68
Lib Cache Hit Ratio .99859
shared pool free memory 5MB out of 100MB

On our init.ora
db_block_buffers=3200
shared_pool_size=100MB
processes=300
db_block_size=4096

My question is:
Should I increase the shared_pool_size and db_block_buffers size?

Thank you in advance!

It looks like the main problem is an invalid index.  Have you tried dropping and recreating that index?  What about the table?  Can you export the table successfully?  If so, try importing it into a test system or into a different schema.  I've seen a case in the past where there was data block corruption in a table that caused ORA-3113 errors.  I needed to drop and re-create the table to fix the problem.  Fortunately for me, that was a case where the table data was all available in an ASCII file on a different system.

I don't think your init*.ora settings cause the ORA-03113 error, but you may want to change some of them to get better performance.  This depends though on how much physical RAM your server has and on how your application was written.  My impression is that shared_pool_size is too high, and that db_block_buffers is too low.  For example, our Oracle8.1.7 system on WindowsNT with 2GB of RAM has:
shared_pool_size = 28000000  (28M)
db_block_buffers = 160000
processes = 250  (but we average only 150 concurrent)
and we also have a 4K block size.

That combination gives us:
Cache Hit Ratio:   97.9
Lib Cache Hit Ratio: 99.67712

markgeer,

I exported the whole database and sent it to our ERP software support team last week.  I told them to do the some thing that I did in house.  They have no problem running the big report.  In addition, I found one table that might cause the ORA-3113 error.  I got ORA-01499: table/index cross reference failure error when I tried "ANALYZE TABLE WIP_RECEIPT_DIST VALIDATE STRUCTURE CASCADE;".  But the support team ran successfully using sqlplus.

Is the Oracle connection being lost in our LAN?  I already tried rebooting the HUBS/SWITCHES, network, and Servers.  No luck.  What should I do to fix this, or at least to isolate the problem. This is becoming a serious issue quickly.  Thank you in advance!

ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101, Netminder or Mindphaser will return to finalize these if they are still open in 7 days.  Experts, please post closing recommendations before that time.

Below are your open questions as of today.  Questions which have been inactive for 21 days or longer are considered to be abandoned and for those, your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20189017.html
https://www.experts-exchange.com/questions/Q.20193407.html
https://www.experts-exchange.com/questions/Q.20229848.html
https://www.experts-exchange.com/questions/Q.20240676.html
https://www.experts-exchange.com/questions/Q.20243991.html
https://www.experts-exchange.com/questions/Q.20184394.html
https://www.experts-exchange.com/questions/Q.20283727.html
https://www.experts-exchange.com/questions/Q.20298905.html



*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations.
If you are interested in the cleanup effort, please click this link
https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643 
POINTS FOR EXPERTS awaiting comments are listed in the link below
https://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @7 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thanks everyone.
Moondancer
Moderator @ Experts Exchange

Recommended disposition:

    Accept Bonger's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer

This question was finalized today.  
Thanks, Dan, for your help.
Moondancer - EE Moderator

Hello Bonger ..

      We are also facing same Problem ....have u fixed problem ..?

      Please let me know if u have got any solution for same ..

     Please help me for same ..If possible give me u r Telephone no.. i can call u as i require to fix it urgently.

     My mail id is <email address edited as per rules>

     Waiting for ur reply..

     If any guys found a solution for it ,please let me know


    Thanks  in advance ...

Regards

Ritesh