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]$
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
- Oracle Critical Database Patch ID for October 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for July 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2024 along with enabled Download Link
- Oracle Critical Database Patch ID for April 2023 along with enabled Download Link
- Oracle Critical Database Patch ID for January 2023 along with enabled Download Link
Excellent
Thank you, Sanjay for your kind review and feedback!.
Thanks.
Team DBsGuru
Good Job!
Please also mention the permissions required to configure TDE on windows.