6 Replies Latest reply: Sep 1, 2006 7:23 AM by 530370 RSS

    Unable to connect remote databse via windows XP(using Instant Client)

    530370
      I have downlaod instant Client and the PATH env variable automatically is set when I install Instant Client. Then I try to connect using the following(from the path C:/XEClient/bin):

      sqlplus username/passwd@//host:[port][service name]
      I get the following error:
      ORA-12170: TNS: Connect timeout occurred


      When I ping the host I get reply.

      Also I am able to connect to that same database thru perl( so I presume there is no problem with the database not avaiable or port not open etc), where additionally ORACLE_HOME and ORACLE_SID env variable are set to the path in the remote machine. The databse version is Oracle 9.2

      Below is the sqlnet.log:

      *******************************************************************************************
      Fatal NI connect error 12170.

      VERSION INFORMATION:
           TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
           Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production
      Time: 30-AUG-2006 14:56:03
      Tracing not turned on.
      Tns error struct:
      ns main err code: 12535
      TNS-12535: Message 12535 not found; No message file for product=NETWORK, facility=TNS
      ns secondary err code: 12560
      nt main err code: 505
      TNS-00505: Message 505 not found; No message file for product=NETWORK, facility=TNS
      nt secondary err code: 60
      nt OS err code: 0
      Client address: <unknown>

      ===========================================================
      Am I missing some set up.
      Can anybody help please...I have been trying since last 3 days.

      Thanks
        • 1. Re: Unable to connect remote databse via windows XP(using Instant Client)
          LaurenzAlbe
          I don't know what the problem is, but maybe I can help you to find out.

          There is one thing that looks suspicious in your setup: "additionally ORACLE_HOME and ORACLE_SID env variable are set to the path in the remote machine"

          On the Client machine you shouldn't set either of these.
          Rather set TNS_ADMIN and point it to the directory where Instant Client is installed.

          Is there any other Oracle software except Instant Client installed on the client machine?
          This might cause problems.

          After checking all that, and if the error prevails, maybe a client trace can help (set TRACE_LEVEL_CLIENT and TRACE_DIRECTORY_CLIENT in your sqlnet.ora in the directory where TNS_ADMIN points to).

          This should produce a largish trace file that might tell you more.

          Yours,
          Laurenz Albe
          • 2. Re: Unable to connect remote databse via windows XP(using Instant Client)
            530370
            Hi Thanks for the quick reply.

            Just confirming...my understanding goes as follows:

            Either we use the tnsnames.ora set up OR use the hostname and port at the connect string i.e. sqlplus user/passwd@//host:port/SID
            ----Is the above understanding wrong?

            As per your suggestion I have done the following:
            I do not have admin rights on my machine, so I cannot add any file in the C:XEClient folder. Hence I added it(tnsnames.ora and sqlnet.ora) on another directory and set my pointer(tns_admin) to that directory. But the error still prevails.

            Also pl verify if my sqlnet.ora entry:
            SQLNET.AUTHENTICATION_SERVICES= (NTS)

            NAMES.DIRECTORY_PATH= (TNSNAMES)

            TRACE_LEVEL_CLIENT = ON

            TRACE_DIRECTORY_CLIENT = C:/Perl

            ================================
            somehow no trace is being generated. Are the above entry correct?

            Message was edited by:
            user527367

            Message was edited by:
            user527367

            Message was edited by:
            user527367
            • 3. Re: Unable to connect remote databse via windows XP(using Instant Client)
              LaurenzAlbe
              The reason why no client trace is generated is probably that TRACE_LEVEL_CLIENT shoule be SUPPORT and not ON. Also, there is a forward slash and not a backslash in the trace directory name.

              Using a different directory than the Instant Client directory for sqlnet.ora and tnsnames.ora is fine as long as TNS_ADMIN points to it.

              If you want to use the //host:post/SID form of connect, you will have to change one line in sqlnet.ora to allow "easy connect":

              NAMES.DIRECTORY_PATH=(EZCONNECT, TNSNAMES)

              It still would be interesting if there is any software besides Instant Client installed on the machine. I am curious because the name of the Instant Client directory (C:\XEClient) is somehow suggestive of other Oracle software.

              Yours,
              Laurenz Albe
              • 4. Re: Unable to connect remote databse via windows XP(using Instant Client)
                530370
                Hi Laurenz,

                Sorry to say...but it still does not connect(incorporated your suggestions), nor is the trace generated. sqlnet.log looks no different. As about any other oracle clients...I did not have any until yesterday evening when we installed the entire 10g oracle...client and database.

                Anyways now too after having installed oracle 10g on D drive, no succes in connecting to remote databse.

                Two questions:

                1. Is XE client compatible with 10g database? As I am trying to connect to 9.2 version as mentioned in my initial query.

                2. When I ping the host I do get reply. BUT when I telnet i.e.

                telnet host port
                ....it says "could not open connection to the host, on port XXX: connect failed"
                Does it mean there is a firewall at the remote end which is preventing the successfull connection? But then how do I connect thru' Perl with no extra info...

                Any other suggestion please let me know...and also do you suggest I uninstall all oracle clients other than XE...or is there any way out ...having multiple clients?

                Another query...hope I can use toad after having set up the XE client?
                Thanks in advance...

                Message was edited by:
                user527367
                • 5. Re: Unable to connect remote databse via windows XP(using Instant Client)
                  LaurenzAlbe
                  It is possible to have different Oracle products running on the same machine, but on Windows - being a single-user word-processing operating system - it is difficult.
                  If you have installed a second Oracle product after Instant Client, your environment is probably messed up, PATH and others.

                  Now please tell me:
                  Do you have Instant Client installed or Oracle XE or both or what? There are so many Oracle products out there that you need to be very specific about product names.

                  If you cannot telnet to the database server on port 1521 (or whatever you use), it is a network or a firewall problem. Period. Or you got the listener port wrong. Or the listener is not started. But that's not the case if you can connect from another machine.

                  So resolve that problem first, and when you can telnet to the machine on port 1521, try again.

                  Yours,
                  Laurenz Albe
                  • 6. Re: Unable to connect remote databse via windows XP(using Instant Client)
                    530370
                    Hi Laurenz,

                    Thanks a lot...your hint about port 1521 helped. Yes I got connected.!!!!

                    Strange it never striked to me to try the default port. I kept trying the specific port I was given by the remote database guys. And it was giving the error ora-12170

                    And that was exactly why thru' Perl I could get connected as it did not use the 'port no' in its connect syntax(hence by taking the default port it used to get connected successfully...thinkso!!!!)

                    Thanks a lot for your help.

                    Best Regards,

                    Message was edited by:
                    user527367