TDE - Transparent Data Encryption

Best option to enable encryption for the data at rest. Security is based on the wallet file and wallet password.

It was 2 node RAC and 10.2.0.2 release. I visited all most of the articles and finally prepared the plan.

The plan was (as per documents and other source material)

1. Enable encryption from the node1 (Prd1)
2. Create the test table and insert some rows to verify the encryption
3. Repeat step 1 from node2 (Prd2)
4. Select the data from the table created in the step2 from the node2

The following shows the actions I took:

Node 1 (Prd1)

[oracle@prd1 oracle]$ cd $ORACLE_BASE/admin/NPRD
[oracle@prd1 NPRD]$ mkdir wallet

Note: I created the same directory structure in the 2nd node

[oracle@prd1 NPRD]$ ssh prd2 mkdir -p /u01/app/oracle/admin/NPRD/wallet
[oracle@prd1 NPRD]$sqlplus sys as sysdba

SQL> alter system set encryption key authenticated BY "xyzabc123";

System altered.

[oracle@prd1 wallet]$ ls -lrt
total 4
-rw------- 1 oracle oinstall 1309 Oct 20 23:09 ewallet.p12

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
nprd1

SQL> conn nbuser/xxxxxxxx
Connected.
SQL> create table tde_test ( SSN number);

Table created.

SQL> alter table tde_test modify (SSN encrypt using 'AES128' no salt);

Table altered.

SQL> insert into tde_test values (12341234);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tde_test;

SSN
----------
12341234

SQL> desc tde_test
Name Null? Type
----------------------------------------- -------- ----------------------------
SSN NUMBER ENCRYPT

Node 2 ( Prd2 ) :

[oracle@prd2 NPRD]$sqlplus sys as sysdba

SQL> alter system set encryption key authenticated BY "xyzabc123";

System altered.

[oracle@prd2 wallet]$ ls -lrt
total 4
-rw------- 1 oracle oinstall 1309 Oct 20 23:45 ewallet.p12

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
nprd2

SQL> conn nbuser/xxxxxxxx
Connected.

SQL> select * from tde_test;
select * from tde_test
*
ERROR at line 1:
ORA-28362: master key not found

SQL> desc tde_test
Name Null? Type
----------------------------------------- -------- ----------------------------
SSN NUMBER ENCRYPT

After couple of minutes thought I tried the following

copy the wallet files from one node to other

[oracle@prd2 wallet]$ ll
total 4
-rw------- 1 oracle oinstall 1309 Oct 20 23:45 ewallet.p12
[oracle@prd2 wallet]$ rm ewallet.p12
[oracle@prd2 wallet]$ ll
total 0

[oracle@prd2 wallet]$ scp prd1:/u01/app/oracle/admin/NPRD/wallet/ewallet.p12 .
[oracle@prd2 wallet]$
[oracle@prd2 wallet]$ ll
total 4
-rw------- 1 oracle oinstall 1309 Oct 20 23:58 ewallet.p12

sqlplus nbuser/xxxxxx

SQL> select * from tde_test;
select * from tde_test
*
ERROR at line 1:
ORA-28362: master key not found

SQL> alter system set wallet open identified by "xyzabc123";
alter system set wallet open identified by "xyzabc123"
*
ERROR at line 1:
ORA-28354: wallet already open

SQL> alter system set wallet close;

System altered.

SQL> alter system set wallet open identified by "xyzabc123";

System altered.

SQL> select * from tde_test;

SSN
----------
12341234

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prd2

I was able to resolve the issue after copying the wallet files from the master node to all other nodes and just open the wallet. The process of setting the encryption from each node was wrong. TDE is the best feature to enable encryption for data at rest. Cost saving and almost no complexity at all while implementing