This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Oct 1, 2013 1:49 PM by jgarry RSS

ORA-03114 not connected to ORACLE

nraju Newbie
Currently Being Moderated

Hi All,

 

Every day first user who is trying to login from application getting ORA-03114 error. After 3 attempts the application is logging to database. Once logged in everything is working fine for all users for the whole day.

Same issue again on next day who is logging in for the 1st time.

 

What could be the reason for this kind of behavior. Please suggest on how to fix this issue.

 

Thanks,

Raju

  • 1. Re: ORA-03114 not connected to ORACLE
    sybrand_b Guru
    Currently Being Moderated

    The reason is likely there is a DBA who

    - doesn't read documentation

    - doesn't use online resources

    - can only be bothered to post questions like 'My car doesn't work, please fix my car' without doing any troubleshooting himself and without providing any details.

     

    Your request must be considered insulting and rude.

    -----------

    Sybrand Bakker

    Senior Oracle DBA

  • 2. Re: ORA-03114 not connected to ORACLE
    nraju Newbie
    Currently Being Moderated

    Hi,

     

    I have read the online info and tried some troubleshooting. But could not get the reason for this issue. The listener log looks good and no error on alertlog about the timed out.

    That is the reason I requested here to know if anyone has faced similar issue.

     

    I am not insulting anyone here or being rude with anyone.

     

    Thanks,

    NR

  • 3. Re: ORA-03114 not connected to ORACLE
    ankitbarsainya Newbie
    Currently Being Moderated

    The last time i faced this issue it was because the session was disconnected by the DBA(read: Myself).

    Probably you can check truss on your listener.

    Also it would be helpful to check what entries exist in the alert log before this error is encountered.

    Regards,

    Ankit

  • 4. Re: ORA-03114 not connected to ORACLE
    nraju Newbie
    Currently Being Moderated

    Hi Ankit,

     

    User tried to login at 7:50, after 3 failed attempts the user got logged in, listener.log shows connection details at 7:54

     

     

    - There are no erros in listener.log

     

    01-OCT-2013 07:46:58 * service_update * abc * 0

    01-OCT-2013 07:54:00 * (CONNECT_DATA=(SERVICE_NAME=ABC)(CID=(PROGRAM=D:\Apa?Software?Foundation\Tom\bin\tom**5.exe)(HOST=CM*****APP)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=app server ip)(PORT=57279)) * establish * ABC * 0

    01-OCT-2013 07:54:02 * service_update * abc * 0

     

    - No erros in the alter log.

     

    Thanks,

    NR

  • 5. Re: ORA-03114 not connected to ORACLE
    EdStevens Guru
    Currently Being Moderated

    nraju wrote:

     

    Hi Ankit,

     

    User tried to login at 7:50, after 3 failed attempts the user got logged in, listener.log shows connection details at 7:54

     

     

    - There are no erros in listener.log

     

    01-OCT-2013 07:46:58 * service_update * abc * 0

    01-OCT-2013 07:54:00 * (CONNECT_DATA=(SERVICE_NAME=ABC)(CID=(PROGRAM=D:\Apa?Software?Foundation\Tom\bin\tom**5.exe)(HOST=CM*****APP)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=app server ip)(PORT=57279)) * establish * ABC * 0

    01-OCT-2013 07:54:02 * service_update * abc * 0

     

    - No erros in the alter log.

     

    Thanks,

    NR

    The listener only reports requests that it receives.  If a connection request has a network timeout before the request gets to the listener, it will not - it cannot - be reported in the listener log.

    Your reported error code (ora-03114) seems to indicate a possibility that the app is either issuing sql statements before it ever gets connected, or the established connection is getting dropped.  Either way, I wouldn't expect to find much about it in the listener log.  The listener only handles the request to get connected to the database.  Once that connection is made, the listener is completely out of the picture.  You can even stop the listener and it will have no effect on established connections.

  • 6. Re: ORA-03114 not connected to ORACLE
    RaghunathDhandapani Newbie
    Currently Being Moderated

    Hi ,

     

    ORA-03114 occurs when an oracle call is made, when no connection is established.

     

    You have said before that user's login through an application, if the application is user writen, you have to check the program. Might be some problems between the program and database.

     

     

    Thanks

    Raghu

  • 7. Re: ORA-03114 not connected to ORACLE
    nraju Newbie
    Currently Being Moderated

    Hi Steven,

     

    Thanks for the explanation. What you said is true.

     

    Is anything to do with the database idle ? because the user who tried to login first in the day he is only facing the issue, after that everything going smooth without any issue.

     

    Thanks,

    NR

  • 8. Re: ORA-03114 not connected to ORACLE
    ankitbarsainya Newbie
    Currently Being Moderated

    Based on information that listener.log has no error at all I presume the request is not even reaching listener in the first 3 attempts.

    Logically thinking the issue is with some firewall, router etc going to sleep state as this issue is only faced by the person trying to login for the first time...

    you can check the expire time for various factors involved.

     

    I'm sorry i can't be of much help right now. When you do find out the solution please post it on this forum.

    Regards,

    Ankit Barsainya

  • 9. Re: ORA-03114 not connected to ORACLE
    nraju Newbie
    Currently Being Moderated

    Hi Ankit,

    We have recently migrated app/db to new windows machine. Before that everything was working fine. No changes made to app/db expect migration to new servers.

     

    Firewalls are disabled on new servers.

     

    sqlnet entries-

    SQLNET.AUTHENTICATION_SERVICES= (NONE)

     

    NAMES.DIRECTORY_PATH= (TNSNAMES)

     

    Thanks,

    NR

  • 10. Re: ORA-03114 not connected to ORACLE
    sb92075 Guru
    Currently Being Moderated

    >We have recently migrated app/db to new windows machine.

    >Before that everything was working fine.

    >No changes made to app/db expect migration to new servers.

     

    whether or not above is/was true is moot.

    Existing configuration does not know or care about what may or may not have occurred or existed in the past.

    You must deal with the present & now errors exist.

    I suggest that deploy packet sniffing software to see at the lowest level what occurs when error get thrown.

    Most likely cause is application logic bug.

  • 11. Re: ORA-03114 not connected to ORACLE
    ankitbarsainya Newbie
    Currently Being Moderated

    Agree with your suggestion to deploy packet sniffing software to get to the root of this issue. Although I hardly think this is an application bug but more of a network issue.

    This is a network issue and probably not one related to oracle database.

    Also if possible the next time before someone logs in to the database you can probably try tnsping for the database from a client to see if the connection is being made to the database or not, I'm quiet certain that the connection request is not reaching the database.

    Once this is confirmed you can connect with your network team to narrow down on the real issue.

    Hope this helps,

    Ankit Barsainya

  • 12. Re: ORA-03114 not connected to ORACLE
    sb92075 Guru
    Currently Being Moderated

    ankitbarsainya wrote:

     

    Agree with your suggestion to deploy packet sniffing software to get to the root of this issue. Although I hardly think this is an application bug but more of a network issue.

    This is a network issue and probably not one related to oracle database.

    Also if possible the next time before someone logs in to the database you can probably try tnsping for the database from a client to see if the connection is being made to the database or not, I'm quiet certain that the connection request is not reaching the database.

    Once this is confirmed you can connect with your network team to narrow down on the real issue.

    Hope this helps,

    Ankit Barsainya

     

    tnsping NEVER, EVER reports any detail regarding status of the DATABASE!

    tnsping can only report about existence & status of listener!

  • 13. Re: ORA-03114 not connected to ORACLE
    nraju Newbie
    Currently Being Moderated

    Hi Ankit/SD,

     

    I will try the packet sniffing and network related stuff. I will update the thread based on the result.

     

    Thanks for your prompt help.

     

    Thanks,

    NR

  • 14. Re: ORA-03114 not connected to ORACLE
    EdStevens Guru
    Currently Being Moderated

    nraju wrote:

     

    Hi Ankit/SD,

     

    I will try the packet sniffing and network related stuff. I will update the thread based on the result.

     

    Thanks for your prompt help.

     

    Thanks,

    NR

    You might also try a sqlnet level 16 client trace.

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points