How to Configure TDE in Oracle 19c
===========================================
In this Article i will discuss how we can configure TDE in oracle 19c standalone database running on Linux
STEP 1: Create pfile from spfile in below location.
============================================================
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /backup/19.3.0/dbhome_1/dbs/spfileorcl.ora
SQL>
SQL>
SQL> create pfile='/tmp/orcl_pre_tde.ora' from spfile;
File created.
SQL>
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.
* 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 /backup/app/oracle/admin/orcl/wallet
pre-output
SQL> show parameter WALLET_ROOT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root string
SQL>
SQL> show parameter TDE_CONFIGURATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tde_configuration string
SQL>
SQL>
alter system set WALLET_ROOT="/backup/app/oracle/admin/orcl/wallet" scope=spfile;
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.
SQL>
}
STEP 3: Restart the Database
===================================
[oracle@localhost orcl]$ sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 28 21:47:14 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1207958960 bytes
Fixed Size 8895920 bytes
Variable Size 822083584 bytes
Database Buffers 369098752 bytes
Redo Buffers 7880704 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter WALLET_ROOT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root string /backup/app/oracle/admin/orcl/wallet
SQL>
SQL>
SQL> !
[oracle@localhost orcl]$ cd /backup/app/oracle/admin/orcl/wallet
[oracle@localhost wallet]$ ls -lrt
total 0
[oracle@localhost wallet]$
alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=spfile;
SQL> alter system set TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;
System altered.
SQL>
SQL> show parameter TDE_CONFIGURATION
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tde_configuration string KEYSTORE_CONFIGURATION=FILE
SQL>
SQL>
SQL> show parameter wallet_root
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root string /backup/app/oracle/admin/orcl/wallet
SQL> show parameter tde_configuration
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tde_configuration string KEYSTORE_CONFIGURATION=FILE
SQL>
Step 4: Create software Keystore
===========================================
administer key management create keystore '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;
SQL>
SQL> administer key management create keystore '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;
keystore altered.
SQL>
SQL> !
[oracle@localhost wallet]$ ls -lrt
total 4
-rw------- 1 oracle oinstall 2555 Apr 28 21:57 ewallet.p12
[oracle@localhost wallet]$
[oracle@localhost wallet]$
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 '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;
SQL>
SQL> administer key management create auto_login keystore from keystore '/backup/app/oracle/admin/orcl/wallet/tde' identified by welcome123;
keystore altered.
SQL>
SQL>
SQL> administer key management create auto_login keystore from keystore '/backup/app/oracle/admin/orcl/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 /backup/app/oracle/admin/orcl/wallet/tde OPEN_NO_MASTER_KEY AUTOLOGIN SINGLE NONE UNDEFINED 0
/
SQL>
SQL>
STEP 6: Open the software Keystore
=========================================
administer key management set keystore open force keystore identified by welcome123;
SQL>
SQL> administer key management set keystore open force keystore identified by welcome123;
keystore altered.
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;
WRL_TYPE WRL_PARAMETER STATUS CON_ID
-------------------- ---------------------------------------- ------------------------------ ----------
FILE /backup/app/oracle/admin/orcl/wallet/tde OPEN_NO_MASTER_KEY 0
/
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 /backup/app/oracle/admin/orcl/wallet/tde OPEN 0
/
SQL>
[oracle@localhost tde]$ ls -lrt
total 12
-rw------- 1 oracle oinstall 2555 Apr 28 22:38 ewallet_2021042814383622.p12
-rw------- 1 oracle oinstall 3995 Apr 28 22:38 ewallet.p12
-rw------- 1 oracle oinstall 4040 Apr 28 22:38 cwallet.sso
[oracle@localhost tde]$
[oracle@localhost tde]$
[oracle@localhost tde]$ pwd
/backup/app/oracle/admin/orcl/wallet/tde
[oracle@localhost tde]$
SQL>
SQL> alter tablespace users encryption online encrypt;
Tablespace altered.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC CON_ID
---------- ------------------------------ --- --- --- --- ----------
1 SYSAUX YES NO YES 0
0 SYSTEM YES NO YES 0
2 UNDOTBS1 YES NO YES 0
4 USERS YES NO YES 0
3 TEMP NO NO YES 0
6 TEST YES NO YES 0
6 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 YES
TEST ONLINE NO
6 rows selected.
SQL>
SQL>
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>
No comments:
Post a Comment