Database 12.2 New Features – Online TDE

Database 12.2 was recently released by Oracle, and with it came a ton of new features. One of the new features is the ability to alter tables and tablespaces while the resource is online. This means that changes are possible while the database is online and processing workloads!

This is a huge upgrade, and has one very good use case for database administrators, and this is encrypting databases! Yes, now you can encrypt the tablespace with TDE without taking the database offline!

To demonstrate this, let’s encrypt a database while running a swingbench workload!

Here we see a workload on the database, nothing special, just a single user doing some OLTP workload. The swingbench database is a pluggable database, called “swing” that resides in the container database named “cdb”. While you do not need to use the multitenant feature to do this, multitenant is rapidly becoming the default way to run databases, and non-multitenant databases are now depreciated in 12.1.0.2. Eventually all databases will need to become multitenant. Deprecation does not mean desupported. It means in this case that you of course can have still non-multitenant Oracle databases, but in the future release only PDB databases will be allowed.  You can also have a single-tenant deployment (a CDB with one PDB ) for free, no Multitenant license required for this architecture! Of course multitenant databases (a CDB with up to 4096  PDBs) require the  multitenant Option to be purchased.

Here, we can see the workload, doing a modest 25000 transactions per minute. 

Before the database can be encrypted, a few house keeping tasks need to be performed. 

First a wallet needs to be created that can store the encryption key . To setup TDE the location for the wallet needs to be set. The first step is to edit the sqlnet.ora and add in the location in the wallet. In the same, /home/oralce/wallet will be used, and the following lines are added tot he sqlnet.ora configuration file.

ENCRYPTION_WALLET_LOCATION=
  (SOURCE=
   (METHOD=FILE)
    (METHOD_DATA=
     (DIRECTORY=/home/oracle/wallet)))

Next, connect to the container database and create the keystore. In this example the password for the keystore is “passw0rd”

SQL> administer key management create keystore ‘/home/oracle/wallet’ identified by passw0rd;

keystore altered.

Next we need to open the keystore

SQL> administer key management set keystore open identified by passw0rd container=all;

keystore altered.

Next we will  create and activate a master key in the root container and one in each of the pluggable databases. Using the CONTAINER=ALL clause does it in a single step.

SQL> administer key management set key  identified by passw0rd with backup container=all;

keystore altered.

Next, switch to the Pluggable database

SQL> alter session set container = swing;

Session altered.

From the pluggable database, you can see the encryption key now;

SQL> SELECT con_id, key_id FROM v$encryption_keys;

    CON_ID KEY_ID
———- ——————————————————————————
3 ARPS0CDZ2k+Ev07CjDGJarEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

SQL>

Next, let’s look at the database files, all of the swingbench data is in the swing01.dbf file.

SQL> select name from v$datafile;

NAME
——————————————-
/u01/data/cdb/swing/system01.dbf
/u01/data/cdb/swing/sysaux01.dbf
/u01/data/cdb/swing/undotbs01.dbf
/u01/data/cdb/swing/users01.dbf
/u01/data/swing01.dbf
/u01/data/crypt.dbf

6 rows selected.

 

Also, all of the swingbench data resides in the SOE tablespace, so this will be the space that will be encrypted online.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
2  FROM USER_TABLESPACES;

TABLESPACE_NAME                STATUS    CONTENTS
—————————— ——— ———————
SYSTEM                         ONLINE    PERMANENT
SYSAUX                         ONLINE    PERMANENT
UNDOTBS1                       ONLINE    UNDO
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT
SOE                            ONLINE    PERMANENT
SECURE                         ONLINE    PERMANENT

7 rows selected.

Now is the easy part… encyypting the soe tablespace that uses the swing01 file!

SQL> alter tablespace soe  encryption online using ‘aes192’
2  encrypt file_name_convert= (‘swing01’, ‘swing01-crypt’);

Tablespace altered.

While the tablespace can be encrypted ONLINE, you will see a performance impact, in this case about a %50 impact to the transactions per second. You will want to plan this during non-peak times.

But, the big news is the database is still process requests,and as soon the process is complete, the performance will return to expected levels.

Best of all, no new disk space is needed in order to do this, and it can be done with no outage to the system!