How to Configure TDE Auto Login Wallet in Oracle 19c

May 22, 2020

In this article we will discuss about enabling Transparent Data Encryption – TDE in Oracle 19c. To configure Auto Login Wallet in Oracle 19c there are few parameters which needs to be set in spfile.

How to use TDE Encryption for Database Export in Oracle

Steps to configure Transparent Data Encryption – TDE in Oracle 19c and enable auto login.

Configure TDE Autologin Wallet in Oracle 19c

STEP 1: Create pfile from spfile in below location.

$ORACLE_BASE/admin/db_unique_name/pfile
SQL> create pfile='/scratch/app/oracle/admin/umtqa/pfile/initumtqa7may.ora' from spfile;
File created.
SQL> exit

STEP 2: Configure the Keystore Location and Type

  1. You must configure Keystore location and type by setting WALLET_ROOT and TDE_CONFIGURATION parameters in pfile or spfile.
  2. If necessary, create a wallet directory. Typically, wallet directory is located in $ORACLE_BASE/admin/db_unique_name/wallet. Ideally wallet directory should be empty.
SQL> alter system set WALLET_ROOT="/scratch/app/oracle/admin/umtqa/wallet" scope=spfile;
System altered.

SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;
System altered.

STEP 3: Restart the Database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 4546622232 bytes
Fixed Size 8905496 bytes
Variable Size 838860800 bytes
Database Buffers 3690987520 bytes
Redo Buffers 7868416 bytes
Database mounted.
Database opened.

Step 4: Create software Keystore

SQL> administer key management create keystore '/scratch/app/oracle/admin/umtqa/wallet/tde' identified by welcome1;
keystore altered.

STEP 5: Check the status of the wallet

SQL> SELECT STATUS FROM V$ENCRYPTION_WALLET;
STATUS
CLOSED

SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE
WRL_PARAMETER
STATUS CON_ID

FILE
/scratch/app/oracle/admin/umtqa/wallet/tde/
CLOSED 0

SQL> !ls -lrt /scratch/app/oracle/admin/umtqa/wallet/tde
total 4
-rw------- 1 gbuora dba 2555 May 7 00:03 ewallet.p12

STEP 5: Configure Auto Login Keystore and check the status

SQL> administer key management create auto_login keystore from keystore '/scratch/app/oracle/admin/umtqa/wallet/tde' identified by welcome1;
keystore altered.

SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE
WRL_PARAMETER
STATUS CON_ID

FILE
/scratch/app/oracle/admin/umtqa/wallet/tde/
OPEN_NO_MASTER_KEY 0

SQL> select * from v$encryption_wallet;
WRL_TYPE
WRL_PARAMETER
STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC

CON_ID
FILE
/scratch/app/oracle/admin/umtqa/wallet/tde/
OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE NONE UNDEFINED
0

STEP 6: Open the software Keystore

SQL> administer key management set keystore open force keystore identified by welcome1;
keystore altered.

To switch over to opening the password-protected software keystore when an auto-login keystore is configured and is currently open, specify the FORCE KEYSTORE clause as follows.

STEP 7: Set the Keystore TDE Encryption Master Key

SQL> administer key management set key FORCE KEYSTORE identified by welcome1 with backup; 
keystore altered.

SQL> SELECT * FROM v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID

FILE /scratch/app/oracle/admin/umtqa/wallet/tde/   OPEN   AUTOLOGIN   SINGLE NONE  NO  0

As you can see autologin wallet is open and enabled, now there is no overhead of opening or closing the wallet. Once you will restart the database, wallet will be automatically opened.

Encrypt tablespace:

create tablespace TBS_NAME datafile 'LOCATION' size 100m autoextend on maxsize 31G EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ENCRYPTION DEFAULT STORAGE (ENCRYPT);

CREATE TABLESPACE TBS_NAME DATAFILE 'LOCATION' SIZE 1G AUTOEXTEND ON  maxsize 31G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Check Encrypted Tablespace

SQL> select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC

SYSTEM NO
SYSAUX NO
UNDOTBS1 NO
TEMP NO
USERS NO
ORA_TDS YES
ORA_TDE_AUDIT_TRIAL YES

Must Read: