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)?