Hello Friend's,
In This Post we will discuss about how to Configure Transparent data encryption on 19c database with oracle restart
So let's get started
Enviornment Used for Activity Noncdb to PDB :
Source :
Hostname : dm01db01.database.com
Database : CDBFLY
Version : 19.22.0
PDB : FLYDB
Oacle_Home : /oracle/app/orawork/product/19.0.0/db_1
OS : OEL 8.9
What is TDE :
Transparent Data Encryption (TDE) is an Oracle feature that encrypts data kept in database data files to shield confidential data from unwanted access. TDE provides transparent encryption and decryption operations to applications and users using the database by operating at the storage level, encrypting data before it is written to disk and decrypting it when read from disk.
- You can encrypt sensitive data that you save in tables and tablespaces with Transparent Data Encryption (TDE).
- Transparent Data Encryption (TDE) guarantees the encryption of sensitive data, satisfies compliance standards, and offers features that simplify encryption processes.
- You can set up keystores for individual pluggable databases (PDBs) or the complete container database (CDB) in a multitenant environment.
- Tablespaces as a whole or specific columns can be encrypted using transparent data encryption. Therefore, wallet configuration in SQLNET.ORA is no longer required.
- After configuring the WALLET_ROOT initialization parameter, Oracle advises using the KEYSTORE_CONFIGURATION attribute of the TDE_CONFIGURATION initialization parameter. New with 19c Close of keystore, even in the case of encrypted SYSTEM, SYSAUX, TEMP, and UNDO
Step 1. Create Directory in ASM for wallet
[grid@dm01db01 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM
The Oracle base has been set to /oracle/app/grid_base
[grid@dm01db01 ~]$ asmcmd
ASMCMD> ASMCMD>
ASMCMD>
ASMCMD> cd +DATA
ASMCMD> ls
CDBFLY/
ASMCMD> ls -l
Type Redund Striped Time Sys Name
N CDBFLY/
ASMCMD> cd CDBFLY
ASMCMD> ls
12EFB52A80DDA374E0639188A8C03E28/
12F01259575FB709E0639188A8C091D2/
86B637B62FE07A65E053F706E80A27CA/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
ASMCMD> mkdir wallet
ASMCMD> cd wallet
ASMCMD> mkdir tde
Step 2. Update parameter Wallet_Root
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 22:54:37 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> col DB_UPTIME for a35
col DATABASE_ROLE for a20
col DB_VERSION for a20
set lines 200
col LOG_MODE for a20
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,LOG_MODE,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UPTIME" from v$database,gv$instance;
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>
DB_NAME HOST_NAME DATABASE_ROLE OPEN_MODE DB_VERSION LOGINS LOG_MODE DB_UPTIME
--------- ------------------------------ -------------------- ---------- -------------------- ---------- -------------------- -----------------------------------
CDBFLY dm01db01.database.com PRIMARY READ WRITE 19.0.0.0.0 ALLOWED ARCHIVELOG 07-MAR-2024 00:26:52
SQL> show parameter tde
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
one_step_plugin_for_pdb_with_tde boolean FALSE
tde_configuration string
SQL>
SQL> alter system set WALLET_ROOT='+DATA/CDBFLY/wallet' scope=spfile;
System altered.
Step 3. Restart Database
[oracle@dm01db01 ~]$ srvctl stop database -d CDBFLY
[oracle@dm01db01 ~]$ srvctl start database -d CDBFLY
Step 4. Update parameter TDE_CONFIGURATION
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 23:06:16 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> show parameter WALLET_ROOT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
wallet_root string +DATA/CDBFLY/wallet
SQL>
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";
System altered.
SQL> show parameter tde_configuration
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tde_configuration string KEYSTORE_CONFIGURATION=FILE
col wrl_type for a15
col wrl_parameter for a40
set lines 200
select * from v$encryption_wallet;
SQL> col wrl_type for a15
col wrl_parameter for a40
set lines 200
select * from v$encryption_wallet;SQL> SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
--------------- ---------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM +DATA/CDBFLY/wallet/tde/ NOT_AVAILABLE UNKNOWN SINGLE NONE UNDEFINED 1
ASM NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 2
ASM NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 3
SQL>
Step 5. Create KEYSTORE for container
SQL> col wrl_type for a15
col wrl_parameter for a40
set lines 200
select * from v$encryption_wallet;SQL> SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
--------------- ---------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM +DATA/CDBFLY/wallet/tde/ NOT_AVAILABLE UNKNOWN SINGLE NONE UNDEFINED 1
ASM NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 2
ASM NOT_AVAILABLE UNKNOWN SINGLE UNITED UNDEFINED 3
SQL> administer key management create keystore identified by sultandba;
keystore altered.
SQL> administer key management set keystore open identified by sultandba;
keystore altered.
SQL> administer key management set key identified by sultandba with backup;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
--------------- ---------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM +DATA/CDBFLY/wallet/tde/ OPEN PASSWORD SINGLE NONE NO 1
ASM CLOSED UNKNOWN SINGLE UNITED UNDEFINED 2
ASM CLOSED UNKNOWN SINGLE UNITED UNDEFINED 3
Step 6. Create KEYSTORE for PDB(FLYDB)
SQL> alter session set container=FLYDB;
Session altered.
SQL> administer key management set keystore open identified by sultandba;
keystore altered.
SQL> alter pluggable database FLYDB open read write;
Pluggable database altered.
SQL> administer key management set key identified by sultandba with backup;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
--------------- ---------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM OPEN PASSWORD SINGLE UNITED NO 3
Step 7. Create an Auto-Login or a Local Auto-Login Software Keystore
SQL> conn / as sysdba
Connected.
SQL>
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> administer key management create auto_login keystore from keystore '+DATA/CDBFLY/wallet/tde' identified by sultandba;
keystore altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
--------------- ---------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM +DATA/CDBFLY/wallet/tde/ OPEN PASSWORD SINGLE NONE NO 1
ASM CLOSED UNKNOWN SINGLE UNITED UNDEFINED 2
ASM OPEN PASSWORD SINGLE UNITED NO 3
Step 8. Restart Database
[oracle@dm01db01 ~]$ srvctl stop database -d CDBFLY
[oracle@dm01db01 ~]$ srvctl start database -d CDBFLY
Step 9. Check database wallet Autologin
[oracle@dm01db01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 7 23:54:59 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> col wrl_type for a15
col wrl_parameter for a40
set lines 200
select * from v$encryption_wallet;
SQL> SQL> SQL>
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
--------------- ---------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM +DATA/CDBFLY/wallet/tde/ OPEN AUTOLOGIN SINGLE NONE NO 1
ASM OPEN AUTOLOGIN SINGLE UNITED NO 2
ASM OPEN AUTOLOGIN SINGLE UNITED NO 3
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> alter session set container=FLYDB;
Session altered.
SQL> alter pluggable database FLYDB open read write;
Pluggable database altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID
--------------- ---------------------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM OPEN AUTOLOGIN SINGLE UNITED NO 3
col KEY_ID for a55
col TAG for a20
col CREATION_TIME for a55
set lines 200
select key_id,tag,keystore_type,creation_time from v$encryption_keys;
KEY_ID TAG KEYSTORE_TYPE CREATION_TIME
------------------------------------------------------- -------------------- ----------------- -------------------------------------------------------
AbnJyo9PJE8SvzBl2I39Jk4AAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE 07-MAR-24 06.04.20.241879 PM +00:00
AUH1/BX6e08av2h+OnxKezkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SOFTWARE KEYSTORE 07-MAR-24 06.01.00.853910 PM +00:00
SQL> column name format a40
select name,guid from v$pdbs;SQL>
NAME GUID
---------------------------------------- --------------------------------
PDB$SEED 12EFB52A80DDA374E0639188A8C03E28
FLYDB 12F01259575FB709E0639188A8C091D2
Step 10.Recreate Temp tablespace CDB$ROOT container
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> create temporary tablespace temp_new tempfile '+DATA' size 2G encryption encrypt;
Tablespace created.
SQL> alter database default temporary tablespace temp_new;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SQL> create temporary tablespace temp tempfile '+DATA' size 2G encryption encrypt;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> drop tablespace temp_new including contents and datafiles;
Tablespace dropped.
SQL> select 'ALTER TABLESPACE '||tablespace_name||' ENCRYPTION ONLINE ENCRYPT;' from dba_tablespaces where tablespace_name not in ('TEMP') and 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 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 USERS ENCRYPTION ONLINE ENCRYPT;
Tablespace altered.
SQL> select TABLESPACE_NAME,ENCRYPTED from dba_tablespaces;
TABLESPACE_NAME ENC
------------------------------ ---
SYSTEM YES
SYSAUX YES
UNDOTBS1 YES
TEMP YES
USERS YES
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
Step 11.Drop and recreate temp tspace for the pdb (FLYDB)
SQL> alter session set container=FLYDB;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
FLYDB
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 FLYDB READ WRITE NO
SQL>
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
TEMP
SQL> create temporary tablespace temp_new tempfile '+DATA' size 2G encryption encrypt;
Tablespace created.
SQL> alter database default temporary tablespace temp_new;
Database altered.
SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.
SQL> create temporary tablespace temp tempfile '+DATA' size 2G encryption encrypt;
Tablespace created.
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> drop tablespace temp_new including contents and datafiles;
Tablespace dropped.
SQL>
SQL> select 'ALTER TABLESPACE '||tablespace_name||' ENCRYPTION ONLINE ENCRYPT;' from dba_tablespaces where tablespace_name not in ('TEMP') and 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 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 USERS ENCRYPTION ONLINE ENCRYPT;
Tablespace altered.
Hope This Help's
See You Next Post
Regards
Sultan Khan
ConversionConversion EmoticonEmoticon