2

I want to create a connection to remote Oracle server using SQLplus client and tnsnames.ora without installing Oracle 11g server on the Linux client. I used debian and bash script to generate reports from remote Oracle servers. Is this possible?

I tried installing SQLplus client but I can't find the tnsnames.ora files.

If possible, can you please give me an example of configuring SQLplus client to use tnsnames.ora files?

I have installed the "basic instant client" and the "sqlplus addition" package. I can connect normally with sqlplus user/password@host:port/servicenames. I want to create bash scripts to execute *.sql files on more than 10 instances. So, if I can just connect with sqlplus user/pw@instance It will look simple. it's is possible?

Update : Solved by copying tnsnames.ora files to /etc.

refer to this page on post #3

Thanks to : Wernfried Domscheit, EdStevens, Paul White

closed as off-topic by mustaccio, Marian, Kin, Philᵀᴹ, billinkc May 16 '16 at 1:55

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Marian, Kin, Philᵀᴹ, billinkc
If this question can be reworded to fit the rules in the help center, please edit the question.

  • There are examples of tnsnames.ora in the Oracle documentation; Google search also returns plenty of them. – mustaccio May 8 '16 at 18:47
  • @mustaccio, hi thanks for fast respone, I know the script config of tnsnames.ora, but I don't know how to config sqlplus to use tnsnames.ora. usually tnsnames.ora comes with oracle 11g server. – jimmy May 8 '16 at 18:52
  • You just set $TNS_ADMIN and plonk the file in there – Philᵀᴹ May 12 '16 at 6:34
1

First you have to tell Oracle to use the tnsnames.ora file at all. For this open/create file sqlnet.ora and insert row like this:

NAMES.DIRECTORY_PATH = (TNSNAMES)

You may add other methods for resolution, e.g. 'LDAP', see DIRECTORY_PATH. Entry tnsnames tells Oracle to use an tnsnames.ora file. As you see in documentation NAMES.DIRECTORY_PATH=(tnsnames, onames, hostname) is the default, so actually the sqlnet.ora file could be skipped in this case.

Regarding the location of these two files the answer is not trivial. Different application may search different folders and may have different rules of precedence.

Following folders could be part of search path:

  • Folder defined by Environment variable TNS_ADMIN
  • Folder ${ORACLE_HOME}\network\admin

    In case your files are located in different folder, it would be a good idea to put a symbolic link at this location pointing to your folder.

  • Current folder
  • Folder where your application binaries are stored

...and in case of Windows (just for information) also these ones

  • Folder defined by Registry entry HKLM\SOFTWARE\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{ORACLE_HOME_NAME}\TNS_ADMIN
  • machine.config, resp. web.config file, section oracle.manageddataaccess.client/version/settings/setting/@name="tns_admin"
  • Thank you for hint. I place sqlplus packages and the tnsnames.ora file on /opt/oracle/instantclient_12_1/ and set env TNS_ADMIN to that but I'm not set the NAMES.DIRECTORY_PATH = (TNSNAMES) because can't find sqlnet.ora file. I Think this will not work if you just set the TNS_ADMIN only. I try to copy tnsnames.ora to /etc/ and it's works! Thanks Wernfriend :) – jimmy May 9 '16 at 23:51
3

tnsnames.ora doesn't "come with" anything. The key is not that file, as it is nothing but a names resolution file. And sqlplus is nothing but a CLI application. If you want to connect to an Oracle database (or ANY database) you must have, at a minimum, the client software for that particular database product. For oracle you need to download and install the Oracle client. The easiest would be the "instant client", which you can download from here: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html. If you want the full "Admin" or "Runtime" client, download from edelivery.com and select just the client software. Pay attention to the license terms.

  • If you could add your answer with configure sqlplus to use tnsnames.ora files, maybe it will help other. like add env on $ export TNS_ADMIN=[path to tnsnames.ora]. Thanks all member :) – jimmy May 9 '16 at 11:24
-2

Hope this helps... (wrapped for readability)

sqlplus username/password@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
    (HOST=hostname)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=service_name)
    (INSTANCE_NAME=instance_name)))'

Not the answer you're looking for? Browse other questions tagged or ask your own question.