1

The oracle database was installed by someone and I have around 6 tns.ora files in my machine each in individual path.

4 files are found in Network folders (2 inside Admin inside network) inside Client 1, Client 2, Client 3, Client 5 all under C:\App\Oracle\Product directory.

I have issues when using database through cmd, powershell and python all with the same error : ORA-12154: TNS:could not resolve the connect identifier specified.

How do we configure the correct file everywhere?

Arun
  • 65
  • 1
  • 9
  • 1
    Use `TNS_ADMIN` environment variable and set the path of correct directory in which tnsnanes.ora file in. – Popeye Aug 05 '19 at 14:31
  • 1
    If you have four different Oracle Clients installed then you should really do a cleanup and install just one (or one each for 32-bit and 64-bit, if needed). See https://stackoverflow.com/questions/8450726/how-to-uninstall-completely-remove-oracle-11g-client for help – Wernfried Domscheit Aug 05 '19 at 14:39

1 Answers1

5

You've been given hints of what to do. Here's a more detailed answer; hopefully, it'll help.

Basically, every Oracle software product (I've worked with) has its own TNSNAMES.ORA file. If you want to be able to connect to a new database, you have to enter it into each of those TNSNAMES.ORA files. "6 files" means "6 edits" (or "1 edit + 5 copies") which means that have 6 exactly the same files.

In order to avoid that, use option which will let you to keep and maintain only one TNSNAMES.ORA file. In order to do that, I'd suggest you to

  • create a new directory (such as - on MS Windows - c:\ora_library)
  • copy any of existing tnsnames.ora files in there
    • edit its contents so that it contains all databases you are connecting to
  • then create tns_admin environment variable. Again, on MS Windows (7):
    • Start - right click "Computer" - Properties
    • Advanced system settings
    • Advanced tab - Environment variables button
    • For System variables, create a new variable whose name is tns_admin and its value is directory you've previously created: c:\ora_library

If you check it on the command prompt, it looks like this:

C:\>set tns_admin
tns_admin=C:\ora_library

That's all; the next step is to test it. No matter which Oracle software you try, it should be able to establish connection.

From now on, any change you have to make should be done in the c:\ora_library\tnsnames.ora file; disregard any other file (you can even delete them or rename to e.g. tnsnames.old to avoid confusion).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57