Transparent Data Encryption (TDE) with Oracle 19c and above
Table of contents
Preamble
Since I tested Transparent Data Encryption (TDE) 15 years ago, the implementation has sightly evolved. Sqlnet.ora is no more the way to go since Oracle 18c:
Configuring TDE with sqlnet.ora parameters has been deprecated, but is still possible depending on your database configuration.
If you do not set WALLET_ROOT and TDE_CONFIGURATION initialization parameters, then Oracle Database checks the sqlnet.ora file for the directory location of the keystore like in old good days (in the case of multitenant architecture and isolated mode this would not work as only one location can be specified in sqlnet.ora):
ENCRYPTION_WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/orcl1/wallet/)
)
)
I have simplified my existing sqlnet.ora to keep only non-TDE related parameters, so:
NAMES.DIRECTORY_PATH = (TNSNAMES, ONAMES, HOSTNAME)
And with multitenant architecture, you can work in united mode (easier implementation, lower isolation) or isolated mode (more complex management, safer isolation):
- United mode: keystore and TDE master encryption key created for CDB and pluggable databases (PDBs) that reside in the same keystore. More central way of management.
- Isolated mode: Each pluggable database (PDB) has its own keystore and keystore password, you manage the keystore and its TDE master encryption keys from the PDB only.
Remark:
Even if you start in united mode you can have, later on, a PDB in isolated mode.
The big picture is a master key stored in a security wallet (file, Oracle Key Vault (OKV), or Hardware Security Module (HSM)). In the case of multitenant you can have multiple master encryption keys stored in the wallet as well as secrets:

My testing has been done with Oracle Enterprise edition 21c (21.20) in a multitenant environment. The underlining server is running Red Hat 8.10, has 12 cores and 64GB of memory.
Transparent Data Encryption (TDE) united mode setup
There is a static parameter to change to specify the Wallets root directory (the DIRECTORY parameter of sqlnet.ora). Once done restart the instance:
alter system set WALLET_ROOT = '/u01/app/oracle/admin/orcl1/wallet' scope = spfile;
As it is also a static parameter chnge the default location of the secure external password store (SEPS) or secure external keystore to automate Transparent Data Encryption (TDE) operations:
alter system set EXTERNAL_KEYSTORE_CREDENTIAL_LOCATION='/u01/app/oracle/admin/orcl1/wallet/tde_seps' scope = spfile;
Tips:
Good idea is to set these two staic parameters for all your databases as if you don’t use it there is no drawbacks !
They ask, in official documentation (!!), to set an hidden parameter to set default encryption algorithm. Problem is that this parameter was implemented in Oracle Cloud database then backported to 19c (19.28) and 23ai (23.7) but unfortunately not in 21c that has a short life expectancy. So in below I will try to choose manually the default encryption algorithm in SQL commands. If you are in 19.28+ or 26ai do this to free your mind:
alter system set "_TABLESPACE_ENCRYPTION_DEFAULT_ALGORITHM" = 'AES256' scope = both;
Tips:
You can confirm the parameter is available with this query:
select a.ksppinm AS parameter, c.ksppstvl AS value, a.ksppdesc AS description, b.ksppstdf AS "Default?" from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx order by a.ksppinm;
As I do not plan to test a Key Management Service (KMS), that could be Oracle Key Vault (OKV) or a hardware security module (HSM), I simply set the keystore to be a file:
alter system set TDE_CONFIGURATION = "KEYSTORE_CONFIGURATION=FILE" scope = both;
Create a keystore on CDB with:
SQL> select ew.*, c.name
from v$encryption_wallet ew join v$containers c
on ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
___________ __________________________________________ ________________ ______________ _______________ ________________ __________________ _________ ___________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ NOT_AVAILABLE UNKNOWN SINGLE NONE UNDEFINED 1 CDB$ROOT
FILE NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 2 PDB$SEED
FILE NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 4 PDB1
SQL> administer key management create keystore identified by "super_secure_password";
keystore altered.
SQL> !ls -l /u01/app/oracle/admin/orcl1/wallet
total 0
drwxr-x--- 2 oracle dba 96 Dec 10 10:36 tde
SQL> !ls -l /u01/app/oracle/admin/orcl1/wallet/tde
total 3
-rw------- 1 oracle dba 2553 Dec 10 10:36 ewallet.p12
SQL> select ew.*, c.name
from v$encryption_wallet ew join v$containers c
on ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
___________ __________________________________________ _________ ______________ _______________ ________________ __________________ _________ ___________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ CLOSED UNKNOWN SINGLE NONE UNDEFINED 1 CDB$ROOT
FILE CLOSED UNKNOWN SINGLE UNITED UNDEFINED 2 PDB$SEED
FILE CLOSED UNKNOWN SINGLE UNITED UNDEFINED 4 PDB1
Tips:
It is a good idea to share this password between multiple trusted administrators to avoid losing access to the keystore.
Create a local auto login keystore to be able to open the database without DBA supplying the password to open the keystore. LOCAL means the the AUTO LOGIN keystore can be opened only from the computer where it has been created:
SQL> administer key management create local auto_login keystore from keystore identified by "super_secure_password";
keystore altered.
SQL> select ew.*, c.name
from v$encryption_wallet ew join v$containers c
on ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
___________ __________________________________________ _____________________ __________________ _______________ ________________ __________________ _________ ___________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE NONE UNDEFINED 1 CDB$ROOT
FILE OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE UNITED UNDEFINED 2 PDB$SEED
FILE OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE UNITED UNDEFINED 4 PDB1
SQL> !ls -l /u01/app/oracle/admin/orcl1/wallet/tde
total 6
-rw------- 1 oracle dba 2598 Dec 10 10:51 cwallet.sso
-rw------- 1 oracle dba 2553 Dec 10 10:36 ewallet.p12
To avoid specifying all the time the password in ADMINISTER KEY MANAGEMENT command I create a secure external password store (SEPS) also known as a SEPS wallet (TDE_WALLET in uppercase is supper important). I have also tried to select from V$CLIENT_SECRETS but the view remains empty:
SQL> administer key management add secret 'super_secure_password'
for client 'TDE_WALLET' using tag 'TDE keystore password'
to local auto_login keystore '/u01/app/oracle/admin/orcl1/wallet/tde_seps';
administer key management add secret 'super_secure_password'
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet
SQL> show parameter external_keystore_credential_location
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
external_keystore_credential_locatio string /u01/app/oracle/admin/orcl1/wa
n llet/tde_seps
SQL> !mkdir -p /u01/app/oracle/admin/orcl1/wallet/tde_seps
SQL> administer key management add secret 'super_secure_password'
for client 'TDE_WALLET' using tag 'TDE keystore password'
to local auto_login keystore '/u01/app/oracle/admin/orcl1/wallet/tde_seps';
keystore altered.
SQL> !ls -l /u01/app/oracle/admin/orcl1/wallet/tde_seps
total 4
-rw------- 1 oracle dba 3830 Dec 10 12:10 cwallet.sso
For testing you can close the password opened keystore and open it with SEPS:
SQL> administer key management set keystore close;
keystore altered.
SQL> administer key management set keystore open identified by external store container=all;
keystore altered.
SQL> select ew.*, c.name
from v$encryption_wallet ew join v$containers c
on ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
___________ __________________________________________ _____________________ ______________ _______________ ________________ __________________ _________ ___________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN_NO_MASTER_KEY PASSWORD SINGLE NONE UNDEFINED 1 CDB$ROOT
FILE OPEN_NO_MASTER_KEY PASSWORD SINGLE UNITED UNDEFINED 2 PDB$SEED
FILE OPEN_NO_MASTER_KEY PASSWORD SINGLE UNITED UNDEFINED 4 PDB1
Remark:
Specify UPDATE/DELETE instead of ADD to change/delete the password, example if you mess up:
administer key management delete secret for client 'TDE_WALLET' from local auto_login keystore '/u01/app/oracle/admin/orcl1/wallet/tde_seps'; administer key management update secret 'super_secure_password' for client 'TDE_WALLET' using tag 'TDE keystore password' to local auto_login keystore '/u01/app/oracle/admin/orcl1/wallet/tde_seps';
We have seen that wallet is in OPEN_NO_MASTER_KEY status, so next step is to create encryption keys. Add a TDE master key to your root (CDB) container with:
SQL> administer key management set key using tag 'TDE_KEY01'
using algorithm 'AES256'
force keystore identified by external store
with backup using 'backup'
container = current;
keystore altered.
SQL> select ew.*, c.name
from v$encryption_wallet ew, v$containers c
where ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
________ _______________________________________ __________________ _______________ ____________ _____________ _______________ ______ ________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 1 CDB$ROOT
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 2 PDB$SEED
FILE OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE UNITED UNDEFINED 4 PDB1
SQL> !ls -l /u01/app/oracle/admin/orcl1/wallet/tde
total 13
-rw------- 1 oracle dba 4214 Dec 10 12:48 cwallet.sso
-rw------- 1 oracle dba 2553 Dec 10 12:48 ewallet_2025121011480720_backup.p12
-rw------- 1 oracle dba 4169 Dec 10 12:48 ewallet.p12
SQL> select key_id, tag, key_use, activating_pdbname from v$encryption_keys;
KEY_ID TAG KEY_USE ACTIVATING_PDBNAME
____________________________________________________ _______________ __________ __________________
AYm95tRUL0/Sv1GNlWtStBYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_KEY01 TDE IN PDB CDB$ROOT
Remark:
You can use your own key using mkid and mk parameters (‘mkid:mk | mk’). mkid is the TDE master encryption key ID and mk is the TDE master encryption key.
Now what’s important is to set the first master key in the pluggable database where you plan to encrypt columns or tablespaces:
SQL> alter session set container = pdb1;
Session altered.
SQL> administer key management set key using tag 'TDE_PDB01_KEY01'
using algorithm 'AES256'
force keystore identified by external store
with backup using 'backup'
container = current;
keystore altered.
SQL> !ls -l /u01/app/oracle/admin/orcl1/wallet/tde
total 20
-rw------- 1 oracle dba 5878 Dec 10 12:59 cwallet.sso
-rw------- 1 oracle dba 2553 Dec 10 12:48 ewallet_2025121011480720_backup.p12
-rw------- 1 oracle dba 4169 Dec 10 12:59 ewallet_2025121011592264_backup.p12
-rw------- 1 oracle dba 5833 Dec 10 12:59 ewallet.p12
SQL> alter session set container=cdb$root;
Session altered.
SQL> select key_id, tag, key_use, activating_pdbname from v$encryption_keys;
KEY_ID TAG KEY_USE ACTIVATING_PDBNAME
____________________________________________________ _______________ __________ __________________
AcRdW1xc6E++v2d9sjUZjSEAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_PDB01_KEY01 TDE IN PDB PDB1
AYm95tRUL0/Sv1GNlWtStBYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_KEY01 TDE IN PDB CDB$ROOT
SQL> select ew.*, c.name
from v$encryption_wallet ew, v$containers c
where ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
________ _______________________________________ ______ _______________ ____________ _____________ _______________ ______ ________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 1 CDB$ROOT
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 2 PDB$SEED
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 4 PDB1
Testing
Encrypted columns
This case apply when you have only few columns to encrypt in a tablespace that is not fully encrypted. This method is not transparent to the application as you have to modify your existing tables (ALTER TABLE) or create the object encrypting specifically the column you would like to protect.
To work isolated I start by creating a non-encrypted tablespace in my PDB1 pluggable database:
SQL> alter session set container=pdb1;
Session altered.
SQL> create bigfile tablespace tbs_unencrypted
datafile '/u01/app/oracle/oradata/ORCL1/pdb1/tbs_unencrypted01.dbf' size 10m autoextend on next 1m maxsize 50m
online
--encryption using 'AES256' encrypt
segment space management auto
extent management local autoallocate;
Tablespace created.
In this non-encrypted tablespace I create a table (under my personal YJAQUIER account, you cannot encrypt a column in tables owned by SYS) with no encrypted column and the same fashion table with an encrypted column:
SQL> create table employees (
id number generated always as identity (start with 1 increment by 1) primary key,
descr varchar2(50)
)
tablespace tbs_unencrypted;
Table created.
SQL> create table employees_encrypt (
id number generated always as identity (start with 1 increment by 1) primary key,
descr varchar2(50) encrypt using 'AES256' salt
)
tablespace tbs_unencrypted;
SQL> select * from dba_encrypted_columns;
OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SALT INTEGRITY_ALG
___________ ____________________ ______________ ___________________ _______ ________________
YJAQUIER EMPLOYEES_ENCRYPT DESCR AES 256 bits key YES SHA-1
SQL> insert into employees(descr) values('Yannick Jaquier');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from employees;
ID DESCR
---------- --------------------------------------------------
1 Yannick Jaquier
SQL> insert into employees_encrypt(descr) values('Keanu Reeves');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from employees_encrypt;
ID DESCR
---------- --------------------------------------------------
1 Keanu Reeves
SQL> alter system checkpoint; --To write blocks on disk
System altered.
Now if we try to check the content of Oracle datafiles we see that we can extract from datafile the content of non-encrypted column while for encrypted column it is, obviously, impossible::
[oracle@server01 ~]$ strings /u01/app/oracle/oradata/ORCL1/pdb1/tbs_unencrypted01.dbf | grep Yannick Yannick Jaquier Yannick Jaquier [oracle@server01 ~]$ strings /u01/app/oracle/oradata/ORCL1/pdb1/tbs_unencrypted01.dbf | grep Keanu
Encrypted tablespaces
This case encrypt a whole tablespace, at creation or afterwards with ALTER TABLESPACE ENCRYPT command. Encryption at tablespace level is completely transparent to the application and requires no code change at all !
Let encrypt the existing tablespace I have created in previous step:
SQL> alter session set container=pdb1; Session altered. SQL> alter tablespace tbs_unencrypted encryption online using 'AES256' encrypt; Tablespace altered. SQL> select tablespace_name from dba_tablespaces where encrypted = 'YES'; TABLESPACE_NAME __________________ TBS_UNENCRYPTED SQL> select t.name as tbs_name, e.encryptionalg as alg, e.status from v$tablespace t join v$encrypted_tablespaces e on e.ts# = t.ts#; TBS_NAME ALG STATUS __________________ _________ _________ TBS_UNENCRYPTED AES256 NORMAL
Everything is now obfuscated:
[oracle@server01 ~]$ strings /u01/app/oracle/oradata/ORCL1/pdb1/tbs_unencrypted01.dbf | grep Yannick [oracle@server01 ~]$ strings /u01/app/oracle/oradata/ORCL1/pdb1/tbs_unencrypted01.dbf | grep Keanu
Transparent Data Encryption (TDE) isolated mode setup
SQL> create pluggable database pdb2
admin user yjaquier identified by secure_password
roles = (dba)
default tablespace users datafile '/u01/app/oracle/oradata/ORCL1/pdb2/users01.dbf' size 5m autoextend on next 5m maxsize 100m
file_name_convert = ('/u01/app/oracle/oradata/ORCL1/pdbseed/', '/u01/app/oracle/oradata/ORCL1/pdb2/');
Pluggable database created.
SQL> alter pluggable database pdb2 open;
Pluggable database altered.
SQL> alter pluggable database pdb2 save state;
Pluggable database altered.
SQL> select con_id, name, guid from v$containers;
CON_ID NAME GUID
_________ ___________ ___________________________________
1 CDB$ROOT C8209F27C6B16005E053362EE80AE60E
2 PDB$SEED E4679DE0E35A28A5E053402B4B0A3C6D
3 PDB2 45FECD46F5548C7AE063402B4B0AA073
4 PDB1 38523F06D88C4A9BE063402B4B0A41E6
Remark:
In many of my previous post I have specified something like path_prefix = ‘/u01/app/oracle/oradata/ORCL1/pdb2/’, it is a killer option here as you will not be able to create a SEPS in this situation as this SEPS is outside the pluggable database directory defined by WALLET_ROOT. You will constantly get:
ERROR at line 1: ORA-65315: invalid path specified for file - /u01/app/oracle/admin/orcl1/wallet/tde_seps
To remove this pdbs after testing:
alter pluggable database pdb2 close abort; drop pluggable database pdb2 including datafiles;
This new pluggable database is de facto in united mode, to have it working in isolated mode roughly we need to create a keystore in it and add an encryption key in this key store:
SQL> select ew.*, c.name
from v$encryption_wallet ew join v$containers c
on ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
___________ __________________________________________ _____________________ __________________ _______________ ________________ __________________ _________ ___________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 1 CDB$ROOT
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 2 PDB$SEED
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 4 PDB1
FILE OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE UNITED UNDEFINED 5 PDB2
Let’s move it to isolated mode. Close the united keystore and create a local (isolated) one. I have also create a local auto login keystore:
SQL> alter session set container=pdb2;
Session altered.
SQL> administer key management set keystore close identified by external store container = current;
administer key management set keystore close identified by external store container = current
*
ERROR at line 1:
ORA-28417: password-based keystore is not open
SQL> administer key management force keystore close container=current;
keystore altered.
SQL> administer key management create keystore identified by "pdb2_super_secure_password";
keystore altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select ew.*, c.name
from v$encryption_wallet ew join v$containers c
on ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
___________ ___________________________________________________________________________ _________ __________________ _______________ ________________ __________________ _________ ___________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 1 CDB$ROOT
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 2 PDB$SEED
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 4 PDB1
FILE /u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde/ CLOSED UNKNOWN SINGLE ISOLATED UNDEFINED 5 PDB2
SQL> alter session set container=pdb2;
Session altered.
SQL> administer key management create local auto_login keystore from keystore identified by "pdb2_super_secure_password";
keystore altered.
SQL> !ls -l /u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde/
total 15
-rw------- 1 oracle dba 2598 Dec 12 16:37 cwallet.sso
-rw------- 1 oracle dba 2553 Dec 12 16:35 ewallet.p12
SQL> alter session set container=cdb$root;
Session altered.
SQL> select ew.*, c.name
from v$encryption_wallet ew join v$containers c
on ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
___________ ___________________________________________________________________________ _____________________ __________________ _______________ ________________ __________________ _________ ___________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 1 CDB$ROOT
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 2 PDB$SEED
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 4 PDB1
FILE /u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde/ OPEN_NO_MASTER_KEY LOCAL_AUTOLOGIN SINGLE ISOLATED UNDEFINED 5 PDB2
I also want to create a local PDB SEPS with (location is WALLET_ROOT/pdb_guid/tde_seps) to use external store and avoid specifying password at each command:
SQL> alter session set container=pdb2;
Session altered.
SQL> administer key management add secret 'pdb2_super_secure_password'
for client 'TDE_WALLET' using tag 'PDB2 TDE keystore password'
to local auto_login keystore '/u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde_seps';
administer key management add secret 'pdb2_super_secure_password'
*
ERROR at line 1:
ORA-28368: cannot auto-create wallet
SQL> !mkdir -p /u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde_seps
SQL> administer key management add secret 'pdb2_super_secure_password'
for client 'TDE_WALLET' using tag 'PDB2 TDE keystore password'
to local auto_login keystore '/u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde_seps';
keystore altered.
SQL> !ls -l /u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde_seps
total 4
-rw------- 1 oracle dba 3846 Dec 15 14:50 cwallet.sso
Then create a master key for this PDB:
SQL> alter session set container=pdb2;
Session altered.
SQL> administer key management set key using tag 'TDE_PDB02_KEY01'
using algorithm 'AES256'
force keystore identified by external store
with backup using 'backup'
container = current;
keystore altered.
SQL> select key_id, tag, key_use, activating_pdbname, creation_time, activation_time from v$encryption_keys;
KEY_ID TAG KEY_USE ACTIVATING_PDBNAME CREATION_TIME ACTIVATION_TIME
_______________________________________________________ __________________ _____________ _____________________ __________________________________ __________________________________
AcjYMityQE94v0LWr8Qc4WYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_PDB02_KEY01 TDE IN PDB PDB2 15/12/25 13:53:58,441949000 GMT 15/12/25 13:53:58,441953000 GMT
SQL> alter session set container=cdb$root;
Session altered.
SQL> select ew.*, c.name
from v$encryption_wallet ew join v$containers c
on ew.con_id = c.con_id;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID NAME
___________ ___________________________________________________________________________ _________ __________________ _______________ ________________ __________________ _________ ___________
FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE NO 1 CDB$ROOT
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 2 PDB$SEED
FILE /u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde/ OPEN LOCAL_AUTOLOGIN SINGLE ISOLATED NO 3 PDB2
FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED NO 4 PDB1
If we create a tablespace in this pluggable database we see that this is the key stored in the isolated keystore that is used:
SQL> alter session set container=pdb2;
Session altered.
SQL> create bigfile tablespace tbs_encrypted
datafile '/u01/app/oracle/oradata/ORCL1/pdb2/tbs_encrypted01.dbf' size 10m autoextend on next 1m maxsize 50m
online
encryption using 'AES256' encrypt
segment space management auto
extent management local autoallocate;
SQL> select t.name as tbs_name, e.encryptionalg as alg, e.status from v$tablespace t join v$encrypted_tablespaces e on e.ts# = t.ts#;
TBS_NAME ALG STATUS
________________ _________ _________
TBS_ENCRYPTED AES256 NORMAL
SQL> select c.name,a.name as tbs_name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64
from (select x.con_id, t.name, rawtohex(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#) a, v$containers c
where a.con_id = c.con_id
order by c.name,a.name;
NAME TBS_NAME MASTERKEYID_BASE64
_______ ________________ ___________________________
PDB2 SYSAUX AcjYMityQE94v0LWr8Qc4WY=
PDB2 SYSTEM AcjYMityQE94v0LWr8Qc4WY=
PDB2 TBS_ENCRYPTED AcjYMityQE94v0LWr8Qc4WY=
PDB2 TEMP AcjYMityQE94v0LWr8Qc4WY=
PDB2 UNDOTBS1 AcjYMityQE94v0LWr8Qc4WY=
PDB2 USERS AQAAAAAAAAAAAAAAAAAAAAA=
Alternative last command in shell:
[oracle@server01 ~]$ mkstore -wrl /u01/app/oracle/admin/orcl1/wallet/45FECD46F5548C7AE063402B4B0AA073/tde -list Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.AcjYMityQE94v0LWr8Qc4WYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.45FECD46F5548C7AE063402B4B0AA073 ORACLE.SECURITY.ID.ENCRYPTION. ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.SECURITY.KM.ENCRYPTION.AcjYMityQE94v0LWr8Qc4WYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KT.ENCRYPTION.AcjYMityQE94v0LWr8Qc4WYAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Keystore important operations
Backup keystore
Backup the keystore in a location where your file backup storage will take it. If you lose your keystore then your encrypted database becomes unrecoverable and Oracle confirmed that there is no backdoor to recover the situation. It is not possible to backup this keystore with RMAN:
SQL> administer key management
backup keystore using 'backup'
force keystore
identified by external store
to '/u01/backup/';
keystore altered.
SQL> !ls -l /u01/backup
total 6
-rw------- 1 oracle dba 5833 Dec 11 12:22 ewallet_2025121111224439_backup.p12
The column FULLY_BACKED_UP has now changed. You must backup safely (3-2-2-1-1-0) this wallet as if you loose it your data are lost forever:
SQL> select * from v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_ORDER KEYSTORE_MODE FULLY_BACKED_UP CON_ID ___________ __________________________________________ _________ __________________ _______________ ________________ __________________ _________ FILE /u01/app/oracle/admin/orcl1/wallet/tde/ OPEN LOCAL_AUTOLOGIN SINGLE NONE YES 1 FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED YES 2 FILE OPEN LOCAL_AUTOLOGIN SINGLE UNITED YES 4
Rotate a key
This feature is often missing in younger products like the one for MariaDB but is available in Oracle. It starts by generating a new key:
SQL> alter session set container=pdb1;
Session altered.
SQL> administer key management set key using tag 'TDE_PDB01_KEY02'
using algorithm 'AES256'
force keystore identified by external store
with backup using 'backup'
container = current;
keystore altered.
SQL> -- If you mess up with tag (like me) you can use
SQL> administer key management set tag 'TDE_PDB01_KEY02' for 'AU36sUtJNk+dvx6L0CuFarMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'
force keystore identified by external store
with backup using 'backup';
keystore altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> select key_id, tag, key_use, activating_pdbname, creation_time, activation_time from v$encryption_keys;
KEY_ID TAG KEY_USE ACTIVATING_PDBNAME CREATION_TIME ACTIVATION_TIME
_______________________________________________________ __________________ _____________ _____________________ __________________________________ __________________________________
AVSXp3NGWE87v4mhk4JrBDoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_KEY01 TDE IN PDB CDB$ROOT 11/12/25 16:33:45,087035000 GMT 11/12/25 16:33:45,087038000 GMT
Aa4JiSXUg08UvytO2pctD0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_PDB01_KEY01 TDE IN PDB PDB1 11/12/25 16:34:10,614120000 GMT 11/12/25 16:34:10,614123000 GMT
AU36sUtJNk+dvx6L0CuFarMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_PDB01_KEY02 TDE IN PDB PDB1 11/12/25 16:43:17,828930000 GMT 11/12/25 16:43:17,828940000 GMT
You can get the currently used key with (reminds me the PeopleSoft queries):
SQL> select ek1.key_id, ek1.tag, ek1.key_use, ek1.creation_time, ek1.activation_time, ek1.activating_pdbname
from v$encryption_keys ek1
where ek1.activation_time = (select max(ek2.activation_time) from v$encryption_keys ek2 where ek2.activating_pdbname = ek1.activating_pdbname);
KEY_ID TAG KEY_USE CREATION_TIME ACTIVATION_TIME ACTIVATING_PDBNAME
_______________________________________________________ __________________ _____________ __________________________________ __________________________________ _____________________
AVSXp3NGWE87v4mhk4JrBDoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_KEY01 TDE IN PDB 11/12/25 16:33:45,087035000 GMT 11/12/25 16:33:45,087038000 GMT CDB$ROOT
AU36sUtJNk+dvx6L0CuFarMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA TDE_PDB01_KEY02 TDE IN PDB 11/12/25 16:43:17,828930000 GMT 11/12/25 16:43:17,828940000 GMT PDB1
As my test test tablespace is very small I have not been able to see the STATUS column of V$ENCRYPTED_TABLESPACES to be set to REKEYING value…
Export/import keys
In the case of Data Guard or Transport Tablespace feature, instead of copying the keystore and merging it to the one at destination one easier operation is to export/import keys. On source system export the key with:
SQL> administer key management export keys
with secret "password_to_protect_the_key"
to '/home/oracle/tde_pdb01_key02_exp.dat'
force keystore
identified by external store
with identifier in 'AU36sUtJNk+dvx6L0CuFarMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA';
keystore altered.
SQL> !ls -l /home/oracle/tde_pdb01_key02_exp.dat
-rw-r----- 1 oracle dba 2368 Dec 12 12:19 /home/oracle/tde_pdb01_key02_exp.dat
Then import it on target system with:
SQL> administer key management import keys
with secret "password_to_protect_the_key"
from '/home/oracle/tde_pdb01_key02_exp.dat'
force keystore
identified by external store
with backup using 'backup';
keystore altered.
RMAN encrypted backup
If you encrypt your datafiles then it is not really required to also encrypt your RMAN backup. Because if your backup pieces ot stolen then the attacker can restore them but will not be able to read the datafiles. Of course the auto login keystore must not be at the same place as your backups. Encrypting RMAN backup on top of encrypting datafiles is an extra security measure that I see as a belt and brasses approach. Stolen encrypted backup media will not be readable by the attacker.
RMAN configuration is really simple:
RMAN> configure encryption algorithm 'AES256'; new RMAN configuration parameters: CONFIGURE ENCRYPTION ALGORITHM 'AES256'; new RMAN configuration parameters are successfully stored RMAN> configure encryption for database on; new RMAN configuration parameters: CONFIGURE ENCRYPTION FOR DATABASE ON; new RMAN configuration parameters are successfully stored
Available RMAN encryption algorithms:
SQL> select * from v$rman_encryption_algorithms;
ALGORITHM_ID ALGORITHM_NAME ALGORITHM_DESCRIPTION IS_DEFAULT RESTORE_ONLY CON_ID
_______________ _________________ ________________________ _____________ _______________ _________
1 AES128 AES 128-bit key YES NO 0
2 AES192 AES 192-bit key NO NO 0
3 AES256 AES 256-bit key NO NO 0
Perform a backup with something like:
run {
allocate channel disk1 device type disk;
backup database plus archivelog delete input;
release channel disk1;
}
To confirm it has been encrypted:
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
64 B A A DISK 16-DEC-25 1 1 YES TAG20251216T115922
65 B F A DISK 16-DEC-25 1 1 YES TAG20251216T115930
66 B F A DISK 16-DEC-25 1 1 YES TAG20251216T115930
67 B F A DISK 16-DEC-25 1 1 YES TAG20251216T115930
68 B F A DISK 16-DEC-25 1 1 YES TAG20251216T115930
69 B A A DISK 16-DEC-25 1 1 YES TAG20251216T120941
70 B F A DISK 16-DEC-25 1 1 YES TAG20251216T120942
RMAN> select recid, encrypted from v$backup_set_details bsd order by bsd.recid;
RECID ENC
---------- ---
64 YES
65 YES
66 YES
67 YES
68 YES
69 YES
70 YES
7 rows selected
Issues encountered
ORA-28374: typed master key not found in wallet
Warning, very annoying error. Particularly when doing testing and removing wallets you might then get this below error, but the SQL commands you typed has been executed despite the error:
SQL> administer key management set key using tag 'TDE_KEY01'
using algorithm 'AES256'
force keystore identified by external store
with backup using 'backup'
container = current;
administer key management set key using tag 'TDE_KEY01'
*
ERROR at line 1:
ORA-28374: typed master key not found in wallet
This might be a bug of my 21c release as explained in MOS note (KB186613) OERR: ORA-28374 “typed master key not found in wallet” Reference Note, ensure your are not missing any keys (I knew it was not my case as I removed everything before dropping wallet files):
SQL> select
masterkeyid as required_keys from v$encrypted_tablespaces
minus
select
hextoraw(substr(utl_encode.base64_decode(utl_raw.cast_to_raw(enckeys.key_id)), 3,32)) as available_keys
from v$encryption_keys enckeys;
no rows selected
Set this hidden parameter and bounce the instance, yet it is not acceptable to bounce a production database for such problem and in any case it is quite dangerous to set this “_db_discard_lost_masterkey” hidden parameter:
SQL> alter system set "_db_discard_lost_masterkey"=true scope=memory;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 4294966552 bytes
Fixed Size 9694488 bytes
Variable Size 2734686208 bytes
Database Buffers 1258291200 bytes
Redo Buffers 292294656 bytes
Database mounted.
Database opened.
SQL> administer key management set key using tag 'TDE_KEY01'
using algorithm 'AES256'
force keystore identified by external store
with backup using 'backup'
container = current;
keystore altered.
But my problem was elsewhere… Check the keys you currently have in your wallet (including backups):
[oracle@server01 ~]$ mkstore -wrl /u01/app/oracle/admin/orcl1/wallet/tde -list Oracle Secret Store Tool Release 21.0.0.0.0 - Production Version 21.0.0.0.0 Copyright (c) 2004, 2025, Oracle and/or its affiliates. All rights reserved. Enter wallet password: Oracle Secret Store entries: ORACLE.SECURITY.DB.ENCRYPTION.Aa4JiSXUg08UvytO2pctD0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.AU36sUtJNk+dvx6L0CuFarMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.AVSXp3NGWE87v4mhk4JrBDoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.AVV/EQgC6U8tv9uRz4lKTfQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY ORACLE.SECURITY.DB.ENCRYPTION.MASTERKEY.38523F06D88C4A9BE063402B4B0A41E6 ORACLE.SECURITY.ID.ENCRYPTION. ORACLE.SECURITY.KB.ENCRYPTION. ORACLE.SECURITY.KM.ENCRYPTION.Aa4JiSXUg08UvytO2pctD0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KM.ENCRYPTION.AU36sUtJNk+dvx6L0CuFarMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KM.ENCRYPTION.AVSXp3NGWE87v4mhk4JrBDoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KM.ENCRYPTION.AVV/EQgC6U8tv9uRz4lKTfQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KT.ENCRYPTION.Aa4JiSXUg08UvytO2pctD0oAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KT.ENCRYPTION.AU36sUtJNk+dvx6L0CuFarMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KT.ENCRYPTION.AVSXp3NGWE87v4mhk4JrBDoAAAAAAAAAAAAAAAAAAAAAAAAAAAAA ORACLE.SECURITY.KT.ENCRYPTION.AVV/EQgC6U8tv9uRz4lKTfQAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
Then check the master key usage and value with internal table x$kcbtek. In below AQAAAAAAAAAAAAAAAAAAAAA= means no master key:
SQL> select c.name,a.name as tbs_name,utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||substr(mkeyid,1,4))) || utl_raw.cast_to_varchar2( utl_encode.base64_encode(substr(mkeyid,5,length(mkeyid)))) masterkeyid_base64
from (select x.con_id, t.name, rawtohex(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#) a, v$containers c
where a.con_id = c.con_id
order by c.name,a.name;
NAME TBS_NAME MASTERKEYID_BASE64
___________ __________________ ___________________________
CDB$ROOT SYSAUX AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT SYSAUX AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT SYSAUX AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT SYSAUX AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT SYSTEM AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT SYSTEM AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT SYSTEM AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT SYSTEM AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT TEMP AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT TEMP AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT TEMP AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT TEMP AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT UNDOTBS1 AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT UNDOTBS1 AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT UNDOTBS1 AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT UNDOTBS1 AQlYbsK9vU+ev0Tr+7zV5gA=
CDB$ROOT USERS AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED SYSAUX AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED SYSAUX AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED SYSAUX AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED SYSAUX AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED SYSTEM AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED SYSTEM AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED SYSTEM AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED SYSTEM AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED TEMP AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED TEMP AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED TEMP AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED TEMP AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED UNDOTBS1 AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED UNDOTBS1 AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED UNDOTBS1 AQAAAAAAAAAAAAAAAAAAAAA=
PDB$SEED UNDOTBS1 AQAAAAAAAAAAAAAAAAAAAAA=
PDB1 SYSAUX AU36sUtJNk+dvx6L0CuFarM=
PDB1 SYSAUX AU36sUtJNk+dvx6L0CuFarM=
PDB1 SYSAUX AU36sUtJNk+dvx6L0CuFarM=
PDB1 SYSAUX AU36sUtJNk+dvx6L0CuFarM=
PDB1 SYSTEM AU36sUtJNk+dvx6L0CuFarM=
PDB1 SYSTEM AU36sUtJNk+dvx6L0CuFarM=
PDB1 SYSTEM AU36sUtJNk+dvx6L0CuFarM=
PDB1 SYSTEM AU36sUtJNk+dvx6L0CuFarM=
PDB1 TBS_UNENCRYPTED AU36sUtJNk+dvx6L0CuFarM=
PDB1 TEMP AU36sUtJNk+dvx6L0CuFarM=
PDB1 TEMP AU36sUtJNk+dvx6L0CuFarM=
PDB1 TEMP AU36sUtJNk+dvx6L0CuFarM=
PDB1 TEMP AU36sUtJNk+dvx6L0CuFarM=
PDB1 UNDOTBS1 AU36sUtJNk+dvx6L0CuFarM=
PDB1 UNDOTBS1 AU36sUtJNk+dvx6L0CuFarM=
PDB1 UNDOTBS1 AU36sUtJNk+dvx6L0CuFarM=
PDB1 UNDOTBS1 AU36sUtJNk+dvx6L0CuFarM=
PDB1 USERS AQAAAAAAAAAAAAAAAAAAAAA=
PDB1 USERS AQAAAAAAAAAAAAAAAAAAAAA=
52 rows selected.
Compare the result of previous query with the one of mkstore -list. We see a key (AQlYbsK9vU+ev0Tr+7zV5gA=) of a previous TDE trial has been stored somewhere in Oracle internals (AQAAAAAAAAAAAAAAAAAAAAA= means not encrypted) and is not present in current wallet. Obviously as it was a trial and as I did not encrypt everything I deleted the wallet to restart from scratch ! Big mistake… But in my case I have just created and remove the wallet so it is really strange why Oracle kept a trace of it. Obviously if you encrypted objects and lost the key then it is dead. Also why would have I encrypted something in root container ??!!
So I recreated an master encryption key in CDB$ROOT:
SQL> administer key management set key using tag 'TDE_KEY01'
using algorithm 'AES256'
force keystore identified by external store
with backup using 'backup'
container = current;
keystore altered.
References
- Oracle Database Advanced Security Guide (19c)
- Enable TDE Auto-Login Wallet and Secure External Password Store (SEPS)
- Oracle TDE: External Store for Keystore (SEPS)
- (KB791718) ORA-28374 Typed Master Key Not Found In Wallet
- ORA-28374: typed master key not found in wallet during wallet creation on 12.2.0.1.0
- ORA-28374: Typed Master Key Not Found in Wallet
- ORA-28374 | Frits Hoogland
- Transparent Data Encryption (TDE) — ToonieDBA
- ORA-28374: Typed Master Key Not Found In Wallet





