CHAPTER

5

Networking and Services

Before we dive into Oracle networking, related specifically to the Oracle Database 12c multitenant option, we need to take a look again at some of the core concepts. We will not go into detail here on Oracle networking features and functionality, because that is a book on its own; for more in-depth information, we recommend you review the “Oracle Net Services Administrator Guide.” In this chapter we will cover the new listener registration (LREG) background process, including brief mention of the new Oracle Database 12c multithreaded option, before diving into the details of services and Oracle Database 12c Multitenant.

Oracle Net

Oracle Net is the software component that provides the network layer for communication between the client and the Oracle database instances. It forms a communication channel between the client and database instance once a connection has been established.

Oracle Net Services consists of a number of components, such as Oracle Net, that facilitate connectivity between distributed environments. It also includes the Oracle Net Listener, the Connection Manager (CMAN), and two key configuration utilities: Oracle Net Configuration Assistant (NETCA) and Oracle Net Manager (netmgr).

The Oracle Net Listener

The listener is one of the most important components to consider when establishing a new Oracle Database environment, yet many DBAs treat it lightly, paying it only minimal attention. Perhaps this is because, in many cases, once the listener is configured you can forget about it. But in Oracle Database 12c, with the introduction of multitenant, there is more to the listener than meets the eye.

Let’s begin with a recap of the basics of a database connection in the traditional model. When a database starts it will, by default, register with the listener, providing it with one or more service names. But here is where it can get confusing:

   A service might identify more than one instance (in the case of Oracle Real Application Clusters [RAC]).

   A single instance can be registered by more than one listener.

   The database may register more than one service with the listener.

When looking at the process of a client connecting to a database, we can see there are three high-level steps performed, as shown in Figure 5-1. First, the client initiates communication by requesting a connection from the listener to a particular service. Then the listener identifies the appropriate service and passes the details to the client, after which it will make a direct connection to it. Once the connection is established, it is important to note that the listener is not involved in any way in the communication going forward.

FIGURE 5-1. Basic connection overview

This brings us to an interesting change in Oracle Database12c: the new listener registration (LREG) background process.

The LREG Process

Instance registration with the listener in Oracle 12c behaves a bit differently from that of previous versions. In 12c, the LREG is a new background process that will register the database instance with the listener, which was a task performed in earlier versions by the Process Monitor PMON) process. On UNIX, this process is easily identified at the OS level, because “lreg” is included in the visible process name. This can be seen in the following code block:

The format of the process is ora_lreg_SID.

Note that this background process is classified as critical for the database instance, and, if it is terminated, the Oracle Database instance will actually abort. The code blocks that follow show the output of the alert log when this background process is killed:

From the alert log, notice the following messages when the LREG is terminated:

The LREG background process also writes out information regarding service updates in the listener log. These include service_update, service_register, and service_died messages; here’s an example:

NOTE

When we used the strace utility to view additional detail on the LREG process (though it is not recommended that you use this utility on background processes in production systems), we observed a review of the load average (/proc/loadavg) approximately every 33 seconds.

When running in the new multithreaded mode, LREG does not run as a process on its own, but is run as a thread. Following is an excerpt from a Linux system that shows the LREG process running as a thread (thread ID 4696) under the process ID 4658:

From the preceding example, we now have the operating system process ID 4658. If we review the process listing for the database CDB2, we can easily identify this process:

Taking process 4658, we can again use the ps command to list the threads associated with this:

By reviewing the output of the ps command, we can identify the LREG thread.

NOTE

The new multithreaded option for UNIX-based systems was introduced in 12.1.0.1. To enable the multithreaded mode, the database parameter THREADED_EXECUTION must be set to TRUE and the database restarted. The multithreaded model enables Oracle Database processes to execute as operating system threads in separate address spaces. Some background processes run as processes containing only a single thread, but the other Oracle processes run as threads within processes. Multithreaded brings some interesting new options and changes.

Networking: Multithreaded and Multitenant

One of the key advantages of multitenant is consolidation: multiple database (non-CDB) environments can be configured to run as pluggable databases (PDBs) in a single consolidated container database. This enables you to get the most out of your valuable resources by not having to allocate unnecessary resources. For example, instead of running ten databases on one server, each with its own System Global Area (SGA) and background processes, you could consolidate into a single container database with PDBs, which would mean one SGA and one set of background processes. And the multithreaded mode takes this one step further by reducing the amount of processes on the system, with the possibility of improved performance and scalability.

When you’re configuring the multithreaded option (by setting THREADED_EXECUTION=TRUE and restarting the CDB), consider that you will also need to make a change in the listener to allow threads, rather than processes, to be spawned. To enable this, set the parameter DEDICATED_THROUGH_BROKER_<listener_name>=ON in the listener.ora configuration file, and then restart the listener so that the changes take effect. As a result, when the listener receives a client connection request, it will pass this onto a connection broker (Nnnn), which will then verify the authentication, and a new thread will be spawned in an existing process.

The total number and type of connection brokers can be set using the CONNECTION_BROKERS database parameter. By default, two brokers are configured—one of type DEDICATED and one of type EMON. To view the status of the connection brokers (which are also spawned threads), you can review the status of the services using the listener control (lsnrctl) command, as follows:

A quick way to see the thread and process details is to use the ps command:

And looking further at V$PROCESS, we see the following:

NOTE

Do not attempt to kill thread processes via the command line (using the kill command) because you might end up killing a number of other thread connections as well. Instead, consider using ALTER SYSTEM KILL SESSION by passing in the correct SID and SERIAL# values. Make sure you are working with the correct session by first looking at V$SESSION and V$PROCESS.

If you want to configure certain clients to use the threaded option and others to use processes, you will need to configure two listeners, each one using different ports. It is also possible to create a dedicated listener for a specific PDB, but you’ll learn more about this a bit later in this chapter.

Service Names

When using a multitenant configuration, you will need to be aware of the changes introduced with services. In this section we will cover the important changes you should be aware of when creating and maintaining Oracle Database 12c Multitenant environments.

Default Services and Connecting to PDBs

When creating a new PDB, a new default service is automatically generated for it, with the same name as the PDB. The service will be registered with the listener and client connections, and connections to the PDB can begin to make use of the new service once the PDB is opened. The automatic registration might take a few seconds, although you can run alter system register to force the registration to occur immediately.

So, for example, if a new PDB called PDB1 is created, a new default service of the same name will also be created. The new service details can be viewed by looking at v$services or cdb_services:

Reviewing the listener status and services will indicate whether the new service name is registered:

The end user/application can now connect to this PDB using the new service name via a number of methods, such as via the Oracle Net Services name using the tnsnames.ora file or an easy connect string. The basic easy connect string takes the format @[//Host[:Port]/<service_name>], and when this method is used, no entry is required in the tnsnames.ora file.

Here’s an example of using the easy connect method to connect to the newly created PDB1:

You can also add and use an Oracle Net Services name entry in the tnsnames.ora file:

Once the entry is added to the tnsnames.ora file, it can be used to connect to the PDB:

Figure 5-2 illustrates two basic concepts. When each PDB is created, each will have a service name created that matches the PDB name; the service is automatically registered with the default listener. When a client connection is requested to the listener for a specific service name for a PDB, the listener prompts a server process to be spawned and the connection between the client and the PDB will be established, with the listener no longer involved.

NOTE

When running more than one CDB on a single system with PDBs using the same service names, it is recommended that you use separate listeners for each CDB. If only one listener is used, you will end up with both CDB databases having service names for the PDBs registered with the same listener. The end result could be that an incorrect connection may be established, which may lead to undesirable results. In view of this, it is recommended that all service names on a system should be unique to avoid such collisions or, alternatively, separate listeners configured for each CDB.

FIGURE 5-2. Service name registration (LREG) and client connection

Before moving on to the next section it is worth mentioning that you can also use the local environment variable TWO_TASK (on UNIX) or LOCAL (on Windows) to specify a default connect identifier (connect string). When you set this variable, the user will be able to connect to a database without explicitly specifying the connect string. Here’s an example:

This method might be required by various applications. Also note that the easy connect string can be used as well; this removes the dependency of having a required entry in the tnsnames.ora file (example: export TWO_TASK=//linux3.orademo.net/PDB1.orademo.net).

Creating Services

By default, a service is generated for each PDB on creation. In many cases, the requirement will be to create additional services and associate them with the particular PDB. This can be especially useful in Oracle RAC configurations, where you might want a specific application to connect to its PDB only from one of the instances. Creating a service and setting up the rules to run on only one particular node in the cluster can be an extremely useful capability to have.

When creating a new service, you can set an optional PDB property at creation time, which can be modified at any time after. The PDB property is important because it associates the service to a particular PDB.

If a user connects to a service that does not have a value specified for the PDB property, the user name would be resolved in the context of the root container. However, if the value is specified for the PDB property, the user name will be resolved in the context of the specified PDB.

Note the following regarding service creation:

   Services become active (listed in v$active_services and registered with the listener) only when the PDB is opened.

   Service names must be unique within the CDB, but they must also be unique between all databases using a specific listener.

   When using Oracle Restart or Oracle Clusterware, you can use either the SRVCTL utility or the DBMS_SERVICE package to add, modify, and manage new services. Using the SRVCTL utility in this case is recommended.

   The PDB property must be set using -pdb <PDB> when you’re using SRVCTL. If you’re using the DBMS_SERVICE package, the PDB property will automatically be set to the current connected container, so make sure you connect to the correct PDB before creating the service using DBMS_SERVICE.

   The PDB property cannot be changed with the DBMS_SERVICE package. The service will need to be re-created from within the correct PDB.

   If you are not using Oracle Restart or Oracle Clusterware, the DBMS_SERVICE package is used to add, modify, or remove new services.

   Stopping a service using the SRVCTL utility does not change the status of the PDB it is associated with. The SRVCTL stop command will affect only the service, not the PDB.

   When you unplug a PDB, the service will not be removed, so this should be managed manually. The same applies when a PDB is dropped. If the service is no longer required, it should be removed manually.

Creating a Service with SRVCTL

Creating services with the SRVCTL command line utility is very easy, and you can quickly understand why you should be using this with Oracle Restart or Oracle Clusterware.

In the next two examples, two new services will be created: the first uses a single-instance configuration, and the second uses a two-node Oracle RAC cluster.

Adding a Service for a PDB in a Single-Instance Database The CDB database CDB1 consists of PDB1 and PDB2. It is a single-instance database in which Oracle Restart is used. Each of the PDBs already has the default service created and registered with the default listener. In addition, the following services are required:

   PDB1 will use service CRMDEV.

   PDB2 will use service HRDEV.

As the Oracle database software owner, which in this installation is the user name oracle for the UNIX environment, set the environment to the correct Oracle Home and use the srvctl command to add the two services. Then review their status and start these two services. See the code blocks that follow for more detail on these steps.

1.  Create the services.

2.  Review the service status.

3.  Start the services.

4.  Show the status of the services following the startup.

Now that the services are created, review what the listener knows. Notice that these newly added services are also registered with the listener, as per the extracts from these listener status commands. Here’s the first one:

And here’s the second one:

Reviewing V$SERVICES, you can also see the newly created services:

You can now use these new services to connect to the respective PDBs—here’s an example:

Adding a Service for an Oracle RAC PDB The steps for adding a new service for a PDB under Oracle RAC are almost identical to those used in the preceding example for a single-instance database: the SRVCTL utility is invoked to add the service. In this case, however, you need to keep in mind one particular question: Do you want the service to be enabled on all the instances, or just one?

In the next example, the Oracle RAC database, RCDB, consists of two nodes running two PDB databases: RPDB1 and RPDB2. The requirement is that RPDB1 be accessible only via node 1 (instance 1, RCDB1) and that RPDB2 be accessible from both nodes. By creating two services, this can easily be achieved.

To create the service for RPDB1 to run from only instance 1, we use the following commands, executed as the Oracle Database software owner, which is the oracle UNIX user in this case.

1   Create new service CRMPRT to run on one instance only.

2   Create new service CRMPRD to run on both instances.

3   Review the status of the service.

4   Start both services.

5   Review statuses following service startup:

The status output shows that the service CRMPRD is now running and available on both instances in the Oracle RAC cluster, whereas the service CRMRPT runs only on the preferred instance, RCDB1. Users or clients will be able to start using them for connections—here’s an example:

To modify or remove services, you can use the modify service or remove service option provided by the srvctl command:

NOTE

For more information on using the SRVCTL utility, use the srvctl -h command to obtain detailed help options. If you specify srvctl add service -h, you can also obtain help specific to the addition of new services. Substitute with the modify or remove keyword to list information on the respective help option.

Creating a Service with DBMS_SERVICE

When using environments in which Oracle Restart or Oracle Clusterware is not installed, you need to use the DBMS_SERVICE package to create and manage new services. As mentioned, when creating new services for a specific PDB, you need to ensure that you are connected to that PDB when performing this operation. Otherwise, the service will be created in the context of the PDB you are connected to at the time, as the PDB property is set to the connected PDB.

1.   Switch to the PDB and create the new service.

2.   Review the services.

3.   List all the active services.

4.   Start the service and review active services.

In this section we have demonstrated how easy it is to create new services for a PDB, using either the SRVCTL utility or the DBMS_SERVICE package.

Create a Dedicated Listener for a PDB

In some cases, you may need to use a specific dedicated listener port for one or more PDBs. This will require that you create a new listener and then ensure that the PDB is registered with it. In this section, we will show you how this can be done.

In a consolidated database environment with a large number of PDBs within a CDB, you may need to segment some PDBs off or, from a security point of view, maintain both encrypted and unencrypted connections. To do this, you first create a new listener.

In the following example, we will call the listener LISTENER_PDB and have it listen on port 1531. The following entry is added to the listener.ora file to introduce the new listener:

Once the new listener entry has been added, we can start it with the lsnrctl start listener_pdb command. When the listener is started, we can then add a net alias to the tnsnames.ora file:

We are now ready to configure our PDB, which happens to be PDB1 in this case, to use this listener. We update the LISTENER_NETWORKS parameter specific to the PDB:

The syntax for the LISTENER_NETWORKS parameter is as follows:

NOTE

The listener_address string is an address (or address list) that resolves to a specific listener. An alias can be used, which is the case in the example used here, although it requires that you add an address entry into the tnsnames.ora file prior, as shown in the code block Add net alias to tnsnames.ora file.

If we now review the listener, we will notice that the PDB1 services are registered automatically by the LREG process, following the execution of the preceding commands.

Now that the PDB is registered with the listener, we can connect to it using the new listener that is running on port 1531, as follows:

If you are not using the easy connect string connection method, but are instead using a Net Service name using the tnsnames.ora file entries, you need to make sure to add the following entry to the tnsnames.ora file to reflect the new port change for this PDB:

Summary

In this chapter, we covered only the tip of the iceberg of information available regarding Oracle networking to highlight key features and functionalities you should be aware of when using the Oracle Database 12c Multitenant option. Creating and managing services in a multitenant configuration turns out to be less complex than you might have thought, even when using Oracle RAC. Using the SRVCTL utility or the DBMS_SERVICE package can help you achieve the required results quickly.

Now that you know how to connect, the next logical topic to address is security. For many DBAs, this is a daunting and intimidating topic, but in the next chapter, we will systematically walk you through its key aspects, including explaining the difference between different types of users, roles, and permissions, along with discussions about encryption, isolation, and the lockdown of profiles.