Petya Petrova - Fotolia
How to connect to a pluggable database in Oracle Multitenant
Once you've created pluggable databases in an Oracle Multitenant system, the next step is to connect applications to them so they can be used. Here's how to do that.
In my first article in this series on using the Oracle Multitenant architecture, I showed how to create a container database and the pluggable databases that run inside it. Now, I'll demonstrate how to connect an application to a specific pluggable database, or PDB.
A container database can host multiple pluggable databases, in addition to a root container that stores system metadata and information on common users, who have rights to manage the root and all PDBs. In the Multitenant architecture, applications don't connect to the root container; instead, an application needs to connect to the pluggable database that holds its data.
In the previous article, after creating a container database and two PDBs, I described how to connect to a pluggable database by using the ALTER SESSION command in Oracle's SQL*Plus command-line interface to change my user session from the root container to one of the PDBs.
However, the ALTER SESSION command only works if you're running SQL*Plus locally on the database server. Typically, only database administrators (DBAs) do this. Most end users and their applications are remote from the database server. As a result, they need to connect through the network and contact the Oracle Net Listener process, which manages incoming traffic to Oracle databases.
Looking at the Oracle listener
Continuing on with the administrative work in my Oracle Multitenant testbed installation, I issued the LSNRCTL STATUS command on my database server to display current information about the listener setup for my container database and PDBs. It should be noted that most of the output from that command is removed for brevity's sake in the listing below:
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Service "firstpdb" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "hr_prod" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
From the summary results, I can see that my listener is running on the local host on port 1521. I can also see three services: firstpdb, hr_prod and orcl. And I can see that the ORCL database instance on the server includes handlers that provide connection points to all of the services.
The first two service names are identical to the PDBs I created in the first article -- whenever you create a PDB, the database will generate a service with the same name. You can also use the DBMS_SERVICE package -- or the SRVCTL utility if you're running Oracle Real Application Clusters -- to create other services to connect to a pluggable database.
Making the connection to a PDB
Now that I know the host name, the port and the names of the available services in my Oracle Multitenant environment, I can easily connect to one of the PDBs with SQL*Plus and then use the SHOW CON_NAME command to prove that I'm connected to the correct container, as follows:
[oracle@multi ~]$ sqlplus system/Password1@//localhost:1521/hr_prod
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Apr 18 14:49:04 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Last Successful login time: Thu Apr 18 2019 14:48:22 -05:00
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
SQL> show con_name
CON_NAME
------------------------------
HR_PROD
If your application uses the Java Database Connectivity API to connect to an Oracle database instance in an Oracle Multitenant system, you can provide the same information that I entered in SQL*Plus above to connect it to a pluggable database.
Alternatively, if your application uses a tnsnames.ora configuration file to define the connection information, you can include an entry like the following:
HR_PROD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = my_db_server)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = hr_prod.acme.com)
)
)
TNS, which stands for Transparent Network Substrate, supports application connectivity to Oracle databases via standard network protocols. If you're an experienced Oracle DBA, you likely already use TNS aliases like the one shown above to map databases and listener locations for applications. In this case, the fact that the data is now in a pluggable database is completely transparent to the application.
