3 Replies Latest reply on May 31, 2017 2:40 AM by Shailendra Gavas

    ODBC Oracle Wire Protocol driver Error

    Shailendra Gavas New Member
    Shailendra Gavas May 30, 2017 12:08 AM

      We have configured a Data Synchronization Job in Informatica Cloud which has Source as Oracle and Target as SQL server. We are using ODBC Oracle Wire Protocol driver to connect to Orcale database. This job is configured to run on daily basis at morning. We found that this job runs fine for some occasion but fails sometimes with following error: Looking at the error it first says connection is dead.

       

      RR_4035 SQL Error [ FnName: Fetch Optimize -- [Informatica][ODBC Oracle Wire Protocol driver][Oracle]Connection Dead. FnName: Fetch Optimize -- [Informatica][ODBC Oracle Wire Protocol driver][Oracle]ORA-03114: not connected to ORACLE FnName: Fetch Optimize -- [Informatica][ODBC Oracle Wire Protocol driver][Oracle]ORA-03114: not connected to ORACLE FnName: Fetch Optimize -- [Informatica][ODBC Oracle Wire Protocol driver][Oracle]ORA-03114: not connected to ORACLE FnName: Fetch Optimize -- [Informatica][ODBC Oracle Wire Protocol driver][Oracle]ORA-03114: not connected to ORACLE FnName: Fetch Opt


      The amount of data loaded from Oracle database is @130000 records.


      Our secure Agent is hosted on 64 bit Windows OS. Please suggest how can we resolve this issue as job is failing intermittently.

        • 1. Re: ODBC Oracle Wire Protocol driver Error
          Guru
          nheinze May 30, 2017 4:33 AM (in response to Shailendra Gavas)

          If I were you, I would install a native Oracle client on the Windows machine and use this Oracle client to connect to the DBMS, not the Oracle Wire driver. In general I wouldn't use an ODBC driver for Oracle, DB2, or Sybase.

           

          Regards,

          Nico

          • 2. Re: ODBC Oracle Wire Protocol driver Error
            VIDYASAGAR BALASUBRAMANIAN Guru
            VIDYASAGAR BALASUBRAMANIAN May 30, 2017 5:21 AM (in response to Shailendra Gavas)

            Hi Shailendra,

             

            The driver may report this kind of errors depending on the timeout setting and the usability of the connection. The "connection dead" error can appear by itself if the driver attempts to communicate with the server and discovers that the socket has been closed. If the driver determines that the connection is no longer valid, then it will report that the network operation has timed out and also that the connection is dead.

             

            The driver determines whether a connection is valid by doing the following:

            The driver sends an interrupt to the server to determine whether the connection is valid. The interrupt is used to cancel the current request.

             

            If any of the following are true, then the connection is considered valid and usable:

            • The server replies with a cancel acknowledgment
            • The server replies with a valid response to the original request.
            • If the server has not replied, a simple database operation is performed. The operation is to open and close a  cursor.

            Otherwise, the connection is no longer usable and the socket is closed.

             

             

            Thanks,

            Vidyasagar

            • 3. Re: ODBC Oracle Wire Protocol driver Error
              Shailendra Gavas New Member
              Shailendra Gavas May 31, 2017 2:40 AM (in response to VIDYASAGAR BALASUBRAMANIAN)

              Thanks Vidyasagar,

              I found the same information in the below KB.

              Progress KB - Understanding "Connection Dead" and "Network Operation timed out" errors.

               

              But in the resolution they mentioned about following:

              1. Query Timeout to -1
              2. Failover Mode
                1. AlternateServers: To same connection if we are not using any alternate server
                2. Failover Mode to 2

              I looked for those settings in Informatica cloud connection properties of “OracleProdConnection” but those won’t exist over there. Since I am .Net side and novice on “Informatica” side, do you know how can we change the above settings? If yes can you provide me the steps to do so.


              Thanks again.