Transparent Data Encryption is an excellent and very useful Oracle database feature. In this blog post I will show and highlight Database Upgrade and TDE – Things to Know.
Enabling Tablespace Encryption with TDE
For a simple example I use our Hands-On Lab and the UPGR database (Oracle 220.127.116.11) and the listener declarations in the 12.2 $ORACLE_HOME.
At first I add the following string to the sqlnet.ora file:
# sqlnet.ora # ENCRYPTION_WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/UPGR/wallet)) )
Note 2018, Feb 4:
My previous example had
$ORACLE_BASE instead of
/u01/app/oracle, the real path, in it. With
$ORACLE_BASE the command line upgrade works fine but the DBUA upgrade fails. As Hector and Cindy of my team helped with different test scenarios we learned that this must be a change in the most recent 18.104.22.168 Updates. With a vanilla 22.214.171.124 installation everything works fine but as soon as you use (as I did – and Hector verified it) the January 2018 Update (RU) you need to replace
$ORACLE_BASE with the real path. The same applies to the
orapki call below.
Afterwards I create the directory
$ORACLE_BASE/admin/UPGR/wallet. This is the default location. The database will create the encrypted file
ewallet.p12 in it containing the Master Key once I execute:
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "secret01";
Please note that the Master Key is not identical with “secret01“. Changing the Master Key can be done with the same command but will ask for the password of course. The password is case sensitive.
Furthermore from now on it’s essential to recognize that access to encrypted data requires the wallet to be opened, usually upon startup of the database:
ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "secret01";
Now I will encrypt my USERS tablespaces of the UPGR database. But as some features such as online tablespace encryption are missing in Oracle Database 11g I will workaround it and encrypt the entire database after upgrade.
alter database default tablespace SYSAUX; drop tablespace USERS; -- it's empty in my case - otherwise relocate objects first CREATE TABLESPACE "USERS" DATAFILE '/u02/oradata/UPGR/users_encrypt01.dbf' SIZE 20M AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT STORAGE ( ENCRYPT ) ENCRYPTION USING 'AES256'; alter database default tablespace USERS;
Finally I will check the tablespaces:
select TABLESPACE_NAME , ENCRYPTED from DBA_TABLESPACES order by 1; TABLESPACE_NAME ENC ------------------------------ --- SYSAUX NO SYSTEM NO TEMP NO UNDOTBS1 NO USERS YES
Furthermore, to make my simple text example with the converted USERS tablespace work (as I adjusted all users to have their default tablespace now on USERS) it will be very helpful to grant those users
QUOTA as well:
grant unlimited tablespace to SYS; grant unlimited tablespace to SYSTEM; grant unlimited tablespace to OUTLN; grant unlimited tablespace to LBACSYS; grant unlimited tablespace to APPQOSSYS; grant unlimited tablespace to ANONYMOUS; grant unlimited tablespace to DIP; grant unlimited tablespace to DBSNMP; grant unlimited tablespace to WMSYS; grant unlimited tablespace to XDB; grant unlimited tablespace to ORACLE_OCM;
On a test or development system I may turn the wallet into an auto-open wallet. For a production system I wouldn’t recommend this. On the command line execute:
orapki wallet create -wallet /u01/app/oracle/admin/UPGR/wallet -auto_login
It will of course ask for the password, in my case
secret01. This will create another encrypted file in the location where the wallet is stored:
In addition check the wallet settings with:
orapki wallet display -wallet /u01/app/oracle/admin/UPGR/wallet Oracle PKI Tool : Version 126.96.36.199.0 - Production Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved. Requested Certificates: Subject: CN=oracle User Certificates: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.AT91AjV9CU81... ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.TS.ENCRYPTION.BRR7egpfmPjx... Trusted Certificates:
Oracle Wallet Manager
You can execute such tasks of course also with the Oracle Wallet Manager (owm), a GUI tool: Start it with
You can change the auto login simply with a checkbox – just don’t forget to SAVE afterwards.
Things to Know with Upgrades and TDE?
Is there anything special to mention with database upgrades and TDE? Yes, there is.
As mentioned above you need to use the real path in your sqlnet.ora and your calls to orapki instead of the environment variable $ORACLE_BASE.
Furthermore it is VERY important if you use a release/installation dependent path that you copy your sqlnet.ora and your wallet directory manually as the DBUA won’t do this for you. In case you used the $ORACLE_BASE the DBUA may fail with multiple
ORA-28365: wallet is not open errors:
The DBUA logs aren’t very helpful then:
[Thread-140] [ 2018-01-15 20:55:10.318 CET ] [StepErrorHandler.collectNonIgnorableError:517] Collecting non-ignorable error :[[catupgrd0.log] , ORA-28365: wallet is not open]
But at least from the
catupgrd0.log you may see that the issue happens during the XDB upgrade:
20:55:01 SQL> Rem ================================================================ 20:55:01 SQL> Rem BEGIN XDB RDBMS Object Upgrade from 11.2.0 20:55:01 SQL> Rem ================================================================ ... Elapsed: 00:00:00.08 20:55:04 SQL> 20:55:04 SQL> create table xdb.xdb$cdbports ( 20:55:04 2 pdb number, 20:55:04 3 service number, 20:55:04 4 port number); create table xdb.xdb$cdbports ( * ERROR at line 1: ORA-28365: wallet is not open
The command line upgrade with
catctl.pl works flawless and fine.
Online TDE Conversion in Oracle Database 12.2
In Oracle Database 12.2 we included this cool feature called “Online TDE Conversion” which I wanted to try out as well to encrypt now my entire database without the need for any downtime. Franck Pachot has done some interesting stats on performance impact. It’s not a big overhead at all.
ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT=('/u02/oradata/UPGR/system01.dbf','/u02/oradata/UPGR/system01_encrypted.dbf'); ALTER TABLESPACE SYSAUX ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT=('/u02/oradata/UPGR/sysaux01.dbf','/u02/oradata/UPGR/sysaux01_encrypted.dbf'); ALTER TABLESPACE UNDOTBS1 ENCRYPTION ONLINE ENCRYPT FILE_NAME_CONVERT=('/u02/oradata/UPGR/undotbs01.dbf','/u02/oradata/UPGR/undotbs01_encrypted.dbf');
If I’d like to encrypt the
TEMP tablespace as well I’ll have to recreate it as temporary tablespaces can’t be encrypted online.
Please find this very helpful post by Oracle ACE Director Arup Nanda for further information.
In addition MOS Note 1251597.1: “Quick TDE Setup and FAQ” is very helpful to start with as well.