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.

Cleaning Up older records in AUD$ table

The AUD$ table is primarily used for the purpose of database auditing, including:

1. Storing audit records: The AUD$ table serves as the repository for storing the audit records generated by Oracle Database during or after the execution of audited SQL statements.

2. Tracking user actions: The table records information about various user actions, such as logins, logouts, data modifications, privilege changes, and other database activities.

3. Compliance and security: The audit records stored in the AUD$ table are essential for meeting regulatory compliance requirements and ensuring the security of the database. They provide an audit trail that can be reviewed for investigation, analysis, and forensic purposes.

4. Monitoring user activity: By analyzing the data in the AUD$ table, administrators can monitor and analyze user activity to identify potential security breaches, suspicious behavior, or policy violations.

5. Reporting and analysis: The AUD$ table data can be queried and analyzed to generate reports on user actions, system usage, and overall database activity, enabling administrators to gain insights and make informed decisions regarding database performance, security, and compliance.

While the AUD$ table is critical for auditing purposes, it’s important to manage its growth effectively to avoid performance impacts on the database. This can involve implementing retention policies, archiving old audit data, or utilizing other auditing features available in Oracle Database to strike a balance between auditing requirements and performance considerations.

SQL> show parameter                                                                 

    NAME           TYPE    VALUE
--------------   -------   -----  
audit_trail       string    DB

SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024/1024) as SizeinGB from dba_segments where segment_name=’AUD$’;

OWNER SEGMENT_NAME     SEGMENT_TYPE TABLESPACE_NAME SizeinGB
------ -------------- ------------ ---------------- ----------
SYS         AUD$        TABLE          SYSTEM          115

CREATE TABLESPACE AUDIT_TS DATAFILE ‘/path/to/audit_ts.dbf’ SIZE 10G AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED;

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'AUDIT_TS');
END;
/

SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024/1024) as SizeinGB from dba_segments where segment_name=’AUD$’;

OWNER SEGMENT_NAME     SEGMENT_TYPE TABLESPACE_NAME SizeinGB
------ -------------- ------------ ---------------- ----------
SYS         AUD$        TABLE          AUDIT_TS       115

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

BEGIN
DBMS_AUDIT_MGMT.init_cleanup(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,default_cleanup_interval => 12 /* set_hours */);
END;
/

SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/

Oracle Database 19C Enterprise Manager Express Not Working

Our client, who is currently utilizing Oracle 19c Database, has expressed their desire to leverage Oracle Enterprise Manager (OEM) to monitor and analyze their database usage.

Now Let us check some values from the database

SQL> select dbms_xdb_config.gethttpsport from dual;
GETHTTPSPORT
------------
   0

SQL> select dbms_xdb_config.gethttpport from dual;
GETHTTPPORT
 ------------ 
   0 

As we see ‘0’ in both the above statements we will execute the procedures given below.

SQL> exec dbms_xdb_config.sethttpsport(5500);

SQL> exec dbms_xdb_config.sethttpport(5550);

Let’s assume that you still cannot able to connect, check the wallet files. Check the directory of wallet files, run this command fist;

[oracle@devdb]# lsnrctl stat | grep HTTP

output is;

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/devdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))

Now you have to copy the wallet directory and run ls -l command to check the permission of the wallet files.

[oracle@devdb]# cd /u01/app/oracle/admin/devdb/xdb_wallet
[oracle@devdb xdb_wallet]# ls -l
-rw——-. 1 oracle oracle 3880 Jan 16 10:24 cwallet.sso
-rw——-. 1 oracle oracle 3835 Jan 16 10:23 ewallet.p12

Now as you see it is having the permission on user level only and not the Group level. You have to set it with -rw-r. Changing mode to 640 to allow for the group to read the wallet files makes the EM Express page available via browser.

[root@devdb xdb_wallet]#chmod 640 ewallet.p12

oracle@devdb xdb_wallet]#chmod 640
[oracle@devdb xdb_wallet# ls -l
-rw-r—–. 1 oracle oracle 3880 Jan 16 10:35 cwallet.sso
-rw-r—–. 1 oracle oracle 3835 Jan 16 10:35 ewallet.p12

Now you can connect with browser.

https://devdb:5500/em