This chapter describes the basics of Oracle Net. When using RMAN with active duplication, or to duplicate to a standby database, it’s critical to understand how to configure and use Oracle Net. The examples described shortly deal with Oracle Net in a single-instance (non-RAC) and non-Oracle Restart environment; in other words, a single vanilla Oracle database installed on a host with nothing special configured. This chapter is not a comprehensive guide for Oracle’s networking technology stack. Rather, it’s an introduction to foundational Oracle Net material. I’ve found that a thorough understanding of the default behavior of Oracle Net in a single-instance environment is critical to successfully using RMAN to duplicate databases. Therefore, this chapter focuses on the following elementary topics:

• Detailing the default behavior of an Oracle Net service
• Creating and registering (dynamically and statically) services with the listener
• Controlling the listener

First, let’s briefly discuss some background information on Oracle Net and its components.

Oracle Net

Oracle Net Services is the collection of technology that manages network connectivity between all components of the technology stack. Oracle Net is the component of Oracle Net Services that manages connectivity between remote clients and the Oracle database. The chief components of Oracle Net are:

Service: A service is an object created in the database that is used by Oracle to manage a connection to the database. Any connection to the Oracle database must be through a service. Many times the service is transparent to the client because either a default service is used during a connection or the service name is transparently used when the client uses an alias to resolve  the connection information.

Listener: A process that resides on the database server that listens for incoming connections to the database.

lsnrctl executable: A utility that allows you to start, stop, reload, and view characteristics of the listener process. The lsnrctl utility can start the listener without any special settings. If you need more control over the listener behavior then you can place configuration settings in the listener.ora file.

listener.ora: This file controls the behavior of the listener process and is usually located in the ORACLE_HOME/network/admin directory. If you don’t have a listener.ora file in place, then the listener uses default settings to operate. Some aspects you can control with this file are the name of the listener, the port used for listening for incoming connections, static registration of services with the listener, logging and tracing information, security settings, and so on.

Service registration: Before that service can be used for remote connections to the database, it must be registered with the listener. Registering a service with a listener gives the listener the ability to accept incoming connections via that service. The listener will refuse to connect any remote connections attempting to connect to a service that hasn’t been registered. A service is registered with the listener either dynamically or statically.

LREG: The listener registration process. This process is responsible for dynamically registering services with listeners. The PMON process is responsible for registering services with the listener in Oracle 11g and previous editions. Whenever I mention LREG in this chapter, if you’re working with Oracle 11g and prior, then you should substitute PMON for LREG. The main point here is that there is an Oracle background process that will automatically attempt to dynamically register services with the listener under certain conditions.

Dynamic registration: The LREG background process periodically wakes up and attempts to register any new instances (and associated services) running on the server with any listeners running on the server. By default LREG will only register services with listeners listening on the default port of 1521. If a listener is not listening on port 1521, then LREG will only automatically register an instance’s services if the LOCAL_LISTENER initialization parameter has been defined with the port information a listener is listening for. You can manually instruct LREG to wake up and attempt service registration with the following:

SQL> alter system register;

Static registration: You can instruct the listener to perform registration for a service by placing information regarding the service in the listener.ora configuration file. This method is called static because the information in the listener.ora file is static (unless you change it). In other words, when you start a listener it will perform service registration for any service entries it finds that have been statically configured for the listener in the listener.ora file.

Remote client: A program that connects to a database over the network (e.g., SQL*Plus, RMAN, Java using JDBC, and so on).

Easy connection method: The name given to remote clients connecting over the network to a database where the remote client specifies the host name, the port the listener is listening on, and the service name directly in the string used to connect to the remote database. If you know the name of the host, port, and service name, you can directly enter those on the command line. The syntax is as follows:

$ sqlplus user/pass@[//]host[:port][/service_name][:server][/instance_name]

For example, the following SQL*Plus connection specifies a host, a port, and the service name of the remote database being connected to:

$ sqlplus system/foo@shrek:1521/TRG

In the prior connection string the host is shrek, the port is 1521, and the service name is TRG.

Local naming method: The name given to clients connecting over the network to a database by using the tnsnames.ora file to provide information such as the host the database is running on, the port the listener is listening on, and the service name.

tnsnames.ora: This file maps a connection alias to a host name, a port the listener is listening on, and the service name. This file is used in the local naming connection method and is usually located in the ORACLE_HOME/network/admin directory. For example, say the following entry is placed in the tnsnames.ora file:

TRG =
  (DESCRIPTION =
    (ADDRESS=(PROTOCOL=TCP)(HOST=shrek)(PORT=1521))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=TRG)))

Then a connection to the TRG service using the local naming method would be as:

$ sqlplus system/foo@TRG

The examples in this chapter will use either the easy connect naming method or the local naming method. If you require details on other naming methods (e.g., directory naming method, like Oracle Internet Directory) that Oracle supports, see the Oracle Database Net Services Administrator’s Guide freely available for download on Oracle’s technology website (otn.oracle.com).

Now that you have an understanding of the basic components of Oracle Net, let’s dive deeper into how to use a service to connect remotely to a database. One key aspect to keep in mind when reviewing the following material is that a service, a listener, and how a service gets registered with a listener are three separate concepts.

Services Default Behavior

It’s critical to understand the default behavior of a service within an Oracle database. This lays the foundation for understanding how Oracle Net functions. When you create a new database there will normally be three default services created for you. For reference, here’s the SQL script I used to create a basic single-instance Oracle database:

CREATE DATABASE TRG
   MAXLOGFILES 16
   MAXLOGMEMBERS 4
   MAXDATAFILES 1024
   MAXINSTANCES 1
   MAXLOGHISTORY 680
   CHARACTER SET AL32UTF8
DATAFILE
'/u01/dbfile/TRG/system01.dbf' SIZE 500M REUSE
   EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE undotbs1 DATAFILE
'/u01/dbfile/TRG/undotbs01.dbf' SIZE 800M
SYSAUX DATAFILE
'/u01/dbfile/TRG/sysaux01.dbf' SIZE 500M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'/u01/dbfile/TRG/temp01.dbf' SIZE 500M
DEFAULT TABLESPACE USERS DATAFILE
'/u01/dbfile/TRG/users01.dbf' SIZE 20M
LOGFILE GROUP 1 ('/u01/oraredo/TRG/redo01a.rdo') SIZE 50M,
        GROUP 2 ('/u01/oraredo/TRG/redo02a.rdo') SIZE 50M
USER sys    IDENTIFIED BY foo
USER system IDENTIFIED BY foo;
--
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
conn system/foo
@?/sqlplus/admin/pupbld.sql

In this script, the name of the database is TRG. After the script finishes, we can display the services available in the DBA_SERVICES view thusly:

SQL> select service_id, name, network_name,
to_char(creation_date,'dd-mon-yy hh24:mi') create_date
from v$services
order by service_id;

As you can see, there are by default three services that have been created:

SERVICE_ID NAME           NETWORK_NAME CREATE_DATE
---------- -------------- ------------ ----------------
         1 SYS$BACKGROUND              25-jan-15 15:28
         2 SYS$USERS                   25-jan-15 15:28
         3 TRG            TRG          25-jan-15 15:28

The SYS$BACKGROUND service is the service that the Oracle background processes use when establishing a connection to the database. The SYS$USERS service is used for local connections. A third service is created that gets its name from the setting of the SERVICE_NAMES parameter. By default the SERVICE_NAMES parameter is populated by the value of DB_UNIQUE_NAME, and by default DB_UNIQUE_NAME is populated from the DB_NAME initialization parameter. In other words, for a freshly created Oracle database, if you don’t set the SERVICE_NAMES or the DB_UNIQUE_NAME initialization parameters, a service is created by default with the same name as the value in the DB_NAME initialization parameter:

SQL> show parameter db_name
NAME                                 TYPE        VALUE
---------------------------          ------      ------
db_name                              string      TRG

So we now have a service that can be used to connect remotely to the database. On a remote box, using the easy connection method, let’s try to connect to the database through the TRG service:

$ sqlplus system/foo@shrek:1521/TRG

The connection fails with this error:

ORA-12541: TNS:no listener

A service by itself doesn’t allow for remote connectivity from a client to a database; it’s the combination of three components that does the trick:

• A service must exist.
• A listener process must be running on the database server (host).
• The service must be registered with the listener before remote connections can access the database through the given service.

So far in this example, the first item is true; a service named TRG does exist. We’ve verified that by viewing the information in the data dictionary. Take the second item from the prior list: Is a listener running? From the operating system on the database server, let’s check the status of the listener:

$ lsnrctl status

This error is present in the output, thus indicating that no listener is running:

TNS-12541: TNS:no listener

You can start a listener on the database server without configuring any Oracle Net files. The listener works perfectly fine with default settings. So, without configuring anything, let’s start the listener on the database server:

$ lsnrctl start

Here’s a partial listing of the output:

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shrek)(PORT=1521)))
The listener supports no services
The command completed successfully

We have a listener running now, and by default the listener is listening on port 1521 (as shown in the prior output). Let’s again attempt to connect to the database from a remote client:

$ sqlplus system/foo@shrek:1521/TRG

Now this error is displayed:

ORA-12514: TNS:listener does not currently know of service requested in
connect descriptor

By default the LREG background process (PMON in Oracle 11g and before) periodically checks (every 60 seconds) to see if a new instance and listener are running on the server. If the listener is listening on port 1521, then the LREG process will automatically register any services contained in the SERVICE_NAMES parameter with the listener. It’s been about a minute, so let’s ask the listener to display services that are registered:

$ lsnrctl services

Service "TRG" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...

The output indicates the service TRG has been registered with the instance TRG. By default a service is registered with the listener to listen for a particular instance. The instance in this name also happens to be TRG. In a single-instance database, the instance name is usually the same as the database name (the value in the DB_NAME parameter).

So, by default, you get a service, and once started the listener will by default listen on port 1521, and by default the LREG process will register services with a listener listening on port 1521. Let’s try to connect remotely again:

$ sqlplus system/foo@shrek:1521/TRG

Bingo, all the pieces come together; the remote connection was successful:

SQL>

The prior connection details are depicted in Figure 6-1 and are described as follows. First, LREG registers the TRG service with the listener listening on port1521 on the shrek server (step 1). Second, a remote client uses the easy connection method to connect to the TRG service in the TRG database (step 2). Once successful, the listener hands off the connection to an Oracle server process (step 3). From that point on the Oracle server process handles all communication between the remote client and the database (step 4).

By using default settings you can make your database available for use via remote connections. In the real world the use of default settings is not recommended, nor are they seldom used. Most hackers know that by default an Oracle listener listens on port 1521, so that port is where most hackers start when trying to remotely access a database. Having said that, I will use the default settings in several examples in this chapter, as it’s important to understand how to identify default configurations and modify them appropriately.

It’s worth mentioning at this point that you can modify the default service that gets created by setting the DB_UNIQUE_NAME initialization parameter. Setting DB_UNIQUE_NAME is a requirement when configuring Data Guard environments. When you set the value of DB_UNIQUE_NAME a service is created and named with the setting of DB_UNIQUE_NAME. To modify this parameter you can use the ALTER SYSTEM command. If you’re using an SPFILE you must specify SCOPE=SPFILE, meaning that it cannot be set dynamically in memory:

SQL> alter system set db_unique_name='TRG1' scope=spfile;

Start and stop the instance in order for the value to take effect:

SQL> shutdown immediate;
SQL> startup;

Now, instead of a service with the name of TRG being registered with the listener, the service name should be TRG1:

$ lsnrctl services

After LREG has had a chance to dynamically register the service, the output verifies that the service TRG1 in the instance TRG is registered with the listener:

Service "TRG1" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...

Now that we’ve covered the default behavior of Oracle Net, let’s look at non-default ways to create a service and register it with the listener.

Creating and Registering Services with the Listener

In most circumstances you’ll require more from Oracle Net than just the default behavior. For example, you may want to register several services with the listener. Techniques to accomplish this are listed next:

• Set the SERVICE_NAMES initialization parameter to contain multiple service names and let LREG dynamically register the services (either by having the listener listen on port 1521 or by setting the LOCAL_LISTENER parameter to contain information regarding the port the listener is listening on).
• Static registration creates and registers a service for each service statically listed in the listener.ora file.
• The DBMS_SERVICE internal PL/SQL package allows you to create, activate, and drop services.

Each of these techniques is described in the following sections. First up is setting the SERVICE_NAMES initialization parameter.

Setting SERVICE_NAMES

One method for creating and registering multiple services with the listener is to populate the SERVICE_NAMES initialization parameter. Recall that if you don’t specify a value for SERVICE_NAMES the default value is derived from DB_UNIQUE_NAME, and if DB_UNIQUE_NAME is not set, the default value is derived from DB_NAME. For this example, say that the SERVICE_NAMES parameter is not currently set to anything and has a default value of TRG, which it derived from the DB_NAME (which is TRG for my database). In this example you have the requirement to add a service named HRS to the database. The following ALTER SYSTEM command sets the SERVICE_NAMES parameter to specify a TRG service and an HRS service:

SQL> alter system set service_names='TRG, HRS';

Querying V$SERVICES shows the service has been added to the instance:

SQL> select service_id, name, network_name,
to_char(creation_date,'dd-mon-yy hh24:mi') create_date
from v$services
order by service_id;

Here is the output:

SERVICE_ID NAME                 NETWORK_NAME         CREATE_DATE
---------- --------------       ------------         ---------------
         1 SYS$BACKGROUND                            25-jan-15 15:28
         2 SYS$USERS                                 25-jan-15 15:28
         3 TRG                  TRG                  25-jan-15 15:28
         4 HRS                  HRS                  25-jan-15 16:14

Recall that just because a service has been created doesn’t mean that the service has also been registered with the listener. A service is only dynamically registered with the listener (by LREG) when:

• The listener is listening on port 1521
• The LOCAL_LISTENER initialization parameter contains information regarding the port the listener is listening on.

By default, if the listener is listening on port 1521, LREG will dynamically register services contained in the SERVICE_NAMES parameter with the listener.

To continue this example, assume there is a listener listening on port 1521. Let’s observe any services registered with this listener:

$ lsnrctl services

Here’s a snippet of the relevant output:

Service "HRS" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...
...
Service "TRG" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...

We can see that both the services, TRG and HRS, are registered with the listener, which is listening on port 1521. Let’s see if we can connect remotely using the newly created and registered HRS service:

$ sqlplus system/foo@shrek:1521/HRS
SQL>

Yes, the connection was successful, as evidenced by the SQL prompt.  Now what happens if we stop the listener and restart it by listening on the non-default port of 1529? First stop the currently running listener:

$ lsnrctl stop

In my environment, to configure the listener to listen on port 1529, I must navigate to the ORACLE_HOME/network/admin directory and place within the listener.ora file the following code:

LISTENER =
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = shrek)(PORT = 1529))
       )
     )
   )

Next, start the listener:

$ lsnrctl start

The output indicates the listener is now listening on port 1529 and supports no services (meaning no services are registered with this listener):

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shrek)(PORT=1529)))
The listener supports no services
The command completed successfully

With nothing else configured, this listener will never contain registered services. The LREG background process is currently only attempting to register services for a listener listening on port 1521, which now does not exist on this host. Notice what happens if we attempt to connect from a remote client to this database via a service:

$ sqlplus system/foo@shrek:1529/HRS

We receive a message indicating the service is not registered with the listener:

ORA-12514: TNS:listener does not currently know of service requested
in connect descriptor

How do we get LREG to register listeners running on the non-default port of 1529? There are two techniques:

• Set the LOCAL_LISTENER parameter to contain the host and port information.
• Set the LOCAL_LISTENER parameter to point to an entry in the tnsnames.ora file that contains the host, port, and service information.

Setting the LOCAL_LISTENER to contain host and port information

The first method involves setting the LOCAL_LISTENER initialization parameter to contain information regarding the host and port 1529. This instructs LREG to, instead of looking for listeners on port 1521, attempt to register services with a listener listening on port 1529. Use the ALTER SYSTEM command to set the LOCAL_LISTENER parameter to contain information regarding the host and port:

SQL> alter system set
local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=shrek)(PORT=1529))';

Now let’s view the services registered with the listener:

$ lsnrctl services

Here’s a partial listing of the output:

Service "HRS" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...
...
Service "TRG" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...

And we can verify that a remote connection is now possible via the HRS service:

$ sqlplus system/foo@shrek:1529/HRS
SQL>

The above connection process is shown in Figure 6-2 and described next. First, the initialization parameters SERVICE_NAMES and LOCAL_LISTENER are populated (step 1). Next, the listener.ora file is modified to contain the port 1529 (step 2). Next, the listener is stopped and started so that it is now listening on port 1529 (step 3). Subsequently, the LREG process dynamically registers the services TRG and HRS with the listener (step 4). Then the client process attempts a remote connection to the HRS service on port 1529 on the shrek server (step 5). When successful, the listener hands off the connection to an Oracle server process (step 6). From that point on the Oracle server process handles all communication between the database and client (step 7).

Setting LOCAL_LISTENER to point to an entry in tnsnames.ora

There’s another technique for setting the LOCAL_LISTENER parameter. This involves setting the value of LOCAL_LISTENER to what is effectively a pointer to an entry in the tnsnames.ora file. The entry in the tnsnames.ora file contains the host, port, and service information. Recall that the tnsnames.ora file is usually located in the ORACLE_HOME/network/admin directory. To demonstrate this technique, first place an entry in the tnsnames.ora file as follows:

LL=(ADDRESS=(PROTOCOL=TCP)(HOST=shrek)(PORT=1529))

Next, set the value of LOCAL_LISTENER to point to the entry in the tnsnames.ora file:

SQL> alter system set local_listener='LL';

Now let’s confirm that the listener is listening on port 1529 for services defined in the SERVICE_NAMES parameter:

$ lsnrctl services

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shrek)(PORT=1529)))
Services Summary...
Service "HRS" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...
...
Service "TRG" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...

And let’s also confirm that we can connect from a remote client:

$ sqlplus system/foo@shrek:1529/HRS
SQL>

Statically Registering Services

The material thus far has outlined how to create and register multiple services with the listener using the SERVICE_NAMES parameter in combination with the default behavior of the listener listening on port 1521, and also with setting the listener to listen on a non-default port such as 1529.

This current section demonstrates how to create and register multiple services with a listener using static service registration. Static registration means the details of the service have been statically placed in the listener.ora file (the listener.ora file is static until you change it, anyway). For the material in this chapter covered up to this point, the listener.ora file only contains the following lines that specify which port the listener is listening on for a given host:

LISTENER =
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = shrek)(PORT = 1529))
       )
     )
   )

For this static registration example, set the LOCAL_LISTENER parameter to contain a null value, meaning that no dynamic registration will take place:

SQL> alter system set local_listener='';

Also set SERVICE_NAMES to contain a null value:

SQL> alter system set service_names='';

When checking to see what services are registered with the listener we expect it to be none, because the listener is not listening on the default port of 1521 and we have not set the LOCAL_LISTENER parameter to point at port 1529:

$ lsnrctl services

Here’s a partial listing of the output verifying our thinking that the listener is indeed running but with no registered services:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=shrek)(PORT=1529)))
The listener supports no services

To statically register a service with the listener you need to place an additional section in the listener.ora file that lists the services you require the listener to register. This addition to the listener.ora file is the SID_LIST_LISTENER section. The following lines of code are added to the listener.ora file specifying the static registration information for two services (TRG and HRS):

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TRG) # service name
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = TRG) # instance name
     )
    (SID_DESC =
      (GLOBAL_DBNAME = HRS) # service name
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = TRG) # instance name
     )
  )

In this code, the GLOBAL_DBNAME specifies the service name, and SID_NAME is the instance name. So a service TRG is registered with the TRG instance, and the service HRS is registered with the TRG instance. Also note that any text on a line that appears after a # sign is a comment. Now stop and restart the listener (you could also do a reload; more on this later). When the listener is stopped and started:

$ lsnrctl stop
$ lsnrctl start

Here’s part of the output it displays:

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shrek)(PORT=1529)))
Services Summary...
Service "HRS" has 1 instance(s).
  Instance "TRG", status UNKNOWN, has 1 handler(s) for this service...
Service "TRG" has 1 instance(s).
  Instance "TRG", status UNKNOWN, has 1 handler(s) for this service...

The UNKNOWN status means the service has been statically registered with the listener. In other words, the status of UNKNOWN doesn’t mean the listener isn’t aware of the service; rather, it means that the service was not dynamically registered by LREG. So don’t let the status of UNKNOWN confuse you, as it simply means the service was statically registered.

Now that the services have been statically registered with the listener, we can again connect remotely from a client:

$ sqlplus system/foo@shrek:1529/HRS
SQL>

The following query and output shows the successful connection:

SQL> select instance_name from v$instance;

INSTANCE_NAME
-------------
TRG

If you want to use the local naming method (and not easy connect), then add an entry to the tnsnames.ora file on the client machine, as follows:

HRS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = shrek)(PORT = 1529))
    (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = HRS)))

And now the client connection is initiated as follows:

$ sqlplus system/foo@HRS

The SQL*Plus utility knows from the connection string that local naming is being used and that it needs look in the tnsnames.ora file for details on the host, port, and service name.

The prior static registration and local naming connection method is depicted in Figure 6-3 and described here. First, the listener.ora file is modified to contain the static service information (step 1). Next, the listener is stopped and started so that it statically registers the services contained in the listener.ora file (step 2). Then the client tnsnames.ora file is modified to contain local naming information such as the host, port, and service name (step 3). Next, the remote client attempts to connect to the database (step 4). When successful, the listener hands off the connection to an Oracle server process (step 5). From that point on the Oracle server process handles all communication between the database and remote client (step 6).

Dynamic Registration versus Static Registration

The prior two sections detailed dynamic and static registration options. Which technique is preferable? Either dynamic or static registration is acceptable. There are some specific situations in which you may want to use static registration:

• If it’s required by Oracle Enterprise Manager Cloud Control.
• If you want the service to be immediately available when the listener is started; static registration provides this.
• If you want the listener to automatically register any statically listed services when it starts or reloads.

Dynamic registration also has some advantages:

• You don’t need to maintain information regarding the services in the listener.ora file.
• If need be you can instruct LREG to wake up and perform dynamic registration with the following:

  SQL> alter system register;

This command instructs LREG to immediately register any services with a listener. This works if the listener is listening on the default port of 1521 or if you’ve configured the LOCAL_LISTENER parameter to include information regarding the host and port number that the listener is listening on.

Here are some other differences between dynamic registration and static registration that you should be aware of:

• When you stop an instance, dynamically registered services are no longer registered with the listener, whereas statically registered services still show up as registered with the listener (even though there is no instance running).
• It’s also possible to statically register a service with a listener when there is no instance running.
• By default, statically registered services don’t appear in the V$SERVICES or DBA_SERVICES views. You can change this behavior by using DBMS_SERVICE to create and start the statically registered service.
• Dynamically registered services do appear automatically in the V$SERVICES and DBA_SERVICES views.
• By default, when you connect to a statically registered service, the SERVICE_NAME column in V$SESSION shows the connection coming through the SYS$USERS service. You can change this behavior by using DBMS_SERVICE to create and start the statically registered service.

You may work with a group that has a strong opinion on which approach is best used (because of some of the differences listed previously), and that’s fine.

Why Use Multiple Services?

You may also be asking why not just use the default service that’s created when you create the database; why is it necessary to create multiple services? When you use a service to connect to the database Oracle tracks which service you use. Oracle populates various performance-related views with the service information along with resource usage per service. This allows you to monitor and report on performance metrics per each service. For example, you may have several applications using your database. You can create a service for each application to use when connecting to the database, which will allow you to monitor resource usage per the service connection.

Using DBMS_SERVICE

Oracle provides the built-in PL/SQL package DBMS_SERVICE to manage services. Normally you don’t need to manage services with the degree of control provided through DBMS_SERVICE. However, you’ll be better able to understand and maintain your Oracle Net environment with a basic knowledge of how to use this package. There are several tasks you can perform with DBMS_SERVICE. The main features I’ll cover here are:

• Adding and starting a service
• Stopping and deleting a service

Keep in mind there are other uses for DBMS_SERVICE (e.g., modifying a service, disconnecting a session, and so on). What I want to cover here are some of the features you’ll use most frequently. Let’s start with adding a service.

Adding a service

Suppose you want to use DBMS_SERVICE to add a service named DW to the instance. This next line of code demonstrates how to do so:

SQL> exec DBMS_SERVICE.CREATE_SERVICE(service_name=>'DW',network_name=>'DW'),

Checking in the DBA_SERVICES view we can confirm the creation of the service:

select service_id, name, network_name,
to_char(creation_date,'dd-mon-yy hh24:mi') create_date
from dba_services
order by service_id;

Here is the output:

SERVICE_ID NAME           NETWORK_NAME CREATE_DATE
---------- -------------- ------------ ---------------
         1 SYS$BACKGROUND              25-jan-15 15:28
         2 SYS$USERS                   25-jan-15 15:28
         3 TRG            TRG          25-jan-15 15:28
         4 HRS            HRS          25-jan-15 16:14
         5 DW             DW           25-jan-15 17:12

Now that the service has been created, and before it becomes available for use with remote connections, it must be registered with the listener. In this environment I’m using dynamic registration to register the service with the listener. I can force LREG to wake up and register any new services:

SQL> alter system register;

Assuming dynamic registration has been set up, when I look to see if the new service has been registered, it has not yet been:

$ lsnrctl services
Service "TRG" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...

Before a service created with DBMS_SERVICE can be registered with the listener it must be started. Let’s do that as follows:

SQL> exec DBMS_SERVICE.START_SERVICE(service_name=>'DW'),

Now instruct LREG to wake up and register any started services:

SQL> alter system register;

The service is now registered with the listener:

$ lsnrctl services
...
Service "DW" has 1 instance(s).
  Instance "TRG", status READY, has 1 handler(s) for this service...

We can now connect remotely using this new service:

$ sqlplus system/foo@shrek:1529/DW
SQL>

Removing a service

Removing a service is the opposite of adding it. First, stop any services and then drop them. For example, to stop and delete the DW service do as follows:

SQL> exec DBMS_SERVICE.STOP_SERVICE(service_name=>'DW'),
SQL> exec DBMS_SERVICE.DELETE_SERVICE(service_name=>'DW'),

To wrap this section up, the DBMS_SERVICE package can be used to add and remove services. If you’re using dynamic registration then the service will automatically be registered by LREG with the listener.

Displaying Service Information

When troubleshooting issues with remote connectivity you’ll invariably want to determine which services are registered with the listener and which services exist in the database. You can display service information either through Oracle-provided operating system utilities or via data dictionary views.

LSNRCTL

The lsnrctl utility is the best source for showing which services are registered with the listener. For example:

$ lsnrctl services

The output will show details such as:

• Service names (and associated instance names) registered with the listener
• Whether service was registered dynamically (status of READY) or statically (status of UNKNOWN)
• Host name and port the listener is listening on

You can also display helpful status information regarding the listener as follows:

$ lsnrctl status

The STATUS option includes much of the same information the SERVICES option contains. In addition, the status displays how long the listener has been running as well as parameter and logging files.

Data dictionary views

There are also several data dictionary views available for looking at service information. The two primary views are V$SERVICES and DBA_SERVICES. These views display much of the same information, such as services that have been registered dynamically or that have been created and started via DBMS_SERVICES. The V$SERVICES view shows only services that have ever been active. The DBA_SERVICES shows all services that have ever been created regardless of whether they have ever been active.

You can view the service associated with a remote connection by querying the SERVICE_NAME column of the V$SESSION view. This is helpful for determining connections to the database and the corresponding services being used for the connections.

Other views such as V$SERVICEMETRIC and V$SERVICEMETRIC_HISTORY display resource usage for services. These views are useful for determining the usage and performance metrics for sessions using a particular service.

TNSPING

Oracle provides the tnsping utility to help troubleshoot Oracle Net connectivity issues between a client and a remote database. For example, if you want to verify the existence of a listener on a remote server you can do so as follows from the operating system command line:

$ tnsping <host>:<port>/<service_name>

To further this example, suppose I have a remote client that wants to verify if there’s a listener running remotely on the shrek server. To verify the listener is up and receiving incoming requests I tnsping the shrek server on port 1529 via the TRG service:

$ tnsping shrek:1529/TRG

If successful, you’ll see output similar to this:

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=TRG))(ADDRESS=(PROTOCOL=TCP)
(HOST=shrek)(PORT=1529)))
OK (40 msec)

What’s confusing is that the prior output only means that the listener is up and receiving requests; it doesn’t necessarily indicate that there actually is a service (TRG in this example) running on the remote host. For example, look at what happens when I try to connect to the remote service:

$ sqlplus system/foo@shrek:1529/TRG

This appears in the output:

ORA-12514: TNS:listener does not currently know of service requested in
connect descriptor

In this situation you should verify which services are registered with the remote listener. Logging on to the remote box, run the following code:

$ lsnrctl services

The output verifies the listener doesn’t have any services registered:

The listener supports no services
The command completed successfully

In this scenario ensure that the listener has registered the service that you’re attempting to remotely contact. You can do this by following the instructions laid out in previous sections that show how to either dynamically or statically register services with the listener.

Listener

The listener is the process that resides on the database server and listens for remote clients attempting to connect to the database. When a remote client attempts to connect to a database it specifies the host name, port, and service associated with the database that it wants to connect to. The listener is configured to listen on one or more ports for services that have been registered with the listener either dynamically or statically.

Starting a Listener

The lsnrctl utility allows you start and stop the listener. The START option will start the listener process:

$ lsnrctl start

If nothing has been configured in the listener.ora file, you’ll see:

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=shrek)(PORT=1521))
The listener supports no services
The command completed successfully

By default the listener will listen for incoming connections on port 1521. Additionally, the default name of the listener is LISTENER. You can check the status of the listener with the STATUS option:

$ lsnrctl status

Here’s a portion of the output:

STATUS of the LISTENER
----------------------
Alias                     LISTENER
Version                   TNSLSNR for Solaris: Version 12.1.0.2.0 - Production
Start Date                25-JAN-2015 18:04:16
Uptime                    0 days 0 hr. 4 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /orahome/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /orahome/app/oracle/diag/tnslsnr/shrek/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=shrek)(PORT=1521)))
...

The listener running by itself with no services registered is of little use. Services must be registered with the listener before the database will accept remote connections. If the listener is listening on port 1521 then by default the LREG process periodically checks to see if there are any databases running a box and whether there are any services associated with the database. The LREG process performs dynamic service registration. Service registration makes the listener aware of details about the database running on the box, such as the following:

• Instance name
• Services running within the instance
• Service handlers (dedicated or multiple dispatchers)
• Dispatcher information

If the listener has been configured to listen on a port other than 1521, then you can still have LREG perform dynamic registration by setting the LOCAL_LISTENER initialization parameter to contain details regarding the listener, such as the host and the port it is listening on.

Modifying the Behavior of the Listener

The most common ways that you’ll use to modify the behavior of the listener are setting the listener to listen on a non-default port and naming the listener.

Setting the port

Using the default port of 1521 is almost never recommended. This port number is well known to be the default and is the first port that a hacker will attempt using to remotely access an Oracle database. To set the port to a different value, first stop the listener if it is running:

$ lsnrctl stop

To configure the port number, navigate to the ORACLE_HOME/network/admin directory and add an entry in the listener.ora file:

LISTENER =
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = shrek)(PORT = 1529))
       )
     )
   )

These lines of code instruct the listener to listen on the port of 1529 on the host of shrek. You can instruct a single listener to listen on multiple ports by adding more addresses to the address list. For example:

LISTENER =
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = shrek)(PORT = 1529))
        (ADDRESS = (PROTOCOL = TCP)(HOST = shrek)(PORT = 1530))
       )
     )
   )

Naming the listener

By default when you start the listener process without configuring anything, the default name that Oracle assigns to the listener is LISTENER. You can verify the name of a listener that is currently running via the operating system utility ps. For example:

$ ps -ef | grep tns | grep -v grep

In the output the name appears as the text LISTENER:

oracle   12320     1  0 09:50 ?        00:00:00 /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr
LISTENER -inherit

You can also verify the name of the listener by viewing the output of the lsnrctl utility executed with the STATUS option. For example:

$ lsnrctl status

In the output the alias name appears as LISTENER:

STATUS of the LISTENER
----------------------
Alias         LISTENER

If you require more than one listener running on a host then it’s common to start different listeners with different names listening on different ports. The following lines of code are placed in the listener.ora file and give the listener the name of DGLIST:

DGLIST =
  (DESCRIPTION_LIST=
    (DESCRIPTION=
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = shrek)(PORT = 1531))
       )
     )
   )

To start this listener you need to specify its name (if not using the default name) when executing the lsnrctl utility. For instance:

$ lsnrctl start dglist

If you want to statically register services with this named listener then you must add an additional section to the listener.ora file, as follows:

SID_LIST_DGLIST =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DG1) # service name
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = TRG) # instance name
     )
  )

You can only have one listener listening on the same port on the same host.

Getting help

To view help information for the listener, execute the lsnrctl utility with the help option:

$ lsnrctl help

Here’s a partial listing of the output:

The following operations are available
An asterisk (*) denotes a modifier or extended command:

start           stop            status          services
version         reload          save_config     trace
spawn           quit            exit            set*
show*

Now if you want to view more information about a particular command, you can run the lsnrctl utility and name the option you need help with:

$ lsnrctl help start

Summary

This chapter provides you with the basic information you will need to successfully work with simple single instance non-RAC Oracle Net environments. This knowledge lays the foundation for understanding and implementing Oracle Net. With this information you’ll be able to more effectively implement and troubleshoot Oracle Net configurations. Elementary concepts such as defining terms, creating and registering a service with the listener, dynamic and static registration, and listener usage were covered.

This chapter was included in this book because in many duplication scenarios you’ll be required to use Oracle Net to connect to the target database, auxiliary database, and/or a recovery catalog. You should now be able to quickly configure and successfully use Oracle Net for your basic duplication requirements.