Linking & provisioning TDE Enabled (19c) Oracle EBS PDB

Pre-requisites

1. Oracle: Multi-Tenant TDE for VCDBs
   Make sure to use transparent data encryption (TDE) with virtual container databases to secure data-at-rest. Existing TDE keys can be used or data can be rekeyed upon deployment. Automated KeyStore sanitization ensures production TDE keys are not shared with non-production environments. This workflow allows you to automate the Oracle data lifecycle end-to-end, which results in developer productivity enhancements.

2. For a cluster target, it is recommended to place auto-login keystore on the ACFS storage instead of the Delphix mounted storage.

3. The Oracle EBS database version must be 19c  (12c, 11g is not supported).

4. With 7.0.0.0 release, existing dSources can now have encrypted system tablespaces, and thus the resulting vPDBs will also have encrypted system tablespaces.  Plugin doesn’t support encrypting an existing unencrypted vPDB - the encryption comes from the dSource.
   Prior to 6.0.15.0, If the source has encrypted system tablespaces(SYSTEM, SYSAUX, UNDOTBS*), linking or ingestion of the source in Delphix Engine would fail.

5. Source database keystores must not be on ASM storage.

6. Only software keystores on the same host as the database files are supported. In particular, Oracle Key Vault is not supported.

7. The dSource from which the initial provision is done must be encrypted when it is linked. Existing dSources cannot be encrypted without unlinking and creating a new dSource.

Points to pay attention to:

• A CDB with one PDB (single tenant) is currently the only certified deployment for Oracle E-Business Suite with Database 19c.

• A CDB with multiple PDBs (multi-tenant) is not currently certified for Oracle E-Business Suite.

• A non-CDB architecture is not planned to be certified or supported for EBS with Database 19c.

High-level steps:

1. Create virtual dbTechStack using plugin, Make sure to remember the names of CDB, PDB that end user input in Delphix Engine UI wizard, as same will be used during vCDB and vPDB

2. Refresh target host environment in DE UI

3. Copy source DB keystore (WALLET_ROOT) files cwallet.sso, ewallet.p12 to target server.

4. During VDB provision, use Create a new container database option on Delphix Engine UI to create vCDB & vPDB.

5. Create new EBS services in vPDB.  To avoid confusion, delete the EBS services that have drifted downstream from dSource to target vPDB.

6. Create virtual AppsTier using EBS plugin.

Overview

Provisioning a Virtual Pluggable Database (vPDB) first involves using the GUI or CLI to specify the vPDB parameters (such as the vPDB name and target container) along with the snapshot to provision from. Once the provisioning is started with these parameters, the Delphix Engine does the following:

1. Mounts the snapshot files on the target host.

2. Creates and opens (in mount mode) the auxiliary container database on the target host, using the snapshot files. The auxiliary container database will have both the CDB and PDB data files from the dSource.

3. Completes recovery to bring the auxiliary container database into a consistent state.

4. Finalizes the state of the auxiliary database and unplugs the vPDB datafiles.

5. Plugs the vPDB into the target database, and opens it in read-write mode for general use.

If the dSource is TDE-enabled, then Delphix Engine will need to perform the following additional operations to complete the provision of a TDE-enabled vPDB to a TDE-enabled target container.

1. Mounts the snapshot files on the target host.

2. Creates a keystore with the necessary keys to apply encrypted archived log files.

3. Creates and opens (in mount mode) the auxiliary container database on the target host, using the snapshot files. The auxiliary container database will have both the CDB and PDB data files from the dSource.

4. Completes recovery to bring the auxiliary container database into a consistent state.

5. Rotates the vPDB and auxiliary CDB master encryption keys by generating new keys that are unique to the vPDB / auxiliary CDB and not associated with the source PDB or CDB.

6. Exports only the newly generated keys to an exported keyfile to enable unplug.

7. Finalizes the state of the auxiliary database and unplugs the vPDB datafiles.

8. Imports the keys from the exported keyfile into the target keystore.

9. When provisioning to a vCDB target, converts the auxiliary CDB into the final vCDB and creates the vCDB keystore from the auxiliary CDB keystore.

10. Plugs the vPDB into the target database, and opens it in read-write mode for general use.

If the initial provision of a TDE-enabled vPDB to a vCDB has either failed or been cancelled before step 9, which creates the vCDB keystore, refreshing that vPDB will fail because there is no target keystore to merge into the auxiliary CDB keystore for use during recovery. In this case, it will be necessary to delete the failed/cancelled vPDB and provision again. This does not apply when provisioning to a linked target CDB, which will already have its own keystore configured.

The following diagram illustrates the provisioning steps.

At each stage of provisioning, the keys and exported keyfiles are always on user storage. The exported keyfile is located in the artifact directory, while the auxiliary and target keystores are in the auxiliary keystores directory. Both the artifact directory and auxiliary keystores directory are subdirectories of the TDE keystores root directory, which is either user specified, or if not specified defaults to the toolkit root directory. As for non-TDE-enabled vPDBs, the final vPDB (and vCDB, if applicable) is on Delphix Engine storage while the target linked CDB and its archive logs remain on user storage.

Provisioning a TDE-enabled vPDB

To initiate the provision, Delphix needs the following pieces of information, all of which can be specified in the GUI or CLI:

Linking of source TDE PDB

1. Login to the Delphix Management application.

2. Go to Manage > Environments.

3. Click the Details tab.

4. Click on the edit icon next to ATTRIBUTES.

5. In the TDE Keystores Root field, enter the path for the TDE Keystores root path.

6. Click the tick mark button to save the configuration.

Adding or editing the TDE keystores root directory path

The TDE Keystores root directory path is specified on the Details tab under Environments. To edit the path, do the following:

1. Login to the Delphix Management application.

2. Go to Manage > Environments.

3. Click the Details tab of the environment.

4. Click on the edit icon next to ATTRIBUTES to set or update attributes, including the Keystores root directory path.

Adding or editing the target keystore password

The target Keystore password is specified on the Databases tab under Environments. To add or edit the target Keystore password, do the following:

1. Login to the Delphix Management application.

2. Go to Manage > Environments.

3. Click the Databases tab of the Environment.

4. Click on the edit icon next to TDE Keystore Password to set or update the password. 
   

   

5. Click on Add source. The Add dSource wizard displays.

6. Fill in the details and click Submit.

For more information, see Linking an Oracle pluggable database & Linking data sources with Oracle

After you have successfully linked the source PDB:

• Create a dbTechStack on the target server using the plugin.

• Refresh the target host environment in the Delphix Engine UI.

• Copy the source DB keystore (WALLET_ROOT) files cwallet.sso, ewallet.p12 to target server and mention that location in Parent Database TDE Keystore Location.

  CODE

  On Source CDB, run below query to get WALLET_ROOT(files cwallet.sso, ewallet.p12) location -
  select wrl_parameter from v$encryption_wallet;
  WRL_PARAMETER
  ------------------------
  /home/oravis/wallet/tde/

  It is mandatory to copy the ewallet.p12 file from the source to the target server for the target container database to use it. No exporting of keys needed, this is done by the Delphix engine itself using the path and secrets provided in the configuration. On target server, cwallet.sso, ewallet.p12 files should be owned by ORACLE_HOME installation owner.

The Parent Database TDE keystore Location is the location of the keystore for the dSource, which can be the actual location if it’s a provision back to the source, or else it needs to be copied to the target. the parent keystore password is the password for the dSource keystore. You can decide the TDE Secret for Exported Keys, which is a mandatory input and secret can be anything, it is used when exporting and importing the encryption keys.

Once a TDE-enabled vPDB is provisioned, it can be used the same as a non-TDE-enabled vPDB within Delphix, with the exception of migrate.  There are few caveats:

• A refresh operation will use the parent keystore for the recovery. If the dSource is rekeyed then the user will need to update the parent keystore with the new keys. Similarly, if the location or password to the parent keystore has changed then they should be updated before the refresh.

• A rewind operation will use the target keystore for the recovery. If the vPDB is rekeyed after it is provisioned, then the rekey will update the target keystore, so it does not need to be updated in Delphix.

• For a single vPDB in a vCDB, if the vCDB keystore location is changed, the new path must be updated in Delphix before refresh or rewind.

• Each disable operation will result in the keys being exported to an exported keyfile in the artifact directory, to be used for a subsequent enable. Refresh and rewind operations will first disable the existing vPDB, so those will also result in a new exported keyfile in the artifact directory.

• Provisioning a second-generation vPDB (vvPDB) from a TDE-enabled vPDB is done in the same manner as a first-generation vPDB, by specifying the TDE parameters during provision. The current keystore for the vPDB can be specified as the parent keystore.

TDE keystores root and artifact directory

The artifact directory stores the exported keyfiles used during the workflows for TDE-enabled vPDBs. It is located under the keystores root, in the directory oracle_tde_keystores. Each TDE-enabled vPDB will have its own directory within the oracle_tde_keystores directory, identified by the vPDB name, group name, and a unique identifier, separated by an underscore. If the keystores root directory is not specified, then it defaults to the toolkit directory path.

For example, if the keystores root directory is /work (or keystores root is not specified, and the toolkit directory is /work), the artifact directory for the vPDB tde_vpdb in the group Encrypted could be

/work/oracle_tde_keystores/tde_vpdb_Encrypted_ce7a47e6-8860-4398-bab0-cf0233fc5e3c

Within the artifact directory, there is a subdirectory exported_keys which contains within it the exported keyfiles for each timeflow associated with that vPDB. Each time an export is performed, a new exported keyfile is generated with a timestamp. The contents of the artifact directory may change for future releases, but the path to the artifact directory and the naming convention is not anticipated to change.

As the default keystores root directory root is at the same level as the toolkit directory, it will not be overwritten if a host is refreshed through the Delphix Engine and the toolkit updated. It is the customer's responsibility to maintain the artifact directory and ensure that the contents are not lost, as a disk failure could prevent a TDE-enabled vPDB from being accessed. Thus it is recommended that the keystores root directory be on a disk which is regularly backed up.

If a vPDB is moved to a different host (either through the migrate workflow or an enable after a failover, then the artifact directory will need to be copied to the new target host. See Migrating a TDE-enabled vPDB for details on the manual steps needed for migration.

The artifact directory is not removed when a TDE-enabled vPDB is deleted; the customer can remove it after confirming that the vPDB has been removed (including from any replicated Delphix Engines).

For TDE enabled vPDBs, use manual hooks in configure clone and Pre-snapshot. You should not use hooks utility feature with TDE enabled virtual database.

For versions 19.3 or later, follow the below guidelines if you see the following error:

• ORA-01017: invalid username/password; logon denied or ORA-28040 During cloning: No Matching Authentication Protocol

EBS application database user accounts created in the earlier release use a case-insensitive password version from an earlier release authentication protocol, such as the 10G password version. In case, if your database release user account passwords have not been reset and the following configuration persists:

On source: The database server has been configured with SEC_CASE_SENSITIVE_LOGON set to FALSE, so that it can only authenticate users who have a 10G case-insensitive password version, then, on Target container VDB, it becomes necessary to set the SEC_CASE_SENSITIVE_LOGON to FALSE, to make authentication of users successful.

To take advantage of the password protections introduced in Oracle Database 19c, users must change their passwords. Occasionally, it is necessary to restart the database to resolve connectivity to the database.  This is atypical but may be necessary.

To set-up target:

On target container database, alter system set SEC_CASE_SENSITIVE_LOGON=FALSE scope=both;

Configure clone first hook for a 19c multi-tenant high privileged user:

• Configure  clone hook will blindly create and start the services, No validation on services has been implemented, but this will get improve over time.

#!/usr/bin/env bash
#
# Copyright (c) 2023 by Delphix. All rights reserved.
#
# shellcheck source=/dev/null
# NOTE: Ensure the below environment variables will be set up correctly by the
# shell. If not, hardcode or generate the values below.
HOSTNAME=$(uname -n | cut -d '.' -f1)
CONTEXT_NAME=${DELPHIX_PDB_NAME}_${HOSTNAME}
SOURCE_PDB_NAME=$SOURCE_PDB_NAME_PASSWORD
APPS_PASSWD=$DLPX_SOURCE_APPS_PASSWORD_PASSWORD
. "${ORACLE_HOME}/${ORACLE_SID}_${HOSTNAME}.env";
# Check for local_listener parameter is set for PDB, otherwise set it
# appropriately
check_value=$(sqlplus -s "/ as sysdba" <<EOF
alter session set container="${DELPHIX_PDB_NAME}";
show parameter local_listener;
EOF
)
local_listener=$(echo "$check_value" | awk '{print $11}')
value=("${local_listener//:/ }")
host="${value[0]}"
port="${value[1]}"
curr_port=$(grep PORT < "${ORACLE_HOME}/network/admin/listener.ora" | awk '{print $9}' | sed 's/)//g')
if [[ $port != "$curr_port" || $host != "${HOSTNAME}" ]]; then
sqlplus -s "/ as sysdba" <<EOF
alter session set container="${DELPHIX_PDB_NAME}";
alter system set local_listener='${HOSTNAME}:${curr_port}';
alter system register;
EOF
fi

#Create, Start EBS PDB services and Delete Source PDB services
. "${ORACLE_HOME}/${ORACLE_SID}_$(hostname -s).env";
sqlplus -s "/ as sysdba" <<EOF
alter session set container="${DELPHIX_PDB_NAME}";
BEGIN DBMS_SERVICE.STOP_SERVICE( service_name => '${SOURCE_PDB_NAME}_ebs_patch'); end ;
/
BEGIN DBMS_SERVICE.STOP_SERVICE( service_name => 'ebs_${SOURCE_PDB_NAME}'); end;
/
BEGIN DBMS_SERVICE.DELETE_SERVICE( service_name => '${SOURCE_PDB_NAME}_ebs_patch'); end ;
/
BEGIN DBMS_SERVICE.DELETE_SERVICE( service_name => 'ebs_${SOURCE_PDB_NAME}'); end;
/
BEGIN DBMS_SERVICE.CREATE_SERVICE( service_name => 'ebs_${DELPHIX_PDB_NAME}', network_name => 'ebs_${DELPHIX_PDB_NAME}'); 
end;
/
BEGIN DBMS_SERVICE.CREATE_SERVICE( service_name => '${DELPHIX_PDB_NAME}_ebs_patch', network_name => '${DELPHIX_PDB_NAME}_ebs_patch'); 
end;
/
BEGIN DBMS_SERVICE.START_SERVICE( service_name => 'ebs_${DELPHIX_PDB_NAME}');    
end; 
/
BEGIN DBMS_SERVICE.START_SERVICE( service_name => '${DELPHIX_PDB_NAME}_ebs_patch');    
end; 
/
alter system register;
EOF

#For compatibility with version 6.0.16.0 or later
. ${ORACLE_HOME}/${ORACLE_SID}_${HOSTNAME}.env; sqlplus -s "/ as sysdba" <<EOF
shutdown immediate;
startup;
ALTER PLUGGABLE DATABASE ALL OPEN read write services=all;
alter pluggable database ${DELPHIX_PDB_NAME} open read write services=all;
alter pluggable database all save state instances=all;
EOF
sqlplus "/ as sysdba" <<EOF
@${ORACLE_HOME}/appsutil/install/${CONTEXT_NAME}/adupdlib.sql so
EOF
. "${ORACLE_HOME}/${CONTEXT_NAME}.env";
perl "${ORACLE_HOME}/appsutil/clone/bin/adcfgclone.pl" dbconfig "${ORACLE_HOME}/appsutil/${CONTEXT_NAME}.xml" <<EOF # noqa
${APPS_PASSWD}
EOF

Configure clone first hook for a 19c multi-tenant low privileged user:

#!/usr/bin/env bash
#
# Copyright (c) 2022 by Delphix. All rights reserved.
#
# shellcheck source=/dev/null
# NOTE: Ensure the below environment variables will be set up correctly by the
# shell. If not, hardcode or generate the values below.
# Input the SOURCE_PDB_NAME enclosed in double quotes.
# Input the DLPX_PRIV_USER according to your environment setup.
SOURCE_PDB_NAME="TDEPDB"
DLPX_DB_EXEC_SCRIPT="<Remote BIN location till dlpx_db_exec script>"
DLPX_PRIV_USER=oravis
SOURCE_PDB_NAME=$SOURCE_PDB_NAME_PASSWORD
APPS_PASSWD=$DLPX_SOURCE_APPS_PASSWORD_PASSWORD
CONTEXT_NAME=${DELPHIX_PDB_NAME}_$(hostname -s)

"${DLPX_DB_EXEC_SCRIPT}" -u"${DLPX_PRIV_USER}" ". ${ORACLE_HOME}/${ORACLE_SID}_$(hostname -s).env;"

# Check for local_listener parameter is set for PDB, otherwise set it appropriately
check_value=$("${DLPX_DB_EXEC_SCRIPT}" -u"${DLPX_PRIV_USER}" ". ${ORACLE_HOME}/${CONTEXT_NAME}.env; sqlplus -s \"/ as sysdba\" <<-EOF
alter session set container=${DELPHIX_PDB_NAME};
show parameter local_listener;
EOF
")

local_listener=$(echo "$check_value" | awk '{print $11}')
value=("${local_listener//:/ }")
host="${value[0]}"
port="${value[1]}"

curr_port=$(grep PORT < "${ORACLE_HOME}/network/admin/listener.ora" | awk '{print $9}' | sed 's/)//g')

if [[ $port != "$curr_port" || $host != "$(hostname -s)" ]]; then
    "${DLPX_DB_EXEC_SCRIPT}" -u"${DLPX_PRIV_USER}" ". ${ORACLE_HOME}/${ORACLE_SID}_$(hostname -s).env; sqlplus -s \"/ as sysdba\" <<EOF
    alter session set container=${DELPHIX_PDB_NAME};
    alter system set local_listener='$(hostname -s):${curr_port}';
    alter system register;
EOF
"
fi

#Create, Start EBS PDB services and Delete Source PDB services
"${DLPX_DB_EXEC_SCRIPT}" -u"${DLPX_PRIV_USER}" ". ${ORACLE_HOME}/${ORACLE_SID}_$(hostname -s).env; sqlplus -s "/ as sysdba" <<EOF
alter session set container="${DELPHIX_PDB_NAME}";
BEGIN DBMS_SERVICE.STOP_SERVICE( service_name => '${SOURCE_PDB_NAME}_ebs_patch'); end ;
/
BEGIN DBMS_SERVICE.STOP_SERVICE( service_name => 'ebs_${SOURCE_PDB_NAME}'); end;
/
BEGIN DBMS_SERVICE.DELETE_SERVICE( service_name => '${SOURCE_PDB_NAME}_ebs_patch'); end ;
/
BEGIN DBMS_SERVICE.DELETE_SERVICE( service_name => 'ebs_${SOURCE_PDB_NAME}'); end;
/
BEGIN DBMS_SERVICE.CREATE_SERVICE( service_name => 'ebs_${DELPHIX_PDB_NAME}', network_name => 'ebs_${DELPHIX_PDB_NAME}'); 
end;
/
BEGIN DBMS_SERVICE.CREATE_SERVICE( service_name => '${DELPHIX_PDB_NAME}_ebs_patch', network_name => '${DELPHIX_PDB_NAME}_ebs_patch'); 
end;
/
BEGIN DBMS_SERVICE.START_SERVICE( service_name => 'ebs_${DELPHIX_PDB_NAME}');    
end; 
/
BEGIN DBMS_SERVICE.START_SERVICE( service_name => '${DELPHIX_PDB_NAME}_ebs_patch');    
end; 
/
alter system register;
EOF
"

"${DLPX_DB_EXEC_SCRIPT}" -u"${DLPX_PRIV_USER}" ". ${ORACLE_HOME}/${ORACLE_SID}_$(hostname -s).env; sqlplus \"/ as sysdba\" <<-EOF
@${ORACLE_HOME}/appsutil/install/${CONTEXT_NAME}/adupdlib.sql so
EOF
"
"${DLPX_DB_EXEC_SCRIPT}" -u"${DLPX_PRIV_USER}" ". ${ORACLE_HOME}/${CONTEXT_NAME}.env; perl ${ORACLE_HOME}/appsutil/clone/bin/adcfgclone.pl dbconfig ${ORACLE_HOME}/appsutil/${CONTEXT_NAME}.xml <<-EOF1
${APPS_PASSWD}
EOF1
"

The remaining configure clone & Pre-snapshot hooks remains unchanged. It is mandatory to use hooks mentioned in documentation while provisioning EBS virtual PDB or database.

Refreshing and rewinding a TDE-enabled vPDB

Just like a non-TDE-enabled vPDB, a TDE-enabled vPDB can be refreshed from the dSource or rewound to a previous snapshot or point in time. In each case, no additional manual steps or input from the user is required. The first step of a refresh or rewind operation is to disable the existing vPDB, which will result in a new keyfile exported to the artifact directory. The appropriate snapshot files are then mounted for the auxiliary database so that it can be recovered and brought to a consistent state. Since the vPDB is TDE-enabled, a keystore is needed for the recover operation. For a refresh, the Delphix Engine will use the parent keystore, and for a rewind, the Delphix Engine will use the target keystore, as shown below.

Key rotation

There are two potential places for keys to be rotated in a vPDB environment:

1. dSource: If the dSource keys are rotated and a new snapshot taken with the new key, the customer is responsible for updating the parent keystore before refreshing from the later snapshot encrypted with the new key. The parent keystore would then contain both the new key and the original keys.

2. Target: If the target CDB keys are rotated, the target keystore will be updated. This is why the Delphix Engine uses the target keystore for rewind operations.

In either scenario, the keystore used for recovery will contain the current and all prior keys used to encrypt the datafiles and archive logs, for both the vPDB and CDB used in the auxiliary container.

vPDB encryption key management

During the provisioning process of a TDE-enabled vPDB, Delphix generates a unique encryption key for the vPDB. This unique key is not associated with the parent keystore to ensure that no keys from the parent are imported by the target. During refresh and rewind operations, Delphix reuses that key after recovery has finished. It is possible to customize the key that is used by updating the tdeKeyIdentifier parameter of the source via the CLI. If a valid key_id is entered for a key that is already present in the keystore, that key will be used as the active encryption key for the vPDB at the end of refresh/rewind. If the field is unset, Delphix will generate a new encryption key for the vPDB to be used from that point onward. This procedure is the same when using a vCDB, in which case Delphix will also generate a new unique encryption key for the vCDB that is reused for refresh and rewind, and which can be customized by updating the tdeKeyIdentifier parameter of the CDB source. See the CLI steps for Locating and updating the value of tde encryption key