Thursday, June 14, 2018

Remote Cloning a PDB with Encrypted Data

This post list the steps for remote cloning a PDB that has encrypted data. What's difference in this case, to that of a remote cloning a PDB without the use of 12c TDE is the use of the encryption key. The master key of the source PDB must be available to cloned PDB. There are multiple ways of achieving this. This post shows two convenient ways to use when remote cloning PDBs with encrypted data.

Method 1. Using one_step_plugin_for_pdb_with_tde parameter
According to advance security guide "when ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE is set to TRUE, the database caches the keystore password in memory, obfuscated at the system level, and then uses it for the import operation. The default for ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE is FALSE".
So in order to clone a PDB with encrypted data simply set the ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE to true and run the cloning operation.

1. The remote PDB has a encrypted tablespace
SQL> select tablespace_name,encrypted from dba_tablespaces where encrypted='YES' ;

TABLESPACE_NAME                ENC
------------------------------ ---
ENCTEST                        YES

SQL> select t.name,ENCRYPTIONALG,STATUS FROM V$ENCRYPTED_TABLESPACES  e, v$tablespace t where e.ts#=t.ts#;

NAME       ENCRYPT STATUS
---------- ------- ----------
ENCTEST    AES128  NORMAL
2. Create a key store (encryption wallet) at the CDB root where the clone will be created. Without this the cloning will fail. Creating wallet is shown in a previous post.

3. Set the ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE to true
ALTER SYSTEM SET one_step_plugin_for_pdb_with_tde=TRUE SCOPE=BOTH;
4. Run the remote clone operation. Steps for remote cloning is available in a previous post.
create pluggable database mypdb from cxpdb@PDB1K_LINK 
file_name_convert=('/opt/oracle/oradata/cxcdb/cxpdb/','/opt/oracle/oradata/oracdb/mypdb/') ;

Pluggable database created.
5. Finally open the cloned PDB.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORAPDB                         READ WRITE NO
         4 MYPDB                          MOUNTED


SQL> alter pluggable database mypdb open;

Pluggable database altered.


SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORAPDB                         READ WRITE NO
         4 MYPDB                          READ WRITE NO
6. If no longer used then set the ONE_STEP_PLUGIN_FOR_PDB_WITH_TDE to default value of false.
ALTER SYSTEM SET one_step_plugin_for_pdb_with_tde=FALSE SCOPE=BOTH;


Method 2. Using Key Store Password of the Local CDB
In this method the key store password of the local CDB (CDB where the clone PDB is created) is used during the clone command. As per security guide the encrypted data is still accessible because during the cloning the master key of the remote PDB is copied over. However it's best to re-key after the cloning as the original key information is not shown in the PDB's v$ views.

1. The same remote PDB is used for this example as well. It's also assumed the local CDB has wallet already created.

2. Execute the remote cloning command on the CDB root specifying the key store password.
create pluggable database mypdb from cxpdb@PDB1K_LINK 
file_name_convert=('/opt/oracle/oradata/cxcdb/cxpdb/','/opt/oracle/oradata/oracdb/mypdb/') 
KEYSTORE IDENTIFIED BY  asanga123;

Pluggable database created.
3. Open the PDB and check the encryption key on the clone PDB's v$view. As mentioned in the security guide this return no rows.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORAPDB                         READ WRITE NO
         5 MYPDB                          READ WRITE NO

SQL> alter session set container=mypdb;

Session altered.

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

no rows selected
4. Run the below to re-key. The force option is used due to bug 22826718. Refer 1944507.1 for more.
ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY asanga123 with backup;
 
 SQL> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys order by 1;

    CON_ID KEY_ID                                                  KEYSTORE_TYPE     CREATOR_DB CREATOR_PD
---------- ------------------------------------------------------- ----------------- ---------- ----------
         5 AXj5300QAE8Kv7cOn6U0xJ8AAAAAAAAAAAAAAAAAAAAAAAAAAAAA    SOFTWARE KEYSTORE oracdb     MYPDB