This content has been marked as final. Show 9 replies
Well, you have 3 components to verify:
- Attempt to ping the host... maybe you have a connectivity issue?
- Double-check that you are using the port you think you are
- Double-check that the service name is what you think it is
You can use "tnsping service_name" to find out the correct port used by the listener.
Also, easy connection string is not specific to the instant client. So, start by testing your connect string on a regular client. Once it works, it should also work right away in instant client.
Well I think you're right about the sqlnet.ora. That's the closest approach to a feasible explanation to this problem however I'm not sure why this file is picked when I'm specifying a full connection string...
I've already did some tests before I read your message that may be confirming that sqlnet.ora is picked and therefore the tnsnames.ora file as it is specified in the sqlnet.ora. I tried to connect to an already defined service in the existing tnsnames file and the connection was successful when I typed:
and unsuccessful when I typed it as
It happens that everything after the "@" is interpreted as a service name so there's no way to find the service in the tnsnames. I'd say that since the ORACLE_HOME is defined in the windows registry the program may be searching sqlnet.ora which it is actually defined within network/admin directory and pointing to use tnsnames. I need to remove this file to test whether this works without the sqlnet.ora but I couldn't give it a try yet since it is a production server and I have to go through a process to do changes :-(
Regarding the ping, port and service name I'm completely sure about it. As I previously stated the same connection string is working from other load balanced servers within the same network and since the service is not defined and I don't want to define it either in the tnsnames; tnsping is not helpful. Furthermore I'm completely sure about the correctness of the IP, port number,service name and syntax.
Thanks all for your help!!!
If any of the "other" (i.e. outside instant client directory) sqlnet.ora contain a names.directory.path setting, such as (TNSNAMES), then that might explain what you are seeing.
the ORACLE_HOME is defined in the windows registryWhy do you have a separate Oracle install/home, while trying to use Instant client on the same machine? Can you not just use sqlplus from the existing home?
the program may be searching sqlnet.ora which it is
pointing to use tnsnames. I need to remove this fileTo override sqlnet.ora settings you should be able to use your own sqlnet.ora in the Instant client directory, e.g. with a single line with names.directory_path=(ezconnect).
to test whether this works without the sqlnet.ora but
Additionally, having an sqlnet.ora file (possibly empty for defaults) in the Instant client dir and setting TNS_ADMIN=<path to instant client dir> (via command line just before you run sqlplus), should override reading of sqlnet.ora file from multiple locations altogether.
To override reading of oracle settings from the registry, you could, at least as a temporary workaround, create an empty oracle.key file in the Instant client dir -- then sqlplus will look directly in registry branches HKCU and HKLM instead of under HKxx\software\oracle.
It seems as if a default setup of Instant client (on win x86) implies it will find settings from other oracle installations and source .ora files from other oracle home directory paths.
Thanks again for your help!
Regading your question about having sqlplus and instant client in the same server the problem is that I'm on a shared environment used by multiple remote applications for different users and one of these applications has one functionality that depends on instant client to connect to the database and it's bundled with it...
Thanks again for your comments and different workarounds!
FYI I've choosen the approach of placing an sqlnet.ora within instant client home directory and setting ezconnect which worked pretty well.