Connection Basics

Before you can dig into your data with SQL and PL/SQL you must first get connected to your database. While there are many architectures, tools, and drivers for connecting such as JDBC, ODBC, ROracle, node-oracledb, Oracle Provider for OLE DB, OCI, cx_Oracle, etc. They essentially all boil down to just two methods: either the Oracle Client libraries or a JDBC thin driver. The distinction of thin JDBC driver is significant because Oracle’s JDBC OCI driver is still dependent on the Oracle Client.

Oracle Client Connections

For everyone not using JDBC Thin driver connections, they’ll need an Oracle client. Oracle offers a full client that not only contains the needed libraries but also a variety of tools to use those libraries with databases and other utilities. The other option is an instant client which is a subset of libraries and tools which can be copied into place directly without needing an installer.

In either case, the same core configuration files are used to define how connections are made: sqlnet.ora, tnsnames.ora, and possibly ldap.ora.

The sqlnet.ora file is located in the ORACLE_HOME/network/admin directory. The sqlnet.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable. It is not technically necessary to have a sqlnet.ora file, as missing values will be assigned defaults. However, it is usually recommended to define one. Even if you want use default values, the existence of the file will provide documentation of the intent. There are many values one can define in the sqlnet.ora file for configuration, security, timeouts, logging/tracing, and more. The most fundamental parameter is the NAMES.DIRECTORY_PATH value as this will determine not only the methods of name resolution to use; but also the search order for them.
The default is:

NAMES.DIRECTORY_PATH=(tnsnames, ldap, ezconnect)

Meaning name resolution will first attempt to look in a local tnsnames.ora file (see below,) if unable to resolve the name it will then look to an LDAP server such as OID or Active Directory, if the name has still not been resolved the connect string will then attempt to use it as an ezconnect string of host name and service name, optionally including port. To change the search order, simply rearrange the values in parentheses and they will be processed from left to right. If you don’t want to use a particular naming method, remove it. For example, if you don’t use an LDAP server for database name resolution then you can remove it from the path.

NAMES.DIRECTORY_PATH=(tnsnames, ezconnect)

As mentioned before, its not required to have a sqlnet.ora file at all, as defaults will be used, so a minimal file consisting of just the intended search path is sufficient to get started; but you may want to include a default domain for your most commonly used connect strings.

NAMES.DEFAULT_DOMAIN=mycompany.com

Using a default domain will allow your to abbreviate your connection strings from something like the following:

sqlplus myuser@testdb.mycompany.com

to

sqlplus myuser@testdb

While sqlnet.ora parmaters may be the driving force behind all connections, the file most will be using more directly will be tnsnames.ora. This file is located in the ORACLE_HOME/network/admin directory. The tnsnames.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable. On Linux and UNIX platforms, the tnsnames.ora file can also be stored in /etc or for Solaris /var/opt/oracle. The tnsnames.ora file is where local name definitions are stored, allowing you to create your own naming aliases for whatever connections descriptions you want.

A connection description consists, minimally, of 2 main parts. The ADDRESS which specifies the host or cluster information, including port and protocol, and CONNECT_DATA which specifies the service name to connect to or, for older systems, the SID (if you encounter old definitions still using SID, definitely try to upgrade to the service names when possible.) A minimal TNS description would look something like the following: (whitespace is legal to enhance readability, but is not necessary.)

testdb.mycompany.com=
   (DESCRIPTION=
      (ADDRESS=
         (PROTOCOL=tcp)
         (HOST=testserver)
         (PORT=1521)
      ) 
      (CONNECT_DATA=
         (SERVICE_NAME=testdb)
      )
   )

Other parameters may be added to the description to define failover, timeouts, security info, etc.)

You can add as many definitions as you want within the tnsnames.ora file and they will be searched top to bottom, if a name is encountered more than once, the last assignment the file will be used.

The ldap.ora file may not be present or needed in many systems, as it only applies when you have an LDAP server running with the Oracle naming schema installed and populated with names and descriptions. If your sqlnet.ora NAMES.DIRECTORY_PATH value includes ldap, then you should have an ldap.ora file to tell your clients where to find the ldap server and under which DN to find the Oracle entries. There are only 3 parameters to set in the ldap.ora file: The type of server, the DN where Oracle entries are located within the directory, and a list of LDAP server names and ports. For example:

DIRECTORY_SERVERS= (ldap1.mycompany.com:389:636,ldap2.mycompany.com:389:636)
DEFAULT_ADMIN_CONTEXT = "dc=mycompany,dc=com"
DIRECTORY_SERVER_TYPE = OID

Parameters for ldap timeouts or binding certificates are defined in the sqlnet.ora if needed. If you have multiple servers, all must use the same DN for the default_admin_context.

Comments may be added to any of the 3 files by using an octothorpe (pound sign, #) as the first character of the line. In the examples above I’ve used upper case for the Oracle-parameter names and lower case for the user-values; but all 3 files are case-insensitive.

JDBC Thin Driver Connections

The Oracle JDBC thin driver supports a variety of connection methods by setting various properties; but it also supports multiple URL formats as well.

The following form is one of the more common in modern code.

jdbc:oracle:thin:@//<host>:<port>/<service>
Example: jdbc:oracle:thin:@//myhost:1521/myservice

With the introduction of 10gR2, the thin driver added support for reading a local tnsnames.ora file. The driver still won’t use the Oracle client though, it will only read the connection information. To use this form you must also set the directory path in the oracle.net.tns_admin parameter so the driver knows where to find the tnsnames.ora file.

jdbc:oracle:thin:@<tnsnames entry>
Example: jdbc:oracle:thin:@mytnsentry

Rather than looking up the connection descriptor from a tnsnames.ora file, the description can be embedded directly. With this form, it is not necessary to set the tns_admin location.

jdbc:oracle:thin:@<Oracle Net Connection descriptor>
Example: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1521))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=myservice)))

It is also possible to lookup the connection descriptor from an LDAP server configured as a TNS name service.

jdbc:oracle:thin:@ldap://<ldap server><:ldap port>/<descriptor name>,<dn of descriptor base path>
Example: jdbc:oracle:thin:@ldap://myldapserver:636/mydb,cn=OracleContext,dc=mycompany,dc=com

This last form is the url style from 1998 and older; before version 8 of the database introduced services as a connection method instead of using SID. Despite recommendations from Oracle to not use this form anymore, it is unfortunately still common for applications to default to this form decades after it became obsolete.

jdbc:oracle:thin:@<host>:<port>:<SID>
Example: jdbc:oracle:thin:@myhost:1521:MYDBSID

Common Errors

ORA-01017: invalid username/password; logon denied
This may seem trivial to mention; but I’ve been contacted many times by users running into this error and being stymied as to what it means.

The error means exactly what it says. Check for typos in the name and/or password. Also note passwords in older databases were not case sensitive; but in new databases they might be. So be sure you have the capitalization correct. It’s also possible your username simply doesn’t exist in the requested database.

ORA-04031: unable to allocate X bytes of shared memory
This error means your connection attempt has reached a database server, contacted a listener and requested access to a database; but the instance has insufficient memory available to create a user session.

If the server is overloaded with numerous active databases there may be no immediate solution. You will need to wait until sufficient resources are available. If the server is overloaded because of runaway sessions or a few users/processes are consuming more than their share of memory resources an administrator may be needed to terminate offending sessions/processes.

ORA-12154: TNS:could not resolve the connect identifier specified
This error means you have attempted to connect to a database and the lookup of the name (either in TNSNAMES.ORA or ldap) has failed.

Either there is no database with such an entry or you have a typo in the name. A missing entry in tnsnames could be due to the file itself is missing or you have multiple oracle clients or tns_admin directory; each with its own tnsnames.ora file but populated differently.

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
This error means your connection attempt has reached a database server, contacted a listener and requested access to a database but the listener doesn’t know what database you are referencing.

This error could also mean your database is down; or it could mean your database is up but hasn’t registered with its listener yet. Most databases will auto-register with the listener shortly after startup. If the error persists for more than a few minutes you may need to contact your DBA to investigate.

ORA-12541: TNS:no listener
This error means your connection attempt has reached a server but there is no listener for the requested port (default is 1521.)

The error could be due to your definition specifying an invalid host and/or port and not necessarily a problem on the database servers.

TNSPING works but connection fails
The TNSPING utility only checks connectivity to a database listener. That is, it will attempt to resolve the name based on your client configuration (tnsnames, ldap, nis, ezconnect.) Assuming the name resolves, it will attempt a connection to the host and port, then ask the listener to respond. If those steps complete without incident, then TNSPING will report success (OK) with the time it took to complete those steps (usually a few milliseconds for local servers, longer for remote servers and/or those with many layers of software and hardware between them.)

TNSPING does not attempt to communicate to a database; nor is there an option to force it to. Thus TNSPING may succeed, but the target db may be down. TNSPING does not verify the service name or sid specified in the connection either. It only checks if there is a listener able to respond.

Summary

Hopefully this introduction will help get you started with the core parameters needed for connections as well as give some pointers of what some common errors might mean and what to do about them.