Instructor's Notes

(If applicable)

• Lesson 22: Introduction to Oracle Networking
• Getting started with Oracle Net: a hands-on, practical overview including configuring files, coding the TNSNAMES.ORA file and configuring and managing the Listener.

Instructor's Notes

(If applicable)

• Oracle Net is the software that interfaces between the database and the network protocol, for example TCP/IP. So the database itself does not and is not capable of directly communicating with the network protocol - that is the job of Oracle Net.
• Oracle Net uses a “transparent network substrate” (TNS) protocol. This protocol is transparent to the database user, who simply makes requests of remote database objects. It is the job of the TNS protocol (Oracle Net) to translate the logical request into a physical request.
• Oracle Net is the new name for the Oracle networking software. It started as SQL*Net (many people still call it that), then Net8 and now Oracle Net.

Instructor's Notes

(If applicable)

• Oracle Net is configured by configuration files. These include (but are not limited to):
• TNSNAMES.ORA - This file contains a list of service names, which translate into the instances the client can connect to. It (tnsnames.ora) is used on outgoing requests.
• SQLNET.ORA - this file is used to configure Oracle Net on the client. For example, tracing can be turned on.
• LISTENER.ORA - This file configures the listener on the host (server).

Instructor's Notes

(If applicable)

• The TNSNAMES.ORA file contains a list of service names which are translated into instances that the client can connect to. A copy of the tnsnames file must reside on every client. Since this can be an overwhelming task, many sites use a directory, for example, Oracle Names.
• The TNSNAMES.ORA file contains connect descriptors. A connect descriptor identifies the host, protocol, port and instance you can connect to.

Instructor's Notes

(If applicable)

• The tnsnames.ora file is used by Oracle Net to translate outgoing Net service name requests such as CONNECT dave/dave@angela into:
• protocol
• host
• port on host (1521 is the default port for the Oracle listener)
• instance service name (or SID for Oracle8i)
• In this snippet from a TNSNAMES.ORA file (see slide), we see the Net service name “angela” defined. After saving the changes to the file, the client can issue the connect command:
  • SQL> connect dave/dave@angela
• Warning
  • The TNSNAMES.ORA file is notoriously finicky about accepting changes. I recommend making a backup copy before editing, and using an existing entry for a template when creating a new connect descriptor. Consider using the GUI-based Net Configuration tool provided by Oracle to make changes to this file.

Instructor's Notes

(If applicable)

• The host name entry in the connect descriptor can be a alpha-numeric host name (versus an IP address). In this case, check the contents of the /etc/hosts file for the IP address translation.

Instructor's Notes

(If applicable)

• The listener is an Oracle Net component that listens for database requests on the host (A.K.A. server).
• When the listener starts, it reads the LISTENER.ORA configuration file. This file is, by default, in the $ORACLE_HOME/network/admin directory. However, the location of this file can be changed by specifying an alternative directory in the $TNS_ADMIN environment variable.
• The listener can be managed with the listener utility (LSNRCTL). For example, we can start, stop and list the status of the listener. Examples follow on the following pages.

Instructor's Notes

(If applicable)

• The status command will display the basic status of the listener. In this case, we can see that the listener is started (it has been up for 114 days!).
• If the listener was not running, you would see something like:
• LSNRCTL> status
• Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
• TNS-01103: Protocol specific component of the address is incorrectly specified
• TNS-12541: TNS:no listener
• TNS-12560: TNS:protocol adapter error
• TNS-00511: No listener
• Linux Error: 2: No such file or directory
• LSNRCTL>

Instructor's Notes

(If applicable)

• Use the LSNRCTL STATUS command or the UNIX "ps" command to determine if your listener is started.
• If the listener is not started, enter LSNRCTL from the OS prompt and enter the START command.

Instructor's Notes

(If applicable)

• Stopping the listener is accomplished with the LSNRCTL STOP command. If LSNRCTL is not available or will not respond, you can resort to the UNIX "kill -9" command.

Instructor's Notes

(If applicable)

• If the DBA has password protected the listener, some commands, such as STOP, require the listener password. Use the CHANGE_PASSWORD command to password protect the listener.
• If the STOP command fails with the TNS-001169 error, you will need to give the utility the password. As shown in this slide, the command used to supply the password (LSNRCTL does not prompt for the password) is SET PASSWORD.

Instructor's Notes

(If applicable)

• The listener is configure with a parameter file called listener.ora. This file is, by default, found in $ORACLE_HOME/network/admin. However, this can be overridden with the $TNS_ADMIN environment variable.
• The snippet shown in this slide illustrates that the listener is configured to listen on the ANGELA host, for TCP requests on port 1521. It is listening, and can spawn sessions for instance (SID) DAVE.
• Use care when modifying this file. Create a backup copy first. Consider using the GUI-based Net Configuration tool provided by Oracle.

Instructor's Notes

(If applicable)

Instructor's Notes

(If applicable)

• Oracle Net Workshop
  1. Backup your TNSNAMES.ORA file
  2. Create a new connect descriptor in your local TNSNAMES.ORA file. The service name should be “CLASS” and it should connect you to the database you have been using in class this week. Test by using the descriptor name in a connect string (e.g. CONNECT DAVE/DAVE@CLASS). Make sure it works! Alternative: Connect to a student's machine. If you can ping the student's machine, you can connect! Test.
  3. Check the status of the listener on the machine you are attempting to connect to.
  4. Stop the listener.
  5. Attempt connections using the TNSNAMES entry created in step 2. Does the connection work? Why?
  6. Re-Start the listener.
  7. Insure that you can connect to the database.
  8. Restore your original TNSNAMES.ORA file.