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
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_namefor 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_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 220.127.116.11.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.104.22.168.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 22.214.171.124.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.
The following articles may provide more information or related information to this article:
Oracle Documentation detailing relevant parameters.