Encryption in Oracle 19c Database (TDE)

 Hi Guy's,

Please find the below steps to do encryption & decrypt at tablespace level using TDE. 

It has advanced security feature that encrypts sensitive data stored in tables, tablespaces, and database backups, protecting it from unauthorized access at the storage level. The encryption and decryption processes are managed automatically by the database.

Encryption Steps :-

Configure the Software Keystore Location. one time configuration 

Create directory at OS level  +DATA_DG/prddb/WALLET/tde 

mkdir -p wallet/tde

Need to reboot for effect this parameter, wallet root can be ASM or File system 

ALTER SYSTEM SET WALLET_ROOT='+DATA_DG/prddb/WALLET' SCOPE=SPFILE SID = '*';

No need to reboot

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID='*';

Change the database default encryption algorithm 

ALTER SYSTEM SET "_TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM" = 'AES256' SCOPE = BOTH SID = '*';

 Create the Software Keystore.

Creating a Password-Protected Software Keystore , user shoud has been granted the ADMINISTER KEY MANAGEMENT or SYSKM privilege

SQL>ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY ikea12345#;

 Open the Keystore.

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY ikea12345#;

Check the status of the keystore:

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;

STATUS

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

OPEN_NO_MASTER_KEY

Set the master encryption key by executing the following command:

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY ikea12345# WITH BACKUP USING 'prddbtest_BKP';

Check the status of the keystore:

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;

STATUS

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

OPEN

Creating an Auto-Login Software Keystore for auto start whenever database got restart and also its useful in dataguard environment for applying redologs after a role switch (Failover or Switchover)

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA_DG/prddb/WALLET/tde' IDENTIFIED BY ikea12345#;

Aautomatically encrypt future tablespaces that you will create.

ALTER SYSTEM SET ENCRYPT_NEW_TABLESPACES = ALWAYS;

Start Encrypting Tablespaces

ALTER TABLESPACE USERS ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

ALTER TABLESPACE DATA_TS  ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

ALTER TABLESPACE INDEX_TS ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

Optional Tablespaces:

 ALTER TABLESPACE SYSAUX ENCRYPTION ONLINE USING 'AES256' ENCRYPT ;

ALTER TABLESPACE SYSTEM ENCRYPTION ONLINE USING 'AES256' ENCRYPT ; 

                                 *********** Done ************

• You can also Decrypt the tablespace but ensure you have enough space as this process creates new datafiles while decrypting.

Why Extra Space is Required:-

• Parallel Datafiles: During an ONLINE DECRYPT operation, Oracle creates a new, unencrypted version of every datafile in the target tablespace.
• Double Storage: For the duration of the conversion, the tablespace effectively occupies double its normal size because both the original encrypted files and the new decrypted files must exist simultaneously on disk.
• Data Consistency: This approach allows users to continue reading from and writing to the tablespace while it is being decrypted. Oracle manages the synchronization between the old and new files until the process is finished.
• Example :- If datafile size 10 Gb and data occupied in this datafile 1gb then how to ensure this size in enough to start decrypt.

The Space Rule for Online Decryption:-

• Allocation: It requires space equal to the allocated size (10 GB), not the occupied data (1 GB).
• Total Requirement: You must have at least 10 GB of free space on the storage mount or ASM diskgroup to hold the temporary duplicate.

Rollback or Decrypt 

• ALTER TABLESPACE USERS  ENCRYPTION ONLINE DECRYPTED ;
• ALTER TABLESPACE DATA_TS ENCRYPTION ONLINE DECRYPTED ;

Happy Learning