How to Connect to the Oracle 12c Database Instance - dummies

How to Connect to the Oracle 12c Database Instance

By Chris Ruel, Michael Wessler

An Oracle 12c database instance isn’t much good if you can’t connect to it. Establishing a reliable, persistent, and secure connection to the database from the client is essential.

Oracle Net is supported by

  • Database administrator (DBA)-managed listener processes

  • Client- and server-side configuration files

  • Command-line utilities

  • Optional GUI administration tools

Connections can come into the database via several lighter-weight non-Oracle protocols such as ODBC or JDBC.

Local versus remote connections with Oracle 12c

Connections into the database can be one of two kinds:

  • Local (bequeath): A local connection originates from the same server the database is on and doesn’t use the database listener process.

  • Remote: All other connections from outside the database server or those from the server using the listener are remote connections. The easy way to determine whether a user is connecting remotely is if you have @TNS_ALIAS in the connect screen.

Communication flow with Oracle 12c

Connections to an Oracle database typically come across from a client located away from the database; over a network infrastructure; to the database server; through a database listener process; and, finally, into the database itself.

On the client side, the program calling the database references tnsnames.ora to find the database server host and protocol to send the request to. The request then leaves the client and goes onto the network utilization OracleNet. The default port for Oracle Net communications is 1521. Over this Oracle Net protocol is where database communications traffic flows between the client and database server.

After a client’s communication request reaches the database server host, it’s handed off to the listener. The database listener is a separate Oracle software process on the database server that listens for incoming requests on the defined OracleNet port (1521). When it gets a request, the listener identifies which database instance is targeted for that request and establishes a connection to that database instance.

On the server side, the listener uses the listener.ora file to make this determination. When the connection is established and the session begins, the listener steps out of the picture and allows communication between the database and client. Each client session has a dedicated server process on the server side. Within this dedicated server process, the user’s session code is executed. This represents the communication flow.

image0.jpg

The client contains the client application and tnsnames.ora file. It communicates to the database server over Oracle Net on port 1521. On the database server, a listener process is configured by way of the listener.ora file. The listener routes the incoming request to the target database instance (either dev11g or db01) and establishes the initial connection handshake between the database instance and client.

How to set up tnsnames.ora with Oracle 12c

You must provide the address or location of the database you’re trying to connect to. This information is often stored in the tnsnames.ora text file, which exists on the client you’re connecting from.

Note this “client” can be a user’s workstation, a web application server, or even another database server.

Here is a sample tnsnames.ora file that can be found in ORACLE_HOME_DIRECTORY/network/admin:

dev12c =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = orasvr01)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SERVICE_NAME = dev12c)
  )
db01 =
 (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = orasvr02)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SERVICE_NAME = db01)
  )

This particular tnsnames.ora contains 2 TNS (Transport Network Substrate) aliases, one for dev12c and one for db01. When connecting to a database instance, you actually specify the TNS alias (not database name). The TNS alias can be any name (such as dev12c or something more generic like dev or trainingdb). That flexibility means you can have a generic alias and not hardcode the database name.

Under HOST you specify either the DNS host name or the IP address of the server containing the database instance. Again, try to avoid hardcoded values such as IP address and use DNS names if possible.

PORT is the port the server-side listener process is listening on. It’s also the port you connect across the network on for your OracleNet traffic (thus the firewalls must be open on that port).

SERVICE_NAME is the service name of the database instance you’re attempting to connect to. You can also use SID, although Oracle is promoting the use of SERVICE_NAME instead.

The tnsnames.ora file is text based, and you can edit it by hand. After making changes, it’s not necessary to restart the database or listener process.

How to test the connection with Oracle 12c

The best way to test a connection is via the application, but that isn’t always possible. Preferably, you’re on the client tier and actually go through the same network path as the client applications

To execute a connectivity test, follow these steps to determine whether you can connect to the database instance:

  1. Go to the client tier.

  2. See whether Oracle client software such as SQL*Plus and tnsnames.ora is installed.

  3. Execute a sqlplus username@tns_alias, such as sqlplus mwhalen@dev12c.

  4. Enter the password to connect to the database.

Using the tnsping utility is an even faster method that doesn’t require a password. This utility connects over the network via the listener and establishes a handshake. It then terminates the connection and reports the results, which you see here:

$ tnsping dev12c
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 19-MAY-2013 12:20:07
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 
localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = dev12c))) OK (20 msec)

Testing connections is a good verification step for the DBA. If problems occur, it lets you catch them first instead of relying on users to report them later.