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.
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 22.214.171.124 database
Yes, 126.96.36.199 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 188.8.131.52.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 184.108.40.206.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 220.127.116.11 example, the permissions are set correctly when I create the keystore.
Introducing TDE to my 18.104.22.168 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 22.214.171.124 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 126.96.36.199.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 188.8.131.52.0 - 64bit Production SQL> administer key management create keystore '/etc/ORACLE/WALLETS/DB12' identified by "oracle"; System altered.
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 184.108.40.206.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Enter wallet password:
And then for the 220.127.116.11 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.
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 18.104.22.168 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/22.214.171.124 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/126.96.36.199 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  Jobs failed  Jobs pending  ------------- 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 188.8.131.52 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:
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