Transparent Data Encryption (TDE) In Oracle 12c

-- Creating Keystore directory
$ mkdir -p /orafs/app/oracle/12.2.0.1/db_1/server_wallet_home

-- Editing sqlnet.ora file as following:
$ vi /orafs/app/oracle/12.2.0.1/db_1/network/admin/sqlnet.ora

### Advance security wallet location
ENCRYPTION_WALLET_LOCATION =
 (SOURCE =
   (METHOD = FILE)
   (METHOD_DATA =
     (DIRECTORY = /orafs/app/oracle/12.2.0.1/db_1/server_wallet_home)
    )
  )


-- Bouncing Database 
SQL > shutdown immediate;
SQL > startup;

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status: NOT_AVAILABLE

-- Creating the keystore
SQL> administer key management create keystore '/orafs/app/oracle/12.2.0.1/db_1/server_wallet_home' identified by "wlt123";

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status: CLOSED

-- Opening the keystore

SQL> administer key management set keystore open identified by "wlt123";

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status : OPEN_NO_MASTER_KEY

-- Creating a MASTER KEY
SQL> administer key management set key identified by "wlt123" with backup;

-- Checking status of wallet
SQL > set pages 200 lines 200
col WRL_PARAMETER for a100
SELECT wrl_type, wrl_parameter, status from gv$encryption_wallet order by inst_id;

## Query status : OPEN


-- Creating auto login setup :

SQL> administer key management create auto_login keystore from keystore '/orafs/app/oracle/12.2.0.1/db_1/server_wallet_home' identified by "wlt123";





 ======================== Checking TDE =======================

---- Encrypting column of an existing table:

SQL> alter table test.account modify (ACCNAME encrypt);

Table altered.

-- You can turn off the encryption for a table:
SQL> alter table test.account modify (ACCNAME decrypt);

Table altered.


--- Encrypting a tablespace online using TDE encryption

SQL> alter tablespace test_tsp ENCRYPTION online ENCRYPT FILE_NAME_CONVERT = ('test_dbs.dbf', 'test_dbs_encrypt.dbf');

Tablespace altered.


SQL> select file_name from dba_data_files where tablespace_name = 'test_tsp';

FILE_NAME
-------------------------------------------------------------------------------------
/u01/app/oracle/oradata/test_dbs_encrypt.dbf


--- Decrypting the same tablespace online using TDE encryption
SQL> alter tablespace saidur_tsp ENCRYPTION ONLINE DECRYPT FILE_NAME_CONVERT = ('test_dbs_encrypt.dbf', 'test_dbs.dbf');

Tablespace altered.


 ============================== End =====================