Oracle database - if (not) exists clause is backported to 19c

 You may have read my blog post about the new feature of the if (not) exists clause available in database 23ai HERE, where a detailed description can be found. This is one of the most wanted features for a lot of (old fashioned) developers waiting for 23ai on-premises (which is still not available yet). 

The good news, this feature was now back-ported to 19c!
After the installation of the Oracle Database Release Update 19.28 you are able to use this feature in 19c. 

You don't need to change the compatible parameter of your database, it can still stays on 19.0.0.

You can see at the SQL logon message that my database is running on 19.28 and I am able to use the new if (not) exists syntax:

Like at 23ai, the table jso_test is first created with a column with a length of 10. The second - new - create table would create the table with a column with a length of 20. As "if not exists" is specified, it does not throw an error, BUT the length of the column is still 10. As before, one need to add an alter table to modify this column to a length of 20 characters. 

The third attempt is like it was until 19.27 where the if (not) exists clause did not exists. It throws an error. 

Now you can start changing your database scripts to use the if (not) exits clause, but don't forget to alter the columns to reflect schema changes. And you don't have to wait for 23ai on-premises!

Very good news for all database developers scripting the schema changes. 

An underestimated feature: Using Oracle Remote Listener with Standard Edition 2 Licenses - Part 4 - Security through "Hiding the database"

In the fourth part of the series "Remote Listener for Standard Edition 2 Databases", I highlight some security features that can be gained by using Remote Listener. These features can also be used with standard listeners, but they are particularly useful when running remote listeners.

First of all, the remote listener is “only” a listener, and therefore the same recommendations and restrictions apply - from a security perspective - as for local listeners. Nevertheless, especially running with an Oracle Standard Edition 2 license, these featurs can be used to provide additional security for the your database connection. This blog post provides an overview of the two features which allow to hide your database server and your database itself by using a remote listener.

1. Hide the database node(s)

By using the remote listener node in a TNS entry instead of the database node(s), end users can be prevented from seeing which database node the database is currently running on. Isn't it better to have the already known application server node, on which the remote listener is running, exposed in your tnsnames.ora (or jdbc connection string)?

If the database node(s) is/are not displayed in the application somewhere or cannot be evaluated in a query (e.g., by selecting v$instance) - because end users do not have SQL access, end users will not know anything about the database server.

Furthermore, a potential attacker who gains access to a client machine cannot easily find out which database server or servers are involved – they would need access to the application server (to execute an lsnrctl status) or do network tracing. The screenshot is an example of the TNS entry for a database service where only the application server name is specified as the host, but not the names of the two database servers on which this database service is started (depending on the role, standby/primary). Just looks like a single-server entry. 

2. Hide the PDB or (Non-CDB) Database Service

Often, there is only one database service that is actually intended to be used by a remote listener—an application database service (please do not use a default service, but all administrators should be aware of this by now). This is because application servers are typically divided into production and test servers, which means that only one dedicated database service needs to be accessed from each dedicated application server.

What nearly nobody know. This service can be configured in the listener.ora file for the remote listener.

A parameter exists which is admittedly relatively rarely used: DEFAULT_SERVICE_<Listener-Name>, i.e., normally DEFAULT_SERVICE_LISTENER.

The 19c database documentation states the following about this parameter:

After reading through the section marked in yellow, one might assume that this parameter does not work in a container database, as the client must EXPLICITLY specify the service name. However, the parameter is still present in 23ai, and there is no longer a classic architecture here. In addition, the parameter is called DEFAULT_SERVICE_<listener> and not DEFAULT_SID_<listener>. So it's time to find out whether the parameter is actually effective.

For testing purposes, Default_SERVICE_LISTENER is set as follows in the listener.ora file of the remote listener:

Now the TNS entry is adjusted and the value for the service is removed.

Will the connection be established even without an explicit service definition in TNSNames.ora?

Yes, it works. 

Conclusion:

By combining the hiding of the database servers and the database service, it is possible to ensure - with relatively simple effort - that as little information as possible about the database and its underlying server(s) is available on the client PCs. 

An underestimated feature: Using Oracle Remote Listener with Standard Edition 2 Licenses - Part 3 - What happens doing a pdb remote clone?

This is the third post of my series regarding Oracle Remote Listener and Standard Edition 2 databases. The first was regarding the Setup of  Remote Listener and the second post was about how helpful a Remote Listener can be for assuring High Availability with SE 2. 

One of the questions I often get is, what happens, if one clones a pluggable database (PDB) from a root container (CDB$ROOT) to another, e.g. from a production system to a test database. It could be awful, if the cloned PDB registers itself at the Remote Listener and the application starts to work with the production PDB and the test PDB on different connections. 

Let's have a deeper look into that. If you haven't read the previous post(s), please do so, because I will refer to them. 

The easiest thing to not allow a PDB to register with the Remote Listener is to put it into a CDB that runs on a server which is not in the listener.ora list of invited nodes (REGISTRATION_INVITED_NODES_LISTENER). But very, very often, a standby or Standard Edition High Availability (SEHA) environment does share one node with the test database. It is therefore not a wise decision to rely on this parameter. 

But what about the parameter for the remote listener, is it inherited with a PDB copy?
If one looks at the parameter for the remote listener per container, one will see that the remote listener entries are displayed by the PDB in exactly the same way as in the root container (remote listener host name has also been “masked” here). The following screenshot does show the parameter for Container ID 3 (my PDB) and for Container ID 1:

What happens if this PDB is now cloned to a new, empty test database via a database link? The new test CDB (called NRLORCL which is standing for No-Remote-Listener-ORCL) runs in parallel to the standby database on the same server (second node), which is a very usual setup for many customers.
There are no own PDBs on the test CDB:

There is no remote listener setup on the new test CDB:

 The services present in the CDB before cloning are only those of the root container:

After creating the user with the correct rights on the production database and a database link, the production PDB (with a remote listener setup) can now be cloned to the test CDB:

 

As long as the PDB is closed (mounted), there are now services running inside of the test CDB. While starting the PDB the first time, the new PDB services are created. We can find them in cdb_services.

 

 

The JSO_APP_SERVICE is cloned and created together with the new standard PDB service, but it is active? What do you think? Well, let's look into v$active_services:

 

 

 

This is, because we used "alter pluggable database save state" to get all the PDB services running when we do a switch to another node with the production. What if the REMOTE_LISTENER parameter was now cloned together with the PDB? Do we have two open databases getting connections from the application? The one in production and our cloned test PDB?

Thank god, nothing worse happend. The remote_listener parameter is not cloned with the PDB and running from the new test CDB. 

As the NRLORCL (test CDB) as root container also has no remote listener entry for the remote listener host, having a running JSO_APP_SERVICE only (but worse enough) has a local effect - i.e. if the database were then operated as primary on the standby node (which is the same system where the test CDB is running), the LOCAL LISTENER would have registered the JSO_APP_SERVICE service twice on this node! Once for the open primary DB and once for the cloned PDB on the test database - something that should be avoided!

Even more worse: if the NRLORCL has the same remote listener parameter setup as the ORCL database, the remote listener parameter would again be inherited to the jsoclonedtestpdb.

This means that the service would also be active twice on the remote listener - once for the primary-standby database combination and once for the test database. What a mess!

One should therefore always bear this in mind:
All application database services are moved to the new server with a PDB clone. A SERVICE_NAME_CONVERT is absolutely necessary for a remote pdb clone with own services as otherwise the original AND the clone will register at the remote listener if a corresponding remote listener parameter is set for the CDB. This would result in connections being established sometimes to one DB and sometimes to the other. 

This might be desirable for a RAC (Real Application Cluster), but never ever for a normal database!

An underestimated feature: Using Oracle Remote Listener with Standard Edition 2 Licenses - Part 2 (High Availability with Standby DB)

This blog post is part 2 of my series "Remote Listener and Standard Edition 2". The first blog post was about the setup of the Remote Listener, now the second is how one can use a Remote Listener for a more or less transparent failover of new connects to a database which is createad as high available standby environment. 

Brief recap:
There are two database servers with a primary standby database (ORCL) and a remote listener set up on a third server. (While one could use "Connection Manager" (CMAN) for Enterprise Edition databases, the license does not allow to use this for Standard Edition 2 databases.) Which kind of primary-standby solution is used does not matter - as they all work same more or less for Standard Edition Databases (depending on archive redo log shipping). 

The solution cookbook (which also works with Standard Edition High Availability (SEHA)).

The PDB is called jsotestpdb and does not yet have its own service for the application. However, as this makes sense, a service with the name JSO_APP_SERVICE is created.
 
The service can be created and started on the primary database using the following syntax:

exec dbms_service.create_service('JSO_APP_SERVICE','JSO_APP_SERVICE');

After the service is created, it can be started: 

exec dbms_service.start_service ('JSO_APP_SERVICE');

As it makes sense for high availability reasons that this service is also started automatically during a switchover, either a “SAVE STATE” of the PDB must be executed or a trigger must be created that starts this service when the PDB is opened (the third option would be to create and start a service in the grid infrastructure). Services are only started when the PDB is opened, this means, it is ensured that this service is never started on the standby side.
The simplest method to execute an automated service start is certainly the save state command (when the service is active):

alter pluggable database jsotestpdb save state;


On the listener side, this now looks as follows (the remote listener host name is hidden):

 

 

 

It is important that both root containers (i.e. the primary and the standby database in mount status) are registered on the listener (section marked as snip from previous picture). 

Also highlighted is the new created and started PDB application service. 

Everything is in place now to prepare the client connections. Therefore an entry is created in TNSNames.ora with the name of the remote listener host as HOST (remote listener host name is hidden in the following picture).

Now one can connect with sqlplus and select the host name, where the connection was routed to (where the primary database is running on).

 

As one can see, the connection was routed to the PDB running on the WINTEST-1 node, because this is the primary database. The standby is running on the WINTEST-2 node and the application service is not running there (the PDB is mounted, not opened). Now doing a switchover (with the toolset of your standby software provider) and reconnect again, after the PDB was opened. We still use the same tnsnames.ora which points to the remote listener host and the service registered there.

 

 

Now the connection is on the WINTEST-2 node, because this is the primary environment now, while WINTEST-1 is now the standby system. Without changing anything on the application, on tnsnames.ora, without any "failover hickups" by timed out connection retries the application does connect direct to the running PDB. That's easy, isn't it?