Oracle Database Service Listener Registration and Oracle Service Names (KBA5885)

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

KBA

KBA# 5885

Issue

The premise behind this article is to look at how Oracle performs its registration of databases services within Oracle listeners and how these facilitate SQLNet based connections (network based connections) to Oracle databases.

Applicable Delphix Versions

Click here to view the versions of the Delphix engine to which this article applies

Major Release	All Sub Releases
ALL	ALL

Resolution

TO PROVIDE INSIGHT INTO HOW ORACLE DATABASES REGISTER THEMSELVES IN ORACLE LISTENERS, this includes Delphix Virtualised Oracle Databases (VDB's) and Oracle sources (dSources) used by Delphix. The database parameter settings that influence the database behaviour around service registration and the commands that can be used to examine both the database and listener configuration are detailed and explained to ensure that the correct Oracle database services are registered in an appropriate Oracle listener.

How does an Oracle database register itself and its services in an Oracle Listener?

The Oracle Database uses a combination of database parameters and an Oracle background process to perform what is termed service registration. When a database starts Oracle uses the background process PMON (older versions 11.2 and earlier) or LREG (12c and higher) to perform the registration of all services defined by the service_names parameter in a specific listener. Oracle uses the local_listener parameter to locate a specific listener to perform the registration and in the absence of the local_listener parameter a default listener would be used (a listener utilizing listener port 1521). For Oracle RAC databases the remote_listener parameter comes into play and services are cross registered against all SCAN listeners that may be running across up to three of the nodes in the cluster. There is also the listener_networks parameter that can be used to control listener registration in a more complex fashion, however this is not often used and will not be discussed here. Oracle RAC Database service registration will be examined in a separate article.

Parameters used for listener registration.

db_name - the name the database was given when it was created.
db_unique_name - a unique name that allows the database to be globally recognized across the organization.
db_domain - a domain name used by the database in registering services that is appended to the db_unique_name for uniquely identifying a database.
service_names - a comma separated list of service names that the database will register against its listener(s).
local_listener - A TNS alias or Oracle SQLNet address string used to locate a specific listener or set of listeners that the databases services will be registered within.

Listener Registration Process Diagram.

By default an Oracle Database will register a default service using the db_unique_name and db_domain parameter settings in the format db_unique_name.db_domain. If neither of these is specified then db_unique_name will default to db_name and this will be used. This default service can be used without issue however Oracle recommends that non-default services be used for end user and applications. Given this, Oracle DBA's will often set there own services via the service_names parameter and these would be registered in the listener pointed to by the local_listener parameter setting.

In the diagram below local_listener is set to a complete address string ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.125)(PORT=1521)).

This aligns with the listener endpoint for the listener named "LISTENER" where its listening endpoint is (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oelsiasm1)(PORT=1521))). The host oelsiasm1 resolves to the IP address 192.168.0.125.

Database Parameters relevant to Listener Service Registration.

To view the database side settings linked to service names and service registration execute the following SQL.

SQL> set pages 500
SQL> set lines 300
SQL> col name format a20
SQL> col value format a80
SQL> col isdefault format a10
SQL> select name,value,isdefault from v$parameter 
     where lower(name) in ('db_name','db_unique_name','db_domain','instance_name','service_names','local_listener') 
     order by name;
     
NAME                 VALUE                                                     ISDEFAULT
-------------------- --------------------------------------------------------- ----------
db_domain                                                                      FALSE
db_name              src121                                                    FALSE
db_unique_name       src121                                                    FALSE
instance_name        src121                                                    TRUE
local_listener       (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.125)(PORT=1521))   TRUE
service_names        src121,plb121_svc                                         FALSE

Listener Listening Endpoints.

To view the listeners endpoint configuration.

[oracle@oelsiasm1 ~]$ lsnrctl status listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-MAY-2020 10:26:28

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 14-MAY-2020 17:41:54
Uptime 0 days 16 hr. 44 min. 35 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/18c/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/oelsiasm1/listener/alert/log.xml

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oelsiasm1)(PORT=1521)))

The unix command nslookup can be used to map the hostname oelsiasm1 to and IP Address, in this case, 192.168.0.125.

[oracle@oelsiasm1 ~]$ nslookup oelsiasm1

Server:  192.168.0.111
Address: 192.168.0.111#53

Name: oelsiasm1.plb.internal
Address: 192.168.0.125

What is now in play given the configuration of the listener and the database?

Given the configuration in play, the local_listener parameter (local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.125)(PORT=1521))') is telling the database src121 (db_name=src121, db_unique_name=src121) to register the services src121,plb121_svc (service_names=src121,plb121_svc) against the listener whose endpoint is (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oelsiasm1)(PORT=1521))).

Listener Service Registration Details

To check the services are correctly registered with the Oracle listener LISTENER and endpoint (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oelsiasm1)(PORT=1521))) on the target host execute the following command.

[oracle@oelsiasm1 ~]$ lsnrctl services listener

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-MAY-2020 10:46:01

Copyright (c) 1991, 2016, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
..
Service "plb121_svc" has 1 instance(s).
   Instance "src121", status READY, has 1 handler(s) for this service...
     Handler(s):
        "DEDICATED" established:15 refused:0 state:ready
           LOCAL SERVER
Service "src121" has 1 instance(s).
   Instance "src121", status READY, has 1 handler(s) for this service...
     Handler(s):
        "DEDICATED" established:15 refused:0 state:ready
          LOCAL SERVER
..
The command completed successfully

This services summary tells you the Service plb121_svc is registered in the listener named LISTENER. The listener, when a new connection request comes in with this service_name plb121_svc in its connect string will be redirect the connection to the database instance src121 where a new database session will be established.