Oracle 19.15 upgrade not working with TDE Wallet

TLDR

It appears that the physical contents of the cwallet.sso and ewallet.p12 files changed enough between Oracle 11 and Oracle 19 that the Oracle 19.15 (19.21 was also tested) binaries no longer liked the physical contents of the TDE wallet files even though the logical contents were correct. Overwriting the existing contents of the old “Oracle 11 style” wallet files with the Oracle 19 binaries with the exact same logical contents allowed the database to work as expected. This happened somewhere between Oracle 19.8 (old Oracle 11 style wallet works fine) and Oracle 19.15.

The Problem

Recently we had an interesting situation where a database that had been upgraded over the years, from Oracle 10 to eventually Oracle 19.8, wouldn’t upgrade to 19.15 cleanly. Once the ORACLE_HOME binaries had been updated to 19.15 (datapatch had yet to be applied) the database would only open in restricted mode and the following message would appear in the alert logs:

ALTER DATABASE OPEN detects that an encrypted tablespace has been restored but the database key has not been activated, or the database has been flashback'ed prior to first set key of the master key (pdb 0).
Database is open in RESTRICTED MODE only.
Please select the latest master key from V$ENCRYPTION_KEYS and execute ADMINISTER KEY MANAGEMENT USE KEY command, and restart the database

We could force the database to open in read write, and then run datapatch and everything would appear to work correctly, but this seemed kind of buggy because any bounce of the instance would still cause the database to open in restricted mode with the same error message in the alert log and we’d have to repeat the force to read write mode again.

This behavior was very strange because there was only a single TDE (Transparent Data Encryption) key in the wallet and it was obviously working just fine with the Oracle 19.8 binaries. However, if you looked into the data dictionary, you’d notice that the creation date and activation date in the old wallet were both NULL and there was also a NO value for masterkey_activated in the data dictionary even though the master key was very obviously activated since we could read and write to encrypted tablespaces just fine.

Some Details

• The database name is going to be orcl, and it will be a standalone database, not a pluggable database in a container database.
• The database was created a long time ago as an Oracle 10 database, upgraded to Oracle 11 at which point the transparent data encryption wallet was added and an encrypted tablespace was created using Oracle 11.1 binaries (which was when TDE for tablespaces was first introduced).
• The database has an encrypted tablespace with a bunch of tables in it.
• The database has a TDE wallet (located here: /u01/app/oracle/admin/orcl/tde_wallet) with the two important files: ewallet.p12 which, of course, contains the actual TDE key and is secured with a password (oracle_4U which, of course, is a bad TDE wallet password because you want your TDE keys file to be pretty secure), and the cwallet.sso file which contains an encrypted version of the oracle_4U password needed to read the ewallet.p12 file). The important detail for these two files is that they were created with Oracle 11.1 binaries which appears to have less information in them than if they were created with the Oracle 19 binaries.
• The database is open and the wallet is open with a wallet_type of AUTOLOGIN.
• All SQL commands were entered using a bequeath connection on the database server (sqlplus / as sysdba).
• Almost certainly this bug exists because the v$encryption_keys view has a NULL for activation_time and the v$database_key_info view has NO as the value for masterkey_activated. These values appear to be read directly from the ewallet.p12 file and it appears that the Oracle 11.1 binaries never set those values in the ewallet.p12 file.

The Fix

For some operations with a TDE wallet, it appears you need to have a password based wallet type and not an auto_login based wallet type. Since our database is currently open with an auto login wallet, we’ll close the wallet and re-open it as a password based wallet. You may not need to move the old cwallet.sso file, but we moved the cwallet.sso file to old.cwallet.sso before we did the below command. The DBA had backed out the 19.15 binaries, so all of the below commands were done with the 19.8 binaries.

SQL> administer key management set keystore close;

We didn’t need to supply a password when closing the wallet because it is currently an autologin wallet.

Next, we’ll open the wallet using a password.

SQL> administer key management set keystore open identified by oracle_4U;

A query against v$encryption_wallet will now show a status of OPEN and a wallet_type of PASSWORD. We can now modify the wallet to contain the exact same logical contents that it currently contains. We’ll first find the key that is currently in use. In our case there was only a single key since the key had never been rotated. If you’ve rotated keys in the past, make sure to choose the currently activated key which is the one in v$encryption_keys with the highest creation_time or activation_time. Interestingly with the old 11.1 wallet, both these values were NULL, which is almost certainly why this bug exists in the first place (apparently, even Oracle can make mistakes with NULLs!).

SQL> select key_id, creation_time, activation_time from v$encryption_keys;

Now, using the key from above, we’re going to ‘update’ the wallet to use the exact same key that it’s already using. Note that you’ll enclose the key in ticks since it’s a literal value.

SQL> administer key management use key 'TheKey_IDValueFromTheQueryAbove' identified by oracle_4U with backup;

The ‘with backup’ clause automatically saves the previous version of the ewallet.p12 file and renamed it to include the current date and time. It was at this point that we noticed that the new ewallet.p12 file which contained the exact same key as the older ewallet.p12 file had grown in size. On our system, we went from an old file size of 1573 bytes to new file size of 2987 bytes.

Additionally, a query against v$encryption_keys showed that our previously NULL activation_time was now set to the time the previous command was run. And v$database_key_info now had the correct value of YES for masterkey_activated.

Our next step was to recreate the cwallet.sso file using the newly created ewallet.p12 file.

SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/orcl/tde_wallet' identified by oracle_4U;

If you check the file size of the new cwallet.sso file (remember, we had moved our old one before so we could open the wallet with a password) against the old file you should notice that the new file is larger than the old one even though, again, it contains the exact same logical contents as the previous file (the encrypted oracle_4U password). On our system the old cwallet.sso file was 1883 bytes and the new cwallet.sso file was 3032 bytes.

Now a query against v$encryption_wallet still shows us that the wallet type is PASSWORD. Interestingly we can change this to AUTOLOGIN while keeping the wallet open with the following command.

SQL> administer key management set keystore close identifed by oracle_4U;

One would sort of expect this to actually close the wallet, but instead, it doesn’t close the wallet, it just updates the wallet_type column of v$encryption_wallet from PASSWORD to AUTOLOGIN.

At this point, one of the DBAs noticed that the fully_backed_up column in v$encryption_wallet was still NO. This is because although we had backed up our previous wallet key file, we had yet to back up the current version of the wallet key file (remember, this is the ewallet.p12 file). Just for fun (or was it just to be pedantic?) we did that next.

SQL> administer key management backup keystore force keystore identified by oracle_4U;

While logically our wallet file contents hadn’t really changed at all (one key in the ewallet.p12 and one password in the cwallet.sso), the updated wallet files created with the 19.8 binaries allowed our upgrade from 19.8 to 19.15 to go exactly as expected and we didn’t get a repeat of the database opening in restricted mode, almost certainly because the activation_time in the ewallet.p12 file for the key was no longer NULL.

Happy DBAing!