AutoUpgrade with Transparent Data Encryption (TDE)

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:

• Oracle Advanced Security Transparent Data Encryption Best Practices

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:

• Database Upgrade and TDE – Things to know – Jan 16, 2018

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