I knew, one fine day I will have to dig into TDE. Resistance is futile. There were so many questions regarding AutoUpgrade with Transparent Data Encryption (TDE) in the past weeks and months. And the team is still working hard on a solution to make the non-CDB to PDB plugin flawless and automated for such cases. But I won’t cover the latter in this post here. I will solely focus on the database upgrade itself.

Photo by Jason Dent on Unsplash
TDE?
Almost everybody of you reading this blog post may have more knowledge of Transparent Data Encryption than I. Hereby I confess: I’m a total newbie to TDE. But with “encryption by default” in our cloud deployments on the ExaCC machines some of my customers have, the day has come to start playing with TDE. If you have suggestions and corrections, please use the comment section and I will include them.
Where do I start?
I used this White Paper written by my PM mate Peter Wahl in 2012 as a starting point:
If you need to read more about TDE, Peter’s paper has all the relevant links.
Introducing TDE to my 11.2.0.4 database
Yes, 11.2.0.4 is my starting point. I would like to do this exercise with two different databases. And I follow the instructions and recommendations from Oracle Advanced Security Transparent Data Encryption Best Practices.
At first, I create a subdirectory for my wallet for my FTEX database:
[oracle@hol ~]$ su root Password: [root@hol oracle]# cd /etc [root@hol etc]# mkdir -pv ORACLE/WALLETS/FTEX mkdir: created directory ‘ORACLE’ mkdir: created directory ‘ORACLE/WALLETS’ mkdir: created directory ‘ORACLE/WALLETS/FTEX’ [root@hol etc]# chown -R oracle:dba ORACLE [root@hol etc]# chmod -R 700 ORACLE [root@hol etc]# exit
Then I add this to my sqlnet.ora – I use a 19c listener for all my databases running from an 19.8.0 Oracle Home:
ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /etc/ORACLE/WALLETS/$ORACLE_SID) ) )
Be aware that the ENCRYPTION_WALLET_LOCATION is deprecated in Oracle Database 19c. Instead use the WALLET_ROOT parameter.
Afterwards I create the keystore for my 11g database:
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 29 14:49:55 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter system set encryption key authenticated by "oracle"; System altered.
Please excuse my very secret password.
In the next step I change the file permission of the keystore:
$ cd /etc/ORACLE/WALLETS/FTEX [FTEX] oracle@hol:/etc/ORACLE/WALLETS/FTEX $ ls -lrt total 4 -rw-r--r--. 1 oracle dba 2840 Jul 29 14:50 ewallet.p12 [FTEX] oracle@hol:/etc/ORACLE/WALLETS/FTEX $ chmod 600 ewallet.p12 [FTEX] oracle@hol:/etc/ORACLE/WALLETS/FTEX $ ls -lrt total 4 -rw-------. 1 oracle dba 2840 Jul 29 14:50 ewallet.p12 [FTEX] oracle@hol:/etc/ORACLE/WALLETS/FTEX
This is only necessary for the 11.2 database. In the following 12.2.0.1 example, the permissions are set correctly when I create the keystore.
Introducing TDE to my 12.2.0.1 database
Again, I need to create a subdirectory for my wallet for my DB12 database:
[oracle@hol ~]$ su root Password: [root@hol oracle]# cd /etc [root@hol etc]# mkdir -pv ORACLE/WALLETS/DB12 mkdir: created directory ‘ORACLE/WALLETS/DB12’ [root@hol etc]# exit
I did the correct settings in my shared sqlnet.ora already for the other database. But I need to create a keystore for my 12.2.0.1 database as well – again with the most secret password for my tests.
You may recognize that I use a different command than as I did use above. Thanks to Peter Wahl (our Oracle TDE PM) who guided me to the up-to-date syntax:
SQL*Plus: Release 12.2.0.1.0 Production on Wed Jul 29 16:02:40 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> administer key management create keystore '/etc/ORACLE/WALLETS/DB12' identified by "oracle"; System altered.
AutoOpen Keystore
For the database upgrade it is very important that the keystore (or wallet as it was called before) is AutoOpen. If you have intentionally a keystore (aka wallet) which is not AutoOpen, change this for the upgrade, and revert afterwards.
I do this here for my two databases, at first the 11g one:
. ftex $ orapki wallet create -wallet /etc/ORACLE/WALLETS/FTEX/ewallet.p12 -auto_login Oracle PKI Tool : Version 11.2.0.4.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password:
And then for the 12.2.0.1 database, I use again a different approach:
. db12 $ sqlplus / as sysdba SQL> administer key management create auto_login keystore from keystore '/etc/ORACLE/WALLETS/DB12' identified by "oracle";
In both cases I get now a cwallet.sso file within the same location of the ewallet.p12.
See also:
Creating a new encrypted tablespace
For my test, I will create an encrypted tablespace. As online encryption is not available in Oracle 11g, I will use this process:
SQL> alter database default tablespace SYSAUX; Database altered. SQL> drop tablespace USERS; Tablespace dropped. SQL> CREATE TABLESPACE "USERS" DATAFILE '/u02/oradata/FTEX/users_encrypt01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256'; Tablespace created.
In my 12.2.0.1 databasse, I can use ONLINE encryption – this is much easier but requires 2x the file space for the operation:
SQL> alter tablespace SYSTEM ENCRYPTION ONLINE USING 'AES256' ENCRYPT FILE_NAME_CONVERT = ('system01.dbf','system_enc01.dbf'); Tablespace altered.
AutoUpgrade with TDE
I just would like to see if it works without any treatments. I changed both keystores to AutoOpen – and I use this config file for the two databases:
global.autoupg_log_dir=/home/oracle/upg_logs # # Database number 1 # upg1.dbname=DB12 upg1.start_time=NOW upg1.source_home=/u01/app/oracle/product/12.2.0.1 upg1.target_home=/u01/app/oracle/product/19 upg1.sid=DB12 upg1.log_dir=/home/oracle/upg_logs upg1.upgrade_node=localhost upg1.target_version=19 upg1.timezone_upg=no upg1.restoration=no # # Database number 2 # upg2.dbname=FTEX upg2.start_time=NOW upg2.source_home=/u01/app/oracle/product/11.2.0.4 upg2.target_home=/u01/app/oracle/product/19 upg2.sid=FTEX upg2.log_dir=/home/oracle/upg_logs upg2.upgrade_node=localhost upg2.target_version=19 upg2.timezone_upg=no upg2.restoration=no
And as usual, I first run “-mode analyze” and check the HTML file:
$ java -jar $OH19/rdbms/admin/autoupgrade.jar -config UP19.cfg -mode analyze AutoUpgrade tool launched with default options Processing config file ... +--------------------------------+ | Starting AutoUpgrade execution | +--------------------------------+ 2 databases will be analyzed Type 'help' to list console commands upg> Job 100 completed Job 101 completed ------------------- Final Summary -------------------- Number of databases [ 2 ] Jobs finished successfully [2] Jobs failed [0] Jobs pending [0] ------------- JOBS FINISHED SUCCESSFULLY ------------- Job 100 for FTEX Job 101 for DB12
Checking the HTML file(s):
firefox /home/oracle/upg_logs/DB12/101/prechecks/db12_preupgrade.html & firefox /home/oracle/upg_logs/FTEX/100/prechecks/ftex_preupgrade.html &
And this is the important message from the HTML output:
Of course, I receive a very similar message for my 12.2.0.1 database:
Looks fine – I will run the AutoUpgrade of my two databases and monitor it with this python webserver:
cd /home/oracle/upg_logs/cfgtoollogs/upgrade/auto python -m SimpleHTTPServer 8080
Then start a browser on: http://127.0.0.1:8080/state.html:
Finally …
Plugin?
But at this point, the story may not be over yet. You may want to plugin these databases into a precreated CDB.
To cover this topic, there will be another blog post.
Addition for Multitenant
For the records following Peter Wahl’s advice, I add here some commands to enable TDE for a Multitenant container database as I did my two above tests only with non-CDBs. In this case, I will use the CDB2 from the Hands-On Lab and create two additional PDBs, PDB1 and PDB2.
SQL> administer key management create keystore '/etc/ORACLE/WALLETS/CDB2' identified by "oracle"; keystore altered. SQL> administer key management set keystore open identified by "oracle" container=all; keystore altered. SQL> administer key management set key identified by "oracle" with backup container = current; keystore altered. SQL> administer key management create auto_login keystore from keystore '/etc/ORACLE/WALLETS/CDB2' identified by "oracle"; keystore altered.
Further Links and Information
- Oracle Advanced Security Transparent Data Encryption Best Practices
- Example: Configuring a software keystore when multiple database share the same sqlnet.ora
- Database Upgrade and TDE – Things to know – Jan 16, 2018
- Refresh AutoUpgrade Monitoring Information automatically – July 16, 2020
–Mike
Thanks Mike .
May i copy your sentence “I knew, one fine day I will have to dig into TDE. Resistance is futile. ”
I have a lot of db ( mainly in cloud ) but the sentence is still valid for me .
Hope that this post is only the first that will cover the Upgrade & TDE in various scenario
Roberto,
you can – and yes, more blog posts will be up there soon 🙂
Cheers,
Mike
Hello Mike,
We are in process to upgrade Database Oracle 12.1.0.2 with TDE to 19.3 using auto upgrade.
There is no auto login wallet configured.
So we open the wallet before starting auto upgrade.
Auto upgrade failed when because it tried to copy cwallet.sso which off course doesn’t exist as we have no autologin.
Hi Yvon,
you need to configure the wallet for the time of the upgrade as “autologin” – otherwise it is supposed to fail as AU does not ask you for a password, but it restarts the database up to 7 times. And it will fail every time as it doesn’t know your PW.
Once the upgrade is completed, you can configure it back to the previous behavior.
Cheers,
Mike
Hi Mike,
I’m try to use autoupgrade on my own VM. I want to upgrade a CDB database from 12.2 (RU Jan 2021) to 19.10. This is a TDE enabled database.
On the analyze stage I receive following error that is related to my PDB that use TDE at tablespace level.
——————————————
[Stage Name] PRECHECKS
[Status] FAILURE
[Start Time] 2021-07-02 16:39:07
[Duration] 0:00:35
[Log Directory] /home/oracle/dblogupgrade/RON122_1/101/prechecks
[Detail] /home/oracle/dblogupgrade/RON122_1/101/prechecks/ron122_preupgrade.log
Precheck failed for ORCLPDB, manual intervention needed for the below checks
[NO_KEYSTORE_FILES]
The TDE is correctly configured.
From 12.2 (as per note 2417041.1) the WRL_PARAMETER is only visible ad CDB level.
Can the error be related to this expected behaviour?
How can I procede further?
Thanks in advance.
Regards.
Giuseppe.
Hi Guiseppe,
are you using the most recent AutoUpgrade?
Is your keystore (wallet) set to Auto Logon?
Thanks,
Mike
Hi Mike.
I tried to use the latest version of autoupgrade.
I think that the issue was related to the WALLET_ROOT. I configured this parameter as per https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/autoupgrade-configuration-file-examples.html#GUID-941F8A45-C3E8-445A-A5C9-85799A285DEC.
Using this parameter, RDBMS expect to find the TDE wallet on WALLET_ROOT/tde. I configured WALLET_ROOT as per the wallet path specified on sqlnet.ora of 12.2. I hoped that autoupgrade make this conversion automatically. But It didn’t. So I created an ASM alias to address the issue and I was able to complete upgrade. I think that a little enhancement need on documentation…
Is it correct?
Regards.
Giuseppe.
Hi Giuseppe,
I will send this to the doc writer.
Cheers,
Mike
Mike, new features in town that might be useful to update the blogpost and/of here as a comment:
*) load_password autoupgrade option
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/autoupgrade-command-line-parameters.html#GUID-32074B0C-E19C-4D39-AC57-49F87564C4FF
This in combination of a directory hint in the “global” section: global.keystore
“load_password” will, afaik currently, add interactively all TDE info needed during the autoupgrade process
Hi Marco,
you are right – I need to rework all the old AutoUpgrade blog posts … oh … you are very right …
Thanks for the reminder,
Mike
Hi Mike,
We don’t have TDE enabled in our databaase, but the autoupgrade script analyze.sh still reports a failure for TDE_PASSWORDS_REQUIRED. How do we overcome this and proceed with the upgrade?
Hi Amit,
which version of AU are you using?
java -jar autoupgrade.jar -version
Cheers,
Mike
Hello Mike,
This is the version I am using.
build.version 22.5.221011
build.date 2022/10/11 14:23:59 -0400
build.hash e9428661
build.hash_date 2022/10/11 12:55:45 -0400
build.supported_target_versions 12.2,18,19,21
build.type production
Please use the newest version from MOS Note:2485457.1 – Download AutoUpgrade – this fixes the issue you are suffering from.
Cheers
Mike
Thanks Mike, Downloaded this version now, but still encountering the same issue.
build.version 23.1.230224
build.date 2023/02/24 14:53:24 -0500
build.hash a1e2990e
build.hash_date 2023/02/24 14:44:39 -0500
build.supported_target_versions 12.2,18,19,21
build.type production
build.label (HEAD, tag: v23.1, origin/stable_devel, stable_devel)
Then you please need to open an SR. Upload the logs:
java -jar autoupgrade.jar -config yourconfig.cfg -zip
and have Support check it.
Cheers
Mike
I encountered the same problem. The customer is not using TDE nor column level encryption but the analyze command still complains about TDE_PASSWORDS_REQUIRED. Any hint would be appreciated.
Hi Johannes,
do you use the most recent version of AU? If not, please download and retest quickly.
If the problem persists, please upload the zip file:
java -jar autoupgrade.jar -config yourconfig.cfg -zip
and mail me the SR number.
Cheers,
Mike
Thanks Mike. I opened the SR. Used earlier version 22.3 of autoupgrade(confirmed by SR analyst) and completed the upgrade. FYI and for the benefit of Johannes, subsequent versions 22.4, 22.5 and 23.x report the same error.
Hi Amit,
can you please share the SR number with me? I did talk to the development team but they will need the logs.
Cheers,
Mike