An underestimated feature: Using Oracle Remote Listener with Standard Edition 2 Licenses - Part 1 (Setup)

Even for small and medium-sized companies that work with Standard Edition 2 (SE2) databases from Oracle, high available and secure systems are a “must have”. How to install a remote listener is covered in this post (more details on high availability and security are covered in the next posts in the near future).

The databases are typically protected with additional software for high availability. The best-known provider is certainly dbvisit with its product “dbvisit Standby”, but there are other products available like the one from the company I work for - this alternative standby software is called robotron*Standby.

All software providing high availability for SE2  work more or less in the same way: a database is permanently recovered from the archived redo logs of the primary in a second environment and thus kept up to date. The applications or users connect (now with pluggable) databases via a TNSNames.ora entry, JDBC connect or easy connects. The host and the listener (ports) for the connections then typically change, if a switchover or failover happens.
The application database service (I really recommend DON'T use the default PDB service for your applications) remains the same in terms of name and is only available where the primary database is running and read/write opened, but there are still a few things to note.

Typically, connection entries for such highly available databases then look like this in TNSNames.ora (analogous in JDBC or with easyconnect):

application_service_name_ha=
 (DESCRIPTION=
   (ADDRESS_LIST=
     (LOAD_BALANCE=off)
     (FAILOVER=on)
        (ADDRESS=(host = proddbhost)(protocol = tcp)(port = 1521))
        (ADDRESS=(host = standbydbhost)(protocol = tcp)(port = 1521))
   )
  (CONNECT_DATA=
   (SERVICE_NAME=application_service_name.domain)
  )
 )

As the default values are not sufficient, additional parameters are often specified for the failover, e.g.TRANSPORT_CONNECT_TIMEOUT, CONNECT_TIMEOUT, RETRY_DELAY or RETRY_COUNT. I don't want to describe all of them here in detail, but in case you need them you have at least heard of them. 

However, the problems that result from this type of connecting to the application service of the database are always the same: you have to store both database hosts in the connect string and the connection failover to the other machine then always takes a moment.
Anyone using JDBC or easyconnect syntax will have a hard time with these complicated connection strings anyway. There are also some applications in which such connection strings cannot be captured due to the length of the string (especially if someone combines more than ONE Standby with a primary database). And last, but not least, the local listener at the database is "open for the whole world", right? So anyone who knows the database servers host name and the standard port can try to access the database.

Customers who use Enterprise Edition licenses with Data Guard are provided with a tool called Connection Manager (CMAN), this tool can be used as a proxy function/proxy listener, which does not only handles to forward connections, but also allows to secure connections/servers/services a little bit more. However, as the Connection Manager is only part of the Enterprise Edition database license, Standard Edition 2 customers must find another solution.

Remote listeners are the best possible solution to create some kind of connection manager by your own. These remote listeners are often installed on systems that have to run “anyway” for an application to work. Typically, these are the application servers, printer servers, etc., which are known to the end user and without an application is not or only partially available. In principle, however, any server that is itself highly available is suitable.

To be able to use a remote listener, a Full Oracle Database Client is required on this server, an Instant Client does NOT work for this. The client can be downloaded e.g. via https://edelivery.oracle.com.
As most listeners are compatible downwards for older releases, the highest version can theoretically be used here. Nevertheless, most of my customers want to use the same release that is also used for the database to stay in sync.

Be aware, that downloading from https://edelivery.oracle.com means one gets the BASE Release (e.g. 19.3.0.0) of the client. It can (and should) be patched (not covered in this blog post). 

After the download, the client must be installed on the server as an administrator - installing on Windows means setting  registry entries and new Windows services are created. Here are some hints:
It is important (for security reasons) that only used components are selected for installation. Setting “Custom” as installation type let one choose the right parts. Most administratos will probably select “Use Windows Built-in Account” for the Oracle Home/Windows Services user, which is fine. The software location is either based on the Oracle standard or it does make sense to use an application server software directory (e.g. create an oracle subdirectory at your application software base directory. It is sufficient to select the “Oracle Net Listener” - dependent components are installed automatically.
However, SQLDeveloper or ODBC driver or other things often required on the application server for installing or updating the application, so this can also be selected here.

After the installation is done, one can find new services in Windows - the only one that must run is the listener service. All other services installed by the client can be disabled or set to manual startup type.

The listener is started automatically, but there is no service registered, because the configuration is missing. 

The Listener.ora file must be adapted so that a database can now register with the Listener.
As always, this file is located under ORACLE_HOME\network\admin. The listener line with EXTPROC1521 as listening endpoint is usually removed  for security reasons, it is not needed.
Now two parameters are added, whereby the “LISTENER” in the parameters stands for the name of the listener (which can of course also have a different name):

VALID_NODE_CHECKING_REGISTRATION_LISTENER = ON
REGISTRATION_INVITED_NODES_LISTENER = (<IP-Addresses>,<HostNames>,<IP-WITH-WILDCARDS>)

Typically, a 2-node environment for high availability would also include both nodes. The listener.ora on the application server appserver1.localdomain could look like this:

# listener.ora Network Configuration File: C:\app\oracle\product\19.0.0\client_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = appserver1.localdomain)(PORT = 1521))
    )
  )

VALID_NODE_CHECKING_REGISTRATION_LISTENER = ON
REGISTRATION_INVITED_NODES_LISTENER = (server1.localdomain,server2.localdomain)

After the configuration is saved, the Listener configuration needs to be reloaded (lsnrctl reload listener or e.g. restart the service).

Now everything is prepared at the remote listener configuration to allow service registration from databases hosted on server1.localdomain and server2.localdomain, the database(s). The next step is to set the remote listener parameter at the CDB to reach the remote listener. In this example one can run on the database the following command:

alter system set remote_listener=' (ADDRESS = (PROTOCOL=TCP)(HOST=appserver1.localdomain)(PORT=1521))' scope=both;

Now the database registers itself with the remote listener.

 

 

In addition, the tnsnames.ora (or a jdbc connection string) can be apapted to connect to the database using the application server host listener instead of the database host listener:

jsotestpdb,jsotestpdb.localdomain =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = appserver1.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = jsotestpdb.localdomain)
    )
  )

The (application) client does not know anything about the database host at the time of connection.

Which impact does this have for standby databases? What about more "security" after setting up a remote listener successful? What happens with PDB Services while cloning to other databases?

Valid questions that will be answered in the next posts. Stay tuned.