Recall from Chapter 2 that a listener is the process that enables remote network connections to a database. Most database environments require a listener to operate.
When a client attempts to connect to a remote database, the client provides three key pieces of information: the host the listener is on, the host port the listener is listening on, and a database service name.
Each database has one or more service names assigned to it. By default, there is usually one service name that is derived from the database’s unique name and domain.
You can manually create one or more service names for a database. DBAs sometimes create more than one service so that resource usage can be controlled or monitored for each service.
For example, a service may be created for a sales application, and a service may be created for the HR application. Each application connects to the database via its
service name. The service connection information appears in the SERVICE_NAME column of the V$SESSION view for each session.
If you start a default listener with no listener.ora file in place, the PMON background process will automatically register any databases (including any pluggable) as a service:
$ lsnrctl start
Eventually, you should see the databases (including any PDBs) registered with the default listener.
When starting the listener, if there is a listener.ora file present, the listener will attempt to statically register any service names that appear in the listener.ora file.
By default, the PDBs are registered with a service name that is the same as the PDB name. The default service is typically the one that you would use to make connections:
$ sqlplus pdbsys/Cr4zyPa$$word1@mm23c:1521/salespdb as sysdba
You can verify which services are running by connecting as SYS to the root container and querying like this:
SQL> select name, network_name, pdb from v$services order by pdb, name;
You can also verify which services a listener is listing for via the lsnrctl utility:
$ lsnrctl services
Oracle recommends that you configure an additional service (besides the default service) for any applications that need to access a PDB.
You can manually configure services by using the SRVCTL utility or the DBMS_SERVICE package. This example shows how to configure a service via the DBMS_SERVICE package.
First, connect as SYS to the PDB that you want to create the service in via the default service:
$ sqlplus pdbsys/Cr4zyPa$$word1@mm23c:1521/salespdb as sysdba
Make sure the PDB is open for read-write mode:
SQL> SELECT con_id, name, open_mode FROM v$pdbs;
Next, create a service. This code creates and starts a service named SALESWEST:
SQL> exec DBMS_SERVICE.CREATE_SERVICE(service_name => ‘SALESWEST’, network_ name => ‘SALESWEST’);
SQL> exec DBMS_SERVICE.START_SERVICE(service_name => ‘SALESWEST’);
Now, application users can connect to the SALESPDB pluggable database via the service:
$ sqlplus appuser/Cr4zyPas$$word1@mm23c:1521/saleswest
Caution If you have multiple CDB databases on one server, ensure that the PDB service names are unique across all CDB databases on the server. It is not advisable to register two PDB databases with the same name with one common listener.this will lead to confusion as to which PDB you are actually connecting to.
