Update 2018/07/24 : Method 1 shown below doesn't work on Oracle cloud where TDE is enabled by default. This is due to bug 24763954 which is closed as not a bug. If remote cloning is done on Oracle cloud then use the method two mention in the post. For more refer MOS notes 2228673.1, 2208792.1, 2415131.1.
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 NORMAL2. 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 NO6. 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 selected4. 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
