Database Upgrade and TDE – Things to Know

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 11.2.0.4) 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 12.2.0.1 Updates. With a vanilla 12.2.0.1 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: cwallet.sso.

In addition check the wallet settings with:

orapki wallet display -wallet /u01/app/oracle/admin/UPGR/wallet

Oracle PKI Tool : Version 11.2.0.4.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 owm:

Oracle Wallet Manager, Oracle Database 11.2.0.4

You can change the auto login simply with a checkbox – just don’t forget to SAVE afterwards.

Oracle Wallet Manager – Changing the AUTO LOGIN – don’t forget to SAVE

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.

Further Information

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.

Documentation:

• Oracle 11.2: Securing Stored Data Using TDE
• Oracle 12.2: Configuring TDE

–Mike