EasyReliableDBA: TDE implementation in oracle 19c and how to change tde algorithm in oracle 19c

Transparent Data Encryption (TDE) in Oracle 19c secures "data at rest" by encrypting tablespaces or columns. It works by storing Master Encryption Keys in an external Keystore (Wallet), which transparently decrypts data for authorized users and applications

Implementing TDE in Oracle 19c involves a straightforward sequence of setting up the keystore location, creating the keystore, setting the master key, and encrypting the data.

Steps to configure keystore using WALLET_ROOT and TDE_CONFIGURATION parameter are below,

1. Set WALLET_ROOT parameter

alter system set wallet_root='<full path of wallet' scope=spfile;

2. shutdown and startup database

shutdown immediate;
startup;

3. Set TDE_CONFIGURATION parameter
*KEYSTORE_CONFIGURATION=FILE means using softwae keystore.

alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID='*';

4. Create keystore

administer key management create keystore identified by <password>;

5. Open keystore

administer key management set keystore open identified by <password>;

6. Set key inside keystore

administer key management set key identified by <password> with backup;

Note: If you were using SQLNET.ENCRYPTION_WALLET_LOCATION parameter, you have to copy previous wallet named ewallet.p12 before executing Step 6.

For More Details

How to Configure Keystore and TDE Encryption Key for United Mode (Doc ID 2586100.1)

https://docs.oracle.com/en/database/oracle/oracle-database/19/asoag/get-started.html

Use Case

mkdir -p /wallet/TRAINING/wallet/tde

SQL> alter system set wallet_root='/wallet/EASYDB/wallet' scope=spfile;

SQL> show parameter wallet_root

NAME TYPE

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

VALUE

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

wallet_root string

/wallet/EASYDB/wallet

SQL> alter system set tde_configuration='KEYSTORE_CONFIGURATION=FILE' scope=both;

System altered.

SQL> show parameter tde_configuration

NAME TYPE

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

VALUE

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

tde_configuration string

KEYSTORE_CONFIGURATION=FILE

SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/wallet/EASYDB/wallet/tde' identified by Uco12345;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/wallet/EASYDB/wallet/tde' identified by Uco12345;

keystore altered.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE

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

WRL_PARAMETER

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

STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC

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

CON_ID

----------

FILE

/wallet/EASYDB/wallet/tde/

NOT_AVAILABLE UNKNOWN SINGLE NONE UNDEFINED

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN FORCE KEYSTORE identified by Uco12345;

keystore altered.

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE identified by Uco12345 WITH BACKUP;

keystore altered.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE

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

WRL_PARAMETER

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

STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC

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

CON_ID

----------

FILE

/wallet/EASYDB/wallet/tde/

OPEN PASSWORD SINGLE NONE NO

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE identified by Uco12345;

keystore altered.

SQL> SELECT * FROM V$ENCRYPTION_WALLET;

WRL_TYPE

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

WRL_PARAMETER

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

STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC

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

CON_ID

----------

FILE

/wallet/EASYDB/wallet/tde/

OPEN AUTOLOGIN SINGLE NONE NO

SQL> DESC V$ENCRYPTION_WALLET;

Name Null? Type

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

WRL_TYPE VARCHAR2(20)

WRL_PARAMETER VARCHAR2(4000)

STATUS VARCHAR2(30)

WALLET_TYPE VARCHAR2(20)

WALLET_ORDER VARCHAR2(9)

KEYSTORE_MODE VARCHAR2(8)

FULLY_BACKED_UP VARCHAR2(9)

CON_ID NUMBER

SQL> SELECT CON_ID,WALLET_TYPE,WRL_PARAMETER,STATUS FROM V$ENCRYPTION_WALLET;

CON_ID WALLET_TYPE

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

WRL_PARAMETER

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

STATUS

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

0 AUTOLOGIN

/wallet/EASYDB/wallet/tde/

OPEN

SQL> COL WRL_PARAMETER FOR A30

SQL> SET LINE 300 PAGES 300

SQL>

SQL> /

CON_ID WALLET_TYPE WRL_PARAMETER STATUS

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

0 AUTOLOGIN /wallet/EASYDB/wallet/tde/ OPEN

SQL>

set linesize 250 pagesize 250

column name format a40

column masterkeyid_base64 format a60

select 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 t.name, RAWTOHEX(x.mkid) mkeyid from v$tablespace t, x$kcbtek x where t.ts#=x.ts#);

select con_id, wallet_type,wrl_parameter, status from v$encryption_wallet;

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

ENCRYPTION

OWNER = EASYUSER

TABLENAME = EASYTABLET

TABLESPACE = EASY_MASTER1

1) CREATE A NEW TABLESPACE WITH ENCRYPTION

create tablespace EASY_MASTER1_TDE datafile '+EASYDATA_DG2' size 8G ENCRYPTION USING 'AES256' DEFAULT STORAGE (ENCRYPT);

2) MOVE THE TABLE TO NEW ENCYPTED TABLESPACE

alter table EASYUSER.EASYTABLET_BKP move tablespace EASY_MASTER1_TDE;

3) REBUILD THE INDEXES

alter index EASYUSER.IX_ENTITYDOC_PREPROID_BANKID_NEW rebuild tablespace EASY_MASTER1_TDE;

4) To check TDE Tablespace

QUERY:

SQL> select a.TS#,a.NAME, b.ENCRYPTIONALG, b.STATUS from v$tablespace a,v$encrypted_tablespaces b where a.TS#=b.TS#;

TS# NAME ENCRYPT STATUS

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

241 EASY_MASTER1 AES256 NORMAL

242 EASY_MASTER2 AES256 NORMAL

499 CUSTOM_TDE AES256 NORMAL

SQL> select a.TS#,a.NAME, b.ENCRYPTIONALG, b.STATUS from v$tablespace a,v$encrypted_tablespaces b where a.TS#=b.TS# and a.NAME='CUSTOM_TDE';

TS# NAME ENCRYPT STATUS

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

499 CUSTOM_TDE AES256 NORMAL

How to change tde algorithm in oracle 19c

Changing the Transparent Data Encryption (TDE) algorithm in Oracle 19c depends on whether you are modifying it for an entire tablespace, a specific column, or setting a new default for the database

1. Change Algorithm for an Existing Tablespace

To change the algorithm for a tablespace that is already encrypted, you can use the Online Encryption feature. This allows you to re-key the tablespace with a new algorithm without taking it offline

ALTER TABLESPACE <tablespace_name> ENCRYPTION ONLINE USING 'AES256' REKEY;

Supported Algorithms: AES128, AES192, AES256, 3DES168, ARIA128, ARIA192, and ARIA256.

Default: If no algorithm is specified, it defaults to AES128

2. Change Algorithm for a Specific Column

You can modify the algorithm for an encrypted column by using the ALTER TABLE statement.

ALTER TABLE <table_name> MODIFY (<column_name> ENCRYPT USING 'AES256');

Note: Column encryption typically defaults to AES192

3. Change the Database Default Algorithm

If you want all future encrypted tablespaces to use a specific algorithm by default (e.g., changing from the standard AES128 to AES256), you can set a hidden initialization parameter

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

Requirement: This parameter was integrated into the April 2021 Release Update (19.11) and later; older 19c versions may require Patch 30398099.

Timing: This must be set before the first SET KEY operation or the creation of the keystore to be fully effective for future operations

4. Verify the Current Algorithm

To confirm which algorithm is currently being used, query the dynamic performance views:

SELECT tablespace_name, encrypted, status FROM dba_tablespaces; -- For detailed encryption info: SELECT * FROM v$encrypted_tablespaces;

For the Database Key

SELECT encryptionalg FROM v$database_key_info;

Question :how to troubleshoot TDE auto login issue

When troubleshooting an Oracle Transparent Data Encryption (TDE) auto-login issue (e.g., wallet/keystore closed or database restart failures), check the items below to pinpoint and resolve the problem

1. Check Keystore Status and Errors

Connect to the database as a user with SYSKM or ADMINISTER KEY MANAGEMENT privileges and check your wallet status:

SELECT WRL_TYPE, WRL_PARAMETER, STATUS, WALLET_TYPE FROM V$ENCRYPTION_WALLET;

Status is CLOSED: The auto-login file (cwallet.sso) is missing, corrupt, or unreadable by the Oracle software owner.

Error ORA-28365: wallet is not open: The auto-login file is not taking effect upon startup, requiring a manual password open

2. Verify Wallet Directory and Permissions

Ensure the sqlnet.ora file points to the correct directory and that permissions are properly set:

Path check: Open $ORACLE_HOME/network/admin/sqlnet.ora and verify the ENCRYPTION_WALLET_LOCATION directory parameter points to the correct wallet path.

File existence: Check that both ewallet.p12 (the password store) and cwallet.sso (the auto-login store) exist in this directory.

Permission check: Ensure the oracle OS user has read, write, and execute permissions on both files

3. Recreate the Auto-Login Wallet

If the .sso file is corrupt, generate a new auto-login file using the orapki command-line utility

orapki wallet create -wallet /path/to/your/wallet -auto_login_local

4. Check for Hidden sqlnet.ora Formatting Issues

Oracle databases are highly sensitive to formatting mistakes in configuration files: [1]

Make sure there are no extra spaces or typos before the ENCRYPTION_WALLET_LOCATION line in the sqlnet.ora file.
Ensure all parentheses in the parameter string are properly closed

5. Validate the Master Key

If the password-protected wallet (ewallet.p12) was moved or recreated, the auto-login file will not sync if it was generated before the master key was set. To fix this, you will need to open the password wallet and set/rotate the key again

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY your_wallet_password WITH BACKUP;

For more Details

https://docs.oracle.com/cd/F75778_01/English/install_upgrade_planning/analytics_security/83967.htm#subhead_cannotautocreatewalletora6

EasyReliableDBA

Friday, 22 May 2026

TDE implementation in oracle 19c and how to change tde algorithm in oracle 19c

Use Case

How to change tde algorithm in oracle 19c

Question :how to troubleshoot TDE auto login issue

No comments:

Post a Comment

Search This Blog