Bug: Patching Oracle AI Database 26ai to RU 2 with autoupgrade tool

 Sometimes also the best tools do have some little bug, even if it is typically bullet proof.

The issue today is with autoupgrade tool build version 26.3.260401 from April 2026. If you need to check your version just use

java -jar autoupgrade.jar -version

and you can see your version, build date, etc..

If you try to analyze or patch your existing environment with 

java -jar autoupgrade.jar -patch -config <myconfig.cfg> -analyze
java -jar autoupgrade.jar -patch -config <myconfig.cfg> -deploy 

you will get an error that in the patch directory nothing newer is found. 

If you look into your autoupgrade error log for this patch job, you will find the following messages (followed by a java call error stack):

*Validating Oracle Patch files
An Oracle newer Release Update file for database is not found in <patch-directory> for the job with prefix <patch1>

 - Bootstrap.processCLIParams#83 

oracle.commonx.utils.errors.ConfigurationError: 

There were conditions found preventing AutoUpgrade Patching from successfully running
*Validating Oracle Patch files
An Oracle newer Release Update file for database is not found in  <patch-directory> for the job with prefix <patch1>

Unfortunately, the previous 26 version of the autoupgrade tool throws the same error. If you want to step back to the last autoupgrade versions 25 - this will not work. They will all throw the same message:

AutoUpgrade Patching has derived the version for prefix <patch1> as [23.26.1.0.0]. AutoUpgrade Patching currently only supports the following major versions: [19].

The bug is known to Oracle and they will fix it. I think the next version will follow soon (as the last one is from April 2026), maybe in the next days.

Until then, as a workaround, you need to download the patches first (you can use analyze mode until it throws the error or the download mode).
If the patches are available in the staging directory you must create the new home first:
java -jar autoupgrade.jar -patch -config myconfig.cfg -mode create_home

Afterwards, analyze the source database, run the fixups and deploy (without -patch):
java -jar autoupgrade.jar -config myconfig.cfg -mode analyze
java -jar autoupgrade.jar -config myconfig.cfg -mode fixups
java -jar autoupgrade.jar -config myconfig.cfg -mode deploy

This should do it. 
I will update the post as soon as the new autoupgrade version is available.

Differences in DBCA responsefiles in Oracle Database 19c and Oracle AI Database 26ai

 After the release of Oracle Database AI 26ai (Enterprise Edition only) there are some questions to ask. 

One is, can I reuse my response files created with the Database Configuration Assistant of my 19c database, where do I maybe need to or would be able to do changes? To answer this question, a view/comparison of the two response files is necessary. The configuration was done on both database versions with “advanced configuration” on Linux (as all other 26ai versions are not available yet, the 19c dbca response file was created with 19c RU 24).

So, let’s start with the comparison. One interesting fact is found at the beginning of the response file. The file version is 12.2.0 (because 19c was/should have been a 12.2.X release) while the 26ai is using response file 23.0.0 – not a big surprise, as 26ai is showing itself as a 23.26 in a lot of different places.

The next interesting fact is the databaseConfigType. In 19c there was SI (Single Node), RAC and RACONENODE available, with 26ai Oracle added SEHA (Standard Edition High Availability). As today (Feb. 5th, 2026) the Standard Edition Software is not available for 26ai there is no chance to test this, but it should make the configuration of SEHA environments easier in the future. Therefore, 26ai added also more SEHA related parameters, e.g the sehaServiceName and the sehaNodeList (unfortunately it is not grouped together in the response file, so one must search for the parameters manually).

The next topic added is the managementPolicy. It controls, how the database handles PDBs when restarting. Flora has a very informative blog post done for 23c, so details how to use that can be found here: https://oracleandme.com/2023/05/16/23c-floating-pdbs-the-lab/

As there is no Database Express management possibility anymore in 26ai, emConfiguration now does only allow CENTRAL (Enterprise Manager Cloud Control) or NONE as values. DBEXPRESS and BOTH are not allowed anymore.

One interesting fact: In my 19c response file the fast recovery area was part of the InitParams line, with 26ai a new parameter recoveryAreaSize was added, but the value is also still part of the initParams line. What will happen, if there are two different sizes specified in the response file? Is there a difference between the setup part and the running part later?

Also new are the specification of the dbOptions and pdboptions, which now can be different, e.g. SPATIAL can be installed at CDB level with dbOptions SPATIAL:true but can be excluded from the PDB pdbOptions with SPATIAL:false.

The last new parameter I found in the 26ai response file is useOMF, which can be set to true of false. In the 19c response file, there is no useOMF parameter – it was set at the background to true.

The last new parameter I’ve found is enableArchive which is default set to false, meaning the database is not using archive log mode after it was created.

sampleSchema installation is now removed from the 26ai response file, typically now one installs them in a production or test environment.

All in all, there are not so many differences in the response files. All new parameters are not mandatory except of SEHA, if one uses this option the depending parameters must be inserted, and the default values are typically set to none.

One funny thing in 26ai is, that createAsContainerDatabase is not mandatory, but the default value is still false. As 26ai can’t be installed as non-CDB, I question myself, why this was inserted (also inserted is the initParams entry “enable_pluggable_database=true”):

#-----------------------------------------------------------------------------
# Name          : createAsContainerDatabase
# Datatype      : Boolean
# Description   : flag to create database as container database
# Valid values  : Check Oracle12c Administrator's Guide
# Default value : false
# Mandatory     : No
#-----------------------------------------------------------------------------
createAsContainerDatabase=true

If you are interested in the response files, you can download them from my storage:

19c: dbca_19c.rsp
26ai: dbca_26ai.rsp

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?

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. One can move directly to blog post 2 - using a high available database (Standard Edition High Availability or Primary-Standby combination) with a remote listener.

How to start preparing your database for the upgrade to Oracle database 23ai - even without an on-premises released database software!

 As many of you already know, Oracle's autoupgrade tool will be the only supported option for database upgrades in the future. This fact can also be found in the support notes of the 23ai database.

However, the autoupgrade tool - and most people don't know this - already supports the upgrade to Oracle database version 23ai in Release 24 (available since roundabout March of this year). The question therefore arises, how can you TODAY use the autoupgrade tool to optimally prepare your existing databases for the upgrade to 23ai ? Especially now, when the perhaps quiet time of the year is approaching, but the official release date of 23ai has been postponed to “sometime in calendar year 2025” (postponed once again)?

The preparation

First of all, the latest version of the autoupgrade.jar must be downloaded from Oracle Support and can be found at Document ID 2485457.1.
This is currently a build from October 2024 (24.7.241021). The autoupgrade.jar can next be copied to the server and tested for functionality. If no Java is installed on the server, the JDK from the highest Oracle Home release can be used. This has always worked so far.
Oracle's support for direct upgrades to 23ai is limited to 19c and 21c databases, this means older database versions cannot be prepared for a direct upgrade to 23ai using the autoupgrade tool. An intermediate upgrade (to 19c) is therefore necessary beforehand or, for example, data migration with Datapump Export-Import is a possible way to move from older database versions to 23ai. However, no pre-test can be one with datapump.

If the execution of autoupgrade.jar (java -jar autoupgrade.jar -version) works on the server, the next step is to create a config file for the database that is to be prepared for the 23ai upgrade.

The following information is important here:

• the log directories (global and per DB)
• Which SID should be tested/analyzed
  
• The source Oracle Home - this needs to be a 19c or 21c database home
• The target Oracle Home - is set, but this directory does not exist - the database is not yet available on-premises
• Start_time and upgrade_node can be set by default (NOW/localhost)
• Important - the autoupgrade tool normally takes the target_version from the target home. However, as this does not exist, it is important to set 23 fixed as target_version here.

 

 

Config file

 Analyzing the existing database

Once this preparation has been completed, the autoupgrade tool can now be run in Analyze mode against the source database. NO changes are made to the source database, this means this procedure is completely safe.
However, it is important that all pluggable databases (PDB) are open. PDBs that are in mount status are not analyzed.

If all PDBs are open, there is no longer a warning and the corresponding autoupgrade job starts analyzing the existing database.

The “status” command can be used to display the progress of the analysis of all jobs on the Autoupgrade CLI. It shows 4 databases (1 CDB + Seed PDB + 2 user PDBs).

To see the details of the individual databases, just add the job with number to the “status” command. Now one can see the stage progress per container (the 4 databases).

The job should be successfully completed after a few minutes. There are typically no errors in the analysis phase.

The analysis result (in various formats, including JSON, text and HTML) is now available for the analyzed database in the Prechecks subdirectory of the log directories for the job. The next picture does show the HTML.

The first overview already shows that there are components in the 19c database that would be removed during an upgrade to 23ai. These are remnants of Oracle Multimedia - marked yellow.

If one now looks in the detailed areas of the databases (CDB and PDBs), one will find valuable information on how to deal with such changed or removed components. The Autoupgrade tool recommends taking a look at various support notes, for example.

In the database analyzed here (which was already upgraded from a 12c environment), the autoupgrade tool also found a configuration of traditional auditing. Traditional auditing was first supplemented by Oracle some time ago with Unified Auditing and then replaced - or is now officially desupported for the first time, so that Unified Auditing must be switched to.
As the configuration of auditing can be a very time-consuming process, it is advisable to adapt the configuration on the existing database even before the upgrade planning officially starts after 23ai.

The autoupgrade tool may already find things as part of the POST check, things which then need to be  done on the 23ai database, such as changing the LOB type at an audit table. Of course, this cannot yet be prepared in the existing environment, but it does provide an indication of any manual work that may need to be done (and maybe any additional downtime) during a later upgrade.

Incidentally, even though the screenshots are now all from a Linux environment, the analysis method was also tested with a 19.8 database under MS Windows. It should therefore be possible to use this procedure on all supported operating systems.

Now start prepare your database for a successful upgrade to database version 23ai.

Oracle database client installation "The java.library.path system variable is missing or invalid"

As I heard it more than once in the last months, it is the right time to write a blog post about it. I still don't know, why it happens so often at the moment (except of a lack of reading the documentation), but I know, how it will (hopefully) work for you, if you run in this problem (without reading the documentation).

The typical environment where this happen is a Red Hat Linux (mostly 8.x and 9.x), but it can happen on any Linux distribution.

The customers of mine all tried to install a 19c 64-bit Client on a Red Hat Linux system as the installer stopped somewhere with this message:

Preparing to launch Oracle Universal Installer from /tmp/OraInstall20XX-XX-XX_XX-XX-XXPM. Please wait ... The java.library.path system variable is missing or invalid. Please set java.library.path with a correct value and retry the operation.

Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class oracle.sysman.oii.oiip.oiipg.OiipgPropertyLoader

First to check is always, if the java environment is set up/available correctly, but typically, it is. Second is to check the documentation for the operating systems requirements of the product you want to install - in case of 19c clients on Linux, the documentation can be found here.

In the above case, the error message  "The java.library.path system variable is missing or invalid" is just wrong. Java is fine and available and the java.library.path is correct.

In this case and with the documentation you can then check the supported (kernel) version and afterwards the Linux system for the required packages, this is e.g. the list for Red Hat Linux 8.x:
bc, binutils, elfutils-libelf, elfutils-libelf-devel, fontconfig-devel, glibc, glibc-devel, ksh, libaio, libaio-devel, libXrender, libX11, libXau, libXi, libXtst, libgcc, libnsl, librdmacm, libstdc++, libstdc++-devel, libxcb, libibverbs, make, policycoreutils, policycoreutils-python-utils, smartmontools, sysstat.

There is also a section in the documentation which allows to check the optional packages required for some products, e.g. ipmiutil (for Intelligent Platform Management Interface), libnsl2 (for Oracle Database Client only), libnsl2-devel (for Oracle Database Client only), net-tools (for Oracle RAC and Oracle Clusterware), nfs-utils (for Oracle ACFS).

Now, what happend to all the customers with the above error message while installing the client? As we found out step by step, one of the required packages were not installed as X86 64bit version: The libnsl. But this isn't told by the installer - it complains about the java.library.path system variable.

All customers just installed libnsl.x86_64 as newest version using 

yum install libnsl.x86_64

and then they retried the installation of the client succesfully. All other packages, except libnsl, were setup with the Red Hat installation, only libnsl was missing.

If you run an Oracle Enterprise Linux you can use the preinstall-rpm for the database (oracle-database-preinstall-19c) - by the way even if you only want to install the client - to get all the libs installed automatically. If you are on Red Hat, you really should check the libraries according to the documentation.

Yes, it is a (mostly) stupid work to compare libs, but if it prevents from a whole day searching for an error with a misleading error message... 

Oracle Database 23c - Thank you for the "if [not] exists" clause

People who know me for a longer time do know, that I started my "Oracle" career as Developer and , mainly with PL/SQL and Oracle Forms. One of the main things I was doing together with a colleague was to set up data models.
These Data Models were created for all kind of applications and customers. Part of the job was, to create all the scripts to set up the Oracle database schemas.
Later, as the application evoluted to new versions, we needed to change the data model from time to time. Adding tables, changing columns, dropping objects, deleting data, ...

For sure, you can use edition-based redefinition, or you can compare the databases between development and production and (let) create scripts with only the differences in it.
But the world is more complicated. The application is installed a couple of times with different versions at the customers. Due to fixes, patches, releases the data model does not look the same for all customers.
The workload to create hundreds of different scripts for any type of difference is uncredible. The solution was - there is one common script that can be used from one release to the next release and all changes are added.
The script tried to create tables or indexes that may already existed or tried to drop tables, which didn't exist anymore, as they have been dropped previously by a patch.
Thanks to that, the log file was full of ORA- errors with "... does already exist" etc. errors. For the installation team it was horrible to control the logs. Was anything added/changed/dropped as intended?
Is an "object does not exist" error fine (as it tried to drop an already dropped object) or is it a real error (because it tried to add a column on a table that does not exist)?
Scripts has been created to replace ORA- errors with comments "NOORAERR-" to allow to skip the "this thing is fine" errors.

Thank God, Oracle now has changed this for the new Oracle 23c database. They added a "if [not] exists" clause for DDL. This can help to avoid so many hours of work for all the checks mentioned before.

Let's do some tests on the already available 23c Developer Edition VM.

Test 1: Create a user.

First, we create the user and retry to create it again using the old and the new syntax. Also, we try to create the (already existing) user with another password as before and try to connect to the database.

• create user my_user identified by <*>;  
• create user my_user identified by <*>;
• create user my_user if not exists identified by <*>;
• create user my_user if not exists identified by <*>;

 

As we can see, the user is created with the first, the classic statement. With the new syntax we don't get an error message, if we try to create the user. That the whole statement is discarded we can see at the identified by clause followed by a try to connect.

 The "new" password is not set, so nothing is done at all and the message "user created" is thrown. This is, what I have expected, but be aware, if you need to use the new password for this user, it must be changed in addition (as alter user) in your scripts. 

Test 2: Drop a user.

For our second test, let's try to drop a user. First we drop it using the old syntax and then we try to drop it again with the old and the new syntax.

• drop user my_user1;
• drop user my_user1;
  
• drop user if exists my_user1;

 

Nothing special here, works as expected.

Test 3: Create a table.

For a next tests I use the user created at the first test scenario and try to create and modify a table in that schema (I have granted some rights already, so the user is allowed to).

Let's go:

• alter session set current_schema=my_user;
• create table my_sales (txt varchar2(20));
• create table if not exists my_sales (txt varchar2(20));

Ok, now we try to create the table again (with different columns as before).

• create table my_sales (txt varchar2(40), txt1 varchar2(10));
• create table if not exists my_sales (txt varchar2(40), txt1 varchar2(10));

As expected, we do get an error message with the old syntax, but not with the new one. If we do a describe on the table, we see it is still defined like it was originally created.

 Test 4: Modify a table (add column).

 Now we can try to alter the table - first we will add the new column varchar2(10).
Which of the following statements will run and which of them will do the right thing?
Which one will throw an error if it runs (twice)? 

Hint: We will start with the original table (see describe in Test 3) for every test.

• Statement 1: alter table my_sales add txt1 varchar2(10);
• Statement 2: alter table my_sales add if not exists txt1 varchar2(10);
• Statement 3: alter table if exists my_sales add  txt1 varchar2(10);
• Statement 4: alter table if exists my_sales add if not exists txt1 varchar2(10);
• Statement 5: alter table if exists my_sales_history add txt1 varchar2(10);

The test starts with Statement 1 - I expect, it will work the first time, the second time it throws an error.

Well, works as it did since I can think of. No wonder - this syntax is used since the first versions of Oracle Databases.

Statement 2 is the one with a not exists for the column. Will it work (twice)? What do you expect?

Unfortunately, this statement throws an error. I have checked therefore the "column clause" at the 23c documentation. And yes, there isn't a "if (not) exists" clause for this part of the statement.

With this in our minds we do know, that Statement 4 will also not run. But what about Statement 3?

Yes, this works - this is an allowed statement. But if you run it two times, you will get an error as the column does already exists.

Statement 5 (using a non-existing table name) should also work without throwing an error.

 Test 5: Modify a table (modify/drop/rename column).

What about modifying/renaming/dropping a column? There is the same restriction as adding, this means there is not a "if (not) exists" syntax at column level. This means, we still get some error at this level.

We only can check, if the table does (not) exists. Allowed syntax is:

• alter table if exists my_sales modify column txt varchar2(40);

not allowed syntax is

• alter table my_sales modify if exists txt varchar2(40);
• alter table my_sales modify txt varchar2(40);

Same with drop column:

• alter table my_sales drop column if exists txt;
• alter table my_sales drop column txt;

What you do think about this behaviour? On the one hand, there can be columns named "if", so this maybe influenced Oracle to not use the clause in an alter table statement for the columns.

On the other hand, if Oracle checks the whole syntax, they could figure out, if the column is named IF or if the IF is followed by a (NOT) EXISTS statement.

Without showing you any tests - if you want to add/change/drop (foreign/unique key) constraints to a table (column), there is also no if (not) exists clause. Now we leave tables and check for other objects, eg. with

Test 6: Create views (procedures/packages/...).

What about following syntax?

• create view if not exists my_view as select upper(txt) uptxt from my_sales;
• create view my_view as select upper(txt) uptxt from my_sales;

By the way - create or replace (views/procedures/...) combined with "if exists" does not make any sense - and the database is telling you that.

Conclusion:

For a lot of statements the new if (not) exists syntax works very well. Unfortunately, it does not work for adding, renaming or modifying table columns, constraints, etc.. There are still some open (weak points) in my opinion therefore.

As the statement is executed only if the if (not) exists succeeds, it can also happen that user or table does not have the status you think it should have, as it had existed before.

It's still the users/developers responsibility to write bullet proof scripts for migrations. We can't get rid of all "unnecessary" errors, but at least there is a bigger amount of error messages not showing up in the installation logs, using the new syntax.

At twitter, a discussion was started by people who said, it would be nice to get a different message for different states, e.g. if a create table if not exists statement should not throw "table created", but a "statement skipped, table exists" message (without ORA- error) to see the difference.

What's your opinion on that (please write a comment)?