()

Configure TDE (Transparent Data Encryption) in Oracle Database 19C-Multitenant


Transparent Data Encryption (TDE)
enables to encrypt sensitive data that stored in tables and tablespaces. TDE can be applied to individual columns or entire tablespaces. After the data is encrypted, this data is transparently decrypted for authorized users or applications when they access this data. TDE helps protect data stored on media (also called data at rest) if the storage media or data file is stolen. To prevent unauthorized decryption, TDE stores the encryption keys in an external security module (software or external Keystore) external to the database, called a Keystore. In a multitenant environment, we can configure Keystore for either the entire container database (CDB) called as United mode or for individual pluggable databases (PDBs) called as Isolated mode. Using online or offline encryption of existing un-encrypted tablespaces enables you to implement Transparent Data Encryption with little or no downtime. You do not need to modify your applications to handle the encrypted data the database manages the data encryption and decryption. All of the objects that are created in the encrypted tablespace are automatically encrypted. 

TDE tablespace encryption uses the two-tiered, key-based architecture to transparently encrypt (and decrypt) tablespaces. The TDE master encryption key is stored in an external security module (software or external Keystore). This TDE master encryption key is used to encrypt the TDE tablespace encryption key, which in turn is used to encrypt and decrypt data in the tablespace.

In this blog post, we are going to discuss Steps are needed to Implement Transparent Data Encryption (TDE) at Tablespace to level in 19c Multitenant.

 

Follow the below steps to configure TDE:
1. Test environment Setup
2. Configure the Software Keystore Location
3. Creating a Password-Protected Software Keystore
4. Open the Keystore

5. Set the TDE Master Encryption Key for CDB$ROOT & PDBs
6. Convert Password-Protected Software Keystore to AutoLogin

7. Take a full backup of Keystore
8. Convert non-encrypted Tablespace to encrypted Tablespace using Offline & Online method
9. Set parameter encrypt_new_tablespaces


Step 1. Test environment Setup: We will create two PDBs PDB1 & PDB2. Once PDBs are ready will create tablespaces TBLS1 & TBLS2. We will use these tablespaces to convert them from non-encryption to encryption using both offline and online methods.

[oracle@test-machine01 CDB1]$
[oracle@test-machine01 CDB1]$ mkdir -p /u01/oradata/PDB1
[oracle@test-machine01 CDB1]$ mkdir -p /u01/oradata/PDB2
[oracle@test-machine01 CDB1]$

[oracle@test-machine01 ~]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 27 16:34:20 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:
SQL> 
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
SQL>
SQL>

SQL> CREATE PLUGGABLE DATABASE PDB1 ADMIN USER PDB_ADMIN IDENTIFIED BY secret123
  FILE_NAME_CONVERT=('/u01/oradata/CDB1/pdbseed/','/u01/oradata/PDB1/'); 
  
Pluggable database created.

SQL> CREATE PLUGGABLE DATABASE PDB2 ADMIN USER PDB_ADMIN IDENTIFIED BY secret123
  FILE_NAME_CONVERT=('/u01/oradata/CDB1/pdbseed/','/u01/oradata/PDB2/');
  
Pluggable database created.

SQL>
SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SQL>


SQL>
SQL>
SQL>
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL>
SQL>
SQL> alter session set container=PDB1;

Session altered.

SQL>
SQL> create tablespace tbls1 datafile '/u01/oradata/PDB1/tbls1.dbf' size 100M autoextend on maxsize unlimited;

Tablespace created.

SQL>
SQL> create tablespace tbls2 datafile '/u01/oradata/PDB1/tbls2.dbf' size 100M autoextend on maxsize unlimited;

Tablespace created.

SQL>
SQL> alter session set container=PDB2;

Session altered.

SQL>
SQL>
SQL> create tablespace tbls1 datafile '/u01/oradata/PDB2/tbls1.dbf' size 100M autoextend on maxsize unlimited;

Tablespace created.

SQL>
SQL> create tablespace tbls2 datafile '/u01/oradata/PDB2/tbls2.dbf' size 100M autoextend on maxsize unlimited;

Tablespace created.

SQL>
SQL>


Step 2. Configure the Software Keystore Location: In previous releases, the SQLNET.ENCRYPTION_WALLET_LOCATION parameter was used to define the Keystore directory location. This parameter has been deprecated. Oracle recommends that you use the WALLET_ROOT static initialization parameter and TDE_CONFIGURATION dynamic initialization parameter instead. Oracle database 12c introduced a new way to manage keystores, encryption keys and secrets using the ADMINISTER KEY MANAGEMENT command. This replaces the ALTER SYSTEM SET ENCRYPTION KEY and ALTER SYSTEM SET ENCRYPTION WALLET commands for key and wallet administration from previous releases.

[oracle@test-machine01 u01]$
[oracle@test-machine01 u01]$ mkdir -p /u01/app/oracle/admin/cdb1/encryption_wallet/
[oracle@test-machine01 u01]$

[oracle@test-machine01 ~]$ sqlplus sys as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 27 16:34:20 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:
SQL>
SQL>
SQL>  alter system set wallet_root='/u01/app/oracle/admin/cdb1/encryption_wallet/' scope=spfile;

System altered.
SQL>
SQL> shut immediate
Database closed.
Database dismounted.

ORACLE instance shut down.
SQL> SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  838858864 bytes
Fixed Size                  9140336 bytes
Variable Size             721420288 bytes
Database Buffers          100663296 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> alter pluggable database all open;

Pluggable database altered.

SQL>
SQL>

SQL> alter system set tde_configuration="keystore_configuration=file";

System altered.

SQL>

 SQL> set lines 300
SQL> show parameter wallet_root

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
wallet_root                          string                           /u01/app/oracle/admin/cdb1/enc
                                                                      ryption_wallet/
SQL>

SQL> show parameter tde_configuration

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
tde_configuration                    string                           keystore_configuration=file
SQL>


Step 3. Creating a Password-Protected Software Keystore: Use the below command to create password protected Keystore. Once Keystore is created you will notice oracle created ewallet.p12 inside wallet_root location.

SQL>
SQL>  administer key management create keystore identified by WelcomE#_1234;

keystore altered.

SQL>
SQL> set pages 300
SQL> set lines 300
SQL> col WRL_PARAMETER for a50
SQL>
SQL> select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                 /u01/app/oracle/admin/cdb1/encryption_wallet//tde/ CLOSED                         UNKNOWN              SINGLE    NONE     UNDEFINED          1
FILE                                                                    CLOSED                         UNKNOWN              SINGLE    UNITED   UNDEFINED          2
FILE                                                                    CLOSED                         UNKNOWN              SINGLE    UNITED   UNDEFINED          3
FILE                                                                    CLOSED                         UNKNOWN              SINGLE    UNITED   UNDEFINED          4

SQL>
SQL>

[oracle@test-machine01 u01]$
[oracle@test-machine01 u01]$ ls -ltr /u01/app/oracle/admin/cdb1/encryption_wallet/
total 0
drwxr-x---. 2 oracle oinstall 25 Nov 28 22:47 tde
[oracle@test-machine01 u01]$
[oracle@test-machine01 u01]$ ls -ltr /u01/app/oracle/admin/cdb1/encryption_wallet/tde
total 4
-rw-------. 1 oracle oinstall 2555 Nov 28 22:47 ewallet.p12
[oracle@test-machine01 u01]$
[oracle@test-machine01 u01]$



Step 4. Open the Keystore: After Keystore is created it will be in closed status. We need to open Keystore using below command. You can check the status in view v$ENCRYPTION_WALLET. You will notice that status is showing OPEN_NO_MASTER_KEY as still, we didn’t create the master key.

SQL>
SQL>
SQL> administer key management set keystore open identified by WelcomE#_1234 container=all;

keystore altered.

SQL>
SQL>

SQL> set pages 300
SQL> set lines 300
SQL> col WRL_PARAMETER for a50
SQL> select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                 /u01/app/oracle/admin/cdb1/encryption_wallet//tde/ OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    NONE     UNDEFINED          1
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          2
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          3
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          4

SQL>
SQL>


Step 5. Set the TDE Master Encryption Key for CDB$ROOT & PDBs: Please note master key is separate for CDB$ROOT and each PDBs. Use the below command to create master key use options like tag and with backup to give the tag to your master key and to take immediate backup of your Keystore. Once the master key is created status will change from OPEN_NO_MASTER_KEY to OPEN.

SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> administer key management set key using tag 'master key' identified by WelcomE#_1234 with backup using 'masterbackup';

keystore altered.

SQL>
SQL>
SQL>
SQL> set pages 300
SQL> set lines 300
SQL> col WRL_PARAMETER for a50
SQL> select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                 /u01/app/oracle/admin/cdb1/encryption_wallet//tde/ OPEN                           PASSWORD             SINGLE    NONE     NO                 1
FILE                                                                    OPEN                           PASSWORD             SINGLE    UNITED   NO                 2
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          3
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          4

SQL>
SQL> col TAG for a50
SQL> col CREATION_TIME for a40
SQL> col ACTIVATION_TIME for a40
SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                                         CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------------------------------ ---------------------------------------- ---------------------------------------- --------------------------------------------------
AfuSzRNi+k+5v/lcIZWL9VgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA                           29-NOV-21 03.23.46.629706 AM +00:00      29-NOV-21 03.23.46.646848 AM +00:00      master key

SQL>

 
[oracle@test-machine01 u01]$
[oracle@test-machine01 u01]$ ls -ltr /u01/app/oracle/admin/cdb1/encryption_wallet/tde
total 12
-rw-------. 1 oracle oinstall 2555 Nov 29 06:23 ewallet_2021112903234616_masterbackup.p12
-rw-------. 1 oracle oinstall 4171 Nov 29 06:23 ewallet.p12
[oracle@test-machine01 u01]$
[oracle@test-machine01 u01]$


SQL>
SQL> alter session set container=PDB1;

Session altered.

SQL> select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          3

SQL> SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

no rows selected

SQL>
SQL> administer key management set key using tag 'pdb1 key' identified by WelcomE#_1234 with backup using 'masterbackup2';

keystore altered.

SQL>
SQL> select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                                                                    OPEN                           PASSWORD             SINGLE    UNITED   NO                 3

SQL>
SQL>  SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                                         CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------------------------------ ---------------------------------------- ---------------------------------------- --------------------------------------------------
AX3+BUpL8k/Dv5dnSAt74J0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA                           29-NOV-21 03.30.05.316987 AM +00:00      29-NOV-21 03.30.05.316990 AM +00:00      pdb1 key

SQL>
[oracle@test-machine01 u01]$
[oracle@test-machine01 u01]$ ls -ltr /u01/app/oracle/admin/cdb1/encryption_wallet/tde
total 20
-rw-------. 1 oracle oinstall 2555 Nov 29 06:23 ewallet_2021112903234616_masterbackup.p12
-rw-------. 1 oracle oinstall 4171 Nov 29 06:30 ewallet_2021112903300474_masterbackup2.p12
-rw-------. 1 oracle oinstall 5819 Nov 29 06:30 ewallet.p12
[oracle@test-machine01 u01]$

SQL>
SQL> alter session set container=CDB$ROOT;

Session altered.

SQL>  select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                 /u01/app/oracle/admin/cdb1/encryption_wallet//tde/ OPEN                           PASSWORD             SINGLE    NONE     NO                 1
FILE                                                                    OPEN                           PASSWORD             SINGLE    UNITED   NO                 2
FILE                                                                    OPEN                           PASSWORD             SINGLE    UNITED   NO                 3
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          4

SQL>  SELECT KEY_ID,creation_time,activation_time,tag FROM V$ENCRYPTION_KEYS;

KEY_ID                                                                         CREATION_TIME                            ACTIVATION_TIME                          TAG
------------------------------------------------------------------------------ ---------------------------------------- ---------------------------------------- --------------------------------------------------
AfuSzRNi+k+5v/lcIZWL9VgAAAAAAAAAAAAAAAAAAAAAAAAAAAAA                           29-NOV-21 03.23.46.629706 AM +00:00      29-NOV-21 03.23.46.646848 AM +00:00      master key
AX3+BUpL8k/Dv5dnSAt74J0AAAAAAAAAAAAAAAAAAAAAAAAAAAAA                           29-NOV-21 03.30.05.316987 AM +00:00      29-NOV-21 03.30.05.316990 AM +00:00      pdb1 key

SQL>


[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ pwd
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace
[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ vi alert_cdb1.log
2021-11-29T06:23:47.125263+03:00
Creating new database key for new master key and wallet
Creating new database key with the new master key
Switching out all online logs for the new master key
2021-11-29T06:23:47.365643+03:00
Thread 1 advanced to log sequence 40 (LGWR switch)
  Current log# 1 seq# 40 mem# 0: /u01/oradata/CDB1/redo01.log
2021-11-29T06:23:47.366537+03:00
Logfile switch for new master key complete
New database key and new master key created successfully
2021-11-29T06:30:05.868766+03:00
PDB1(3):Creating new database key for new master key and wallet
PDB1(3):Creating new database key with the new master key
PDB1(3):New database key and new master key created successfully
[oracle@test-machine01 trace]$
                                                                     


Step 6. Convert Password-Protected Software Keystore to AutoLogin: We need to convert Keystore to Autologin so Keystore is open by itself without waiting for manual Keystore open operation and DB is available immediately after restart to users. Test DB restart after Keystore is converted to autologin we will also notice oracle will create file cwallet.sso. If we tried to convert Keystore to autologin before the master key is created we may encounter the error “ORA-28417: password-based Keystore is not open“.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> administer key management create  auto_login keystore from keystore identified by WelcomE#_1234;

keystore altered.

SQL>  select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                 /u01/app/oracle/admin/cdb1/encryption_wallet//tde/ OPEN                           PASSWORD             SINGLE    NONE     YES                1
FILE                                                                    OPEN                           PASSWORD             SINGLE    UNITED   YES                2
FILE                                                                    OPEN                           PASSWORD             SINGLE    UNITED   YES                3
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          4

SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  838858864 bytes
Fixed Size                  9140336 bytes
Variable Size             721420288 bytes
Database Buffers          100663296 bytes
Redo Buffers                7634944 bytes
Database mounted.
Database opened.
SQL>
SQL>  select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                 /u01/app/oracle/admin/cdb1/encryption_wallet//tde/ OPEN                           AUTOLOGIN            SINGLE    NONE     NO                 1
FILE                                                                    OPEN                           AUTOLOGIN            SINGLE    UNITED   NO                 2
FILE                                                                    OPEN                           AUTOLOGIN            SINGLE    UNITED   NO                 3
FILE                                                                    OPEN_NO_MASTER_KEY             AUTOLOGIN            SINGLE    UNITED   UNDEFINED          4

SQL>


SQL>
SQL> !ls -ltr /u01/app/oracle/admin/cdb1/encryption_wallet//tde/
total 36
-rw-------. 1 oracle oinstall 2555 Nov 29 06:23 ewallet_2021112903234616_masterbackup.p12
-rw-------. 1 oracle oinstall 4171 Nov 29 06:30 ewallet_2021112903300474_masterbackup2.p12
-rw-------. 1 oracle oinstall 5819 Nov 29 06:46 ewallet.p12
-rw-------. 1 oracle oinstall 5864 Nov 29 06:50 cwallet.sso

SQL>



SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
SQL>
SQL> alter pluggable database all open;

Pluggable database altered.

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL>

SQL>


Step 7. Take a full backup of Keystore: Once all the above changes are done take a full backup of Keystore. Once the backup is taken column FULLY_BACKED_UP will change to YES

SQL>
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL>
SQL> administer key management backup keystore using 'Walletfullbackup' force keystore identified by WelcomE#_1234;

keystore altered.

SQL>
SQL> select * from v$ENCRYPTION_WALLET;

WRL_TYPE             WRL_PARAMETER                                      STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
FILE                 /u01/app/oracle/admin/cdb1/encryption_wallet//tde/ OPEN                           PASSWORD             SINGLE    NONE     YES                1
FILE                                                                    OPEN                           PASSWORD             SINGLE    UNITED   YES                2
FILE                                                                    OPEN                           PASSWORD             SINGLE    UNITED   YES                3
FILE                                                                    OPEN_NO_MASTER_KEY             PASSWORD             SINGLE    UNITED   UNDEFINED          4

SQL>

[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ ls -ltr /u01/app/oracle/admin/cdb1/encryption_wallet/tde
total 28
-rw-------. 1 oracle oinstall 2555 Nov 29 06:23 ewallet_2021112903234616_masterbackup.p12
-rw-------. 1 oracle oinstall 4171 Nov 29 06:30 ewallet_2021112903300474_masterbackup2.p12
-rw-------. 1 oracle oinstall 5819 Nov 29 06:46 ewallet_2021112903465756_Walletfullbackup.p12
-rw-------. 1 oracle oinstall 5819 Nov 29 06:46 ewallet.p12
[oracle@test-machine01 trace]$


Step 8. Convert non-encrypted Tablespace to encrypted Tablespace using Offline & Online method: You can convert your tablespaces to encryption using Online or Offline method. Please note in the Online method you will require twice the storage size as of your tablespace. If you are using OMF (Oracle Managed Files) you can perform this conversion without specifying file_name_convert parameter.

SQL>  show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL>

SQL>
SQL>
SQL> alter session set container=PDB1;

Session altered.

SQL>
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL>
SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
TBLS1
TBLS2

6 rows selected.

SQL>

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
TBLS2
SYSAUX
UNDOTBS1
TBLS1
TEMP
SYSTEM

6 rows selected.

SQL>

SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------------------
/u01/oradata/PDB1system01.dbf
/u01/oradata/PDB1sysaux01.dbf
/u01/oradata/PDB1undotbs01.dbf
/u01/oradata/PDB1/tbls1.dbf
/u01/oradata/PDB1/tbls2.dbf

SQL>
SQL>  alter tablespace tbls1 encryption online using 'aes256' encrypt  file_name_convert = ('tbls1.dbf','tbls1_tde.dbf');

Tablespace altered.

SQL>  select name from v$datafile;

NAME
-------------------------------------------------------------------------------
/u01/oradata/PDB1system01.dbf
/u01/oradata/PDB1sysaux01.dbf
/u01/oradata/PDB1undotbs01.dbf
/u01/oradata/PDB1/tbls1_tde.dbf
/u01/oradata/PDB1/tbls2.dbf

SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
TBLS1                          AES256  YES NORMAL
TBLS2
SYSAUX
UNDOTBS1
TEMP
SYSTEM

6 rows selected.

SQL>

[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ pwd
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace
[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ vi alert_cdb1.log
2021-11-29T07:02:00.881182+03:00
PDB1(3): alter tablespace tbls1 encryption online using 'aes256' encrypt  file_name_convert = ('tbls1.dbf','tbls1_tde.dbf')
2021-11-29T07:02:00.918843+03:00
PDB1(3):About to encrypt tablespace TBLS1 (tsn 3/5)
PDB1(3):Rekeying datafile /u01/oradata/PDB1/tbls1.dbf (19) to /u01/oradata/PDB1/tbls1_tde.dbf
2021-11-29T07:02:04.003427+03:00
PDB1(3):Rekey operation committed for file /u01/oradata/PDB1/tbls1_tde.dbf
2021-11-29T07:02:06.082486+03:00
PDB1(3):About to zero out original file "/u01/oradata/PDB1/tbls1.dbf"
PDB1(3):Successfully zero'ed out original file "/u01/oradata/PDB1/tbls1.dbf"
PDB1(3):Successfully deleted original file "/u01/oradata/PDB1/tbls1.dbf"
PDB1(3):Completed rekey for tablespace TBLS1 (tsn 3/5) from key version 0 to 1.
PDB1(3):Completed:  alter tablespace tbls1 encryption online using 'aes256' encrypt  file_name_convert = ('tbls1.dbf','tbls1_tde.dbf')
[oracle@test-machine01 trace]$

SQL>
SQL>
SQL>  alter tablespace tbls2 offline normal;

Tablespace altered.

SQL>  alter tablespace tbls2 encryption offline  using 'aes256' encrypt;

Tablespace altered.

SQL>  alter tablespace tbls2 online;

Tablespace altered.

SQL>
SQL> select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
TBLS1                          AES256  YES NORMAL
TBLS2                          AES256  YES NORMAL
SYSAUX
UNDOTBS1
TEMP
SYSTEM

6 rows selected.

SQL>

[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ pwd
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace
[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ vi alert_cdb1.log
2021-11-29T07:05:33.592007+03:00
PDB1(3): alter tablespace tbls2 offline normal
PDB1(3):Completed:  alter tablespace tbls2 offline normal
2021-11-29T07:05:43.578736+03:00
PDB1(3): alter tablespace tbls2 encryption offline  using 'aes256' encrypt
2021-11-29T07:05:43.582203+03:00
PDB1(3):About to offline encrypt tablespace 6 ..
PDB1(3):Successfully encrypted tablespace 6 with database key.
PDB1(3):Completed:  alter tablespace tbls2 encryption offline  using 'aes256' encrypt
2021-11-29T07:05:51.714848+03:00
PDB1(3): alter tablespace tbls2 online
PDB1(3):Completed:  alter tablespace tbls2 online
[oracle@test-machine01 trace]$


SQL>
SQL> create tablespace TBLS3 datafile '/u01/oradata/PDB1/tbls3.dbf' size 100M autoextend on maxsize unlimited encryption using 'aes256' encrypt;

Tablespace created.

SQL>
SQL> SQL>
SQL>  select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
TBLS1                          AES256  YES NORMAL
TBLS2                          AES256  YES NORMAL
TBLS3                          AES256  YES NORMAL
SYSAUX
UNDOTBS1
TEMP
SYSTEM

6 rows selected.

SQL>


Step 9. Set parameter encrypt_new_tablespaces: You can also set parameter encrypt_new_tablespaces to convert newly created tablespace without manually specifying encrypt clause.

SQL>
SQL> show parameter encrypt

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
encrypt_new_tablespaces              string                           CLOUD_ONLY
SQL>
SQL>
SQL>
SQL> alter system set encrypt_new_tablespaces='ALWAYS';

System altered.

SQL> show parameter encrypt

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
encrypt_new_tablespaces              string                           ALWAYS
SQL>
SQL> create tablespace TBLS4 datafile '/u01/oradata/PDB1/tbls4.dbf' size 100M autoextend on maxsize unlimited;


Tablespace created.

SQL> SQL>
SQL>  select t.name,e.ENCRYPTIONALG,e.ENCRYPTEDTS,e.STATUS from V$ENCRYPTED_TABLESPACES e, v$tablespace t where t.ts#=e.ts#(+);

NAME                           ENCRYPT ENC STATUS
------------------------------ ------- --- ----------
TBLS1                          AES256  YES NORMAL
TBLS2                          AES256  YES NORMAL
TBLS3                          AES256  YES NORMAL
TBLS4                          AES128  YES NORMAL
SYSAUX
UNDOTBS1
TEMP
SYSTEM

8 rows selected.

SQL>

[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ pwd
/u01/app/oracle/diag/rdbms/cdb1/cdb1/trace
[oracle@test-machine01 trace]$
[oracle@test-machine01 trace]$ vi alert_cdb1.log
PDB1(3):create tablespace TBLS4 datafile '/u01/oradata/PDB1/tbls4.dbf' size 100M autoextend on maxsize unlimited
2021-11-29T15:12:39.375320+03:00
PDB1(3):Force tablespace TBLS4 to be encrypted
2021-11-29T15:13:11.285390+03:00
PDB1(3):Completed: create tablespace TBLS4 datafile '/u01/oradata/PDB1/tbls4.dbf' size 100M autoextend on maxsize unlimited
[oracle@test-machine01 trace]$


References:
https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/introduction-to-transparent-data-encryption.html#GUID-2712FAF7-D9FD-4F87-B8F3-B59ACC26D18E


This document is just for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. 
Click here to understand more about our pursuit.


Related Articles


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

3 thoughts on “Configure TDE (Transparent Data Encryption) in Oracle Database 19C-Multitenant

Comments are closed.