We’ll investigate the most common networking case: a network-based connection request over TCP/IP. In this case, the client is situated on one machine and the server resides on another, with the two connected on a TCP/IP network. It all starts with the client. The client makes a request using the Oracle client software (a set of provided application program interfaces, or APIs) to connect to a database. The client could be a SQL*Plus on your laptop, an application using a JDBC connection, TOAD, SQL Developer, and so on.
From the client side, when you connect to an Oracle instance, you need to provide a connection string. The connection string contains the information that allows your client software to locate and connect to the remote database instance over the network. The connection string consists of the following information:
•\ Username: This is sometimes called the schema name or database user account.
•\ Password: The password assigned to the username.
•\ Hostname (or IP address): The host (server) that the database is running on.
•\ Port: The port that the database listener is listening on. The default port is 1521.
•\ Service name: You can conceptually think of a service name as a synonym for the database instance you want to connect to. The service name is oftentimes the instance name or the pluggable database name that you’re connecting to. More technically, a service name represents groups of applications with common attributes, service-level thresholds, and priorities. The number of instances offering the service is transparent to the application, and each database instance may register with the listener as willing to provide many services. So, services are mapped to physical database instances and allow the DBA to associate certain thresholds and priorities with them.
If you’re using a SQL*Plus client to connect to an Oracle instance, you can specify all of the prior information on the command line (this is referred to as the easy connect method). The easy connect method of connecting has this basic syntax:
database_host[:port][/[service_name]
For example, here I connect as the user scott, with a password of tiger, the hostname is localhost, the listener port is 1521, and the pluggable database is ORCL:
$ sqlplus scott/tiger@localhost:1521/ORCL
Starting with Oracle 19c, the easy connect syntax is significantly enhanced and renamed as easy connect plus. This enhanced connection syntax enables easier use of wallets, TLS connections, load balancing, connection timeouts, and so on. This is useful when accessing a service in Oracle’s cloud infrastructure (OCI) which may require TLS for secure communication and wallet information. Listed next is the easy connect plus syntax (this should all go on one line, but doesn’t fit neatly on this page):
[[protocol:]//]host1{,host2}[:port1]{,host2:port2}[/[service_name] [:server_type][/instance_name]][?parameter_name=value{¶meter_ name=value}]
For example, using the easy connect plus syntax to connect to a database in Oracle’s cloud infrastructure specifying the required SSL transport information (this rather lengthy connection string should go on one line, but does not fit nicely on this page):
$ sqlplus scott/tiger@tcps://adb.us-phoenix-1.oraclecloud.com:1522/ gjsogz09yzhnqz4_db202102062120_high.adb.oraclecloud.com?ssl_server_cert_dn =”CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US”
While the prior easy connection methods work fine, it’s usually easier to manage the connection information by placing the service connection details in a file, and then reference the entries in the file when connecting to the instance. This is especially true if you have more than one database in your environment. In Oracle, the tnsnames.ora file is designated to hold this database connection information. This plain text configuration file is commonly found in the $ORACLE_HOME/network/admin directory ($ORACLE_HOME represents the full path to your Oracle installation directory). For example, I have two entries in the following tnsnames.ora file, one for the local database on my laptop:
ORCL=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCL)))
And here is the entry for the connection to a database in Oracle’s cloud:
OCLD = (description= (retry_count=20)(retry_delay=3) (address=(protocol=tcps)(port=1522)(host=adb.us-phoenix-1.oraclecloud. com))(connect_data=(service_name=sogz09yzhnqz4_db202102062120_high. adb.oraclecloud.com))(security=(ssl_server_cert_dn=”CN=adwc.uscom- east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US”)))
Tip TNS stands for Transparent Network Substrate and is “foundation” software built into the Oracle client that handles remote connections, allowing for peer-to- peer communication.
