By: Juan Carlos Olamendy Turruellas
In this article, I want to talk about the methods to connect to Oracle 12c database system. Basically, we have two methods for connecting: locally using the bequeath method and remotely using network services. For the examples below, I will use the following deployment architecture: Oracle database 12c running inside CentOS 6.4 64bits and Oracle Clients running inside Ubuntu 14.04 64bits as shown in the following figure.
When the client and the database system are on the same node (aka server) and have set both ORACLE_SID and ORACLE_HOME environment variables, the client can connect directly to the database system using the bequeath protocol. In this way, the client process (that is, sqlplus command) forks an Oracle dedicated server process (later I will talk about different type of server processes), which in turn communicates with the client process over two pipes (one for reading and one for writing) for processing the requests. Because bequeath protocol relies on pipes, it cannot work outside the local node where the Oracle database system resides. The bequeath protocol is also used for the listener as well (I will talk about network connections and listener below in this article) to fork a new background server process for processing incoming network requests.
Let´s see these concepts on practice.
First step is to launch the client process; let´s say the sqlplus command.
In the figure 2, we can see that the ORACLE_SID and ORACLE_HOME variables point to the instance name orcl and the Oracle 12c database product respectively. After that, we run sqlplus command and we can see that client process is running alone (no more processes related to any instance).
In the figure 3, we connect locally to an Oracle server process using bequeath protocol (calling the fork and exec Unix system calls). We can see that a new process with id “3333” and command line “oracle$ORACLE_SID (DESCRIPTION=(LOCAL=YES)(ADRESS=(PROTOCOL=beq)))” is a child of the sqlplus client process (because sqlplus forks itself and execs the Oracle binary). We can also see the two pipes for the bidirectional communication (one for reading and another one for writing) associated to this process.
Now we are ready to send SQL statements to Oracle server process; for example to start an instance as well as to mount and open a database as shown below.
In order to understand how to connect to an Oracle database system remotely, we need to know how Oracle networking works.
The core of Oracle networking is Oracle Net Services. Oracle Net Services is a set of services to enable the configuration and management of connections between the database system and end-users typically using TCP/IP as the network protocol.
Oracle Net Services comprises of Oracle Net, Oracle Net Listener, Oracle Connection Manager, Oracle Net Configuration Manager and Oracle Net Manager.
Oracle Net is the component which initiates, establishes and maintains connections between the clients and Oracle database systems. So, Oracle Net must be installed on both the client and the server.
At the server side, the Oracle Net Listener service must be running and listening (on some TCP/IP port) for connection requests. After a request arrives, the listener checks the request using its configuration files; and then it decides if it accepts or it rejects the request. When the listener accepts the request, then it disappears from the game. The listener rejects the request because there is no such service registered.
A listener can listen for more than one database instance registered as services. Each database system is represented in Oracle Net by one or more services; although one is typically. A database service is identified by a service name. SERVICE_NAMES parameter in the init.ora file specifies the list of service name associated to an instance. If SERVICE_NAMES parameter is not specified; then the name comprises the DB_NAME plus DB_DOMAIN parameters.
If the TCP/IP connection is related to dedicated server process mechanism, the listener creates the server process for us using the bequeath mechanism (fork and exec Unix system calls). The new dedicated server process inherits (by the system calls) the TCP/IP connection to the client side from the listener, so at the end of the day, there is network connection between the client and server sides. This server process is dedicated to the client process during the life of the session in a one-to-one mapping and enables the processing of SQL statement.
If the TCP/IP connection is related to a shared server process mechanism, then the listener knows the dispatchers associated to the instance (in this scenario, the client process never talks directly to the server process), picks up an available dispatcher and sends back to the client the TCP/IP information about connecting to a particular dispatcher. In this case, the listener and dispatchers are running in different TCP/IP ports. When the client process connects to the dispatcher, then we have a network connection between the client and server sides through the dispatcher. In this case, the pool of shared server processes is available to process SQL statement for several sessions at once in a share mode. When a request from any session arrives, then the dispatcher puts the request into a request queue. The first available shared server process takes the request, serves it and puts it back into a response queue. The dispatcher monitors the response queue upon seeing a result that will send back to the client.
At the client-side, a client process uses the connect descriptors. A connect descriptor comprises two items: database service name and address; and the address in turn comprises three items: communication protocol, the hostname and port number. Typically, the communication protocols are: TPC/IP and TCP/IP with SSL as well as port number 1521 and 1526 on Unix servers.
Let´s see and example of a connect descriptor.
(ADDRESS=(PROTOCOL=tcp) (HOST=server-name) (PORT=1521))(CONNECT_DATA=
In this example, the address part specifies that TCP is the network protocol for the communication; the host is the server name where the listener is listening for requests on the port 1521 as well as the service name is service.company.com.
Then it appears the concept of connect identifier as pointer to connect descriptor as shown below (sales is the identifier).
We connect to database systems from the client side by providing connect strings. A connect string comprises username/password and the connect descriptor as show below.
Or the simplest way with a connect identifier as shown below,
Now let´s see these concepts in practice.
Let´s start by configuring the client-side.
First of all, we need to install the Oracle Net binaries in Ubuntu 14.04 64 bits by downloading from the Oracle site the following binaries oracle-instantclient12.1-sqlplus-12.1.*.x86_64.rpm, oracle-instantclient12.1-basic-12.1.*.x86_64.rpm and oracle-instantclient12.1-devel-12.1.*.x86_64.rpm.
Next step is to install alien command to convert Red Hat rpm packages into Debian deb packages as shown in the figure 5.
Next step is to convert and install *.rpm packages as shown below in the figure 6.
Now we need to set the $ORACLE_HOME (location where the client binaries are installed) and $TNS_ADMIN (set to $ORACLE_HOME/network/admin) environment variables as well as update the $PATH (append with $ORACLE_HOME/bin) and $LD_LIBRARY_PATH (append with $ORACLE_HOME/lib) environment variables in the /etc/profile file as shown below in the figure 7.
We also need to install the libaio1 which is needed by the sqlplus command as shown below in the figure 8.
In order to connect to a database instance on a remote server, we need to configure the connect identifier and their connect descriptors in a configuration file named tnsnames.ora. By default, this file is located at $ORACLE_HOME/network/admin directory.
Another important file for networking is sqlnet.ora containing networking parameters. A typical configuration is shown below in the figure 9.
In order to configure the tnsnames.ora file we need the following information about the remote database system:
To get the previous required information, we need to go to the server-side. First of all, we need to know that the listener is well configured and running. The listener configuration is stored at the listener.ora file located at $ORACLE_HOME/network/admin.
All the configuration parameters in the listener.ora file have default values, so we don´t have to configure manually the listener. When an instance is started, the database system registers itself automatically on the listener (more specifically the PMON process) and then the listener starts listening for connection requests related to this database instance. For automatic registering, the only requirement is that the parameter file (init.ora) or SPFILE file contain the SERVICE_NAMES and INSTANCE_NAME parameters.
We can check that the listener is running as well as to find out the connection information using the hostname and lsnrctl commands as shown below in the figure 10.
We can see that:
Now with the right information in hand, we can go to the client-side and finished the configuration in the tnsnames.ora file as shown in the figure 11.
Now, we can establish a connection to the remote database with username: c##testuser, password:testuser and connect identifier:remote_orcl as shown below in the figure 12.
You can also check on the server-side that there is remote connection as shown below in the figure 13.
Now let´s see how to connect in the Oracle 12c multitenant world. The multitenant option enables a single container database (CDB) to host multiple pluggable databases (PDB).
In order to explain how the concepts above are applied to Oracle multitenant option, let´s execute some commands in practice.
First of all, we go to the server side and connect to the root of a container database and check the details about the instance and the CDB as show below in the figure 14.
Now, we can see how many pluggable databases are in the container CDB$ROOT as shown in the figure 15.
Next step is to open the sample pluggable database named PDBORCL (because it is actually in the mounted state) as show below in the figure 16.
After that, we switch the session to the PDBORCL pluggable database, create the testuser user and finally grant access to PDBORCL as show below in the figure 17.
Now we can see that the listener has registered two important services:
The information can be queried using the command lsnrctl status as shown below in the figure 18.
Now we´re going to the client side and configure the $ORACLE_HOME/network/admin/tnsnames.ora with the new Oracle service as shown below in the figure 19.
And now we can connect to the PDB service as shown below in the figure 20.
In this article, I´ve explained the mechanisms that a client has in order to connect to Oracle database system. I´ve detailed using real-world examples the two common method: local and remote connection.
Fabulous! I read probably 10 different sites trying to get my head around how to create a user in Oracle 12c XE. Had I started here, I would have been done a long time ago!