1. Ensure that the keystore is open before you attempt to export tables containing encrypted columns.

   In a multitenant environment, if you are exporting data in a pluggable database (PDB), then ensure that the wallet is open in the PDB. If you are exporting into the root, then ensure that the wallet is open in the root.

   To find if the keystore is open, query the STATUS column of the V$ENCRYPTION_WALLET view. If you must open the keystore, then run the following SQL statement:

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN 
   IDENTIFIED BY software_keystore_password 
   [CONTAINER = ALL | CURRENT];
   

   The software_keystore_password setting is the password for the keystore. The keystore must be open because the encrypted columns must be decrypted using the TDE table keys, which requires access to the TDE master encryption key. The columns are reencrypted using a password, before they are exported.

2. Run the EXPDP command, using the ENCRYPTION_PASSWORD parameter to specify a password that is used to encrypt column data in the export dump file set.

   The following example exports the employee_data table. The ENCRYPTION_PWD_PROMPT = YES setting enables you to prompt for the password interactively, which is a recommended security practice.

   expdp hr TABLES=employee_data DIRECTORY=dpump_dir
   DUMPFILE=dpcd2be1.dmp ENCRYPTION=ENCRYPTED_COLUMNS_ONLY
   ENCRYPTION_PWD_PROMPT = YES
   
   Password: password_for_hr
   

3. To import the exported data into the target database, ensure that you specify the same password that you used for the export operation, as set by the ENCRYPTION_PASSWORD parameter.

   The password is used to decrypt the data. Data is reencrypted with the new TDE table keys generated in the target database. The target database must have the keystore open to access the TDE master encryption key. The following example imports the employee_data table:

   impdp hr TABLES=employee_data DIRECTORY=dpump_dir 
   DUMPFILE=dpcd2be1.dmp 
   ENCRYPTION_PWD_PROMPT = YES
   
   Password: password_for_hr

While importing, you can use either the password or the keystore TDE master encryption key to decrypt the data. If the password is not supplied, then the TDE master encryption key in the keystore is used to decrypt the data. The keystore must be present and open at the target database. The open keystore is also required to reencrypt column encryption data at the target database.

You can use the ENCRYPTION_MODE=TRANSPARENT setting to transparently encrypt the dump file set with the TDE master encryption key stored in the keystore. A password is not required in this case. The keystore must be present and open at the target database, and it must contain the TDE master encryption key from the source database for a successful decryption of column encryption metadata during an import operation.

The open keystore is also required to reencrypt column encryption metadata at the target database. If a keystore already exists on the target database, then you can export the current TDE master encryption key from the keystore of the source database and import it into the keystore of the target database.

• Use the ENCRYPTION_MODE parameter to specify the encryption mode. ENCRYPTION_MODE=DUAL encrypts the dump set using the TDE master encryption key stored in the keystore and the password provided.

For example, to use dual encryption mode to export encrypted data:

expdp hr DIRECTORY=dpump_dir1 
DUMPFILE=hr_enc.dmp
ENCRYPTION=all 
ENCRYPTION_PASSWORD=encryption_password
ENCRYPTION_PWD_PROMPT=yes
ENCRYPTION_ALGORITHM=AES256 
ENCRYPTION_MODE=dual

Password: password_for_hr
Encryption Password: password_for_encryption

1. Log in to the instance of the database as a user who has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

   In a multitenant environment, log in to the root or the appropriate PDB. For example:

   sqlplus sec_admin@hrpdb as syskm
   Enter password: password
   Connected.
   

2. Reset the TDE master encryption key on the first Oracle Real Application Clusters (Oracle RAC) node.

   For example, for column encryption:

   ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP USING 'emp_key_backup';
   

3. Copy the keystore file with the new TDE master encryption key from the first node to all of the other nodes.

   To find the keystore file location, query the WRL_PARAMETER column in the V$ENCRYPTION_WALLET view. To find the WRL_PARAMETER settings for all of the database instances, query the GV$ENCRYPTION_WALLET view.

4. Close and then reopen the keystore on any node.

   If you are using a multitenant container database (CDB), then run the following statement in the root:

   ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE IDENTIFIED BY software_keystore_password [CONTAINER = ALL | CURRENT];
   

All of the Oracle RAC nodes are now configured to use the new TDE master encryption key.