Skip Headers
Oracle® Database Advanced Security Guide
12g Release 1 (12.1)

E50333-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

Beta Draft: 2014-10-15

6 Using Transparent Data Encryption with Other Oracle Features

You can use Oracle Data Encryption with a number of other Oracle features, such as Oracle Data Guard or Oracle Real Application Clusters.

Topics:

How Transparent Data Encryption Works with Export and Import Operations

You can use Oracle Data Pump to export and import tables that contain encrypted columns, as well as encrypt entire dump sets.

Topics:

About Exporting and Importing Encrypted Data

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.

You can use Oracle Data Pump to export and import tables containing encrypted columns. Oracle Data Pump 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

Exporting and Importing Tables with Encrypted Columns

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

  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
    

Using Oracle Data Pump to Encrypt Entire Dump Sets

Oracle Data Pump enables you to encrypt entire dump sets, as opposed to encrypting just Transparent Data Encryption columns.

The ENCRYPTION_MODE parameter specifies the encryption mode. ENCRYPTION_MODE=DUAL encrypts the dump set using the TDE master encryption key stored in the keystore and the password provided.

Example 6-1 shows how to use dual encryption mode to export encrypted data.

Example 6-1 Exporting Encrypted Data in Dual Encryption Mode

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

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, for successful decryption during import. The open keystore is also required to reencrypt column encryption data at the target database. If a keystore already exists on the target database, then you can export and import the current TDE master encryption key into the keystore of the target database.

See Also:

How Transparent Data Encryption Works with Oracle Data Guard

For both software keystores and hardware keystores, Oracle Data Guard supports Transparent Data Encryption (TDE).

If the primary database uses TDE, then each standby database in a Data Guard configuration must have a copy of the encryption keystore from the primary database. If the primary database uses TDE, then each standby database in a Data Guard configuration must have an encryption keystore with the keystore from the primary database merged into it. If you reset the TDE master encryption key in the primary database, then you must merge the keystore on the primary database that contains the TDE master encryption key to each standby database.

Note the following:

  • Encrypted data in log files remains encrypted when data is transferred to the standby database. Encrypted data also stays encrypted during transit.

  • TDE works with SQL*Loader direct path loads. The data loaded into encrypted columns is transparently encrypted during the direct path load.

  • Materialized views work with TDE tablespace encryption. You can create both materialized views and materialized view logs in encrypted tablespaces. Materialized views also work with TDE column encryption.

See Also:

How Transparent Data Encryption Works with Oracle Real Application Clusters

Oracle Real Application Clusters (Oracle RAC) nodes can share software keystores. Hardware security module keystores must be shared by using a network connection. You can store software keystores on non-shared file systems in Oracle RAC.

Topics:

See Also:

Oracle Key Vault Administrator's Guide for information about using TDE with Oracle RAC in an Oracle Key Vault environment

About Using Transparent Data Encryption with Oracle Real Application Clusters

For software keystores, Oracle Database enables Oracle Real Application Clusters (Oracle RAC) nodes to share the keystore. Hardware security modules use a network connection for each database instance.

This eliminates the need to manually copy and synchronize the software keystore across all of the nodes. 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 hardware security modules, use a network connection for each database instance. Thus, all database instances have access to the hardware security module.

Keystore operations that must be performed or synchronized on all of the instances, such as opening or closing the keystore or rekeying can be performed on any one Oracle RAC instance. The synchronization operation applies to all of the other Oracle RAC instances in the cluster. This means that when you open and close a keystore for one instance, then it opens and closes for all of the Oracle RAC instances. Similarly, a TDE master encryption key rekey operation that you perform on one database instance applies to all of the database 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.

Using a Non-Shared File System to Store a Software Keystore in Oracle RAC

If you are not using a shared file system to store the software keystore, then you must copy the keystore to all of the nodes after a TDE master encryption key rekey.

If you must reset the TDE master encryption key for the database, then follow these steps:

  1. Log in to the database instance 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 RAC node.

    See "Setting and Resetting the TDE Master Encryption Key in the Keystore" for more information.

  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 these statements in the root.)

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

Note:

Any keystore operation, such as opening or closing the keystore, performed on any one Oracle RAC instance applies to all other Oracle RAC instances. This is true even if you are not using a shared file system.

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

How Transparent Data Encryption Works with SecureFiles

You can use SecureFiles to store LOBS. SecureFile storage has three features: compression, deduplication, and encryption.

SecureFiles encryption uses TDE to provide the encryption facility for LOBs. When you create or alter tables, you can specify the SecureFiles encryption or LOB columns that must use the SecureFiles storage. You can enable the encryption for a LOB column by either using the current Transparent Data Encryption (TDE) syntax or by using the ENCRYPT clause as part of the LOB parameters for the LOB column. The DECRYPT option in the current syntax or the LOB parameters turn off encryption.

Example 6-2 shows how to create a SecureFiles LOB in a CREATE TABLE statement.

Example 6-2 Creating a SecureFiles LOB

CREATE TABLE table1 ( a BLOB ENCRYPT USING 'AES256')
    LOB(a) STORE AS SECUREFILE (
    CACHE
    );

Example 6-3 shows an example of creating a SecureFiles LOB that uses password protections for the encrypted column.

Example 6-3 Creating a SecureFiles LOB with a Column Password Specified

CREATE TABLE table1 (a VARCHAR2(20), b BLOB)
    LOB(b) STORE AS SECUREFILE (
        CACHE
        ENCRYPT USING 'AES192' IDENTIFIED BY password
    );

All of the LOBS in the LOB column are encrypted with the same encryption specification.

See Also:

Oracle Database SecureFiles and Large Objects Developer's Guide for more information about SecureFiles encryption

How Transparent Data Encryption Works in a Multitenant Environment

In a multitenant environment, the Transparent Data Encryption operations that you perform will depend on whether you are in the root or a PDB.

Topics:

About Using Transparent Data Encryption in a Multitenant Environment

You can use Transparent Data Encryption 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.

    See the following sections for more information:

  • 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".

Operations That Must Be Performed in Root

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

  • 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

How the CONTAINER=ALL Setting Works for Key and Keystore Operations

You can specify the CONTAINER=ALL setting for the key and keystore operations described in this section. Specifying the CONTAINER=ALL setting performs the same operation on all of the PDBs within the CDB. Remember that you can only use the CONTAINER=ALL setting in the root. The CONTAINER clause is optional. If you omit the CONTAINER clause, then the default is CONTAINER = CURRENT.

The permitted CONTAINER=ALL operations are as follows:

  • Opening a keystore. If you open the keystore using the CONTAINER=ALL setting, then the keystores on all of the associated PDBs open.

  • Closing a keystore. Closing a keystore using the CONTAINER=ALL setting closes the keystores on all of the associated PDBs.

  • Creating a TDE master encryption key. Creating a TDE master encryption key using the CONTAINER=ALL setting creates the key on all of the PDBs that are open. You can check the keys that were created recently by querying the CREATION_TIME column in the V$ENCRYPTION_KEYS view. You can also specify a tag with CONTAINER=ALL operation, but be aware that this operation creates the keys in all of the PDBs with the same tag. You should have individual tags for each TDE master encryption key, because the tags can help identify PDBs on which the create key operation succeeded in case of an error. You can modify the tag by using the ADMINISTER KEY MANAGEMENT SET TAG statement later on.

  • Performing a rekey operation. Performing a rekey operation with the CONTAINER=ALL setting creates and then activates the key on all of the PDBs that are open. You can check the keys that were created recently by querying the CREATION_TIME column in the V$ENCRYPTION_KEYS view. To find the keys that were activated recently, query the ACTIVATION_TIME column in the V$ENCRYPTION_KEYS view. You can also specify a tag with CONTAINER=ALL operation, but be aware that this operation creates the keys in all of the PDBs with the same tag. The tag can also help identify PDBs on which the create key operation succeeded in case of an error. You can modify the tag by using the ADMINISTER KEY MANAGEMENT SET TAG statement later on.

Operations That Can Be Performed in Root or in a PDB

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

Exporting and Importing TDE Master Encryption Keys for a PDB

To export or import TDE master encryption keys for a PDB, you use the ADMINISTER KEY MANAGEMENT EXPORT and ADMINISTER KEY MANAGEMENT IMPORT statements.

Topics:

About Exporting and Importing TDE Master Encryption Keys for a PDB

You can export and import any TDE master encryption key from the root in the same way that you export and import the TDE master encryption key for a non-CDB database.

You can also export and import all of the TDE master encryption keys that belong to the PDB by exporting and importing the TDE master encryption keys from within a PDB. Export and import of TDE master encryption keys in a PDB supports the PDB unplug and plug operations. During a PDB unplug and plug, all of the TDE master encryption keys that belong to a PDB, as well as the metadata, are involved. Therefore, the WITH IDENTIFIER clause of the ADMINISTER KEY MANAGEMENT EXPORT statement is not allowed when you export keys from within a PDB. The WITH IDENTIFIER clause is only permitted in the root.

Suppose you want to export TDE master encryption keys in a PDB. You must run the ADMINISTER KEY MANAGEMENT EXPORT statement without the WITH IDENTIFIER clause in the source database, for example:

ADMINISTER KEY MANAGEMENT EXPORT KEYS WITH SECRET "my_secret"
TO '/etc/TDE/export.exp'
IDENTIFIED BY password;

This ADMINISTER KEY MANAGEMENT EXPORT operation exports not only the keys but creates metadata that is necessary for PDB environments (as well as for cloning operations).

Inside a PDB, the export operation of TDE master encryption keys exports the keys that were created or activated by a PDB with the same GUID as the PDB where the keys are being exported. Essentially, all of the keys that belong to a PDB where the export is being performed will be exported.

The importing of TDE master encryption keys from an export file within a PDB takes place only if the TDE master encryption key was exported from another PDB with the same GUID. To support the plug-in of a PDB into a CDB, the import will also import the TDE master encryption keys from an export file that contains the TDE master encryption keys of a non-CDB exported without the WITH IDENTIFIER clause. Because the PDB-specific details, such as the PDB name and database ID, can change from one CDB to the next, the PDB-specific information is modified during the import to reflect the updated PDB information.

Exporting or Importing a TDE Master Encryption Key for a PDB

To export or import a TDE master encryption for a PDB, you must open the keystore and then use the ADMINISTER KEY MANAGEMENT statement with the EXPORT ENCRYPTION KEYS WITH SECRET or IMPORT ENCRYPTION KEYS WITH SECRET clause.

  1. Log in to the PDB as a user who was granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.

    For example:

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

    To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

  2. Ensure that the keystore is open.

    You can query the STATUS column of the V$ENCRYPTION_WALLET view to find if the keystore is open. If you find that you must open the keystore, then see the following sections:

  3. Perform the export or import operation, as shown in the examples in "Examples of Exporting and Importing TDE Master Encryption Keys for a PDB".

Examples of Exporting and Importing TDE Master Encryption Keys for a PDB

To export or import TDE master encryption keys for a PDB, you must use the ADMINISTER KEY MANAGEMENT statement.

Example 6-4 shows how to export a TDE master encryption key from the PDB hr_pdb1.

Example 6-4 Exporting a TDE Master Encryption Key from a PDB

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

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "my_secret" TO '/export.p12' IDENTIFIED BY password_cdb1;

Example 6-5 shows how to import a TDE master encryption key into the PDB hr_pdb2.

Example 6-5 Importing a TDE Master Encryption Key into a PDB

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

ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "my_secret" FROM '/tmp/export.p12' IDENTIFIED BY password_cdb2 WITH BACKUP;

Unplugging and Plugging a PDB with Encrypted Data in a CDB

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

Topics:

Exporting a TDE Master Encryption Key from a PDB

You can unplug a PDB from one CDB and then plug it into another CDB. The database that was unplugged contains data files and other associated files. The export file is another file that forms part of the unplugged PDB files and should be transported with the unplugged PDB.

  1. Export the TDE master encryption key of the PDB that you want to unplug.

    See "Exporting and Importing TDE Master Encryption Keys for a PDB".

  2. Unplug the PDB, as described in Oracle Database Administrator's Guide.

Plugging a PDB That Has Encrypted Data into a CDB

To plug a PDB that has encrypted data into a CDB, you must import the TDE master encryption key into the PDB and then configure it there.

  1. Create the PDB by plugging the unplugged PDB into the CDB, as described in Oracle Database Administrator's Guide.

    During the open operation of the PDB after the plug operation, Oracle Database determines if the PDB has encrypted data. If so, it opens the PDB in the RESTRICTED mode.

    See Oracle Database Administrator's Guide for more information about the Open Mode of a PDB.

  2. Import the TDE master encryption key into the PDB.

    See "Exporting and Importing TDE Master Encryption Keys for a PDB".

  3. Close the PDB and then re-open the PDB, as described in Oracle Database Administrator's Guide.

  4. Open the keystore.

    See the following sections:

  5. Set the TDE master encryption key for the PDB.

    See the following sections:

Opening and Closing a Keystore 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.

  • 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.

Finding the Keystore Status for All of the PDBs in a Multitenant Environment

The V$ENCRYPTION_WALLET view displays the status of the keystore in a PDB, whether it is open, closed, uses a software or hardware keystore, and so on. You can create a convenience function that uses this view to find the status for keystores in all of the PDBs in a CDB.

Example 6-6 shows how to create this function.

Example 6-6 Function to Find the Keystore Status of All of the 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;

How Transparent Data Encryption Works with Oracle Call Interface

Transparent Data Encryption does not have any effect on the operation of Oracle Call Interface (OCI).

For most practical purposes, TDE is transparent to OCI except for the row shipping feature. You cannot use the OCI row shipping feature with TDE because the key to make the row usable is not available at the receipt-point.

How Transparent Data Encryption Works with Editions

Transparent Data Encryption does not have any effect on the Editions feature of Oracle Database.

For most practical purposes, TDE is transparent to Editions. Tables are always noneditioned objects. TDE Column Encryption encrypts columns of the table. Editions are not affected by TDE tablespace encryption.

How Transparent Data Encryption Works in a Multidatabase Environment

If there are multiple Oracle databases installed on the same server (for example, databases sharing the same Oracle binary but using different data files), then each database must access its own Transparent Data Encryption keystore.

Keystores are not designed to be shared among databases. By design, there must be one keystore per database. You cannot use the same keystore for more than one database.

To configure the sqlnet.ora file for a multidatabase environment, use one of the following options:

  • Option 1: If the databases share the same Oracle home, then keep the sqlnet.ora file in the default location, which is in the ORACLE_HOME/network/admin directory.

    In this case, it is ideal to use the default location. Ensure that the sqlnet.ora file has no WALLET_LOCATION or ENCRYPTION_WALLET_LOCATION entries. Transparent Data Encryption accesses the keystore from the default sqlnet.ora location if these two entries are not in the sqlnet.ora file.

  • Option 2: If Option 1 is not feasible for your site, then you can specify the keystore location based on an environment variable setting, such as ORACLE_SID.

    For example:

    ENCRYPTION_WALLET_LOCATION =
     (SOURCE =
      (METHOD = FILE)
       (METHOD_DATA =
        (DIRECTORY = /home/oracle/wallet/$ORACLE_SID)
    
  • Option 3: If Options 1 and 2 are not feasible, then use separate sqlnet.ora files, one for each database. Ensure that you correctly set the TNS_ADMIN environment variable to point to the correct database configuration. See SQL*Plus User's Guide and Reference for more information and examples of setting the TNS_ADMIN variable.

Caution:

Using a keystore from another database can cause partial or complete data loss.