How to Configure TDE in Oracle 19c Standalone Database in Oracle Linux 7.9 - Oracle Transparent Data Encryption Demo
How to Configure TDE in Oracle 19c Standalone Database in Oracle Linux 7.9:
In this video, I will demonstrate how we can configure TDE in oracle 19c standalone database running on Linux 7.9
Here are the high level steps:
STEP 1: Create pfile from spfile in below location
SQL> show parameter spfile;
SQL> create pfile='/home/oracle/srcdb19cpretde.ora' from spfile;
STEP 2: Configure the Keystore Location and Type
We are going to configure Keystore location and type by setting WALLET_ROOT and TDE_CONFIGURATION parameters in pfile or spfile.
Note: from 18c onwards we have to configure WALLET_ROOT,TDE_CONFIGURATION parameter and need bounce the database, SQLNET.ORA no longer needed
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.
mkdir -p /u01/app/oracle/admin/srcdb19c/wallet
Pre-Checks:
SQL> show parameter WALLET_ROOT
SQL> show parameter TDE_CONFIGURATION
alter system set WALLET_ROOT="/u01/app/oracle/admin/srcdb19c/wallet" scope=spfile;
Note: Bounce the database otherwise you will get below error
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;
alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-46693: The WALLET_ROOT location is missing or invalid.
STEP 3: Bounce the Database
[oracle@srlab ~]$ sqlplus "/as sysdba"
SQL>shut immediate;
SQL>startup;
SQL> show parameter WALLET_ROOT
[oracle@srlab ~]$ cd /u01/app/oracle/admin/srcdb19c/wallet
[oracle@srlab wallet]$ ls -lrt
Now set the KEYSTORE for TDE
alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;
SQL> show parameter TDE_CONFIGURATION
SQL> show parameter wallet_root
SQL> show parameter tde_configuration
STEP 4: Create software Keystore
administer key management create keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123;
SQL> administer key management create keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123;
[oracle@srlab wallet]$ ls -lrt
STEP 5: Check the status of the wallet
set lines 200
column WRL_PARAMETER format a40
select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123;
SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123;
SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123;
SQL> select * from v$encryption_wallet;
STEP 6: Open the software Keystore
administer key management set keystore open force keystore identified by welcome123;
SQL> administer key management set keystore open force keystore identified by welcome123;
STEP 7: Set the Keystore TDE Encryption Master Key
administer key management set key FORCE KEYSTORE identified by welcome123 with backup;
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
SQL> administer key management set key FORCE KEYSTORE identified by welcome123 with backup;
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
[oracle@srlab tde]$ ls -lrt
[oracle@srlab tde]$ pwd
SQL> alter tablespace users encryption online encrypt;
SQL> select * from v$tablespace;
SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from DBA_TABLESPACES;
SQL> select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status from v$tablespace t, v$encrypted_tablespaces e where t.ts#=e.ts#;
Activity log:
login as: oracle
oracle@192.168.56.103's password:
Last login: Sat Sep 11 23:40:37 2021 from 192.168.56.1
[oracle@srlab ~]$ ps -ef |grep pmon
oracle 6295 1 0 07:23 ? 00:00:00 ora_pmon_srcdb19c
oracle 9083 8068 0 07:41 pts/0 00:00:00 grep --color=auto pmon
[oracle@srlab ~]$ ps -ef |grep tns
root 22 2 0 03:25 ? 00:00:00 [netns]
oracle 9092 8068 0 07:41 pts/0 00:00:00 grep --color=auto tns
oracle 22048 1 0 04:45 ? 00:00:00 /u01/app/oracle/product/19.0.0.0/db_2/bin/tnslsnr SRCDB19C_LISTENER -inherit
[oracle@srlab ~]$
[oracle@srlab ~]$ . oraenv
ORACLE_SID = [oracle] ? srcdb19c
The Oracle base has been set to /u01/app/oracle
[oracle@srlab ~]$ sql
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 12 07:42:02 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
SRCDB19C
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRCDB19CPLUG READ WRITE NO
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
.0/db_2/dbs/spfilesrcdb19c.ora
SQL>
SQL> create pfile='/home/oracle/srcdb19cpretde.ora' from spfile;
File created.
SQL> show parameter WALLET_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root string
SQL> show parameter TDE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string
SQL> alter system set WALLET_ROOT="/u01/app/oracle/admin/srcdb19c/wallet" scope=spfile;
System altered.
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;
alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-46693: The WALLET_ROOT location is missing or invalid.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 2432695832 bytes
Fixed Size 9137688 bytes
Variable Size 553648128 bytes
Database Buffers 1862270976 bytes
Redo Buffers 7639040 bytes
Database mounted.
Database opened.
SQL> show parameter wallet
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /u01/app/oracle/admin/srcdb19c
/wallet
SQL> show parameter tde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;
System altered.
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;
System altered.
SQL> show parameter tde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string KEYSTORE_CONFIGURATION=FILE
SQL> show parameter wallet
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /u01/app/oracle/admin/srcdb19c
/wallet
SQL> administer key management create keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123;
administer key management create keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123
*
ERROR at line 1:
ORA-46633: creation of a password-based keystore failed
For this issue, I have created director "tde" under "/u01/app/oracle/admin/srcdb19c/wallet/" location.
mkdir -p /u01/app/oracle/admin/srcdb19c/wallet/tde
[oracle@srlab ~]$ sql
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 12 08:13:24 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show parameter tde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string KEYSTORE_CONFIGURATION=FILE
SQL>
SQL> show parameter wallet
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
ssl_wallet string
wallet_root string /u01/app/oracle/admin/srcdb19c
/wallet
SQL>
SQL> alter system set WALLET_ROOT="/u01/app/oracle/admin/srcdb19c/wallet" scope=spfile;
System altered.
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;
System altered.
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;
System altered.
SQL>
SQL> administer key management create keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123;
keystore altered.
SQL> set lines 200
column WRL_PARAMETER format a40
select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ---------------------------------------- ------------------------------ ----------
FILE /u01/app/oracle/admin/srcdb19c/wallet/tde/ CLOSED 1
FILE CLOSED 2
FILE CLOSED 3
SQL> administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/srcdb19c/wallet/tde' identified by welcome123;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
-------------------- ---------------------------------------- ---------- -------------------- --------- -------- --------- ---FILE /u01/app/oracle/admin/srcdb19c/wallet/tde/ OPEN_NO_MA AUTOLOGIN SINGLE NONE UNDEFINED 1
STER_KEY
FILE OPEN_NO_MA AUTOLOGIN SINGLE UNITED UNDEFINED 2
STER_KEY
FILE OPEN_NO_MA AUTOLOGIN SINGLE UNITED UNDEFINED 3
STER_KEY
SQL> administer key management set keystore open force keystore identified by welcome123;
keystore altered.
SQL> administer key management set key FORCE KEYSTORE identified by welcome123 with backup;
keystore altered.
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ---------------------------------------- ---------- ----------
FILE /u01/app/oracle/admin/srcdb19c/wallet/tde/ OPEN 1
FILE OPEN 2
FILE OPEN_NO_MA 3
STER_KEY
SQL> administer key management set key FORCE KEYSTORE identified by welcome123 with backup;
keystore altered.
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
---------- --------------------------------------------- ---------- ----------
FILE /u01/app/oracle/admin/srcdb19c/wallet/tde/ OPEN 1
FILE OPEN 2
FILE OPEN_NO_MA 3
STER_KEY
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 1
0 SYSTEM YES NO YES 2
1 SYSAUX YES NO YES 1
1 SYSAUX YES NO YES 2
2 UNDOTBS1 YES NO YES 1
2 UNDOTBS1 YES NO YES 2
3 TEMP NO NO YES 1
3 TEMP NO NO YES 2
4 USERS YES NO YES 1
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
4 USERS YES NO YES 3
14 rows selected.
SQL> alter tablespace users encryption online encrypt;
Tablespace altered.
SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from DBA_TABLESPACES;
TABLESPACE_NAME STATUS ENC
------------------------------ ---------- ---
SYSTEM ONLINE NO
SYSAUX ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
USERS ONLINE YES
SQL> select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status from v$tablespace t, v$encrypted_tablespaces e where t.ts#=e.ts#;
NAME TS# ENCRYPT ENC KEY_VERSION STATUS
------------------------------ ---------- ------- --- ----------- ----------
USERS 4 AES128 YES 1 NORMAL
USERS 4 AES128 YES 1 NORMAL
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRCDB19CPLUG MOUNTED
SQL> alter pluggable database SRCDB19CPLUG open read write;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 SRCDB19CPLUG READ WRITE NO
SQL> alter pluggable database SRCDB19CPLUG save state;
Pluggable database altered.
SQL> select WRL_TYPE, WRL_PARAMETER, STATUS, CON_ID from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
---------- --------------------------------------------- ---------- ----------
FILE /u01/app/oracle/admin/srcdb19c/wallet/tde/ OPEN 1
FILE OPEN 2
FILE OPEN_NO_MA 3
STER_KEY
SQL> alter session set container=SRCDB19CPLUG;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SRCDB19CPLUG READ WRITE NO
SQL> select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status from v$tablespace t, v$encrypted_tablespaces e where t.ts#=e.ts#;
no rows selected
SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from DBA_TABLESPACES;
TABLESPACE_NAME STATUS ENC
------------------------------ ---------- ---
SYSTEM ONLINE NO
SYSAUX ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
USERS ONLINE NO
SQL>
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
4 USERS YES NO YES 3
SQL> alter tablespace users encryption online encrypt;
alter tablespace users encryption online encrypt
*
ERROR at line 1:
ORA-28361: master key not yet set
SQL> administer key management set key FORCE KEYSTORE identified by welcome123 with backup;
keystore altered.
SQL> alter tablespace users encryption online encrypt;
Tablespace altered.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
0 SYSTEM YES NO YES 3
1 SYSAUX YES NO YES 3
2 UNDOTBS1 YES NO YES 3
3 TEMP NO NO YES 3
4 USERS YES NO YES 3
SQL> select TABLESPACE_NAME, STATUS, ENCRYPTED from DBA_TABLESPACES;
TABLESPACE_NAME STATUS ENC
------------------------------ ---------- ---
SYSTEM ONLINE NO
SYSAUX ONLINE NO
UNDOTBS1 ONLINE NO
TEMP ONLINE NO
USERS ONLINE YES
SQL> select t.name, e.ts#, e.encryptionalg, e.encryptedts, e.key_version, e.status from v$tablespace t, v$encrypted_tablespaces e where t.ts#=e.ts#;
NAME TS# ENCRYPT ENC KEY_VERSION STATUS
------------------------------ ---------- ------- --- ----------- ----------
USERS 4 AES128 YES 1 NORMAL
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 SRCDB19CPLUG READ WRITE NO
SQL>
Conclusion: We've successfully configured the TDE - Transparent Data Encryption for Tablespace Level on Oracle 19c Standalone Database is running on Oracle Linux 7.9.
Hope this helps!
Ramesh.
Stay Connected with Me following Social Media Platforms:
DBA Blog: https://www.databaselogwriter.com
Linkedin: https://www.linkedin.com/in/ramesh-kumar-krishnamoorthy-3a67ba69
Twitter: https://twitter.com/sachinrameshdba
Facebook: https://www.facebook.com/rameshkumar.krishnamoorthy.9
Facebook Page: https://www.facebook.com/oraclef1
Instagram: https://www.instagram.com/oraclef1_1
Post a Comment: