Oracle TDE Setup - nchillal/Oracle GitHub Wiki

Get the DB_UNIQUE_NAME

In PR/HA/DP/DS

DB_UNIQUE_NAME=$(sqlplus -s / as sysdba <<!
SET HEADING OFF
SELECT value FROM v\$spparameter WHERE name = 'db_unique_name';
!
);

DB_UNIQUE_NAME=$(echo ${DB_UNIQUE_NAME}|tr -d '\n')

Create a directory to store the wallet.

In PR/HA/DP/DS

mkdir -p ${ORACLE_BASE}/admin/${DB_UNIQUE_NAME}/wallet && ls -ld ${ORACLE_BASE}/admin/${DB_UNIQUE_NAME}/wallet

Change permission of the wallet directory created above

chmod -R 700 ${ORACLE_BASE}/admin

Specify an Oracle Wallet Location in the sqlnet.ora File

ENCRYPTION_WALLET_LOCATION =
   (SOURCE = (METHOD = FILE)
     (METHOD_DATA =
      (DIRECTORY = ${ORACLE_BASE}/admin/<DB_UNIQUE_NAME>/wallet)
     )
   )

NOTE: If the compatibility of the database is set to a release earlier than Oracle Database Release 10.2, then restart the database.

Create the Master Encryption Key

In PR

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY &pwd;

The preceding command achieves the following

  • If no encrypted wallet is present in the directory specified, an encrypted wallet is created (ewallet.p12), the wallet is opened, and the master encryption key for TDE is created/re-created.
  • If an encrypted wallet is present in the directory specified, the wallet is opened, and the master encryption key for TDE is created/re-created.

Verify wallet location and its status

COLUMN wrl_parameter FORMAT a60
SET LINESIZE 200 PAGESIZE 1000
SELECT * FROM v$encryption_wallet;

Open the oracle wallet if not open

ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY &pwd;

Create a auto-login wallet (On Primary)

cd ${ORACLE_BASE}/admin/${DB_UNIQUE_NAME}/wallet
orapki wallet create -wallet . -auto_login

Change permissions for wallet files

chmod 600 ewallet.p12 cwallet.sso

Change permission to make these files immutable

chattr +i ewallet.p12 cwallet.sso

NOTE: Should be logged in as root.

Check immutable bit has not been set

lsattr ewallet.p12 cwallet.sso

NOTE: This is how it should be after making it immutable

----i--------e--- cwallet.sso
----i--------e--- ewallet.p12

Copy ewallet.p12 file from Primary Database server to Standby Database servers.

scp -rp ewallet.p12 <Standby hostname>:${ORACLE_BASE}/admin/${DB_UNIQUE_NAME}/wallet

Create a auto-login wallet (On Standby)

cd ${ORACLE_BASE}/admin/${DB_UNIQUE_NAME}/wallet
orapki wallet create -wallet . -auto_login

Create an encrypted tablespace

CREATE TABLESPACE data_tde_ts  ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT)
DATAFILE '+data' SIZE 8192M;

CREATE TABLESPACE index_tde_ts  ENCRYPTION USING 'AES256'
DEFAULT STORAGE (ENCRYPT)
DATAFILE '+data' SIZE 8192M;

List all encrypted tablespace

SELECT  tablespace_name, encrypted
FROM    dba_tablespaces
WHERE   tablespace_name LIKE '%TDE%';

To know which encryption algorithm each of them use

SELECT * FROM v$encrypted_tablespaces;

Close wallet

ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
ALTER SYSTEM SET ENCRYPTION WALLET CLOSE IDENTIFIED BY &pwd;

Change password for oracle wallet

Do not do change password if you unsure of it does.

cd ${ORACLE_BASE}/admin/${DB_UNIQUE_NAME}/wallet
orapki wallet change_pwd -wallet .

As root user, change permission to make ewallet.p12 and cwallet.sso mutable

chattr -i ewallet.p12 cwallet.sso

NOTE: This is how it should look after making it mutable.

-------------e--- cwallet.sso
-------------e--- ewallet.p12

Troubleshooting (MRP going down due to wallet not open).

  1. Verify wallet location (SQL is above)

  2. If v$encryption_wallet shows different wallet path than default ($ORACLE_BASE/admin/<DB_UNIQUE_NAME>/wallet), for database to pickup correct wallet, entry below content in sqlnet.ora to make sure correct encryption wallet location is picked.

ENCRYPTION_WALLET_LOCATION =
   (SOURCE = (METHOD = FILE)
     (METHOD_DATA =
      (DIRECTORY = ${ORACLE_BASE}/admin/<DB_UNIQUE_NAME>/wallet)
     )
   )
⚠️ **GitHub.com Fallback** ⚠️