TDE(transparent data encryption) in ORACLE database

TDE implementation in databases as below

-- pre checks in database for TDE ( should show closed)

select * from GV$ENCRYPTION_WALLET

  INST_ID WRL_TYPE             WRL_PARAMETER                                                          STATUS
---------- -------------------- ---------------------------------------------------------------------- ------------------
         2 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           CLOSED
         3 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           CLOSED
         4 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           CLOSED
         1 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           CLOSED


select * from V$ENCRYPTION_WALLET

WRL_TYPE             WRL_PARAMETER                                                          STATUS
-------------------- ---------------------------------------------------------------------- ------------------
file                 /oracle/app/admin/ORCL/wallet                                      CLOSED

-- Create Directories in all respective node servers

mkdir -p -m 0700 /oracle/app/admin/ORCL/wallet

ssh subhen-db03 "mkdir -p -m 0700 /oracle/app/admin/ORCL/wallet"
ssh subhen-db04 "mkdir -p -m 0700 /oracle/app/admin/ORCL/wallet"
ssh subhen-db05 "mkdir -p -m 0700 /oracle/app/admin/ORCL/wallet"

mkdir -p -m 0700 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet
ssh subhen-db03 "mkdir -p -m 0700 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet"
ssh subhen-db04 "mkdir -p -m 0700 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet"
ssh subhen-db05 "mkdir -p -m 0700 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet"

-- Create wallet in 1st node server

[oracle@subhen-db01 bin]$ which orapki
/oracle/app/database/11.2.0.3/dbhome_1/bin/orapki
[oracle@subhen-db01 bin]$ orapki wallet create -wallet /oracle/app/admin/ORCL/wallet -pwd 'XXXXXX' -auto_login
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.


-- Restart database
srvctl stop database -d ORCL
srvctl start database -d ORCL

-- Set Master Key from sqlplus

[oracle@subhen-db01 bin]$ orapki wallet display -wallet /oracle/app/admin/ORCL/wallet -pwd 'XXXXXX'
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.

Requested Certificates:
User Certificates:
Trusted Certificates:
Subject:        OU=Class 1 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 3 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Class 2 Public Primary Certification Authority,O=VeriSign\, Inc.,C=US
Subject:        OU=Secure Server Certification Authority,O=RSA Data Security\, Inc.,C=US
Subject:        CN=GTE CyberTrust Global Root,OU=GTE CyberTrust Solutions\, Inc.,O=GTE Corporation,C=US

SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "XXXXXX";

System altered.

-- Shutdown database
srvctl stop database -d ORCL


-- Copy wallets into all directories of all servers..

cd /oracle/app/admin/ORCL/wallet
scp -p * oracle@subhen-db03:/oracle/app/admin/ORCL/wallet
scp -p * oracle@subhen-db04:/oracle/app/admin/ORCL/wallet
scp -p * oracle@subhen-db05:/oracle/app/admin/ORCL/wallet

cp -p * /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet/
scp -p * oracle@subhen-db03:/oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet
scp -p * oracle@subhen-db04:/oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet
scp -p * oracle@subhen-db05:/oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet

-- Startup database
srvctl start database -d ORCL


-- Check the status


SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                                          STATUS
-------------------- ---------------------------------------------------------------------- ------------------
file                 /oracle/app/admin/ORCL/wallet                                      OPEN

SQL> select * from gv$encryption_wallet;

   INST_ID WRL_TYPE             WRL_PARAMETER                                                          STATUS
---------- -------------------- ---------------------------------------------------------------------- ------------------
         1 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           OPEN
         3 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           OPEN
         2 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           OPEN
         4 file                 /oracle/app/database/11.2.0.3/dbhome_1/admin/ORCL/wallet           OPEN


-- TDE implementation finished

-- creating a tablespace as encrypted and assigning a new user this encrypted tablespace

SQL> CREATE TABLESPACE subu_ENCRYPTED  DATAFILE '+DATA' SIZE 2G
AUTOEXTEND ON NEXT 104857600 MAXSIZE UNLIMITED
  2    3  LOGGING ONLINE PERMANENT BLOCKSIZE 8192
  4  EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
  5  ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL> create user subu_XBRL identified by subu#123
default tablespace subu_ENCRYPTED
temporary tablespace TEMP
quota UNLIMITED on subu_ENCRYPTED
profile  APP_PROFILE;