Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have installed Oracle 11g R2 standard edition (server software, 2GB download from Oracle).

I found that the 'Environment Variable PATH' has not been set. Which of these directory should I use for the PATH value:-

  1. Oracle base
  2. Software location
  3. Database file location

Also do I need to create 'TNS_ADMIN' environment variable and 'TNSNAMES.ora' file

Kindly advice. Thank you

share|improve this question

migrated from stackoverflow.com Aug 19 '12 at 20:02

This question came from our site for professional and enthusiast programmers.

1  
You should post this question on serverfault.com –  Annjawn Aug 12 '12 at 17:21

2 Answers 2

up vote 2 down vote accepted

As @ik_zelf mentioned, your PATH variable will need to include the "bin" directory of your Oracle software install. This will allow you to execute commonly-needed commands/utilities like "sqlplus" and "lsnrctl" without changing directories. This is typically $ORACLE_HOME/bin

Your TNS_ADMIN variable needs to be set in a similar matter, pointing to the location of your tnsnames.ora, listener.ora, etc. By default, SQL Plus checks this directory (defined by TNS_ADMIN) for tnsnames.ora. This is typically $ORACLE_HOME/network/admin

If you want to connect to your database via TNS, your tnsnames.ora file (assuming a ORACLE_SID of "myDatabase", a host of "myHostname" and the default port) would look something like this:

myDatabase = 
    (DESCRIPTION = 
        (ADDRESS = (PROTOCOL = TCP)(HOST = myHostname)(PORT = 1521))
        (CONNECT_DATA = (SERVICE_NAME = myDatabase)))

This would allow you to connect locally simply by doing the following from a command line:

sqlplus username/password@myDatabase

Of course, as SQL Plus looks for the tnsnames.ora file in the location defined by $TNS_ADMIN, this will only function locally. To access this database via TNS (Transparent Network Substrate) from a remote machine, you would have to install the Oracle client and ensure that the TNS entry for myDatabase (as described above) is included in the remote machine's tnsnames.ora file as well.

share|improve this answer
    
Today when I check my PATH envionment variable it is set to the following path :- C:\app\thoshiba\product\11.2.0\dbhome_1\bin. This might have got created after I logged in to Oracle Enterprise Manager with sys account. However, I dont find any TNS_ADMIN environment variable. SQLPlus seems to work fine, I am able to login to SQLPLUS with / as sysdba –  sujimon Aug 13 '12 at 9:59

Normally you use the oraenv script to set you environment correctly for a given ORACLE_SID. The ORACLE_SID and ORACLE_HOME combinations are maintained in /etc/oratab or /var/opt/oracle/oratab which is read by the oraenv script that should be sourced (called with a .)

What oraenv by default sets does is make sure that PATH contains at least $ORACLE_HOME/bin

If you want to have some control over where your SQL*net files are located it is smart to create a TNS_ADMIN environment variable that points to the directory where amongst others tnsnames.ora is located. The tnsnames.ora file is most important to be able to point to a - local or remote - database using SQL*net.

If you only access your database from the same machine as where the database is running and you do not access an other database, a tnsnames.ora is not needed, just convenient. Normally we have a tnsnames.ora that contains lots of database on the site.

share|improve this answer
    
Thank you for your advice. While installing Oracle, in the Typical Installation Step the global db name was 'orcl' and i had provided an adnminstrative password. So I was trying to log in to Oracle Enterprise Manager with userid='orcl' and pass='mypass'. Today I came across this thread in Oracle forums : forums.oracle.com/forums/thread.jspa?threadID=843479 which says to login using 'sys' and the 'password' selected at install time and this resolved the login issue. I found a tnsnames.ora on my hard drive using windows search –  sujimon Aug 13 '12 at 9:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.