You can use Oracle Data Pump to export and import tables that have encrypted columns.

For both software and hardware keystores, the following points are important when you must export tables containing encrypted columns:

• Sensitive data should remain unintelligible during transport.

• Authorized users should be able to decrypt the data after it is imported at the destination.

When you use Oracle Data Pump to export and import tables containing encrypted columns, it uses the ENCRYPTION parameter to enable encryption of data in dump file sets. The ENCRYPTION parameter allows the following values:

• ENCRYPTED_COLUMNS_ONLY: Writes encrypted columns to the dump file set in encrypted format

• DATA_ONLY: Writes all of the data to the dump file set in encrypted format

• METADATA_ONLY: Writes all of the metadata to the dump file set in encrypted format

• ALL: Writes all of the data and metadata to the dump file set in encrypted format

• NONE: Does not use encryption for dump file sets

You can export and import tables with encrypted columns using the ENCRYPTION=ENCRYPTED_COLUMNS_ONLY setting.

Oracle Data Pump can encrypt entire dump sets, not just Transparent Data Encryption columns.

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

Password: password_for_hr

Oracle Database enables Oracle RAC nodes to share a software keystore.

A configuration with a hardware security module uses a network connection from each instance of the database to the shared hardware security module. This eliminates the need to manually copy the software keystore to each of the other nodes in the cluster. Oracle recommends that you create the software keystore on a shared file system. This enables all of the instances to access the same shared software keystore. If you configure Oracle RAC to use Automatic Storage Management (ASM), then store the keystore on the ASM disk group.

For a hardware security module based configuration, configure the database instance on each Oracle RAC node to connect to the shared hardware security module. Thus, all instances of the database have access to the hardware security module.

Keystore operations that must be performed on all of the instances (such as opening or closing the keystore, or rekeying the TDE master encryption key) can be issued on any one Oracle RAC instance. Internally, the Oracle database takes care of synchronizing the keystore context on each Oracle RAC node, so that the effect of the keystore operation is visible to all of the other Oracle RAC instances in the cluster. This means that when you open the keystore on one instance, then it also opens for each of the other Oracle RAC instances. Similarly, when a TDE master encryption key rekey operation takes place, the new key becomes available to each of the Oracle RAC instances. This means that when you open the keystore on one database instance, it opens for each of the other Oracle RAC instances. You can perform other keystore operations, such as exporting TDE master encryption keys, rotating the keystore password, merging keystores, or backing up keystores, from a single instance only.

When using a shared file system, ensure that the ENCRYPTION_WALLET_LOCATION or WALLET_LOCATION parameter setting in the sqlnet.ora file for all of the Oracle RAC instances point to the same shared software keystore location. You also must ensure security of the shared software keystore by assigning the appropriate directory permissions.

If you do not use a shared file system to store the software keystore, then you must copy the keystore to the associated nodes.

TDE can be used for both columns and tablespaces in a multitenant environment.

Note the following:

• The keystore that you create resides in the host multitenant environment, not within any particular PDB. Multiple PDBs can access a single keystore while running on this host. Each PDB that uses encryption has a Transparent Data Encryption TDE master encryption key stored in this keystore.

• Each PDB has its own TDE master encryption key. You must manage the TDE master encryption key for each PDB from within the PDB only, using the PDB-specific key management ADMINISTER KEY MANAGEMENT statements. From the root or a PDB, you can query the appropriate views to find information about the TDE master encryption keys of the PDBs in a CDB. For example, the PDBID column of the V$ENCYRYPTION_KEYS view indicates the PDBs to which a TDE master encryption key belongs.

• You can manage the Transparent Data Encryption TDE master encryption keys independently within the keystore for each PDB. You can rotate the TDE master encryption keys for each PDB individually. See Exporting and Importing the TDE Master Encryption Key for more information.

• You perform most of the keystore operations from the root. Keystore operations such as rotating a keystore password, merging keystores, and so on must be performed in the root. There are a few key management operations that you can perform within a PDB, such as opening, closing, resetting, and creating keys. The operations can also be performed for all of the PDBs from the root. Where applicable, the ADMINISTER KEY MANAGEMENT statement has the CONTAINER clause. Setting CONTAINER=ALL performs the action on all of the PDBs.

• An auto-login keystore is open (for example, in the root) or if the keystore is closed. In this scenario, include the FORCE KEYSTORE clause in the ADMINISTER KEY MANAGEMENT statement for the following operations: rotating a keystore password; creating, using, rekeying, tagging, importing, exporting, migrating, or reverse migrating encryption keys; opening or backing up keystores; adding, updating, or deleting secret keystores.

• You can use an external store for passwords in a multitenant environment. If you have configured the password-based software keystore to use an external store, then include the IDENTIFIED BY EXTERNAL STORE clause in the ADMINISTER KEY MANAGEMENT statement for the following operations: backing up, opening, or closing keystores; adding, updating, or deleting secret keystores; creating, using, rekeying, tagging, importing, or exporting encryption keys.

• If you plan to move a PDB that uses Transparent Data Encryption to a new host computer, then you must move its TDE master encryption key as well. To move the TDE master encryption key from one host computer to another, use the procedures described in Exporting and Importing the TDE Master Encryption Key.

You must perform specific ADMINISTER KEY MANAGEMENT keystore operations only in the root.

These operations are as follows:

• Creating password-based software keystores, using the ADMINISTER KEY MANAGEMENT CREATE KEYSTORE statement

• Creating auto-login software keystores, using the ADMINISTER KEY MANAGEMENT CREATE [LOCAL] AUTO_LOGIN KEYSTORE FROM KEYSTORE statement

• Changing the software keystore password, using the ADMINISTER KEY MANAGEMENT ALTER KEYSTORE PASSWORD statement

• Merging software keystores, using the ADMINISTER KEY MANAGEMENT MERGE KEYSTORE statement

• Backing up software keystores, using the ADMINISTER KEY MANAGEMENT BACKUP KEYSTORE keystore

• Migrating from a software keystore to a hardware keystore, using the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY... MIGRATE USING statement

• Reverse migrating from a hardware security module to a software keystore, using the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY... REVERSE MIGRATE statement

• Adding, updating, and deleting secrets, using the ADMINISTER KEY MANAGEMENT ADD|UPDATE|DELETE SECRET statement

• Selectively exporting and importing keys, based on a query or identifier list

You can perform the some keystore operations in either the root or a PDB.

These operations are as follows:

• Opening keystores, using the ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN statement

• Closing keystores, using the ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE statement

You can perform the following key management operations either in the root or a PDB:

• Creating a tag for the TDE master encryption key, using the ADMINISTER KEY MANAGEMENT SET TAG statement

• Creating a TDE master encryption key, using the ADMINISTER KEY MANAGEMENT CREATE KEY statement

• Resetting or rotating the TDE master encryption key, using the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY statement

• Activating a TDE master encryption key, using the ADMINISTER KEY MANAGEMENT USE KEY statement

• Exporting TDE master encryption keys, using the ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS statement

• Importing TDE master encryption keys, using the ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS statement

The EXPORT and IMPORT clauses of ADMINISTER KEY MANAGEMENT EXPORT can export or import TDE master encryption keys for a PDB.

Topics:

• About Exporting and Importing TDE Master Encryption Keys for a PDB
  You can export and import TDE master encryption keys from the root in the same way that you export and import this key for a non-CDB database.
• Exporting or Importing a TDE Master Encryption Key for a PDB
  The ADMINISTER KEY MANAGEMENT statement can export or import a TDE master encryption key for a PDB.
• Example: Exporting a TDE Master Encryption Key from a PDB
  The ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS statement can export TDE master encryption keys for a PDB.
• Example: Importing a TDE Master Encryption Key into a PDB
  The ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS statement can import a TDE master encryption key into a PDB.

You can add or remove PDBs that have encrypted data to and from a CDB.

Topics:

• Unplugging a PDB That Has Encrypted Data
  You can unplug a PDB from one CDB and then plug it into another CDB.
• Plugging a PDB That Has Encrypted Data into a CDB
  To plug a PDB that has encrypted data into a CDB, you first plug in the PDB and then configure its encryption key.
• Unplugging a PDB That Has Master Keys Stored in an HSM
  You can unplug a PDB from one CDB that has been configured with an HSM and then plug it into another CDB also configured with an HSM.
• Plugging a PDB That Has Master Keys Stored in an HSM
  The ADMINISTER KEY MANAGEMENT statement can import an HSM master key to a PDB that has been moved to another CDB.

You should be aware of how keystore open and close operations work in a multitenant environment.

For each PDB in a multitenant environment, you must explicitly open the password-based software keystore or hardware keystore in the PDB to enable the Transparent Data Encryption operations to proceed. (Auto-login and local auto-login software keystores open automatically.) Closing a keystore on a PDB blocks all of the Transparent Data Encryption operations on that PDB.

In a CDB, the open and close keystore operations in a PDB depends on the open and close status of the keystore in the root.

Note the following:

• Before you can manually open a software password-based or hardware keystore in an individual PDB, you must open the keystore in the root.

• If an auto-login keystore is in use, or if the keystore is closed, then include the FORCE KEYSTORE clause in the ADMINISTER KEY MANAGEMENT statement when you open or close the keystore.

• If the keystore is a password-based software keystore that uses an external store for passwords, then set the IDENTIFIED BY clause to EXTERNAL STORE.

• Before you can set a TDE master encryption key in an individual PDB, you must set the key in the root.

• Auto-login and local auto-login software keystores open automatically. You do not need to manually open these from the root first, or from the PDB.

• If you close a keystore in the root, then the keystores in the dependent PDBs also close. A keystore close operation in the root is the equivalent of performing a keystore close operation with the CONTAINER clause set to ALL.

• If you open a keystore in the root and set the CONTAINER clause to ALL, then the keystores in the dependent PDBs also open.

You can create a convenience function that uses the V$ENCRYPTION_WALLET view to find the status for keystores in all PDBs in a CDB.

CREATE OR REPLACE PROCEDURE all_pdb_v$encryption_wallet
IS
    err_occ            BOOLEAN;
    curr_pdb           VARCHAR2(30);
    pdb_name           VARCHAR2(30);
    wrl_type           VARCHAR2(20);
    status             VARCHAR2(30);
    wallet_type        VARCHAR2(20);
    wallet_order       VARCHAR2(12);
    fully_backed_up    VARCHAR2(15);
    wrl_parameter      VARCHAR2(4000);
    cursor sel_pdbs IS SELECT NAME FROM V$CONTAINERS
                       WHERE NAME <> 'PDB$SEED' order by con_id desc;
  BEGIN
 
    -- Store the original PDB name
    SELECT sys_context('userenv', 'con_name') INTO curr_pdb FROM DUAL;
    IF curr_pdb <> 'CDB$ROOT' THEN
      dbms_output.put_line('Operation valid in ROOT only');
    END IF;
 
    err_occ := FALSE;
    dbms_output.put_line('---');
    dbms_output.put_line('PDB_NAME                       WRL_TYPE STATUS                        ');
    dbms_output.put_line('------------------------------ -------- ------------------------------');
    dbms_output.put_line('WALLET_TYPE          WALLET_ORDER FULLY_BACKED_UP');
    dbms_output.put_line('-------------------- ------------ ---------------');
    dbms_output.put_line('WRL_PARAMETER');
    dbms_output.put_line('--------------------------------------------------------------------------');
    FOR pdbinfo IN sel_pdbs LOOP
 
      pdb_name := DBMS_ASSERT.ENQUOTE_NAME(pdbinfo.name, FALSE);
      EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER = ' || pdb_name;
 
      BEGIN
        pdb_name := rpad(substr(pdb_name,1,30), 30, ' ');
        EXECUTE IMMEDIATE 'SELECT wrl_type from V$ENCRYPTION_WALLET' into wrl_type; 
        wrl_type := rpad(substr(wrl_type,1,8), 8, ' ');
        EXECUTE IMMEDIATE 'SELECT status from V$ENCRYPTION_WALLET' into status;
        status := rpad(substr(status,1,30), 30, ' ');
        EXECUTE IMMEDIATE 'SELECT wallet_type from V$ENCRYPTION_WALLET' into wallet_type;
        wallet_type := rpad(substr(wallet_type,1,20), 20, ' ');
        EXECUTE IMMEDIATE 'SELECT wallet_order from V$ENCRYPTION_WALLET' into wallet_order;        
        wallet_order := rpad(substr(wallet_order,1,9), 12, ' ');
        EXECUTE IMMEDIATE 'SELECT fully_backed_up from V$ENCRYPTION_WALLET' into fully_backed_up;
        fully_backed_up := rpad(substr(fully_backed_up,1,9), 15, ' ');
        EXECUTE IMMEDIATE 'SELECT wrl_parameter from V$ENCRYPTION_WALLET' into wrl_parameter;
        wrl_parameter := rpad(substr(wrl_parameter,1,79), 79, ' ');
        dbms_output.put_line(pdb_name || ' ' || wrl_type || ' ' || status);
        dbms_output.put_line(wallet_type || ' ' || wallet_order || ' ' || fully_backed_up);
        dbms_output.put_line(wrl_parameter);
 
      EXCEPTION
        WHEN OTHERS THEN
        err_occ := TRUE;
      END;
    END LOOP;
 
    IF err_occ = TRUE THEN
       dbms_output.put_line('One or more PDB resulted in an error');
    END IF;
  END;
.
/
set serveroutput on
exec all_pdb_v$encryption_wallet;