Transparent Data Encryption In Oracle 11g (11.2.0.4) RAC Database

Transparent Data Encryption In Oracle 11g (11.2.0.4) RAC Database

by

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…

Leave a Comment