1 2 Previous Next 24 Replies Latest reply: Oct 1, 2013 4:27 PM by EdStevens Go to original post RSS
      • 15. Re: ORA-03114 not connected to ORACLE
        ankitbarsainya

        sb92075 wrote:

         

        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!

        I'm not expecting there to be any report but just the confirmation whether or not application is able to connect with the database.

        This way we will be able to figure out if the connection is being made at all or not.

        This is just a way of trying to pin point the exact issue in this case.

        Corrections Welcome,

        Ankit Barsainya

        • 16. Re: ORA-03114 not connected to ORACLE
          sb92075

          ankitbarsainya wrote:

           

          sb92075 wrote:

           

          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!

          I'm not expecting there to be any report but just the confirmation whether or not application is able to connect with the database.

          This way we will be able to figure out if the connection is being made at all or not.

          This is just a way of trying to pin point the exact issue in this case.

          Corrections Welcome,

          Ankit Barsainya

           

          >I'm not expecting there to be any report but just the confirmation whether or not application is able to connect with the database.

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

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

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


          which part of the above do you not understand?


          post reproducible test case using COPY & PASTE to demonstrate your statement is true & valid

           

          • 17. Re: ORA-03114 not connected to ORACLE
            ankitbarsainya

            Are you suggesting when the below command is executed we won't even get a notification for whether or not the connection was successfully made:

            tnsping RACNODE1

            Assuming ofcourse RACNODE1 is a valid database instance which is up and running and reachable?

            I'd suggest YOU to check if there is anything returned by the above statement.

            For the record it'll provide you with network lag when tried to connect to the database along with other details.

            Regards,

            Ankit Barsainya

            • 18. Re: ORA-03114 not connected to ORACLE
              sb92075

              ankitbarsainya wrote:

               

              Are you suggesting when the below command is executed we won't even get a notification for whether or not the connection was successfully made:

              tnsping RACNODE1

              Assuming ofcourse RACNODE1 is a valid database instance which is up and running and reachable?

              I'd suggest YOU to check if there is anything returned by the above statement.

              For the record it'll provide you with network lag when tried to connect to the database along with other details.

              Regards,

              Ankit Barsainya

               

               

              please comment on what is posted below & explain how tnsping reports success against DOWN database!

               

              [oracle@localhost ~]$ lsnrctl status

               

               

              LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 01-OCT-2013 09:23:05

               

               

              Copyright (c) 1991, 2010, Oracle.  All rights reserved.

               

               

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))

              STATUS of the LISTENER

              ------------------------

              Alias                     LISTENER

              Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

              Start Date                28-SEP-2013 05:38:10

              Uptime                    3 days 3 hr. 44 min. 56 sec

              Trace Level               off

              Security                  ON: Local OS Authentication

              SNMP                      OFF

              Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

              Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

              Listening Endpoints Summary...

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))

                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))

              Services Summary...

              Service "orcl" has 1 instance(s).

                Instance "orcl", status READY, has 2 handler(s) for this service...

              Service "orclXDB" has 1 instance(s).

                Instance "orcl", status READY, has 1 handler(s) for this service...

              The command completed successfully

              [oracle@localhost ~]$ sqlplus / as sysdba

               

               

              SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 1 09:23:21 2013

               

               

              Copyright (c) 1982, 2010, Oracle.  All rights reserved.

               

               

               

               

              Connected to:

              Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

              With the Partitioning, OLAP, Data Mining and Real Application Testing options

               

               

              SQL> shutdown immediate

              Database closed.

              Database dismounted.

              ORACLE instance shut down.

              SQL> exit

              Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

              With the Partitioning, OLAP, Data Mining and Real Application Testing options

              [oracle@localhost ~]$ tnsping orcl

               

               

              TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 01-OCT-2013 09:24:17

               

               

              Copyright (c) 1997, 2010, Oracle.  All rights reserved.

               

               

              Used parameter files:

               

               

               

               

              Used TNSNAMES adapter to resolve the alias

              Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

              OK (30 msec)

              [oracle@localhost ~]$

              • 19. Re: ORA-03114 not connected to ORACLE
                EdStevens

                ankitbarsainya wrote:

                 

                Are you suggesting when the below command is executed we won't even get a notification for whether or not the connection was successfully made:

                tnsping RACNODE1

                 

                 

                That is exactly what we are saying.

                You can get a 'successful' tnsping against a non-existent database, as long as the net service name references a reachable IP address with a listener on the specified port.

                 

                see: http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/  (tnsping-101 )

                 

                Assuming ofcourse RACNODE1 is a valid database instance which is up and running and reachable?

                I'd suggest YOU to check if there is anything returned by the above statement.

                For the record it'll provide you with network lag when tried to connect to the database along with other details.

                Regards,

                Ankit Barsainya

                • 20. Re: ORA-03114 not connected to ORACLE
                  ankitbarsainya

                  Thanks for sharing this with me.

                  But I'm primarily focusing on the problem mentioned in the titled and here we are aware that the database is existing along with the listener.

                  All I'm asking to check here is whether or not tnsping is able to make the connection to the said listener so that we can confirm that issue is not with the database but the network.

                  Anyway the point is moot now that OP has gone back to consult with his network team.

                  Regards,

                  Ankit Barsainya

                  • 21. Re: ORA-03114 not connected to ORACLE
                    Mark D Powell

                    Raju, what full version of Oracle is the target datbase?  What kind of applciation?  Running locally or remote using what version of the Oracle client?  What full version of the OS supports the database, the applciation?

                     

                    We have encountered numerous ORA-03114 errors over the years which would occur causing the existing connection to be broken, but almost all of these errors were due to version specific bugs and a couple of these bugs were also platform specific.

                     

                    Do you have DCD (dead connection detection) in use?  There is an Oracle support note 1550470.1 about DCD and this issue.  There are also current notes for EBS auto-invoicing, datapump, rman, and some other products hitting this error but I did not see any note about just connecting.

                     

                    Since you should know what kind of front-end application is in use and the code base it is written in you should be able to perform a better problem search on support.

                     

                    HTH -- Mark D Powell --

                    • 22. Re: ORA-03114 not connected to ORACLE
                      TSharma-Oracle

                      Instead of spending time here, as somebody suggested above , start the client level 16 trace on your application server and upload those to support or here. Somebody should be able to read and diagnose from those traces. It should be easy because its not intermittent and you have a pattern when that error occurs.You do not have to wait. Just start the trace right before you start the app and close it right after you get the error in order to avoid the huge log.

                       

                      Also, Do you start the application everyday? Keep an eye on the system resource when you try to connect to oracle for first time everyday.

                      • 23. Re: ORA-03114 not connected to ORACLE
                        jgarry

                        Are you sure it is not something like the user starting to work before dns has properly served the location of the server?  I often have that issue with my home ISP and websites, no Oracle involved.  A couple of reload page and all is fine.  Stupid ISP.

                        • 24. Re: ORA-03114 not connected to ORACLE
                          EdStevens

                          ankitbarsainya wrote:

                           

                          Thanks for sharing this with me.

                          But I'm primarily focusing on the problem mentioned in the titled and here we are aware that the database is existing along with the listener.

                          All I'm asking to check here is whether or not tnsping is able to make the connection to the said listener so that we can confirm that issue is not with the database but the network.

                          Anyway the point is moot now that OP has gone back to consult with his network team.

                          Regards,

                          Ankit Barsainya

                           

                          All I'm asking to check here is whether or not tnsping is able to make the connection to the said listener

                           

                          Now you are saying to use it to check the connection to the listener.

                          Previously you were saying to check the connection to the database.



                          "try tnsping for the database from a client to see if the connection is being made to the database"


                          "I'm not expecting there to be any report but just the confirmation whether or not application is able to connect with the database"

                           

                          "Are you suggesting when the below command is executed we won't even get a notification for whether or not the connection was successfully made:

                          tnsping RACNODE1

                          Assuming ofcourse RACNODE1 is a valid database instance which is up and running and reachable?

                          1 2 Previous Next