Oracle connectivity

Oracle connectivity requires special attention to the settings in three different files: listener.ora, sqlnet.ora, and tnsnames.ora.

listener.ora file

the listener.ora file is the server-side definition file for the Oracle database listener. This file contains instructions for listening protocols. SID descriptions can be set using two different methods:

• An SID setting

• A GLOBAL_DBNAME setting

The following example illustrates entries in a typical listener.ora file:

SID_LIST_LISTENER = 
	(SID_LIST = 
	(SID_DESC =
		(SID_NAME = PLSExtProc)
		(ORACLE_HOME = /work/oracle901)
		(PROGRAM = extproc)
	)
	(SID_DESC =
		(GLOBAL_DBNAME = ORA9i.foo.com)
		(ORACLE_HOME = /work/oracle901)
		(SID_NAME = ORA9i)))

sqlnet.ora file

The sqlnet.ora file defines Oracle network configuration properties. It controls logging and tracing as well as whether domain names are required on the connection.

tnsnames.ora file

The tnsnames.ora file is equivalent to the Sybase interfaces file. It contains network configuration information for DirectConnect for Oracle.

These three files are related, and entries in tnsnames.ora file will depend on the entries in listener.ora and sqlnet.ora files. The following examples illustrate entries in the tnsnames.ora file:

Example 1

If the listener.ora file is configured for TCP and is defined with only a SID setting, the tnsnames.ora file might take the following form:

ORA9I =
	(DESCRIPTION =
		(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = TCP)(HOST =
				oradb_box)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SID = ORA9ISID)
	)

)

Example 2

If the listener.ora file is configured for TCP and the listener is defined with a GLOBAL_DBNAME setting, you can use the previous example or the following example, which uses GLOBAL_DBNAME:

ORA9I =
	(DESCRIPTION =
		(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = TCP)(HOST =
				oradb_box)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVICE_NAME = ORA9ISID.foo.com)
	)
)

Example 3

If the sqlnet.ora file has the entry NAMES.DEFAULT_DOMAIN=foo.com, you will need to include the domain suffix in the connection name as shown:

ORA9I.FOO.COM =
	(DESCRIPTION =
		(ADDRESS_LIST =
			(ADDRESS = (PROTOCOL = TCP)(HOST =
				oradb_box)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVICE_NAME = ORA9I.foo.com)
	)
)

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF