Configure TDE AUTO_LOGIN in Oracle 19c
What is TDE?
In Oracle, TDE stands for Transparent Data Encryption. It is a feature that allows you to encrypt sensitive data stored in the database at the column, tablespace, or entire database level. TDE provides an additional layer of security by automatically encrypting data when it is written to disk and decrypting it when it is read into memory.
With TDE, the encryption and decryption processes are transparent to applications and users accessing the data. This means that you can continue to use your existing applications without any modifications while ensuring that the data is protected.
TDE uses industry-standard encryption algorithms, such as Advanced Encryption Standard (AES), to encrypt the data. The encryption keys used for TDE are stored in the Oracle Wallet, which is a secure repository for storing encryption keys and credentials.
By implementing TDE, you can prevent unauthorized access to sensitive data in case of data breaches, stolen media, or unauthorized direct access to database files. It helps organizations meet compliance requirements and protect data privacy.
It’s worth noting that TDE is an Oracle Database Enterprise Edition feature and may require an additional license.
Step 0: Check Oracle Database STATUS
[grid@lntdh1 ~]$ crsctl stat res ora.racdb.db -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.racdb.db
1 ONLINE ONLINE lntdh1 Open,HOME=/soft/racD
atabase,STABLE
2 ONLINE ONLINE lntdh2 Open,HOME=/soft/racD
atabase,STABLE
--------------------------------------------------------------------------------
[grid@lntdh1 ~]$ srvctl status database -d racdb
Instance racdb1 is running on node lntdh1
Instance racdb2 is running on node lntdh2
[grid@lntdh1 ~]$ srvctl status database -d racdb -v
Instance racdb1 is running on node lntdh1. Instance status: Open.
Instance racdb2 is running on node lntdh2. Instance status: Open.
[grid@lntdh1 ~]$
Step 1: Create Wallet folder in ASM
[grid@lntdh1 ~]$ asmcmd lsASMCMD> DATA/ FRA/ OCR/ ASMCMD> cd OCR ASMCMD> mkdir racdb ASMCMD> cd racdb ASMCMD> mkdir wallet ASMCMD> cd wallet ASMCMD> mkdir tde ASMCMD> cd tde ASMCMD> pwd +OCR/racdb/wallet/tde ASMCMD> Step 2: Update wallet details in parameter file SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show parameter db_n NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string racdb SQL> show parameter tde_configuration NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ tde_configuration string SQL> SQL> show parameter wallet_root NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ wallet_root string SQL> SQL> alter system set wallet_root='+OCR/racdb/wallet' scope=spfile sid='*'; System altered. SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID='*'; alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID='*' * ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-46693: The WALLET_ROOT location is missing or invalid. [oracle@lntdh1 ~]$ srvctl stop database -d racdb [oracle@lntdh1 ~]$ srvctl start database -d racdb SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH SID='*'; System altered. SQL> show parameter TDE_CONFIGURATION NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ tde_configuration string KEYSTORE_CONFIGURATION=FILE SQL> show parameter WALLET_ROOT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ wallet_root string +OCR/racdb/wallet SQL>
Step 3: Create KEYSTORE for container
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> administer key management create keystore identified by WALLET_PASSWORD;
keystore altered.
SQL> administer key management set keystore open identified by PASSWORD_TEST;
administer key management set keystore open identified by PASSWORD_TEST
*
ERROR at line 1:
ORA-28353: failed to open wallet
SQL> administer key management set keystore open identified by WALLET_PASSWORD;
keystore altered.
SQL>
###############################################################
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> alter session set container=pdb1;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 MOUNTED
SQL> administer key management set keystore open identified by PASSWORD_TEST;
administer key management set keystore open identified by PASSWORD_TEST
*
ERROR at line 1:
ORA-46627: keystore password mismatch
SQL> administer key management set keystore open identified by WALLET_PASSWORD;
keystore altered.
SQL> administer key management set key identified by PASSWORD_TEST with backup;
administer key management set key identified by PASSWORD_TEST with backup
*
ERROR at line 1:
ORA-46627: keystore password mismatch
SQL> administer key management set key identified by WALLET_PASSWORD with backup;
administer key management set key identified by WALLET_PASSWORD with backup
*
ERROR at line 1:
ORA-28388: database is not open in read/write mode
SQL> alter pluggable database pdb1 open;
Pluggable database altered.
SQL> administer key management set key identified by WALLET_PASSWORD with backup;
administer key management set key identified by WALLET_PASSWORD with backup
*
ERROR at line 1:
ORA-46671: master key not set in root container
SQL>
###############################################################
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> administer key management set key identified by WALLET_PASSWORD with backup;
keystore altered.
SQL>
###############################################################
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> administer key management set key identified by WALLET_PASSWORD with backup;
keystore altered.
SQL>
###############################################################
Step 4: Create AUTO_LOGIN wallet
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> administer key management create auto_login keystore from keystore '+OCR/racdb/wallet/tde' identified by WALLET_PASSWORD; keystore altered. SQL> col WRL_PARAMETER for a27 col WRL_TYPE for a5 col STATUS for a10 set lines 200 select * from v$encryption_wallet; SQL> select * from v$encryption_wallet; WRL_T WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ----- --------------------------- ---------- -------------------- --------- -------- --------- ---------- ASM +OCR/racdb/wallet/tde/ OPEN PASSWORD SINGLE NONE NO 1 ASM CLOSED UNKNOWN SINGLE UNITED UNDEFINED 2 ASM OPEN PASSWORD SINGLE UNITED NO 3 SQL> [oracle@lntdh1 ~]$ srvctl stop database -d racdb [oracle@lntdh1 ~]$ srvctl start database -d racdb SQL> select * from v$encryption_wallet; WRL_T WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ----- --------------------------- ---------- -------------------- --------- -------- --------- ---------- ASM +OCR/racdb/wallet/tde/ OPEN AUTOLOGIN SINGLE NONE NO 1 ASM OPEN AUTOLOGIN SINGLE UNITED NO 2 ASM OPEN AUTOLOGIN SINGLE UNITED NO 3
Step 5: TEMP tablespace in container database
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select tablespace_name,file_name,bytes/1024/1024 size_mb from dba_temp_files; TABLESPACE_NAME FILE_NAME SIZE_MB ------------------------------ -------------------------------------------------- ---------- TEMP +DATA/RACDB/TEMPFILE/temp.283.1138455825 35 SQL> create temporary tablespace temp_ts tempfile '+DATA' size 35M encryption encrypt; Tablespace created. SQL> alter database default temporary tablespace temp_ts; Database altered. SQL> drop tablespace temp including contents and datafiles; Tablespace dropped. SQL> create temporary tablespace temp tempfile '+DATA' size 35M encryption encrypt; Tablespace created. SQL> alter database default temporary tablespace temp; Database altered. SQL> drop tablespace temp_ts including contents and datafiles; Tablespace dropped. SQL>
Step 6: Enable TDE for all other tablespace in Container database
SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> select 'alter tablespace ' || tablespace_name ||' encryption online encrypt;' from dba_tablespaces; 'ALTERTABLESPACE'||TABLESPACE_NAME||'ENCRYPTIONONLINEENCRYPT;' -------------------------------------------------------------------------- alter tablespace SYSTEM encryption online encrypt; alter tablespace SYSAUX encryption online encrypt; alter tablespace UNDOTBS1 encryption online encrypt; alter tablespace TEMP encryption online encrypt; <== NOT NEEDED, AS ALREDY DONE IN PREVIOUS STEP alter tablespace USERS encryption online encrypt; alter tablespace UNDOTBS2 encryption online encrypt; 6 rows selected. SQL> alter tablespace SYSTEM encryption online encrypt; Tablespace altered. SQL> alter tablespace SYSAUX encryption online encrypt; Tablespace altered. SQL> alter tablespace UNDOTBS1 encryption online encrypt; Tablespace altered. SQL> alter tablespace USERS encryption online encrypt; Tablespace altered. SQL> alter tablespace UNDOTBS2 encryption online encrypt; Tablespace altered. SQL>
Step 7: Enable TDE of TEMP tablespace in PDB
SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> select tablespace_name,file_name,bytes/1024/1024 size_mb from dba_temp_files; TABLE FILE_NAME SIZE_MB ----- --------------------------------------------------------------------------- ---------- TEMP +DATA/RACDB/FD2A5683339926D5E053790BA8C061C6/TEMPFILE/temp.295.1138456871 36 SQL> create temporary tablespace temp_ts tempfile '+DATA' size 36M encryption encrypt; Tablespace created. SQL> alter database default temporary tablespace temp_ts; Database altered. SQL> drop tablespace TEMP including contents and datafiles; Tablespace dropped. SQL> create temporary tablespace temp tempfile '+DATA' size 36M encryption encrypt; Tablespace created. SQL> alter database default temporary tablespace temp; Database altered. SQL> drop tablespace temp_ts including contents and datafiles; Tablespace dropped. SQL>
Step 8: Enable TDE for all other tablespaces in PDB
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB1 READ WRITE NO
SQL> select 'alter tablespace ' || tablespace_name ||' encryption online encrypt;' from dba_tablespaces where encrypted='NO';
'ALTERTABLESPACE'||TABLESPACE_NAME||'ENCRYPTIONONLINEENCRYPT;'
--------------------------------------------------------------------------
alter tablespace SYSTEM encryption online encrypt;
alter tablespace SYSAUX encryption online encrypt;
alter tablespace UNDOTBS1 encryption online encrypt;
alter tablespace UNDO_2 encryption online encrypt;
alter tablespace USERS encryption online encrypt;
SQL> alter tablespace SYSTEM encryption online encrypt;
Tablespace altered.
SQL> alter tablespace SYSAUX encryption online encrypt;
Tablespace altered.
SQL> alter tablespace UNDOTBS1 encryption online encrypt;
Tablespace altered.
SQL> alter tablespace UNDO_2 encryption online encrypt;
Tablespace altered.
SQL> alter tablespace USERS encryption online encrypt;
Tablespace altered.
SQL>
Harshad Vengurlekar
Experienced OCM-certified Oracle Database Administrator with over 18 years of expertise in designing, implementing, and managing complex database solutions. My expertise spans performance optimization, security, and high-stakes solution implementation. Adept at managing complex environments with precision.
No Comments