Transparent Data Encryption In Oracle: In this article we will discuss about configuring and enabling Transparent Data Encryption In Oracle 11g (11.2.0.4) RAC Database.
Let’s see steps how to configure TDE.
- Take full database RMAN backup
- Check Tablespaces details
- Check Datafiles details
- Take a pfile backup
- Create wallet directory on both node and change the directory permission
- Edit and add encryption wallet location in sqlnet.ora on both node
- Create wallet keystore and check status
- Take Database restart and check wallet status
- Create wallet auto login keystore and check status
- Copy both keys to another node
- Take database restart and check wallet status
- Make all tablespaces offline except system, sysaux, undotbs and temporary tablespaces
- Enable encryption on all datafiles and check encryption status
- Make all tablespaces online except system, sysaux, undotbs and temporary tablespaces
- Check application connectivity
- Application testing
Take full database RMAN backup:
Take full database RMAN backup before starting the TDE implementation activity. Use below script to take full Database RMAN backup.
run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
crosscheck backup;
crosscheck archivelogall;
backup as backupset database format '/u02/backup/rman/Fullback_%T_%U'
plusarchivelog format '/u02/backup/rman/Archive_%T_%U';
backup current controlfile format '/u02/backup/rman/Controlback_%T_%U';
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
}
exit;
Check Tablespaces details:
To check all tablespace utilization:
set lines 400 pages 400
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",
nvl(fs.freespace,0) as "Freespace(MB)",
round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",
round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"
from
(select round(sum(d.bytes)/(1024*1024)) as totalspace,d.tablespace_name tablespace
from dba_data_files d
group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace,f.tablespace_name tablespace
from dba_free_space f
group by f.tablespace_name) fs
where t.tablespace=fs.tablespace (+)
order by 5 desc;
Check all tablespaces online and encryption status:
set lines 3000
set pages 40000
col file_name for a90
col tablespace_name for a30
select file_name, tablespace_name, online_status from dba_data_files order by tablespace_name;
select tablespace_name from dba_tablespaces where contents='TEMPORARY' and STATUS='ONLINE';
select tablespace_name from dba_tablespaces where contents='UNDO' and STATUS='ONLINE';
select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces;
select TABLESPACE_NAME, ENCRYPTED from dba_tablespaces where ENCRYPTED='YES';
Check Datafiles details:
set lines 3000
set pages 40000
col file_name for a90
col tablespace_name for a30
select file_name, tablespace_name, online_status from dba_data_fileswhere tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','UNDOTBS2','TEMP','DEV_IAS_TEMP','TEMP_NEW') order by tablespace_name;
To get script for make offline all tablespaces except default, temporary and undo tablespaces execute below script.
set heading off
set lines150
set pages 40000
spool /home/oracle/TDE_09june2023/tbsp_offline.sql
select 'alter tablespace '||tablespace_name|| ' offline;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','DEV_IAS_TEMP','TEMP_NEW','UNDOTBS1','UNDOTBS2');
spool off;
To get script for encrypted all offline tablespace's datafiles except default, temporary and undo tablespaces execute below script.
set heading off
set lines 150
set pages 40000
spool /home/oracle/TDE_09june2023/datafiles_encrypt.sql
select 'alter database datafile ''' || file_name ||''' encrypt;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','DEV_IAS_TEMP','TEMP_NEW','UNDOTBS1','UNDOTBS2');
spool off;
Take a pfile backup:
SQL> create pfile='/home/oracle/initorcl.ora_09june23' from spfile;
File created.
SQL>
Wallet directory creation on both node and change the directory permission:
mkdir -p /u01/app/oracle/admin/$ORACLE_SID/wallet
chmod -Rf 700 /u01/app/oracle/admin/orcl11/wallet
Edit and add encryption wallet location in sqlnet.ora on both node:
select * from v$ENCRYPTION_WALLET;
ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/$ORACLE_SID/wallet))
select * from v$ENCRYPTION_WALLET;
Create wallet keystore and check status:
select wrl_type, wrl_parameter, status from v$encryption_wallet;
alter system set encryption key identified by password;
ls -ltrh /u01/app/oracle/admin/$ORACLE_SID/wallet
select wrl_type, wrl_parameter, status from v$encryption_wallet;
Take Database restart and check wallet status:
After taking database restart check wallet status using below query.
select wrl_type, wrl_parameter, status from v$encryption_wallet;
If status is close, then open the wallet using below command.
alter system set encryption wallet open identified by password;
To close the wallet, use below command.
alter system set encryption wallet close identified by password;
Create wallet auto login keystore and check status:
[oracle@rac-node-1 ~]$
[oracle@rac-node-1 ~]$ cd /u01/app/oracle/admin/orcl/wallet/
[oracle@rac-node-1 wallet]$ ls -ltr
total 4
-rw-r--r-- 1 oracle asmadmin 2840 Jun 9 11:32 ewallet.p12
[oracle@rac-node-1 wallet]$
[oracle@rac-node-1 wallet]$
[oracle@rac-node-1 wallet]$
[oracle@rac-node-1 wallet]$orapki wallet create -wallet "/u01/app/oracle/admin/$ORACLE_SID/wallet" -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
Enter wallet password: xxxxxx
[oracle@rac-node-1 wallet]$
[oracle@rac-node-1 wallet]$
[oracle@rac-node-1 wallet]$ ls -ltr
total 8
-rw-r--r-- 1 oracle asmadmin 2840 Jun 9 11:32 ewallet.p12
-rw------- 1 oracle oinstall 2917 Jun 9 11:44 cwallet.sso
[oracle@rac-node-1 wallet]$
Copy both keys to another node:
[oracle@rac-node-1 ~]$ cd /u01/app/oracle/admin/$ORACLE_SID/wallet
[oracle@rac-node-1 wallet]$pwd
/u01/app/oracle/admin/orcl1/wallet
[oracle@rac-node-1 wallet]$ ls -ltr
total 2
-rw------- 1 oracle oinstall 2917 Jun 9 11:44 cwallet.sso
-rw-r--r-- 1 oracle asmadmin 2840 Jun 9 11:32 ewallet.p12
[oracle@rac-node-1 wallet]$
[oracle@rac-node-1 wallet]$scp * rac-node-2:/u01/app/oracle/admin/orcl2/wallet
cwallet.sso 100% 2917 2.9KB/s 00:00
ewallet.p12 100% 2840 2.8KB/s 00:00
Take database restart and check wallet status:
After taking database restart check wallet status using below query and wallet status should be auto open.
select wrl_type, wrl_parameter, status from v$encryption_wallet;
Make all tablespaces offline except system, sysaux, undotbs and temporary tablespaces:
SQL> set lines 400 pages 4000
SQL> select 'alter tablespace '||tablespace_name|| ' offline;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','DEV_IAS_TEMP','TEMP_NEW','UNDOTBS1','UNDOTBS2');
alter tablespace USERS offline;
alter tablespace PRDHST offline;
alter tablespace PRDBRN offline;
alter tablespace PRDHGC offline;
alter tablespace PRDGC offline;
alter tablespace FCAT_SIT offline;
6 rows selected.
Enable encryption on all datafiles and check encryption status:
select 'alter database datafile ''' || file_name ||''' encrypt;' from dba_data_files where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','DEV_IAS_TEMP','TEMP_NEW','UNDOTBS1','UNDOTBS2');
alter database datafile '+DATA/orcl/datafile/PRDarch.364.998996507' encrypt;
alter database datafile '+DATA/orcl/datafile/PRDhst.365.1000906543' encrypt;
alter database datafile '+REDO/orcl/datafile/orcltasml.277.1126048589' encrypt;
alter database datafile '+DATA/orcl/datafile/users.281.866139183' encrypt;
alter database datafile '+DATA/orcl/datafile/PRDhst.286.867095069' encrypt;
alter database datafile '+DATA/orcl/datafile/PRDbrn.285.867095177' encrypt;
6 rows selected.
Make all tablespaces online except system, sysaux, undotbs and temporary tablespaces:
select 'alter tablespace '||tablespace_name|| ' online;' from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','TEMP','DEV_IAS_TEMP','TEMP_NEW','UNDOTBS1','UNDOTBS2');
alter tablespace USERS online;
alter tablespace PRDHST online;
alter tablespace PRDBRN online;
alter tablespace PRDHGC online;
alter tablespace PRDGC online;
alter tablespace FCAT_SIT online;
6 rows selected.
Check application connectivity:
Start the applicationand check status.
Transparent Data Encryption In Oracle 19c. Click here…