Written By

Introduction

This article explains If you have a Non-Pluggable database/Traditional database of 12c and Transparent Data Encryption is configured to the database non-PDB migration to Pluggable Database(Multitenant) and with an example how TDE works. This document is only applicable for the versions from 12c.
 

TDE and Advantages of TDE

Transparent Data Encryption is an Feature of Oracle Advanced Security, It helps in encrypting critical, sensitive data like pass codes which placed in tables and tablespaces, it doesn't mean the data stores in the wallet..in fact it's encrypts the data.  Transparent data encryption manages the encrypted keys and the master encryption key which plays role in encryption or decryption of keys. The master encryption (which stored in Oracle wallet) is created by the database and which can be managed directly by the user.
 
TDE provides strong encryption for the database and integrated support for all Oracle database technologies. TDE supports with Backups(Secure backups, RMAN backups and Logical Backups ) and also protects database files on disk. There is no surprise that TDE supports in HA solutions such as RAC/ASM, GG, Data Guard and Exadata Engineered Systems as well.  More importantly TDE is compatible with all the applications such as EBS, JD Edwards, Siebel, SAP, PeopleSoft and many and no changes required in order to let TDE function. For the encrypted data automatically decrypts for the authorized users so that there will be no impact to the application and it does not even impact SQL query results.
 
To manage transparent data encryption keystore operations Oracle introduced SYSKM user where it helps in creating Keystore, Opening and closing , Changes in Master Kys, Managing Column and tablespace keys and full access to check TDE information through various views such as v$encryption_wallet, v$encrypted_tablespaces. For encryption and decryption TDE stores the encryption keys in security model external to the database i.e. Key store.

TDE with PDB's

The above brief of TDE applicable to generic database , when we discuss with multitenant architecture where we can manage one or more databases(pdb) which is introduced from 12c, So one master key cannot survive all of the PDB's, Every PDB should have its own master key to use encrypt data in the PDB.  This article represents migration of TDE from non-PDB(generic database) to PDB, in such scenario the master key must be transported from the source database wallet to the new PDB after it moved to different host.  When we come to column encryption each PDB maintains its own ENC$(the key for encrypted columns  - dba_encrypted_columns).
 
Creation of keystore operation not allowed from within a pluggable database

Migration of TDE

I will ensure this article must helpful for end to end migration, hence adding few more steps "How to configure TDE" on non-container database level
 

Configuration of TDE

Before creating and managing keys add the entries of wallet location and the method of source in sqlnet.ora file as follows.
 
-bash-3.2$ cat sqlnet.ora
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u02/app/oracle/oradata/mcdb/wallet)))
-bash-3.2$
 
Administer key management is an key management interface for TDE, It helps in managing software, hardware keystores, managing encryption keys and secrets.
 
SQL> select * from v$pdbs;
 
no rows selected
 
SQL> administer key management create keystore '/u02/app/oracle/oradata/mcdb/wallet' identified by "oracle123";
 
keystore altered.
 
SQL>
 
SQL> select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;
 
WRL_TYPE                 CON_ID WRL_PARAMETER                                      STATUS
-------------------- ---------- -------------------------------------------------- ------------------------------
FILE                          0 /u02/app/oracle/oradata/mcdb/wallet                CLOSED
 
After creation of keystore by default the wallet will be in closed, So we need to manually open the keystore by using below command.
 
SQL> administer key management set keystore open identified by "oracle123";
 
keystore altered.
 
SQL> select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;
 
WRL_TYPE                 CON_ID WRL_PARAMETER                                      STATUS
-------------------- ---------- -------------------------------------------------- ------------------------------
FILE                          0 /u02/app/oracle/oradata/mcdb/wallet                OPEN_NO_MASTER_KEY
 
SQL>
 
The master encryption will not be active after we open the wallet, we need to activate key by setting the key. by mentioning backup clause it will ensure to create backup keystore before creating any new master keys.
 
SQL> administer key management set key identified by "oracle123" with backup;
 
keystore altered.
 
SQL>
 
SQL> select wrl_type,con_id,wrl_parameter,status from v$encryption_wallet;
 
WRL_TYPE                 CON_ID WRL_PARAMETER                                      STATUS
-------------------- ---------- -------------------------------------------------- ------------------------------
FILE                          0 /u02/app/oracle/oradata/mcdb/wallet                OPEN
 
SQL>
 
Our aim is to Migrate the Transparent Data Encryption of non-container database as Pluggable database, so we need to create the manifest file in order to clone into existing container database(In my case the container database is in Remote server, Hence need to copy the created XML file and the database files as well)
 
SQL> select name from v$datafile;
 
NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/TEDDB/datafile/o1_mf_system_blw4d3l0_.dbf
/u02/app/oracle/oradata/TEDDB/datafile/o1_mf_sysaux_blw49flb_.dbf
/u02/app/oracle/oradata/TEDDB/datafile/o1_mf_undotbs1_blw4h4ql_.dbf
/u02/app/oracle/oradata/TEDDB/datafile/o1_mf_users_blw4h3sp_.dbf
 
In order to create manifest file ensure the database is in Read-Only mode
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount exclusive
ORACLE instance started.
 
Total System Global Area  613797888 bytes
Fixed Size                  2291328 bytes
Variable Size             503318912 bytes
Database Buffers          104857600 bytes
Redo Buffers                3330048 bytes
Database mounted.
SQL> alter database open read only;
 
Database altered.
 
SQL>
 
Use the DBMS_PDB package which is available from 12.1 to create manifest file, This package is also used term as unplug and plug.
SQL> exec dbms_pdb.describe(pdb_descr_file => '/home/oracle/noncdb_to_cdb.xml');
 
PL/SQL procedure successfully completed.
 
SQL> !ls -ltr /home/oracle/noncdb_to_cdb.xml
-rw-r--r-- 1 oracle oinstall 4010 Apr 15 13:28 /home/oracle/noncdb_to_cdb.xml
 
SQL>
 
After the above steps, Copy all the database files and also the above XML file.  The below information of PDB's is from container database. If it's an non-container database the output results to "No Rows"
 
SQL> select name from v$pdbs;
 
NAME
------------------------------
PDB$SEED
EBSPDB
 
SQL>
 
The Create pluggable database command has many options with many clauses, It may vary from environment to environment. In my example the source database is using OMF hence the FILE_NAME_CONVERT will not work if in case the file locations are different, Hence you must configure OMF "DB_CREATE_FILE_DEST" to the location where you are preferring to Plug the database.
 
SQL> create pluggable database tdepdb as clone using '/u02/app/oracle/oradata/tdepdb/noncdb_to_cdb.xml' copy;
 
Pluggable database created.
 
SQL> select name from v$Pdbs;
 
NAME
------------------------------
PDB$SEED
EBSPDB
TDEPDB
 
SQL>
 
We able to Plug the database successfully and now the remaining part is to deal with the TDE(how to migrate)  , So connect to the source database to export keys and ensure the database is in READ-WRITE mode.  (Off the topic: Note that Creation of keystore operation not allowed from within a pluggable database)
 
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ ONLY
 
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System Global Area  613797888 bytes
Fixed Size                  2291328 bytes
Variable Size             503318912 bytes
Database Buffers          104857600 bytes
Redo Buffers                3330048 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
 
OPEN_MODE
--------------------
READ WRITE
 
SQL>
 
To export the encryption keys the wallet should be open in source database and issue the command to export.
 
SQL> administer key management set keystore open identified by "oracle123";
 
keystore altered.
 
SQL> select wrl_type,wrl_parameter,status from v$encryption_wallet;
 
WRL_TYPE             WRL_PARAMETER                            STATUS
-------------------- ---------------------------------------- ---------
FILE                 /u02/app/oracle/oradata/mcdb/wallet      OPEN
 
SQL> administer key management export encryption keys with secret "oracle123" to '/home/oracle/noncdb_to_cdb.dat' identified by "oracle123";
 
keystore altered.
 
SQL>
 
-bash-3.2$ scp /home/oracle/noncdb_to_cdb.dat 192.168.0.130:/home/oracle/noncdb_to_cdb.dat
noncdb_to_cdb.dat                                                                                    100% 2548     2.5KB/s   00:00
-bash-3.2$
 
After the exporting keys, copy the exported file to the Container database server to import, but ensure at the root level the wallet is opened. Connect to the pluggable database in Read-Write Mode, Open the wallet at PDB level.
 
SQL> select name from v$pdbs;
 
NAME
------------------------------
TDEPDB
 
SQL> select * from v$encryption_wallet;
 
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS                         WALLET_TYPE          WALLET_OR FULLY_BAC
------------------------------ -------------------- --------- ---------
    CON_ID
----------
FILE
/u02/app/oracle/oradata/container/wallet
CLOSED                         UNKNOWN              SINGLE    UNDEFINED
         0
 
 
SQL> administer key management set keystore open identified by "oracle123";
 
keystore altered.
 
SQL> administer key management import keys with secret "oracle123" from '/home/oracle/noncdb_to_cdb.dat' identified by "oracle123" with backup;
 
keystore altered.
 
SQL>
SQL> select wrl_type,wrl_parameter,status,wallet_type from v$encryption_wallet;
 
WRL_TYPE             WRL_PARAMETER                            STATUS                         WALLET_TYPE
-------------------- ---------------------------------------- ------------------------------ --------------------
FILE                 /u02/app/oracle/oradata/container/wallet OPEN                           PASSWORD
 
SQL> select key_id from v$encryption_keys;
 
no rows selected
 
SQL>
                
There is no surprise that why the encryption key is returning No rows, We need to explicitly need to activate the key.  For the key information we can connect to source non-container database and  use the same key to activate as below.
 
Encryption Key output from non-Container database
 
SQL> select key_id from v$encryption_keys;
 
KEY_ID
------------------------------------------------------------------------------
AbilrBMrBE9tvx8kAioBGQsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
SQL>
 
Connect to the PDB level and issue below commands.
 
SQL> administer key management use key 'AbilrBMrBE9tvx8kAioBGQsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' identified by "oracle123" with backup using 'Activating_to_PDB';
 
keystore altered.
 
SQL> select key_id from v$encryption_keys;
 
KEY_ID
------------------------------------------------------------------------------
AbilrBMrBE9tvx8kAioBGQsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
 
SQL>
 
SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where type = 'ERROR';
 
no rows selected
 
SQL>
 
After activating the key, Now we are done with the Migration of TDE from non-container to the PDB, Finally we will do small test of wallet.
 
SQL> select name from v$pdbs;
 
NAME
--------
TDEPDB
 
SQL> select con_id,name,open_mode from v$pdbs;
 
    CON_ID NAME     OPEN_MODE
---------- -------- ----------
         4 TDEPDB   READ WRITE
 
 
SQL> select count(*) from tdeuser.tdetab1;
select count(*) from tdeuser.tdetab1
                             *
ERROR at line 1:
ORA-28365: wallet is not open
SQL> administer key management set keystore open identified by "oracle123";
 
keystore altered.
 
SQL>  select count(*) from tdeuser.tdetab1;
 
  COUNT(*)
----------
     90737
 
SQL>
 

Conclusion

Migration of non-container database to pluggable database is normal for whom they are looking to enhance the feature of multitenant but this article shows how to Migrating Transparent data encryption of non-Container database to PDB using keys export and import method in step by step manner. (Also covered how to clone of PDB/Plugging)
 

References: