-1

I am in WINDOWS 10Pro x64. I installed gem for my RUBY environment: ruby_oci8

I installed ORACLE C:\instantclient_12_2 and added to PATH, I also installed SQLPLUS utils and added to PATH

I need to connect to REMOTE ORACLE DB

my tnsnames.ora, pointed by TNS_ADMIN -> C:\ORACLE\network\admin in System Variables:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <host>)(PORT = <port>))
    )
    (CONNECT_DATA =
      (SID = <sid>)
    )
  )

I am new to this. Please help me with some other checks.

Thanks a lot!

1 Answers1

1

It is nigh unto impossible to debug that which you cannot see, and you have provided very little. Why not use copy and paste to show exactly what you did and the exact result - full command line, and full response? And in your follow-on comments you mention a couple of other error messages, but again with no context.

Since you mention sqlplus and "run connect @", I will assume your command looked something like this:

C:> sqlplus scott/tiger@orcl

With this usage, the '@' is a delimiter indicating that what follows ('orcl' in this example) is the net service name. When sqlplus parses out the command line, it will take this value ('orcl') and look for a match in the local file 'tnsnames.ora'. By default it will be located in %ORACLE_HOME/network/admin. A typical entry might look something like this:

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = somehostname)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

Where the first line is the net service name that sqlplus is looking for. If the requested net service name is not found, you get 'ORA-12154: TNS:could not resolve the connect identifier specified '. This is a very definitive error that means exactly one of two things: 1) the file 'tnsnames.ora' could not be located, or 2) the file was located but there is no entry for the requested net service name.

So, you need to prove that there is a tnsnames.ora file in the expected location, and that it has an entry for the requested net service name.

EdStevens
  • 3,708
  • 2
  • 10
  • 18
  • AS far AS my local machine is concerned: 1) the file 'tnsnames.ora' IS JUST NOT located and I don't have a %ORACLE_HOME/network/admin – Davide Tessarollo Oct 07 '21 at 17:36
  • Am I missing something in the installation? – Davide Tessarollo Oct 07 '21 at 17:41
  • tnsnames.ora does not get created by default, as every value of every potential entry would be dependent on local configuration. I've not worked with instant client, but you can set the system env variable TNS_ADMIN to point to some directory of your choice, and put the tnsnames.ora in that directory. I'd _strongly_ suggest an appropriate directory under your C:\instantclient_12_2. You can take the example entry I showed, and modify it to meet your requirements. – EdStevens Oct 07 '21 at 18:04
  • please check my edited – Davide Tessarollo Oct 07 '21 at 18:24
  • Ok, so you have created a tnsnames.ora, and pointed to it with TNS_ADMIN. So, _now_ what are the results of your connection attempt with sqlplus? Also note, in my example, I specified SERVICE_NAME=, but you have changed that to SID=. SERVICE_NAME is the preferred syntax. Also, there is no reason to mask the PORT or SERVICE_NAME/SID when you post here. The HOST= is the only thing that could be considered "sensitive". The service name has no meaning outside of your organization, and the port _should_ be the default of 1521, which everyone in the world knows. – EdStevens Oct 07 '21 at 20:41
  • Hello. Same error – Davide Tessarollo Oct 07 '21 at 21:01
  • I used your tns template but no solution. Please notice that if I get error in the Ruby class instance, I get error the same via sqlplus and I get error via them both – Davide Tessarollo Oct 07 '21 at 21:04
  • connect @ORCL returns connection failed, host or target object doesn’t exist – Davide Tessarollo Oct 07 '21 at 21:12
  • You may get the same with either, but sqlplus cuts out some variables. You now say "host or target object doesn’t exist" I'm guessing that's actually 'TNS-12545: Connect Failed Because Target Host or Object Does Not Exist' (please provide actual error code, so that there is no confusion). That is different from your earlier ORA-12154. In order to get TNS-12545, you actually found the entry in tnsnames, but the host specified in HOST= was not known to your network. You would have to take that up with your network admin. – EdStevens Oct 07 '21 at 21:45
  • YES I get ORA-12154. But consider that some hours ago it worked. Its behaviour is unstable. Consider that I connect successfully and stably to the same Database with Oracle sqldeveloper. Consider that I connect with the VPN on. I need to make it stable. Any suggestions – Davide Tessarollo Oct 08 '21 at 08:16
  • So if it is "unstable", are you getting a consistent error message? If so, what is it - EXACTLY. We need the full, exact, complete erroror message - copy and paste, not your cut-down transcription. Almost every connection error code if very definitive, so if the connection is "unstable", then _something_ is being changed. – EdStevens Oct 08 '21 at 15:27