LATEST TOPICS

Oracle 12c: Implemented TDE? Cloning a PDB? you need to perform few additional steps

With Oracle 12c multitenant architecture, we need to maintain TDE encryption keys in both root (CDB$ROOT) and pluggable database (PDB) level. If we clone a PDB (which is encrypted with TDE) to another PDB within the same or different container, we need to perform few additional steps before we can use the cloned PDB. In this post, I will walk through a demonstration to cover the different steps that we need to perform if we are cloning from a pluggable database which is encrypted with TDE.

In the following demonstration, I am cloning a PDB called CDB1_PDB_2 from an existing PDB called CDB1_PDB_1 within the same container. However, TDE is enabled for the container database. Let’s see what happens when we clone the PDB.

---//
---// cloning CDB1_PDB_2 from CDB1_PDB_1 //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SQL> CREATE PLUGGABLE DATABASE cdb1_pdb_2 FROM cdb1_pdb_1
  2  FILE_NAME_CONVERT=('/data/oracle/orpcdb1/cdb1_pdb_1/','/data/oracle/orpcdb1/cdb1_pdb_2/')
  3  ;

Pluggable database created.


---//
---// opening the cloned PDB //---
---//
SQL> ALTER PLUGGABLE DATABASE cdb1_pdb_2 OPEN;
     
Warning: PDB altered with errors.

As we can observe, the PDB clone operation completed successfully without any issues. However, when we tried to open the cloned database, it threw some errors. Let’s see what are those errors by viewing the PDB_PLUG_IN_VIOLATIONS dictionary view.

---//
---// errors reported while opening the cloned PDB //---
---//
SQL> select name,cause,message,status from PDB_PLUG_IN_VIOLATIONS where name='CDB1_PDB_2';

NAME            CAUSE                MESSAGE                                            STATUS
--------------- -------------------- -------------------------------------------------- -------------------------
CDB1_PDB_2      Wallet Key Needed    PDB needs to import keys from source.              PENDING

As we can see from the PDB violation report, when we tried to open the cloned PDB, it was looking for TDE encryption key (wallet key) which seems to be missing from the cloned PDB and which is why the cloned PDB was opened in RESTRICTED mode as found from the following query.

---//
---// 
---//
SQL> select con_id,name,open_mode,restricted from v$pdbs where name='CDB1_PDB_2';

    CON_ID NAME            OPEN_MODE  RES
---------- --------------- ---------- ---
         4 CDB1_PDB_2      READ WRITE YES

If we look into the wallet status for the cloned PDB, we could see it is missing the MASTER key information as found from the following query. Without the TDE master key, Oracle will not be able to read the TDE encrypted data from the cloned pluggable database.

---//
---// TDE master key is missing from cloned PDB //---
---//         
SQL> alter session set container=CDB1_PDB_2;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_2

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   OPEN_NO_MASTER_KEY        AUTOLOGIN            SINGLE    UNDEFINED          0

SQL> 

We need to import the TDE master encryption key from the source pluggable database CDB1_PDB_1 to the target (cloned) pluggable database CDB1_PDB_2 to be able use the cloned PDB. We can utilise the ADMINISTER KEY MANAGEMENT EXPORT and ADMINISTER KEY MANAGEMENT IMPORT commands to export and import the encryption keys from source to target as discussed below.

If we try to use the TDE encryption without fixing the PDB violations, we will end up with facing errors similar to the following

---//
---// error raised trying to use TDE //---
---//         
SQL> create table t
  2  (
  3  name varchar (20),
  4  id number(10) ENCRYPT,
  5  contact number(10)
  6  );
create table t
*
ERROR at line 1:
ORA-28361: master key not yet set

Let’s take an export of the TDE master encryption key from the source pluggable database CDB1_PDB_1 as shown below.

---//
---// export TDE master key from source pluggable database //---
---//       
SQL> alter session set container=CDB1_PDB_1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   OPEN                      AUTOLOGIN            SINGLE    NO                 0


---//
---// master key export is not allowed if wallet is configured for AUTOLOGIN //---
---//
SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "oracle" TO '/home/oracle/tde_pdb1_mk.exp' IDENTIFIED BY oracle;
ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "oracle" TO '/home/oracle/tde_pdb1_mk.exp' IDENTIFIED BY oracle
*
ERROR at line 1:
ORA-28417: password-based keystore is not open

SQL> !oerr ora 28417
28417, 0000, "password-based keystore is not open"
// *Cause:  Password-based keystore was not opened.
// *Action: Close the auto login keystore, if required, and open a 
//          password-based keystore.

We can’t export or import the encryption keys, if the wallet is set for AUTOLOGIN. In that case we need to disable AUTOLOGIN and reopen the wallet using the wallet (keystore) password before we can take an export of the encryption key. Let’s disable AUTOLOGIN by removing the cwallet.sso file from wallet location as show below.

---//
---// removing SSO file configured for AUTOLOGIN //---
---//
SQL> !ls -lrt /data/oracle/orpcdb1/wallet/*
-rw-r--r-- 1 oracle dba 2408 May 30 15:49 /data/oracle/orpcdb1/wallet/ewallet_2016053010193935.p12
-rw-r--r-- 1 oracle dba 6264 May 30 15:51 /data/oracle/orpcdb1/wallet/ewallet.p12
-rw-r--r-- 1 oracle dba 3848 May 30 15:51 /data/oracle/orpcdb1/wallet/ewallet_2016053010210582.p12
-rw-r--r-- 1 oracle dba 6309 May 30 16:42 /data/oracle/orpcdb1/wallet/cwallet.sso

SQL> !rm /data/oracle/orpcdb1/wallet/cwallet.sso

SQL> !ls -lrt /data/oracle/orpcdb1/wallet/cwallet.sso
ls: /data/oracle/orpcdb1/wallet/cwallet.sso: No such file or directory

Once we delete the SSO (Single Sign On) file from wallet location, we need to close and reopen the wallet with the help wallet (keystore) password. Make sure that the wallet is closed and reopened from the root (CDB$ROOT) container. If we attempt to close the wallet from PDB, it will still remain in the OPEN state.

---//
---// wallet will remain in OPEN state if closed from PDB //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;

keystore altered.


SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   OPEN                      AUTOLOGIN            SINGLE    NO                 0


---//
---// close the wallet from root (CDB$ROOT) container //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   CLOSED                    UNKNOWN              SINGLE    UNDEFINED          0

---//
---// reopen the wallet from root (CDB$ROOT) container using wallet password //---
---//
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   OPEN                      PASSWORD             SINGLE    NO                 0

We have now reopened the wallet with password login. Now, we should be able to tale an export of the TDE encryption key from the source pluggable database CDB1_PDB_1 as shown below.

---//
---// export TDE master key from source pluggable database //---
---//
SQL> alter session set container=CDB1_PDB_1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

---//
---// wallet is in closed state within source pluggable database //---
---//
SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   CLOSED                    UNKNOWN              SINGLE    UNDEFINED          0


---//
---// open the wallet in source pluggable database to export TDE master key //---
---//
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle;

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   OPEN                      PASSWORD             SINGLE    NO                 0

---//
---// TDE master key from source PDB is exported to file '/home/oracle/tde_pdb1_mk.exp' //---
---//
SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "oracle" TO '/home/oracle/tde_pdb1_mk.exp' IDENTIFIED BY oracle;

keystore altered.

SQL> !ls -lrt /home/oracle/tde_pdb1_mk.exp
-rw-r--r-- 1 oracle dba 2612 Jun  9 16:32 /home/oracle/tde_pdb1_mk.exp

Once we take the export of the encryption key from source pluggable database, we can use the export file (/home/oracle/tde_pdb1_mk.exp) to import the master encryption key into the cloned pluggable database as shown below.

---//
---// import TDE master key into cloned database //---
---//
SQL> alter session set container=CDB1_PDB_2;

Session altered.


SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_2

---//
---// wallet should be in open state to be able to import the TDE master key //---
---//
SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "oracle" FROM '/home/oracle/tde_pdb1_mk.exp' IDENTIFIED BY oracle WITH BACKUP;
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "oracle" FROM '/home/oracle/tde_pdb1_mk.exp' IDENTIFIED BY oracle WITH BACKUP
*
ERROR at line 1:
ORA-46658: keystore not open in the container


SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   CLOSED                    UNKNOWN              SINGLE    UNDEFINED          0

---//
---// open the wallet within cloned PDB using wallet password //---
---//
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY oracle;

keystore altered.


SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   OPEN_NO_MASTER_KEY        PASSWORD             SINGLE    UNDEFINED          0

---//
---// import the TDE master key from export file '/home/oracle/tde_pdb1_mk' //---
---//
SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "oracle" FROM '/home/oracle/tde_pdb1_mk.exp' IDENTIFIED BY oracle WITH BACKUP;

keystore altered.


SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                  STATUS                    WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
-------------------- ------------------------------ ------------------------- -------------------- --------- --------- ----------
FILE                 /data/oracle/orpcdb1/wallet/   OPEN                      PASSWORD             SINGLE    NO                 0

The keystore (wallet) is now completely opened in the cloned pluggable database CDB1_PDB_2. This means the master encryption key is loaded into the cloned pluggable database. However, if we look into encryption_keys for the cloned pluggable database, we could see there is no encryption key present for the cloned pluggable database. This indicates that the imported key is not yet activated in the cloned pluggable database.

We can either activate the imported (from source PDB) TDE master key in cloned pluggable database using the ADMINISTER KEY MANAGEMENT USE KEY command or we can set a new master key by using the ADMINISTER KEY MANAGEMENT SET KEY command. If we activate the imported key from source pluggable database, both source and cloned pluggable database will share the same TDE master encryption key as shown below.

---//
---// activate the imported TDE master key //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_2

SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;

no rows selected

---//
---// find the key id of source pluggable database //--
---//
SQL> alter session set container=CDB1_PDB_1;

Session altered.

SQL> select KEY_ID from v$encryption_keys;

KEY_ID
-------------------------------------------------------
AVLL+jm2Lk/4v9gHR7Uo6GQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

---//
---// activate the source TDE key (imported earlier) in cloned PDB //---
---//
SQL> alter session set container=CDB1_PDB_2;

Session altered.

SQL> ADMINISTER KEY MANAGEMENT USE KEY 'AVLL+jm2Lk/4v9gHR7Uo6GQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
  2  IDENTIFIED BY oracle WITH BACKUP
  3  ;

keystore altered.


---//
---// verify TDE master key is activated in cloned PDB //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_2


SQL> select KEY_ID,ACTIVATION_TIME from v$encryption_keys ;

KEY_ID                                                  ACTIVATION_TIME
------------------------------------------------------- -----------------------------------
AVLL+jm2Lk/4v9gHR7Uo6GQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    09-JUN-16 09.15.37.824558 PM +05:30

---//
---// same TDE master key is shared by source and cloned pluggable databases //----
---//
SQL> alter session set container=CDB1_PDB_1;

Session altered.

SQL>  select KEY_ID,ACTIVATION_TIME from v$encryption_keys ;

KEY_ID                                                  ACTIVATION_TIME
------------------------------------------------------- -----------------------------------
AVLL+jm2Lk/4v9gHR7Uo6GQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA    09-JUN-16 09.15.37.824558 PM +05:30

The cloned pluggable database CDB1_PDB_2 is now fully configured to support TDE. We can restart the cloned pluggable database and validate if the reported violations are now resolved as shown below.

---//
---// restart cloned PDB and validate the errors are fixed //---
---//
SQL> alter pluggable database cdb1_pdb_2 close;

Pluggable database altered.

SQL> alter pluggable database cdb1_pdb_2 open;

Pluggable database altered.

SQL> select name,cause,message,status from PDB_PLUG_IN_VIOLATIONS where name='CDB1_PDB_2';

NAME            CAUSE                MESSAGE                                            STATUS
--------------- -------------------- -------------------------------------------------- -------------------------
CDB1_PDB_2      Wallet Key Needed    PDB needs to import keys from source.              RESOLVED

SQL> 

As expected the violations are now resolved for the cloned pluggable database CDB1_PDB_2. If required, we can re-enable the AUTOLOGIN for TDE wallet using the following command.

---//
---// enable AUTOLOGIN for wallet //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE
  2  FROM KEYSTORE '/data/oracle/orpcdb1/wallet/'
  3  IDENTIFIED BY oracle;

keystore altered.

Footnote:

Cloning a pluggable database is a simple task. However, it can become complicated if TDE comes into picture. When you are cloning a pluggable database, make sure to check and address any violations reported by PDB_PLUG_IN_VIOLATIONS while opening the cloned pluggable database. In case of TDE related errors, if the target container database doesn’t have TDE configured, we need to configure TDE for the target container before cloning a TDE enabled pluggable database to that container.

Clef two-factor authentication
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed