The tasks in this chapter show a TCP/IP connection between a database server and a client computer. The following conditions are assumed about the database server and client computer:

• Database server

  • The server is running on a network that can access the client

  • An Oracle database is installed

  • A listener is configured

  • TCP/IP protocol support is installed

• Client computer

  • The client computer is running on a network that can access the database server

  • Oracle Client is installed

  • TCP/IP protocol support is installed

In a TCP/IP network, each computer has a unique IP address. A name resolution service, such as Domain Name System (DNS), can be used to map the IP address of a computer with its host name. If a name resolution service is not used, then the mapping is typically stored in a centrally maintained file called hosts. This file is located in the /etc directory on Linux and the \windows\system32\drivers\etc directory on Microsoft Windows. For example, an entry for a database server computer named sales-server may look like the following:

#IP address of server     host name       alias
192.0.2.203             sales-server    sales.us.example.com

Before using Oracle Net to connect a client computer to a database server, confirm that the client computer can successfully communicate with the database server computer. Evaluating network connectivity can eliminate network-based errors.

The following procedure describes how to confirm network connectivity:

1. Confirm that the database server computer can communicate with itself with a loopback test as follows:

   1. To confirm hardware connectivity, enter the following command at the command line:

      ping ip_address
      

      In the preceding command, ip_address is the IP address of the database server computer, such as the following:

      ping 192.0.2.203
      

   2. To confirm the DNS or host name is configured properly, enter the following command at the command line:

      ping host_name
      

      In the preceding command, host_name is the host name of the server.

   3. To test the TCP/IP setup for the server, enter the following command:

      ping 127.0.0.1
      
      ping6 ::1
      

      The IP address 127.0.0.1 is the standard IPv4 address for a loopback test. The IP address ::1 (0: 0: 0: 0: 0: 0: 0: 1) is the standard IPv6 address for a loopback test.

2. Verify the client computer can successfully communicate with the database server computer.

   The method for verification varies according to the network protocol. For TCP/IP, you can use PING, FTP or TELNET utilities.

   If the client computer cannot reach the server, then verify that the network cabling and network interface cards are correctly connected. Contact your network administrator to correct these problems.

Oracle Net Listener and the Oracle Database server must be running in order for the database server to receive connections. The following procedure describes how to start Oracle Net Listener:

1. Start the listener with the Listener Control utility. From the command line, enter the following:

   lsnrctl
   LSNRCTL> START [listener_name]
   

   In the preceding command, listener_name is the name of the listener defined in the listener.ora file. It is not necessary to identify the listener if you are using the default name LISTENER.

   A status message indicating that the listener has successfully started displays.

2. Start the database as follows:

   1. Start SQL*Plus without connecting to the database using the following command:

      SQLPLUS /nolog
      

   2. Connect to the database as SYSDBA using the following command:

      SQL> CONNECT username as sysdba
      

      You will be prompted to enter a password.

      Note:

      For simplicity, this example does not perform the password management techniques that a deployed system normally uses. In a production environment, follow the Oracle Database password management guidelines, and disable any sample accounts. See Oracle Database Security Guide for password management guidelines and other security recommendations.

   3. Start the database using the following command:

      SQL> STARTUP database_name
      

      In the preceding command, database_name is the name of the database.

      See Also:

      Oracle Database Administrator's Guide for additional information about starting the database

3. Confirm that database service registration with the listener has completed using the Listener Control utility and the following command:

   LSNRCTL> SERVICES [listener_name]
   

   The SERVICES command lists the services supported by the database, along with at least one available service handler. If the database service registration is not listed, then enter the following SQL command:

   SQL> ALTER SYSTEM REGISTER;

   See Also:

   "Monitoring Services of a Listener" for additional information about the SERVICES command

If Oracle Connection Manager is installed, then start Oracle Connection Manager as follows:

1. Start the Oracle Connection Manager Control utility (CMCTL) using the following commands:

   cmctl
   CMCTL> ADMINISTER [instance_name]
   

   In the preceding command, instance_name is the name of Oracle Connection Manager to administer. You can determine the name by viewing the cman.ora file. The file is located on the Oracle Connection Manager computer in the ORACLE_HOME/network/admin directory.

   Oracle Connection Manager displays a status message indicating the name of the instance, and informs you that the instance has not yet been started.

   Note:

   If you do not provide an instance name as an argument, then provide Oracle Connection Manager with a fully qualified host name. This is the default. After you issue the ADMINISTER command, CMCTL displays the instance name as follows:

   CMAN_fully_qualified_host_name

2. Start Oracle Connection Manager that you have chosen to administer using the following command:

   cmctl> STARTUP
   

   Oracle Connection Manager confirms that the instance has been started, and provides status for the instance.

3. Exit from the Oracle Connection Manager Control utility using the following command:

   cmctl> EXIT

There are several methods to connect to an Oracle database. Table 6-1 lists syntax to connect to a database.

tool username@connect_identifier

SQLPLUS system@sales
Enter password: password

username@connect_identifier

exec sql connect :username identified by :password 

username@net_service_name 

SQLPLUS /nolog
SQL> CONNECT username@net_service_name

SQLPLUS /nolog
SQL> CONNECT scott@serverx
Enter password: password

(DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
      (CONNECT_DATA=(SERVICE_NAME=sales.example.com)) 
      (SECURITY=(SQLNET.KERBEROS5_CC_NAME=/usr/tmp/krbcache))
)

(DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=sales-svr)(PORT=1521))
      (CONNECT_DATA=(SERVICE_NAME=sales.example.com)) 
      (SECURITY=
          (KERBEROS5_CC_NAME=/tmp/krbuser1/krb.cc)
          (KERBEROS5_PRINCIPAL=krbprinc1@example.com)))

After network connectivity has been verified as described in "Confirming Network Availability", you can use the Easy Connect naming method to connect to the database. This naming method provides out-of-the-box TCP/IP connectivity to databases. It extends the functionality of the host naming method by enabling clients to connect to a database server with an optional port and service name in addition to the host name of the database. The following is the syntax to connect using Easy Connect:

CONNECT username/password@host[:port][/service_name][:server_type][/instance_name]

If Oracle Database server installation was performed in Typical mode, then the default service name used by the Oracle instance is the database name, and the following Easy Connect syntax can be used to connect to that instance:

SQLPLUS /nolog
SQL> CONNECT username@"host/db_name"
SQL> Enter password: password