How to Enable Oracle TDE 19c Step by Step

Oracle TDE 19c

I have talked about how to extract plain text from a normal, non-encrypted data file before. If you're considering a more secure way to protect data files, you should go for configuring Oracle TDE.

There're 5 major steps to enable Oracle Transparent Data Encryption (TDE) 19c on a RAC database in this post.

Set Wallet Parameters
Create Keystores
Set TDE Master Key
Prepare Wallet for Node 2
Encrypt DATA

For single-instance databases, the steps are almost the same, just skipping step D to continue.

Set Wallet Parameters

We should let the database know where to find the wallet by setting related parameters.

Please note that, although SQLNET.ENCRYPTION_WALLET_LOCATION parameter specified in sqlnet.ora is still one of the search order of wallet location, this parameter has been deprecated.

Check Node 1 Environment

We should make sure the environment before doing it.


[oracle@primary01 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/19.0.0/db_1

[oracle@primary01 ~]$ echo $ORACLE_UNQNAME

ORCLCDB

[oracle@primary01 ~]$ sqlplus / as sysdba

...

SQL> show pdbs;



    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 ORCLPDB                        READ WRITE NO

SQL> show con_name;



CON_NAME

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

CDB$ROOT

Set WALLET_ROOT

WALLET_ROOT is a static parameter used to specify the base location of wallet. But how do we determine where to put the wallet? Here we follow the conventional location of xdb_wallet in a single-instance or a RAC DB.


SQL> alter system set wallet_root='/u01/app/oracle/product/19.0.0/db_1/admin/ORCLCDB/wallet' scope=spfile sid='*';



System altered.



SQL> exit;

Don't use symbol ? to represent $ORACLE_HOME when setting the parameter, it costs you several failed startups before finding the truth.

Please note that, I know you could have considered putting wallet in ASM, a shared space for it, but I think wallet in ASM is pretty hard to mange and migrate to another place, e.g. standby or testing database.

We should restart the database to take WALLET_ROOT effect.


[oracle@primary01 ~]$ srvctl stop database -d orclcdb

[oracle@primary01 ~]$ srvctl start database -d orclcdb

[oracle@primary01 ~]$ sqlplus / as sysdba

...

SQL> show parameter wallet_root



NAME                                 TYPE        VALUE

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

wallet_root                          string      /u01/app/oracle/product/19.0.0

                                                 /db_1/admin/ORCLCDB/wallet

In this case, we place it in the file system instead of ASM. However, you can move the wallet into ASM later if you have changed your mind.

Set TDE_CONFIGURATION

TDE_CONFIGURATION can be set dynamically. In which , the keystore type that we choose is FILE.


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



System altered.

It's a dynamic parameter, no need to restart the database.

Create Keystores

Create Password-Protected Keystore

We created a password-protected keystore. Please note that, welcome1 is the password, you should use yours.


SQL> administer key management create keystore identified by welcome1;



keystore altered.

Create Auto-Login Keystore

Auto-Login Keystore enables us to open and close password-protected keystore automatically whenever we need.


SQL> administer key management create auto_login keystore from keystore identified by welcome1;



keystore altered.

Check Keystore Files

We should check what files we have now.


[oracle@primary01 ~]$ cd $ORACLE_HOME/admin/$ORACLE_UNQNAME/wallet/tde

[oracle@primary01 tde]$ pwd

/u01/app/oracle/product/19.0.0/db_1/admin/ORCLCDB/wallet/tde

[oracle@primary01 tde]$ ll

total 8

-rw------- 1 oracle asmadmin 2600 Aug  7 14:54 cwallet.sso

-rw------- 1 oracle asmadmin 2555 Aug  7 14:53 ewallet.p12

In which, ewallet.p12 is the password-protected keystore and cwallet.sso is the auto-login keystore.

Set TDE Master Key

Open Keystore

Before we can set the TDE master key in the keystore, we should open it. To open password-protected keystore, we should use FORCE KEYSTORE clause, no matter which container you're in.


SQL> administer key management set keystore open force keystore identified by welcome1 container=all;



keystore altered.

We check current status of our wallet.


SQL> select con_id, wallet_type, status from v$encryption_wallet;



    CON_ID WALLET_TYPE          STATUS

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

         1 PASSWORD             OPEN_NO_MASTER_KEY

         2 PASSWORD             OPEN_NO_MASTER_KEY

         3 PASSWORD             OPEN_NO_MASTER_KEY

As status OPEN_NO_MASTER_KEY told us, there's nothing in the keystore. So next, let's set a TDE master key in the keystore.

Set Key

We'd like to use the master key in all container and additionally backup the old keystore.


SQL> administer key management set key force keystore identified by welcome1 with backup container=all;



keystore altered.

There's somewhat different in the keystore.


SQL> select con_id, wallet_type, status from v$encryption_wallet;



    CON_ID WALLET_TYPE          STATUS

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

         1 PASSWORD             OPEN

         2 PASSWORD             OPEN

         3 PASSWORD             OPEN

Furthermore, it did a backup for the old password-protected keystore.


[oracle@primary01 tde]$ ll

total 20

-rw------- 1 oracle asmadmin 5512 Aug  7 16:48 cwallet.sso

-rw------- 1 oracle asmadmin 2555 Aug  7 16:48 ewallet_2021080708481713.p12

-rw------- 1 oracle asmadmin 5467 Aug  7 16:48 ewallet.p12

Close Keystore

To start using the auto-login keystore, we should close the password-protected keystore.


SQL> administer key management set keystore close identified by welcome1 container=all;



keystore altered.



SQL> select con_id, wallet_type, status from v$encryption_wallet;



    CON_ID WALLET_TYPE          STATUS

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

         1 AUTOLOGIN            OPEN

         2 AUTOLOGIN            OPEN

         3 AUTOLOGIN            OPEN

Prepare Wallet for Node 2

A simple copy from the node 1 can make this.

Check Node 2 Environment

Let's see the environment of node 2.


[oracle@primary02 ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/19.0.0/db_1

[oracle@primary02 ~]$ echo $ORACLE_UNQNAME

ORCLCDB

Copy Wallet

We should copy the entire wallet to node 2 for enabling to use TDE.


[oracle@primary02 ~]$ scp -rp primary01:$ORACLE_HOME/admin/$ORACLE_UNQNAME/wallet $ORACLE_HOME/admin/$ORACLE_UNQNAME/

ewallet.p12                                   100% 5467     9.2MB/s   00:00

cwallet.sso                                   100% 5512    11.1MB/s   00:00

ewallet_2021080708481713.p12                  100% 2555     5.8MB/s   00:00

[oracle@primary02 ~]$ cd $ORACLE_HOME/admin/$ORACLE_UNQNAME/wallet/tde

[oracle@primary02 tde]$ ll

total 20

-rw------- 1 oracle asmadmin 5512 Aug  7 16:48 cwallet.sso

-rw------- 1 oracle asmadmin 2555 Aug  7 16:48 ewallet_2021080708481713.p12

-rw------- 1 oracle asmadmin 5467 Aug  7 16:48 ewallet.p12

We preserved all the permission mode, ownership and timestamp for the wallet.

Encrypt DATA

I did all the following operations on node 2 purposely to verify the wallet copying is working.


[oracle@primary02 tde]$ sqlplus / as sysdba

...

SQL> alter session set container=ORCLPDB;



Session altered.



SQL> show con_name;



CON_NAME

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

ORCLPDB

For comparing normal data and encrypted data, we prepare a control test.

Create Normal and Encrypted Tablespaces

Normal Tablespace


SQL> create tablespace NOR_DATA datafile '+DATA/ORCLCDB/ORCLPDB/NOR_DATA_01.dbf' size 10m autoextend on next 10m maxsize unlimited;



Tablespace created.

Encrypted Tablespace

AES128

If you didn't specify any encryption algorithm, AES128 is used by default.


SQL> create tablespace TDE_DATA datafile '+DATA/ORCLCDB/ORCLPDB/TDE_DATA_01.dbf' size 10m autoextend on next 10m maxsize unlimited encryption encrypt;



Tablespace created.

AES256

Explicitly specifying AES256 encryption algorithm enables the most secure encryption, if you really want it.


SQL> create tablespace TDE_DATA datafile '+DATA/ORCLCDB/ORCLPDB/TDE_DATA_01.dbf' size 10m autoextend on next 10m maxsize unlimited encryption using 'AES256' encrypt;



Tablespace created.

The performance overhead of using AES256 is roughly considered 40% slower than AES128, therefore, I would recommend AES128 which is a balanced solution.

If you have any benchmark about comparing those algorithm, please comment your thinking below.

Insert Data on Both Tablespaces

Normal Table


SQL> conn hr/hr@orclpdb

Connected.

SQL> create table nor_test (id varchar2(10)) tablespace NOR_DATA;



Table created.



SQL> insert into nor_test (id) values ('A123456789');



1 row created.

Encrypted Table


SQL> create table tde_test (id varchar2(10)) tablespace TDE_DATA;



Table created.



SQL> insert into tde_test (id) values ('A123456789');



1 row created.

Don't forget to commit the change.


SQL> commit;



Commit complete.



SQL> exit;

As you noticed, string A123456789 has been inserted into both tables for doing some comparison later.

Comparing Data Files

We should exclude any external factors before comparing both data files by stopping the database.

Stop RAC DB


[oracle@primary01 ~]$ srvctl stop database -d orclcdb

Copy Both Data Files out of ASM


[grid@primary01 ~]$ asmcmd cp +DATA/ORCLCDB/ORCLPDB/NOR_DATA_01.dbf /tmp/NOR_DATA_01.dbf

copying +DATA/ORCLCDB/ORCLPDB/NOR_DATA_01.dbf -> /tmp/NOR_DATA_01.dbf

[grid@primary01 ~]$ asmcmd cp +DATA/ORCLCDB/ORCLPDB/TDE_DATA_01.dbf /tmp/TDE_DATA_01.dbf

copying +DATA/ORCLCDB/ORCLPDB/TDE_DATA_01.dbf -> /tmp/TDE_DATA_01.dbf

There're more ways to copy ASM files from one place to another, or vice versa.

Comparing Content

We extract strings from both data files.


[grid@primary01 ~]$ strings /tmp/NOR_DATA_01.dbf | grep "A123456789"

A123456789

[grid@primary01 ~]$ echo $?

0

[grid@primary01 ~]$ strings /tmp/TDE_DATA_01.dbf | grep "A123456789"

[grid@primary01 ~]$ echo $?

1

As you can see, the plain text in the normal data file is shown. On the other side, we got nothing from the encrypted data file. That's the power of TDE.

Start RAC DB

We can observe whether the behavior of TDE is persistent or not after a restart.


[oracle@primary01 ~]$ srvctl start database -d orclcdb

Check Wallet Status


[oracle@primary02 tde]$ sqlplus / as sysdba

...

SQL> set linesize 200;

SQL> column inst_id format 9;

SQL> column con_id format 9;

SQL> column wallet_type format a10;

SQL> column status format a5;

SQL> column wrl_parameter format a62;

SQL> select inst_id, con_id, wallet_type, status, wrl_parameter from gv$encryption_wallet order by 1,2;



INST_ID CON_ID WALLET_TYP STATU WRL_PARAMETER

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

      1      1 AUTOLOGIN  OPEN  /u01/app/oracle/product/19.0.0/db_1/admin/ORCLCDB/wallet/tde/

      1      2 AUTOLOGIN  OPEN

      1      3 AUTOLOGIN  OPEN

      2      1 AUTOLOGIN  OPEN  /u01/app/oracle/product/19.0.0/db_1/admin/ORCLCDB/wallet/tde/

      2      2 AUTOLOGIN  OPEN

      2      3 AUTOLOGIN  OPEN



6 rows selected.

Auto-login keystore is enabling and working, we should additionally check the encrypted data.


SQL> conn hr/hr@orclpdb

Connected.

SQL> select * from tde_test;



ID

----------

A123456789

The operation is transparent!

14 thoughts on “How to Enable Oracle TDE 19c Step by Step”

ab January 4, 2023 at 5:50 am


perfect doc for TDE enable on RAC PDB/CDB database
1. Ed Chen January 4, 2023 at 7:48 am
  
  
  Thanks a lot.
Alfy January 31, 2023 at 9:27 am


Super!
1. Ed Chen January 31, 2023 at 10:51 am
  
  
  Thanks for your appreciation.
Karel February 25, 2023 at 12:37 pm


Great Post!!! Thanks
1. Ed Chen February 25, 2023 at 1:33 pm
  
  
  It is a pleasure.
Mamatha Ratkal March 8, 2023 at 3:59 am


Very informative step by step instruction
1. Ed Chen March 8, 2023 at 5:12 am
  
  
  I’m glad the procedure is useful.
Lokesh Gurram June 6, 2023 at 12:14 pm


Very nice article for beginners.
1. Ed Chen June 6, 2023 at 12:50 pm
  
  
  Thanks for your appreciation.
Sumeet September 24, 2023 at 9:13 am


Thanks for sharing this precise and very objectively presented DOC with all ..
1. Ed Chen September 24, 2023 at 9:33 am
  
  
  My pleasure!
PRAKASH PATEL November 3, 2023 at 3:39 pm


Hello Ed,

Thanks for sharing the TDE troubleshooting.

Hope you recognize me.

Prakash Patel [ Former ADP USA]
1. Ed Chen November 4, 2023 at 11:26 am
  
  
  You’re welcome!