Using Transparent Data Encryption with Other Oracle Features

6 Using Transparent Data Encryption with Other Oracle Features

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

How Transparent Data Encryption Works with Export and Import Operations
Oracle Data Pump can export and import tables that contain encrypted columns, as well as encrypt entire dump sets.
How Transparent Data Encryption Works with Oracle Data Guard
An Oracle Data Guard primary database and secondary secondary database can share both a software keystore and an external keystore.
How Transparent Data Encryption Works with Oracle Real Application Clusters
Oracle Real Application Clusters (Oracle RAC) nodes can share both a software keystore and an external keystore.
How Transparent Data Encryption Works with SecureFiles
SecureFiles, which stores LOBS, has three features: compression, deduplication, and encryption.
How Transparent Data Encryption Works in a Multitenant Environment
In a multitenant environment, the TDE operations that you can perform depend on whether you are in the root or a PDB.
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).
How Transparent Data Encryption Works with Editions
Transparent Data Encryption does not have any effect on the Editions feature of Oracle Database.
Configuring Transparent Data Encryption to Work in a Multidatabase Environment
Each Oracle database on the same server (such as databases sharing the same Oracle binary but using different data files) must access its own TDE keystore.

Parent topic: Using Transparent Data Encryption

6.1 How Transparent Data Encryption Works with Export and Import Operations

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

About Exporting and Importing Encrypted Data
You can use Oracle Data Pump to export and import tables that have encrypted columns.
Exporting and Importing Tables with Encrypted Columns
You can export and import tables with encrypted columns using the ENCRYPTION=ENCRYPTED_COLUMNS_ONLY setting.
Using Oracle Data Pump to Encrypt Entire Dump Sets
Oracle Data Pump can encrypt entire dump sets, not just Transparent Data Encryption columns.

Parent topic: Using Transparent Data Encryption with Other Oracle Features

6.1.1 About Exporting and Importing Encrypted Data

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

For both software and external 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

Parent topic: How Transparent Data Encryption Works with Export and Import Operations

6.1.2 Exporting and Importing Tables with Encrypted Columns

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

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

Parent topic: How Transparent Data Encryption Works with Export and Import Operations

6.1.3 Using Oracle Data Pump to Encrypt Entire Dump Sets

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

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_ALGORITHM=AES256 ENCRYPTION_MODE=dual

Password: password_for_hr

See Also:

Exporting and Importing the TDE Master Encryption Key
Oracle Database Utilities for details on using Oracle Data Pump and the associated encryption parameters
Creating an Encrypted Column in an External Table

Parent topic: How Transparent Data Encryption Works with Export and Import Operations

6.2 How Transparent Data Encryption Works with Oracle Data Guard

An Oracle Data Guard primary database and secondary secondary database can share both a software keystore and an external keystore.

About Using Transparent Data Encryption with Oracle Data Guard
For both software keystores and external keystores, Oracle Data Guard supports Transparent Data Encryption (TDE).
Configuring TDE and Oracle Key Vault in an Oracle Data Guard Environment
You can configure Oracle Data Guard in a multitenant environment so that it can work with TDE and Oracle Key Vault.

Parent topic: Using Transparent Data Encryption with Other Oracle Features

6.2.1 About Using Transparent Data Encryption with Oracle Data Guard

For both software keystores and external 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 you reset the TDE master encryption key in the primary database, then you must copy the keystore from the primary database that contains the TDE master encryption key to each standby database.

Note the following:

Re-key operations with wallet-based TDE will cause the Managed Recovery Process (MRP) on the standby databases to fail because the new TDE master encryption key is not yet available. In order to circumvent this problem, use the ADMINISTER KEY MANAGEMENT CREATE KEY statement on the primary database to insert new TDE master encryption keys into the wallet. Copy the wallet to the standby databases, and then execute a ADMINISTER KEY MANAGEMENT USE KEY statement on the primary.
Encrypted data in log files remains encrypted when data is transferred to the standby database. Encrypted data also stays encrypted during transit.

Related Topics

Merging Software Keystores

Parent topic: How Transparent Data Encryption Works with Oracle Data Guard

6.2.2 Configuring TDE and Oracle Key Vault in an Oracle Data Guard Environment

You can configure Oracle Data Guard in a multitenant environment so that it can work with TDE and Oracle Key Vault.

The following scenario shows the configuration with Oracle Key Vault in a single-instance, multitenant Oracle Data Guard environment with one physical standby database. The version for the primary and standby databases must be release 12.2.01 with the January 2020 release update or later applied. To complete this procedure, you must perform each step in the sequence shown. After you complete the procedure, Oracle Data Guard will use Oracle Key Vault for TDE key management exclusively, and there will be no TDE wallet on your database servers. Oracle recommends that you monitor the alert logs of both primary and standby databases.

On both the primary and standby databases, execute the opatch lspatches command to check the patch release.
```
$ORACLE_HOME/OPatch/opatch lspatches
```
Output similar to the following appears:
```
31312468;Database Jul 2020 Release Update : 12.2.0.1.200714 (31312468)
```
Download the Oracle Key Vault deployment script that the Oracle Key Vault administrators prepared to enable database administrators to automatically register their Oracle databases with Oracle Key Vault.
Oracle Key Vault RESTful Services Administrator's Guide has an example of how to create a script to automatically enroll Oracle databases as endpoints. The deployment scripts reside on a shared file system from which database administrators can download. There are two different versions of these deployment scripts. The primary.zip file is for the primary database, and the secondary.zip file is for all standby databases. You can use these scripts for an Oracle Data Guard or an Oracle RAC environment.

Another component that the Oracle Key Vault administrators prepare and add to the deployment script is a configuration file that contains all details for the deployment scripts to connect to Oracle Key Vault.
Copy the two deployment scripts (primary.zip and secondary.zip) that an Oracle Key Vault administrator created for database administrators to download from a shared location.
1. Copy the primary.zip file to the primary database.
```
$ scp user@ip_address:/path/to/file/primary.zip .
```
2. Copy the secondary.zip file to the standby database.
```
$ scp user@ip_address:/path/to/file/secondary.zip .
```
On their respective servers, extract the zip files.
```
$ unzip primary.zip

$ unzip secondary.zip
```

Execute the primary-run-me.sh and secondary-run-me.sh scripts, which contain the commands for the RESTful API to execute in Oracle Key Vault.

The Oracle Key Vault RESTful services will execute these commands in order to register this database in Oracle Key Vault with unique wallet and endpoint names.

Primary database: For example:

$ more primary-run-me.sh

#!/bin/bash
export EP_NAME=${ORACLE_SID^^}_on_${HOSTNAME/.*}
export WALLET_NAME=${ORACLE_SID^^}
curl -Ok --tlsv1.2 https://Oracle_Key_Vault_IP_address:5695/okvrestclipackage.zip
unzip -oj okvrestclipackage.zip lib/okvrestcli.jar -d ./lib
cat > /home/oracle/deploy-primary.sh  << EOF
#!/bin/bash
mkdir -pv ${ORACLE_BASE}/product/okv
okv manage-access wallet create --wallet ${WALLET_NAME} --unique FALSE
okv admin endpoint create --endpoint ${EP_NAME} --description "$HOSTNAME, $(hostname -i)" 
    --type ORACLE_DB --platform LINUX64 --subgroup "USE CREATOR SUBGROUP" --unique FALSE --strict-ip-check TRUE
okv manage-access wallet set-default --wallet ${WALLET_NAME} --endpoint ${EP_NAME}
expect << _EOF
    set timeout 120
    spawn okv admin endpoint provision --endpoint ${EP_NAME} --location ${ORACLE_BASE}/product/okv --auto-login FALSE
    expect "Enter Oracle Key Vault endpoint password: "
    send "change-on-install\r"
    expect eof
_EOF
EOF

Standby database: For example:

$ more secondary-run-me.sh

$ more run-me.sh
#!/bin/bash
export EP_NAME=${ORACLE_SID^^}_on_$(hostname -s)
export WALLET_NAME=${ORACLE_SID^^}
curl -Ok --tlsv1.2 https://Oracle_Key_Vault_IP_address:5695/okvrestclipackage.zip 
unzip -oj okvrestclipackage.zip lib/okvrestcli.jar -d ./lib
cat > /home/oracle/deploy-standby.sh << EOF
#!/bin/bash
okv admin endpoint create --endpoint ${EP_NAME} --description "$(hostname -f) $(hostname -i)" --subgroup "USE CREATOR SUBGROUP" --unique FALSE
okv admin endpoint update --endpoint ${EP_NAME} --strict-ip-check TRUE
okv manage-access wallet set-default --wallet ${WALLET_NAME} --endpoint ${EP_NAME}
expect << _EOF
    set timeout 120
    spawn okv admin endpoint provision --endpoint ${EP_NAME} --location /etc/ORACLE/KEYSTORES/${ORACLE_UNQNAME^^}/okv --auto-login FALSE
    expect "Enter Oracle Key Vault endpoint password: "
    send "change-on-install\r"
    expect eof
_EOF
EOF

Create the following directories on the primary database and the standby database.
For example:
```
$ mkdir -pv /u01/opt/oracle/product/okv 
$ mkdir -pv /u01/opt/oracle/product/tde 
$ mkdir -pv /u01/opt/oracle/product/tde_seps
```
In this specification:
- /u01/opt/oracle/product/okv is the installation directory for the Oracle Key Vault client software.
- /u01/opt/oracle/product/tde will store an auto-login wallet, which only contains the future Oracle Key Vault password, enabling an auto-login Oracle Key Vault configuration.
- /u01/opt/oracle/product/tde_seps will store an auto-login wallet, which only contains the future Oracle Key Vault password. This will hide the Oracle Key Vault password from the SQL*Plus command line and potentially from the database administrator to enforce separation of duties between Oracle database administrators and Oracle Key Vault administrators.

Execute the RESTful API on the primary database first, because the deployment script on the standby databases depends on the presence of the shared virtual wallet in Oracle Key Vault that the script on the primary database creates.

On the primary:

$ ./deploy-primary.sh

A message similar to the following should appear:

$ ./primary-run-me.sh
mkdir: created directory ‘/u01/opt/oracle/product/okv’
{
  "result" : "Success",
  "value" : {
    "status" : "PENDING",
    "locatorID" : "CC7B4E69-79D5-4F8F-BE63-CB515B6DA4AB"
  }
}
{
  "result" : "Success",
  "value" : {
    "status" : "PENDING",
    "locatorID" : "EC5B78D6-F60D-4E81-883D-4B48D773A7AC"
  }
}
{
  "result" : "Success"
}
spawn okv admin endpoint provision --endpoint DAVID122_on_david --location /u01/opt/oracle/product/okv --auto-login FALSE
Enter Oracle Key Vault endpoint password:
{
  "result" : "Success",
  "value" : {
    "javaHome" : "/u01/opt/oracle/product/12.2/db_1/jdk/jre"
  }
}

On all standbys:

$ ./deploy-standby.sh

A message similar to the following should appear:

./standby-run-me.sh

mkdir: created directory ‘/u01/opt/oracle/product/okv’
{
  "result" : "Success",
  "value" : {
    "status" : "PENDING",
    "locatorID" : "803E93C8-8775-4C71-975B-7BAE2413E0DB"
  }
}
{
  "result" : "Success"
}
spawn okv admin endpoint provision --endpoint DAVID122_on_goliath --location /u01/opt/oracle/product/okv --auto-login FALSE
Enter Oracle Key Vault endpoint password:
{
  "result" : "Success",
  "value" : {
    "javaHome" : "/u01/opt/oracle/product/12.2/db_1/jdk/jre"
  }
}

On the primary and standby databases, execute the root.sh script to deploy the PKCS#11 library.

# /u01/opt/oracle/product/okv/bin/root.sh

The following output should appear:

Creating directory: /opt/oracle/extapi/64/hsm/oracle/1.0.0/
Copying PKCS library to /opt/oracle/extapi/64/hsm/oracle/1.0.0/
Setting PKCS library file permissions

Execute the okvutil changepwd command to change the password for the wallet that you installed, starting from the primary database and then to the standby.
Because all database administrators downloaded the same deployment script, all databases have the same password into Oracle Key Vault. This step enables each database to have a unique password.
```
$ /u01/opt/oracle/product/okv/bin/okvutil changepwd -t wallet -l /u01/opt/oracle/product/okv/ssl/

Enter wallet password: default_password
Enter new wallet password: Oracle_Key_Vault_password
Confirm new wallet password: Oracle_Key_Vault_password
Wallet password changed successfully
```
On the primary and standby databases, execute the following statements.
1. Add the following entry to the sqlnet.ora file:
```
ENCRYPTION_WALLET_LOCATION =
 (SOURCE = (METHOD = OKV)
  (METHOD_DATA =
   (DIRECTORY = /u01/opt/oracle/product/tde)))
```
2. Execute the following statements to add the Oracle Key Vault password as a secret into an auto-open wallet to replace the Oracle Key Vault password in the SQL*Plus command line with EXTERNAL STORE.
```
ADMINISTER KEY MANAGEMENT ADD SECRET 'Oracle_Key_Vault_password' 
FOR CLIENT 'OKV_PASSWORD' 
TO LOCAL AUTO_LOGIN KEYSTORE '/u01/opt/oracle/product/tde_seps';

ALTER SYSTEM SET EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION = '/u01/opt/oracle/product/tde_seps' SCOPE = SPFILE;
```
3. Execute the following statement to add the Oracle Key Vault password as a secret to enable an auto-login connection into Oracle Key Vault.
```
ADMINISTER KEY MANAGEMENT ADD SECRET 'Oracle_Key_Vault_password' 
FOR CLIENT 'HSM_PASSWORD' 
TO LOCAL AUTO_LOGIN KEYSTORE '/u01/opt/oracle/product/tde';
```
4. Optionally, if patch 30398099 is installed, then change the database default algorithm from AES128 to either AES192 or AES256.
  Note that the following parameter is preceded by an underscore.
```
ALTER SYSTEM SET "_tablespace_encryption_default_algorithm" = 'AES256' SCOPE = BOTH;
```
5. Configure the primary and standby databases to always encrypt new tablespaces:
```
ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS SCOPE = BOTH;
```
6. Restart the primary and standby databases so that the preceding ALTER SYSTEM SET WALLET_ROOT statement takes effect.
In the primary database, create your first TDE master encryption keys in Oracle Key Vault.
Check the alert.log of the standby database. The managed recovery process (MRP) should not be stopped, since the standby database finds the correct master key in the shared virtual wallet in Oracle Key Vault.
1. Primary root container: Set the first master encryption key.
  For all ADMINISTER KEY MANAGEMENT statements that do not change the TDE configuration, the password will be replaced by EXTERNAL STORE. This enables separation of duties between the database administrators and the Oracle Key Vault administrators because the Oracle Key Vault administrators do not need to share the Oracle Key Vault password with the database administrators.
```
sqlplus sys as syskm
Enter password: password

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY EXTERNAL STORE 
CONTAINER = CURRENT;
```
2. All primary PDBs: Set the first, tagged, master key for each open PDB. The benefit of tagging the PDB keys is that they can later be easily identified to belong to a certain PDB.
```
SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG '''||UPPER(SYS_CONTEXT('USERENV', 'CON_NAME'))||' '||TO_CHAR (SYS_EXTRACT_UTC (SYSTIMESTAMP), 
'YYYY-MM-DD HH24:MI:SS"Z"')||''' FORCE KEYSTORE IDENTIFIED BY EXTERNAL STORE;' 
AS "SET KEY COMMAND" FROM DUAL;
```
3. Execute the generated output of this SELECT statement.
  For example:
```
ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG 'pdb_name date time' 
IDENTIFIED BY EXTERNAL STORE;
```
Perform the following steps in the root container.
1. Optionally, encrypt the USERS tablespace in the root container.
  For example:
```
ALTER TABLESPACE USERS ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
```
2. Observe the alert.log of the standby database to confirm that the USERS tablespace there is also encrypted.
Optionally, encrypt the USERS tablespace in the PDBs.
Oracle does not recommend that you encrypt the SYSTEM, SYSAUX, TEMP, and UNDO tablespaces because it is only implemented in Oracle Database release 19c and later.
For example:
```
ALTER TABLESPACE USERS ENCRYPTION ONLINE USING 'AES256' ENCRYPT;
```
Create a tablespace and table in the primary database PDB.
When you create the tablespace in the primary database, it will be encrypted by default even though there are no encryption keywords in that statement. Observe the alert.log of the standby database to confirm the encrypted tablespace is created there as well.
```
CREATE TABLESPACE protected DATAFILE SIZE 50M;

CREATE TABLE SYSTEM.TEST TABLESPACE protected 
AS SELECT * FROM DBA_OBJECTS;
```
Confirm that you can select from the table that is stored in an encrypted tablespace.
```
SELECT COUNT(*), OWNER FROM SYSTEM.TEST 
GROUP BY OWNER 
ORDER BY 1 DESC;
```

On the standby database, execute the following query to list the encrypted tablespaces for the root container, all PDBs, and the encryption algorithm.

SELECT C.NAME AS PDB_NAME, T.NAME AS TBS_NAME, E.ENCRYPTIONALG AS ALG 
FROM V$TABLESPACE T, V$ENCRYPTED_TABLESPACES E, V$CONTAINERS C 
WHERE E.TS# = T.TS# AND E.CON_ID = T.CON_ID AND E.CON_ID = C.CON_ID ORDER BY E.CON_ID, T.NAME;

PDB_NAME        TBS_NAME             ALG     
--------------- -------------------- ------- 
CDB$ROOT        USERS                AES256  
FINPDB          PROTECTED            AES256 
FINPDB          USERS                AES256

Optionally, validate the configuration.

Perform an Oracle Data Guard switchover between the primary and standby databases.
See Oracle Data Guard Concepts and Administration.

Perform the following steps in the new primary database.
Select from the encrypted table in your PDB.
Because there is an auto-open connection into Oracle Key Vault, the following query does not require that you enter the Oracle Key Vault password.
```
SELECT COUNT(*), OWNER FROM SYSTEM.TEST 
GROUP BY OWNER 
ORDER BY 1 DESC;

24 rows selected.
```

Rekey the PDB.

SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG '''||UPPER(SYS_CONTEXT('USERENV', 'CON_NAME'))||' '||TO_CHAR (SYS_EXTRACT_UTC (SYSTIMESTAMP), 
'YYYY-MM-DD HH24:MI:SS"Z"')||''' 
FORCE KEYSTORE 
IDENTIFIED BY EXTERNAL STORE;' 
AS "RE-KEY COMMAND" FROM DUAL;

Execute the generated output of this SELECT statement.

For example:

ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG 'pdb_name date time' 
IDENTIFIED BY EXTERNAL STORE;

Perform another Oracle Data Guard switchover.
See Oracle Data Guard Concepts and Administration.
Select from the encrypted table in your PDB.
Because there is an auto-open connection into Oracle Key Vault, the following query does not require that you enter the Oracle Key Vault password.
```
SELECT COUNT(*), OWNER FROM SYSTEM.TEST 
GROUP BY OWNER 
ORDER BY 1 DESC;

24 rows selected.
```

Rekey the PDB.

SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG '''||UPPER(SYS_CONTEXT('USERENV', 'CON_NAME'))||' '||TO_CHAR (SYS_EXTRACT_UTC (SYSTIMESTAMP), 
'YYYY-MM-DD HH24:MI:SS"Z"')||''' 
FORCE KEYSTORE 
IDENTIFIED BY EXTERNAL STORE;' 
AS "RE-KEY COMMAND" FROM DUAL;

Execute the generated output of this SELECT statement.

For example:

ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG 'pdb_name date time' 
IDENTIFIED BY EXTERNAL STORE;

Parent topic: How Transparent Data Encryption Works with Oracle Data Guard

6.3 How Transparent Data Encryption Works with Oracle Real Application Clusters

Oracle Real Application Clusters (Oracle RAC) nodes can share both a software keystore and an external keystore.

About Using Transparent Data Encryption with Oracle Real Application Clusters
Oracle requires a shared software keystore for Oracle Real Application Clusters (Oracle RAC), or a shared common virtual wallet in Oracle Key Vault among cluster instances.
Configuring TDE in Oracle Real Application Clusters for Oracle Key Vault
You can configure TDE in Oracle Real Application Clusters (Oracle RAC) on Oracle Exadata Cloud at Customer (ExaCC) and other servers for centralized key management provided by Oracle Key Vault.

Parent topic: Using Transparent Data Encryption with Other Oracle Features

6.3.1 About Using Transparent Data Encryption with Oracle Real Application Clusters

Oracle requires a shared software keystore for Oracle Real Application Clusters (Oracle RAC), or a shared common virtual wallet in Oracle Key Vault among cluster instances.

A TDE configuration with Oracle Key Vault uses a network connection from each instance of the database to the external key manager. In Oracle Key Vault, you must create one endpoint for each instance of the Oracle RAC-enabled database, and one virtual wallet for each Oracle RAC-enabled database. Then, make that virtual wallet the default wallet of all endpoints that belong to that database. In an Oracle RAC-enabled Data Guard configuration, all instances (primary and all standby databases) share that one virtual wallet. With this configuration, set key and re-key operations are completely transparent because all participating instances are automatically synchronized. This eliminates the need to manually copy the software keystore to each of the other nodes in the cluster.

Oracle does not support the use of individual TDE wallets for each Oracle RAC node. Instead, use shared wallets for TDE in the Oracle RAC environment. This enables all of the instances to access the same shared software keystore. If your site uses Oracle Automatic Storage Management Cluster File System (Oracle ACFS), then this is the preferred location for a shared wallet. Directly sharing the wallet in Oracle Automatic Storage Management (Oracle ASM) (for example, +DATA/$ORACLE_UNQNAME/WALLETS) is an alternative if Oracle ACFS is not available.

Keystore operations (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. Similarly, when a TDE master encryption key rekey operation takes place, the new key becomes available to each of the 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 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.

Note:

Storing TDE master encryption keys in individual wallets per Oracle Real Application Clusters (Oracle RAC) instance is not supported. As an alternative, use Oracle Key Vault for centralized key management across your on-premises or Cloud-based database deployments, or Oracle Automatic Storage Management (Oracle ASM), or Oracle ASM Cluster File System (Oracle ACFS) to provide local shared wallets.

Parent topic: How Transparent Data Encryption Works with Oracle Real Application Clusters

6.3.2 Configuring TDE in Oracle Real Application Clusters for Oracle Key Vault

You can configure TDE in Oracle Real Application Clusters (Oracle RAC) on Oracle Exadata Cloud at Customer (ExaCC) and other servers for centralized key management provided by Oracle Key Vault.

The following scenario assumes that you have a multitenant two-node Oracle RAC configuration. In this procedure, you must complete the following steps in the order shown. After you have completed this procedure, the Oracle RAC environment will exclusively use Oracle Key Vault for key management for Transparent Data Encryption. This procedure assumes that you have installed the January 2020 or later upgrade for Oracle Database release 12.2.0.1.

Before you begin, monitor the alert logs of your running Oracle RAC database. The Java version that is included in the default Oracle Database release 12.2.0.1 installation can be used to install the Oracle Key Vault client with the RESTful services. The provision command of the RESTful API requires a symbolic link in /usr/bin/java that points to the Java home in the Oracle database. For example:

# ln -sv $ORACLE_HOME/jdk/jre/java /usr/bin/java

Download the Oracle Key Vault deployment script that the Oracle Key Vault administrators prepared to enable database administrators to automatically register their Oracle databases with Oracle Key Vault.
Oracle Key Vault RESTful Services Administrator's Guide has an example of how to create a script to automatically enroll Oracle databases as endpoints. The deployment scripts reside on a shared file system from with database administrators can download. There are two different versions of these deployment scripts. One script is for only the first node (which is called lead node in this procedure) and the other script is for all other nodes (which are called secondary nodes in this procedure). You can use these scripts for an Oracle RAC or an Oracle Data Guard environment.

Another component that the Oracle Key Vault administrators prepare and add to the deployment script is a configuration file that contains all details for the deployment scripts to connect to Oracle Key Vault.
Copy the two deployment scripts (primary.zip and secondary.zip) that an Oracle Key Vault administrator created for database administrators to download from a shared location.
1. Copy the primary.zip file to the lead node.
```
$ scp user@ip_address:/path/to/file/primary.zip .
```
2. Copy the secondary.zip file to each secondary node.
```
$ scp user@ip_address:/path/to/file/secondary.zip .
```
Extract the zip files.
1. On the lead node: Extract the primary.zip file.
```
$ unzip primary.zip
```
2. On the secondary nodes: Extract the secondary.zip file.
```
$ unzip secondary.zip
```
Create the following directories on all nodes:
For example:
```
$ mkdir -pv /u01/opt/oracle/product/okv 
$ mkdir -pv /u01/opt/oracle/product/tde 
$ mkdir -pv /u01/opt/oracle/product/tde_seps
```
In this specification:
- /u01/opt/oracle/product/okv is the Oracle_Key_Vault_installation_directory.
- /u01/opt/oracle/product/tde will store an auto-login wallet, which only contains the future Oracle Key Vault password, enabling an auto-login Oracle Key Vault configuration.
- /u01/opt/oracle/product/tde_seps will store an auto-login wallet, which only contains the future Oracle Key Vault password, providing the ability to hide the Oracle Key Vault password from the database administrator. Set the static parameter EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION to the /u01/opt/oracle/product/tde_seps directory.

Execute the primary-run-me.sh and secondary-run-me.sh scripts, which contain the commands for the RESTful API to execute in Oracle Key Vault.

The Oracle Key Vault RESTful services will execute these commands in order to register this database in Oracle Key Vault with unique wallet and endpoint names.

On the lead node: This script creates a shared wallet (for the lead and all secondary nodes) and an endpoint in Oracle Key Vault, associates this endpoint for the lead node with the shared wallet, and downloads and installs the Oracle Key Vault client into an existing installation directory.

$ more primary-run-me.sh
#!/bin/bash
export EP_NAME=${ORACLE_SID^^}_on_$(hostname -s)
export WALLET_NAME=${ORACLE_UNQNAME^^}${HOSTNAME//[!0-9]/}
curl -Ok https://192.168.1.181:5695/okvrestclipackage.zip --tlsv1.2
unzip -Voj okvrestclipackage.zip lib/okvrestcli.jar -d ./lib
cat > /home/oracle/deploy-OKV.sh << EOF
#!/bin/bash
okv manage-access wallet create --wallet ${WALLET_NAME} --unique FALSE
okv admin endpoint create --endpoint ${EP_NAME} --description "$(hostname -f) $(hostname -i)" --subgroup "USE CREATOR SUBGROUP" --unique FALSE
okv admin endpoint update --endpoint ${EP_NAME} --strict-ip-check TRUE
okv manage-access wallet set-default --wallet ${WALLET_NAME} --endpoint ${EP_NAME}
expect << _EOF
    set timeout 120
    spawn okv admin endpoint provision --endpoint ${EP_NAME} --location /etc/ORACLE/KEYSTORES/${ORACLE_UNQNAME^^}/okv --auto-login FALSE
    expect "Enter Oracle Key Vault endpoint password: "
    send "change-on-install\r"
    expect eof
_EOF
EOF

Secondary nodes: This script only creates an endpoint for the secondary nodes, associates the endpoint of the secondary nodes with the shared wallet, and downloads and installs the Oracle Key Vault client into the existing installation directory on each secondary node.

$ more run-me.sh
#!/bin/bash
export EP_NAME=${ORACLE_SID^^}_on_$(hostname -s)
export WALLET_NAME=${ORACLE_UNQNAME^^}${HOSTNAME//[!0-9]/}
curl -Ok --tlsv1.2 https://<OKV-IP-addr>:5695/okvrestclipackage.zip
unzip -Voj okvrestclipackage.zip lib/okvrestcli.jar -d ./lib
cat > /home/oracle/deploy-OKV.sh << EOF
#!/bin/bash
okv admin endpoint create --endpoint ${EP_NAME} --description "$(hostname -f) $(hostname -i)" --subgroup "USE CREATOR SUBGROUP" --unique FALSE
okv admin endpoint update --endpoint ${EP_NAME} --strict-ip-check TRUE
okv manage-access wallet set-default --wallet ${WALLET_NAME} --endpoint ${EP_NAME}
expect << _EOF
    set timeout 120
    spawn okv admin endpoint provision --endpoint ${EP_NAME} --location /etc/ORACLE/KEYSTORES/${ORACLE_UNQNAME^^}/okv --auto-login FALSE
    expect "Enter Oracle Key Vault endpoint password: "
    send "change-on-install\r"
    expect eof
_EOF
EOF

Execute the RESTful API on the lead node first, because all secondary nodes depend on the presence of the shared wallet in Oracle Key Vault that the lead node creates.

$ java -jar okvrestservices.jar -c config.ini

Output similar to the following should appear:

[Line 1 OK] [CREATE WALLET] [3E48990A-82A0-48BC-ACEC-FF80CB380D38]
[Line 2 OK] [CREATE ENDPOINT] [6FA40F80-558C-456A-84E3-25AE73B245DD]
[Line 3 OK] [SET DEFAULT WALLET] [FINRAC1_on_rac122a:FINRAC]
[Line 4 OK] [GET ENROLLMENT TOKEN] [FINRAC1_on_rac122a]
The endpoint software for Oracle Key Vault installed successfully.
[Line 4 OK] [PROVISION] 
[Line 4 OK] [CLEANUP]

Make a note of the UUID of the wallet, which appears in Line 1 and is in bold.

After you execute this command on the lead node, execute it on all secondary nodes.

After successful installation of the Oracle Key Vault client, execute the root.sh script to install the PKCS library on all nodes.

# Oracle_Key_Vault_installation_directory/bin/root.sh

The following output should appear:

Creating directory: /opt/oracle/extapi/64/hsm/oracle/1.0.0/
Copying PKCS library to /opt/oracle/extapi/64/hsm/oracle/1.0.0/
Setting PKCS library file permissions

Execute the Oracle Key Vault okvutil changepwd command on all nodes to change the password for the Oracle Key Vault client that you installed.
Because all database administrators downloaded the same deployment script, all databases have the same password into Oracle Key Vault. This step enables each database to have a unique password.
```
$ Oracle_Key_Vault_installation_directory/bin/okvutil changepwd -t wallet -l Oracle_Key_Vault_installation_directory/ssl/

Enter wallet password: default_password
Enter new wallet password: Oracle_Key_Vault_password
Confirm new wallet password: Oracle_Key_Vault_password
Wallet password changed successfully
```

On all nodes, add the Oracle Key Vault password into a local auto-login wallet to hide the Oracle Key Vault password from database administrators.

ADMINISTER KEY MANAGEMENT ADD SECRET 'Oracle_Key_Vault_password' 
FOR CLIENT 'OKV_PASSWORD' 
TO LOCAL AUTO_LOGIN KEYSTORE '/u01/opt/oracle/product/tde_seps';

Execute the following statement once on any node.

ALTER SYSTEM SET EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION='/u01/opt/oracle/product/tde_seps' 
SCOPE = SPFILE SID = '*';

Add the encryption wallet location in the sqlnet.ora file on all instances:
```
ENCRYPTION_WALLET_LOCATION =
    (SOURCE = (METHOD = OKV))
```

Add the environment variables for ORACLE_BASE and ORACLE_UNQNAME to srvctl.

Execute this command on any node.

$ srvctl setenv database -db database_name -t "ORACLE_UNQNAME=database_name, ORACLE_BASE=/u01/opt/oracle"

Restart the database.

Execute this command on any node.

$ srvctl stop database -db database_name -o immediate 
$ srvctl start database -db database_name

Optionally, define the database default encryption algorithm after applying Oracle patch 30398099.
By default, Oracle Database applies the AES128 algorithm to encryption clauses that do not specify an encryption algorithm. Patch 30398099 allows you to choose from the AES128, AES192, and AES256 encryption algorithms. If you have applied this patch, then you can execute the following command to set the encryption clause:
```
ALTER SYSTEM SET "_TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM" = 'encryption_algorithm' 
SCOPE = BOTH SID = '*';
```
In the root container, open the keystore, which opens the connection to Oracle Key Vault for the root container and all open PDBs.
Note that the Oracle Key Vault password has been replaced in all subsequent ADMINISTER KEY MANAGEMENT commands with EXTERNAL STORE, because the database automatically retrieves the Oracle Key Vault password from the local auto-login wallets that you created on all nodes in Step 8.
```
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN
IDENTIFIED BY EXTERNAL STORE 
CONTAINER = ALL;
```
In the root container, set the master encryption key.
```
ADMINISTER KEY MANAGEMENT SET KEY 
IDENTIFIED BY EXTERNAL STORE 
CONTAINER = CURRENT;
```
The EXTERNAL STORE clause replaces the Oracle Key Vault password on the SQL*Plus command line and hides it from database administrators, enabling separation of duty between database administrators and Oracle Key Vault administrators.
Create and activate a tagged master encryption key in all PDBs in this container.
The benefit of adding tagged master encryption keys to PDBs is that it enables you to easily identify keys that belong to a certain PDB.
1. Connect to each PDB and execute the following SELECT statement to create an ADMINISTER KEY MANAGEMENT command that contains the PDB name and time stamp as a tag for the PDB's master encryption key.
```
CONNECT C##SEC_ADMIN@pdb_name AS SYSKM
Enter password: password

SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG '''||SYS_CONTEXT('USERENV', 'CON_NAME')||' '|| 
TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||''' 
IDENTIFIED BY EXTERNAL STORE;' "SET KEY COMMAND" FROM DUAL;
```
2. Execute the generated output of this SELECT statement.
  For example:
```
ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG 'pdb_name date time' 
IDENTIFIED BY EXTERNAL STORE;
```
On all nodes, add the Oracle Key Vault password into an auto-login wallet to enable auto-login connection into Oracle Key Vault.
This step is mandatory in Oracle RAC. Having an auto-login connection into Oracle Key Vault is especially important when Oracle RAC nodes are automatically restarted (for example, while applying quarterly release upgrades using the opatchauto patch tool).
```
ADMINISTER KEY MANAGEMENT ADD SECRET 'Oracle_Key_Vault_password' 
FOR CLIENT 'HSM_PASSWORD' 
TO LOCAL AUTO_LOGIN KEYSTORE '/u01/opt/oracle/product/tde';
```
Change the default behavior of the database to always encrypt new tablespaces with the AES128 algorithm (or the algorithm that you specified in Step 13), even if the CREATE TABLESPACE command does not contain the encryption clauses.
Execute this statement once on any node:
```
ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS SCOPE = BOTH SID = '*';
```

On both instances, change sqlnet.ora to look like the following example:

ENCRYPTION_WALLET_LOCATION =
   (SOURCE = (METHOD = OKV)
     (METHOD_DATA =
      (DIRECTORY = /u01/opt/oracle/product/tde)))

Log in to the PDB and create a tablespace. For example, to create a tablespace named protected:
```
CREATE TABLESPACE protected DATAFILE SIZE 50M;
```

Confirm that the tablespace is encrypted even though the encryption clauses were omitted.

SELECT C.NAME AS pdb_name, T.NAME AS tablespace_name, E.ENCRYPTIONALG AS ALG 
FROM V$TABLESPACE T, V$ENCRYPTED_TABLESPACES E, V$CONTAINERS C 
WHERE E.TS# = T.TS# AND E.CON_ID = T.CON_ID AND E.CON_ID = C.CON_ID 
ORDER BY E.CON_ID, T.NAME;

Create a table in the encrypted tablespace that you just created.
For example:
```
CREATE TABLE SYSTEM.test TABLESPACE protected 
AS SELECT * FROM DBA_OBJECTS;
```

Select from this table to confirm that you can read encrypted data:

SELECT COUNT(*), OWNER FROM SYSTEM.test 
GROUP BY OWNER 
ORDER BY 1 DESC;

In the PDBs, encrypt the existing application tablespaces.
If you omit the encryption algorithm, then the default algorithm (AES128, or the algorithm that you specified in Step 13) is applied.
```
ALTER TABLESPACE tablespace_name ENCRYPTION ONLINE ENCRYPT;
```
Optionally, validate the configuration.
1. Confirm that the auto-login for Oracle Key Vault is working.
  You can test this by restarting the database, logging into the PDB, and then selecting from the encrypted table. To restart the database:
```
$ srvctl stop database -db database_name -o immediate
$ srvctl start database -db database_name
```
  After logging in to the PDB, select from the SYSTEM.test table.
```
SELECT COUNT(*), OWNER FROM SYSTEM.test 
GROUP BY OWNER 
ORDER BY 1 DESC;
```
2. Confirm that the master encryption key re-key operations in all open PDBs are successful.
  First, as a user who has the SYSKM administrative privilege, execute the following SELECT statement to create an ADMINISTER KEY MANAGEMENT command that contains the PDB name and time stamp.
```
SELECT ' ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG '''||SYS_CONTEXT('USERENV', 'CON_NAME')||' '|| 
TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS')||''' 
FORCE KEYSTORE IDENTIFIED BY EXTERNAL STORE;' "RE-KEY COMMAND" FROM DUAL;
```
  Next, execute the generated output of this SELECT statement.
```
ADMINISTER KEY MANAGEMENT SET KEY 
USING TAG 'pdb_name date time' 
FORCE KEYSTORE IDENTIFIED BY EXTERNAL STORE;
```
3. Drop the protected tablespace and its table, test.
```
DROP TABLESPACE protected
INCLUDING CONTENTS AND DATAFILES;
```

Related Topics

Supported Encryption and Integrity Algorithms

Parent topic: How Transparent Data Encryption Works with Oracle Real Application Clusters

6.4 How Transparent Data Encryption Works with SecureFiles

SecureFiles, which stores LOBS, has three features: compression, deduplication, and encryption.

About Transparent Data Encryption and SecureFiles
SecureFiles encryption uses TDE to provide the encryption facility for LOBs.
Example: Creating a SecureFiles LOB with a Specific Encryption Algorithm
The CREATE TABLE statement can create a SecureFiles LOB with encryption specified.
Example: Creating a SecureFiles LOB with a Column Password Specified
The CREATE TABLE statement can create a SecureFiles LOB with a column password.

Parent topic: Using Transparent Data Encryption with Other Oracle Features

6.4.1 About Transparent Data Encryption and SecureFiles

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.

Parent topic: How Transparent Data Encryption Works with SecureFiles

6.4.2 Example: Creating a SecureFiles LOB with a Specific Encryption Algorithm

The CREATE TABLE statement can create a SecureFiles LOB with encryption specified.

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

Example 6-1 Creating a SecureFiles LOB with a Specific Encryption Algorithm

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

Parent topic: How Transparent Data Encryption Works with SecureFiles

6.4.3 Example: Creating a SecureFiles LOB with a Column Password Specified

The CREATE TABLE statement can create a SecureFiles LOB with a column password.

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

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

Example 6-2 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
    );

Parent topic: How Transparent Data Encryption Works with SecureFiles

6.5 How Transparent Data Encryption Works in a Multitenant Environment

In a multitenant environment, the TDE operations that you can perform depend on whether you are in the root or a PDB.

About Using Transparent Data Encryption in a Multitenant Environment
TDE can be used for both columns and tablespaces in a multitenant environment.
Operations That Must Be Performed in Root
You must perform specific ADMINISTER KEY MANAGEMENT keystore operations only in the root.
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.
Moving PDBs from One CDB to Another
You can clone or relocate encrypted PDBs within the same container database, or across container databases.
Exporting and Importing TDE Master Encryption Keys for a PDB
The EXPORT and IMPORT clauses of ADMINISTER KEY MANAGEMENT EXPORT can export or import TDE master encryption keys for a PDB.
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.
Managing Cloned PDBs with Encrypted Data
You can clone a PDB that has encrypted data in a CDB.
How Keystore Open and Close Operations Work in a Multitenant Environment
You should be aware of how keystore open and close operations work in a multitenant environment.
Finding the Keystore Status for All of the PDBs in a Multitenant Environment
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.

Parent topic: Using Transparent Data Encryption with Other Oracle Features

6.5.1 About Using Transparent Data Encryption in a Multitenant Environment

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.

6.5.2 Operations That Must Be Performed in Root

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 an external keystore, using the ADMINISTER KEY MANAGEMENT SET ENCRYPTION KEY ... MIGRATE USING statement
Reverse migrating from an external keystore 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

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.

Parent topic: How Transparent Data Encryption Works in a Multitenant Environment

6.5.3 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

Parent topic: How Transparent Data Encryption Works in a Multitenant Environment

6.5.4 Moving PDBs from One CDB to Another

You can clone or relocate encrypted PDBs within the same container database, or across container databases.

If you are trying to move a non-CDB or a PDB in which the SYSTEM, SYSAUX, UNDO, or TEMP tablespace is encrypted, and using the manual export or import of keys, then you must first import the keys for the non-CDB or PDB in the target database's CDB$ROOT before you create the PDB. Import of the keys are again required inside the PDB to associate the keys to the PDB.

Clone or relocate the non-CDB or PDB using the following syntax:

CREATE|RELOCATE PLUGGABLE DATABASE database_name KEYSTORE 
IDENTIFIED BY EXTERNAL STORE|target_keystore_password NO REKEY;<

Parent topic: How Transparent Data Encryption Works in a Multitenant Environment

6.5.5 Exporting and Importing TDE Master Encryption Keys for a PDB

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

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.

Parent topic: How Transparent Data Encryption Works in a Multitenant Environment

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

You can 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.

You should include the FORCE KEYSTORE clause if the root has an auto-login keystore or if the keystore is closed. If the keystore has been configured to use an external store for the password, then use the IDENTIFIED BY EXTERNAL STORE clause. For example, to perform an export operation for this scenario:

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

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.

Note:

Within a PDB, you can only export the keys of a PDB as a whole. The ability to export them selectively based on a query or an identifier is restricted to the root.

Parent topic: Exporting and Importing TDE Master Encryption Keys for a PDB

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

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.
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 software keystore, then you can optionally include the FORCE KEYSTORE clause in the ADMINISTER KEY MANAGEMENT statement when you perform the export or import operation. This clause enables you to open a software keystore during the operation without having to separately open the auto-login keystore or the password-based keystore.

For example:
```
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE IDENTIFIED BY keystore_password;
```
Perform the export or import operation.

6.5.5.3 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 6-3 shows how to export a TDE master encryption key from the PDB hr_pdb1. In this example, the FORCE KEYSTORE clause is included in case the auto-login keystore is in use, or if the keystore is closed.

Example 6-3 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' FORCE KEYSTORE IDENTIFIED BY password_cdb1;

Parent topic: Exporting and Importing TDE Master Encryption Keys for a PDB

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

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

Example 6-4 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' FORCE KEYSTORE IDENTIFIED BY password_cdb2 WITH BACKUP;

Parent topic: Exporting and Importing TDE Master Encryption Keys for a PDB

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

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 External Keystore
You can unplug a PDB from one CDB that has been configured with an external keystore and then plug it into another CDB also configured with an external keystore.
Plugging a PDB That Has Master Keys Stored in an External Keystore
The ADMINISTER KEY MANAGEMENT statement can import an external keystore master key to a PDB that has been moved to another CDB.

Parent topic: How Transparent Data Encryption Works in a Multitenant Environment

6.5.6.1 Unplugging a PDB That Has Encrypted Data

You can unplug a PDB from one CDB and then plug it into another CDB.

The database that is unplugged contains data files and other associated files. Because each PDB can have its own unique keystore, you do not need to export the TDE master encryption key of the PDB that you want to unplug.

Unplug the PDB as you normally unplug PDBs, as described in Oracle Database Administrator’s Guide.

Parent topic: Unplugging and Plugging a PDB with Encrypted Data in a CDB

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

When you plug an unplugged PDB into another CDB, the key version is set to 0 because this operation invalidates the history of the previous keys. You can check the key version by querying the KEY_VERSION column of the V$ENCRYPTED_TABLESPACES dynamic view. Similarly, if a control file is lost and recreated, then the previous history of the keys is reset to 0.

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.

If you want to create the PDB by cloning another PDB or from a non-CDB, and if the source database has encrypted data or a keystore set, then you must provide the keystore password by including the keystore identified by keystore_password clause in the CREATE PLUGGABLE DATABASE ... FROM SQL statement. You must provide this password even if the source database is using an auto-login software keystore. You can find if the source database has encrypted data or a keystore by querying the DBA_ENCRYPTED_COLUMNS data dictionary view.
Import the TDE master encryption key into the PDB.

See Exporting and Importing TDE Master Encryption Keys for a PDB.
Close the PDB and then re-open the PDB, as described in Oracle Database Administrator’s Guide.
Open the keystore.

See the following sections:
- Step 3: Open the Software Keystore
- Step 2: Open the External Keystore
Set the TDE master encryption key for the PDB.

See the following topics:

Parent topic: Unplugging and Plugging a PDB with Encrypted Data in a CDB

6.5.6.3 Unplugging a PDB That Has Master Keys Stored in an External Keystore

You can unplug a PDB from one CDB that has been configured with an external keystore and then plug it into another CDB also configured with an external keystore.

Unplug the PDB.
Move the master keys of the unplugged PDB in the external keystore that was used at the source CDB to the external keystore that is in use at the destination CDB.

See Also:

Oracle Database Administrator’s Guide for information about unplugging PDBs

Parent topic: Unplugging and Plugging a PDB with Encrypted Data in a CDB

6.5.6.4 Plugging a PDB That Has Master Keys Stored in an External Keystore

The ADMINISTER KEY MANAGEMENT statement can import an external keystore master key to a PDB that has been moved to another CDB.

Plug that unplugged PDB into the destination CDB that has been configured with the external keystore.
After the plug-in operation, the PDB that has been plugged in will be in restricted mode.
Ensure that the master keys from the HSM that has been configured with the source CDB are available in the external keystore of the destination CDB.
Log in to the plugged 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.

Open the master encryption key of the plugged PDB.

For example, for a PDB called PDB1:

ALTER SESSION SET CONTAINER = PDB1;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "keystore_passsword";

Import the external keystore master key into the PDB.

ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "HSM" FROM 'HSM' IDENTIFIED BY "keystore_password";

Restart the PDB.

ALTER PLUGGABLE DATABASE PDB1 CLOSE;
ALTER PLUGGABLE DATABASE PDB1 OPEN;

See Also:

Oracle Database Administrator’s Guide for information about plugging PDBs

Parent topic: Unplugging and Plugging a PDB with Encrypted Data in a CDB

6.5.7 Managing Cloned PDBs with Encrypted Data

You can clone a PDB that has encrypted data in a CDB.

About Managing Cloned PDBs That Have Encrypted Data
When you clone a PDB, you must make the master key of the source PDB available to cloned PDB.
Cloning a PDB with Encrypted Data in a CDB
The CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause can clone a PDB that has encrypted data.
TDE Academy Videos: Remotely Cloning and Upgrading Encrypted PDBs
The Oracle TDE Academy provides videos on how to remotely clone and upgrade encrypted pluggable databases (PDBs).

Parent topic: How Transparent Data Encryption Works in a Multitenant Environment

6.5.7.1 About Managing Cloned PDBs That Have Encrypted Data

When you clone a PDB, you must make the master key of the source PDB available to cloned PDB.

This allows a cloned PDB to operate on the encrypted data. To perform the clone, you do not need to export and import the keys because Oracle Database transports the keys for you even if the cloned PDB is in a remote CDB. However, you will need to provide the keystore password of the CDB where you are creating the clone.

Parent topic: Managing Cloned PDBs with Encrypted Data

6.5.7.2 Cloning a PDB with Encrypted Data in a CDB

The CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause can clone a PDB that has encrypted data.

Log in to the root as a user who was granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege.
For example:
```
sqlplus c##sec_admin as syskm
Enter password: password
Connected.
```
Ensure that the software keystore of the PDB that you plan to clone is open.
You can query the STATUS column of the V$ENCRYPTION_WALLET view to find if the software keystore is open.

For example:
```
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE IDENTIFIED BY keystore_password;
```
Use the CREATE PLUGGABLE DATABASE statement with the KEYSTORE IDENTIFIED BY clause to clone the PDB.
For example:
```
CREATE PLUGGABLE DATABASE cdb1_pdb3 FROM cdb1_pdb1 FILE_NAME_CONVERT=('cdb1_pdb1', 'pdb3/cdb1_pdb3') KEYSTORE IDENTIFIED BY "keystore_password";
```
Replace keystore_password with the password of the keystore of the CDB where the cdb1_pdb3 clone is created.

After you create the cloned PDB, encrypted data is still accessible by the clone using the master encryption key of the original PDB. After a PDB is cloned, there may be user data in the encrypted tablespaces. This encrypted data is still accessible because the master key of the source PDB is copied over to the destination PDB. Because the clone is a copy of the source PDB but will eventually follow its own course and have its own data and security policies, you should rekey the master key of the cloned PDB.
Rekey (rotate) the master key of the cloned PDB.
For example:
```
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY keystore_password WITH BACKUP USING 'emp_key_backup';
```
Before you rekey the master key of the cloned PDB, the clone can still use master encryption keys that belong to the original PDB. However, these master keys do not appear in the cloned PDB V$ dynamic views. Rekeying the master key ensures that the cloned PDB uses its own unique keys, which will be viewable in the V$ views.

See Also:

Step 3: Open the Software Keystore
Oracle Database Administrator’s Guide for information about cloning a PDB
Rotating the TDE Master Encryption Key

Parent topic: Managing Cloned PDBs with Encrypted Data

6.5.7.3 TDE Academy Videos: Remotely Cloning and Upgrading Encrypted PDBs

The Oracle TDE Academy provides videos on how to remotely clone and upgrade encrypted pluggable databases (PDBs).

Parent topic: Managing Cloned PDBs with Encrypted Data

6.5.8 How Keystore Open and Close Operations Work in a Multitenant Environment

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 external 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:

You can create a separate keystore password for each PDB in the multitenant environment.
Before you can manually open a software password-based or external 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.

Parent topic: How Transparent Data Encryption Works in a Multitenant Environment

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

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.

The V$ENCRYPTION_WALLET view displays the status of the keystore in a PDB, whether it is open, closed, uses a software or external keystore, and so on.

To create a function that uses theV$ENCRYPTION_WALLET view to find the keystore status, use the CREATE PROCEDURE PL/SQL statement.

Example 6-5 shows how to create this function.

Example 6-5 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;

Parent topic: How Transparent Data Encryption Works in a Multitenant Environment

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

Parent topic: Using Transparent Data Encryption with Other Oracle Features

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

Parent topic: Using Transparent Data Encryption with Other Oracle Features

6.8 Configuring Transparent Data Encryption to Work in a Multidatabase Environment

Each Oracle database on the same server (such as databases sharing the same Oracle binary but using different data files) must access its own TDE 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.

Caution:

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

See Also:

SQL*Plus User's Guide and Reference for more information and examples of setting the TNS_ADMIN variable

Parent topic: Using Transparent Data Encryption with Other Oracle Features