Posted on March 6, 2017 by Erik Benner
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 a tables and tablespaces while the table 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 188.8.131.52. 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.
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;
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;
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.
7 rows selected.
Now is the easy part… encrypting the soe tablespace that uses the swing01 file!
SQL> alter tablespace soe encryption online using ‘aes192’
2 encrypt file_name_convert= (‘swing01’, ‘swing01-crypt’);
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 processing 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!
Erik Benner, Enterprise Architect, Mythics Inc.