How to Plug and Unplug a PDB in TDE Environment

Unplug and Plug of PDB in a Transparent Data Encryption (TDE) Environment

Within an Oracle environment utilizing Transparent Data Encryption (TDE), the process of transferring a pluggable database (PDB) may necessitate additional procedures. In this blog post, we will explore the potential steps involved when performing an Unplug and Plug operation for a pluggable database.

Our intention is to perform an unplugging operation on the pluggable database EBSPDB from the PROD environment, followed by plugging it into the TEST environment. It’s important to note that both containers have Transparent Data Encryption (TDE) configured.

Source Environment: PROD Database with TDE

SQL> show EBSPDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 EBSPDB$SEED                       READ ONLY  NO
         3 EBSPDB                            READ WRITE NO

Target Environment: TEST Database with TDE

SQL> show EBSPDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 EBSPDB$SEED                       READ ONLY  NO

         3 TEST2                             READ WRITE NO

1. Export the encryption key on the source EBSPDB

We have to export the encryption key on the source container PROD while connecting to EBSPDB.

The option force is needed if AUTO LOGIN is used

SQL> alter session set container=EBSPDB;

SQL> ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "<passpharase>" TO '/u01/oracle/tde/export.p12' FORCE keystore IDENTIFIED BY <passpharase>;

keystore altered.

Verify that the key was exported successfully on the host directory.

ls -l /u01/oracle/tde/export.p12

-rw-r--r--. 1 oracle oinstall 3411 Feb 12 13:34 /u01/oracle/tde/export.p12

2. Unplugging EBSPDB from Source Database PROD 

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> alter pluggable database EBSPDB close;

Pluggable database altered.

SQL> show EBSPDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 EBSPDB$SEED                       READ ONLY  NO

         3 EBSPDB                           MOUNTED

         5 EBSPDBCLONE                      MOUNTED

SQL> ALTER PLUGGABLE DATABASE EBSPDB UNPLUG INTO '/u01/oracle/EBSPDB.xml';

Pluggable database altered.

3. Plugging the database into the Target TEST Environment 

SQL> show parameter db_name

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_name                              string      TEST

SQL> show con_name

CON_NAME

------------------------------

CDB$ROOT

SQL> CREATE PLUGGABLE DATABASE EBSPDB USING '/u01/oracle/EBSPDB.xml';

Pluggable database created.

Warning: EBSPDB altered with errors.      

4. Import the key into EBSPDB on TEST

Next, we will proceed with importing the exported key into EBSPDB on the TEST environment.

If EBSPDB is not already open in read/write (R/W) mode, please ensure to open it in such mode before proceeding with the import.

SQL> alter pluggable database EBSPDB open;

Warning: EBSPDB altered with errors.

SQL> show EBSPDBs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 EBSPDB$SEED                       READ ONLY  NO

         3 TEST2                           READ WRITE NO

         4 EBSPDB                           READ WRITE YES

If the keystore on the pluggable database (PDB) is not already open, please ensure to open it.

SQL> alter session set container=EBSPDB;

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ********;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET ******** FROM '/u01/oracle/tde/export.p12' IDENTIFIED BY ******** WITH BACKUP;

keystore altered.

SQL> show con_name

CON_NAME

------------------------------

EBSPDB

SQL> shutdown

Pluggable Database closed.

SQL> startup

Pluggable Database opened.

SQL> conn / as sysdba

Connected.

SQL> alter pluggable database EBSPDB close;

Pluggable database altered.

SQL> alter pluggable database EBSPDB open;

Pluggable database altered.

SQL> alter pluggable database all save state;

Pluggable database altered.

Subsequently, we can proceed to verify on EBSPDB whether the encrypted objects are accessible.