How do I generate a tnsnames.ora file to connect to an Oracle Database?

  • I have installed Oracle 11g, and I can connect as sysman to the Oracle database, but there is no tnsnames.ora file that I can find.

    Do I need to generate the tnsnames.ora file myself? If so, where do I place it? If not, how does Oracle generate it for me? If I do need to generate it, what is the appropriate syntax for the file?

  • Aaron

    Aaron Correct answer

    8 years ago

    You can easily create a tnsnames.ora [text] file. It should be in $ORACLE_HOME/network/admin/ and should look something like this:

     ORATST=
     (description=
       (address_list=
         (address = (protocol = TCP)(host = fu.bar)(port = 1521))
       )
     (connect_data =
       (service_name=oratst)
     )
    )
    

    Good answer, although I found that I had to name my file `$HOME/.tnsnames.ora`; the path suggested in the answer seemed to be ignored by sqlplus.

    @DuffJ then something is wrong with your environment.

  • The default directory for a tnsnames.ora file is

    /u01/app/oracle/product/<version>/<dbname>/network/admin/tnsnames.ora
    

    Contents:

    <alias> = (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <ip>)(PORT = <port>))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = <dbname>)
        )
      )
    
    • alias: you can choose this and use it as connection string later on.
    • port: the default is 1521

    More info here: http://docs.oracle.com/cd/B28359_01/network.111/b28317/tnsnames.htm

    The default directory is nothing of the sort. It's relative to `$ORACLE_HOME` and therefore will be `$ORACLE_HOME/network/admin` (where `$ORACLE_HOME` can be multiple different directories where there's more than one Oracle install/patch level) or can be overrided by the `$TNS_ADMIN` environment variable. It's completely up to the user where the software resides.

    Honestly it is the default on a linux system... If you don't touch a thing it will be put there... thus being the default. But $ORACLE_HOME if not tampered with is indeed a better solution.

    I have run Oracle on literally hundreds of Linux systems and that is NOT the default, nor is there such a thing. Perhaps you mean that's the example in the documentation, well they had to choose something. There is no Linux distro that I know of that has a /u01 filesystem "by default", you must have created it and told Oracle to use it!

  • $ORACLE_HOME/network/admin is the directory where tnsnames.ora file is created by DBCA and/or NetCA.

    $ORACLE_HOME is a path like /u01/app ..., it is an OFA compliant path that the installler uses as default if not overriden by installer.

  • If your OS is Windows 10, you can find tnsnames.ora file in the following unc path:

    C:\app\myAccount\product\11.2.0\dbhome_1\NETWORK\ADMIN
    

    Where myAccount is your Windows account name.

    In other words, it's `%ORACLE_HOME%\NETWORK\ADMIN`, right?

    Yes, that is right. Remember Oracle by default store tnsnames.ora in the $ORACLE_HOME/network/admin directory on UNIX operating systems and in the %ORACLE_HOME%\network\admin directory on Windows operating systems.

    $ORACLE_HOME wasn't working for me on my environment, so knowing the full path really helped... just knowing it was in C:\app\... gave me the start I needed and I was able to track it down from there. Saved me hours of searching!

  • As far as i know the tnsnames.ora needs to be placed at the <ORA_INSTALL_FOLDER>\product\11.1.0\client_1\network\admin\ folder

    Documentation can be found here

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM