Oracle TNS connection with tnsnames.ora. Examples

Installed Oracle XE or 10g? Want to to connect but get error messages like;
ORA-12154: TNS:could not resolve the connect identifier specified?
Try checking your tnsnames.ora file. A sample tnsnames.ora file should be provided when you installed
the Oracle databas server. Can't find it? Here is a sample from the XE installation.

# tnsnames.ora Network Configuration File:

XE =
(DESCRIPTION =
(ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.XX.XX)(PORT = 1521)))
(CONNECT_DATA =
(SID = XE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Port 1521/tcp is one of Oracle default ports for the TNS listener. TNS stands for Transparent Network Substrate. The TNS listener is responsible for managing network connections to the Oracle database.

The next step is to switch to your oracle user.
# su - oracle

From the prompt, you could now try and connect to your database with the SQLPLUS tool.
If you have default installation of Oracle XE 10g, try to log in with the hr account.

$ ./sqlplus

If you get the error message ORA-12162: TNS:net service name is incorrectly specified,
you have forgotten to specify Oracle's SID. You will need to provide the SID to sqlplus to be able to connect properly. The SID in this example is XE.

$ ./sqlplus hr/hr@XE

or

$ ./sqlplus /NOLOG


SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 8 13:56:00 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect hr@XE
Enter password:


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

Make sure you have the tnsnames.ora file in your path. Under /etc for example.
/etc/tnsnames.ora

If you are unsure where sqlplus looks for you tnsnames .ora file. Try running the strace command with the trace option, and log it to a file for analysis.

$ strace -ft ./sqlplus > /tmp/sqlplus_strace

Ok, happy Oracle:ing.

Oracle 10g and XE

A little tips to you Unix haxors that want to play with RDBMs. Oracle has been kind enough
to let us knowledgefreaks download 10g for free! This is great, as many of us has not been able to
play around with this beast of a database. I mean, besides your employeers production Oracle databases. LoL

So what are you waiting for? Open up an account with Oracle and download the software. There is a XE (Express Edition) that works fine on a laptop, if you dont want to install the full Enterprise Edition.

The installation works as a charm, just make sure to change the database password after the installation, and edit your local firewall rules to deny any source ip address besides does you trust. Which should not be many. :-)

What else, well, on a Fedora Core 5, if you have SElinux enforced, (check with getenforce command)
you might run into some problems with the SELinux ACL's. You might wan't to go offline with your Oracle database and modify your SELinux settings, so you don't leave a slot open for any intruders while modifying.

Check dmesg for more information if you run into problems starting Oracle on a SELinux enabled system.
It might look like this.

audit(1155563893.424:77): avc: denied { execmod } for pid=24499 comm="sqlplus" name="libnnz10.so" dev=dm-0 ino=4819681 scontext=user_u:system_r:initrc_t:s0 tcontext=system_u:object_r:lib_t:s0 tclass=file

If SELinux shows audit posts with avc deniced for sqlplus, you might have to disable SELinux to debug. Remember to put it on right away!

SELinux to permissve mode. (Just logging mode)
# setenforce 0

SELinux to Enforcing mode.
# setenforce 1

To display current mode for SELinux
# getenforce


To start oracle-xe for example, simply run the service command.
# service oracle-xe start

Enjoy!