Steps to Implement Transparent Data Encryption(TDE) using fast offline conversion for Oracle Databases

Prerequisite:

Make sure you have applied the patch 23315889(fast offline conversion patch) if you are on Oracle 11g Database or latest CPU patches are applied which already include all the mandatory patches before proceeding with below steps.

Below steps can be used for Oracle 11g,12c , 18c, 19c Databases

Step 1: Take a Backup of Database using RMAN.

Make sure you have full Database backup using RMAN and Validated.

Step 2: Take Backup of $TNS_ADMIN ( $ORACLE_HOME/network/admin) Directory

Please take backup of network configuration files which includes backup of $TNS_ADMIN Directory of $ORACLE_HOME in all the DB nodes.

Step 3: Make Sure You Inform Application owners or End User for Downtime

Please make sure you take sufficient downtime based on the size of your Database if it is Production Environment . If you have identical test environment as Production , we have very good opportunity to make a note of timings at each step of implementation phase. It will surely be useful.

Step 4:Shut down applications

If you are using Oracle ERP Applications make sure all the applications are stopped with adstpall.sh script or other applications it needs to be completed stoped.

Step 5:Create a specific wallet by specifying the wallet location in the sqlnet.ora file(sqlnet_ifile.ora in Oracle ERP Environment).

$ORACLE_HOME/network/admin is the location where you find network configuration files( sqlnet.ora or sqlnet_ifile.ora(In Oracle ERP) needs to be modified).

sqlnet.or or sqlnet_ifile.ora contents :

ENCRYPTION_WALLET_LOCATION =

(SOURCE = (METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /home/oracle/wallet/$ORACLE_SID)

)

)

Note: DIRECTORY location like above makes it easier to copy wallet files across multiple databases. However we are free to have any location of our choice.

Step 6: Restart Database instance and Listener for Oracle RAC Environment running on node1 and node2 or Listener and DB in Standalone DB using SQLPLUS

After completing step 1 to step5 perform step 6 mandatory. This is important to detect the wallet location by Database.

Make sure you bring down all the instances and databases services from all the other nodes if you are working in RAC Database Environment. All the steps need to be performed from DB node1 or Primary Database Instance and all the other instances and Database services are down in other servers.

DB node1:

=====================

[root@doyenhost01 bin]# su – oracle

[oracle@doyenhost01 ~]$ srvctl status database -d uatdb

Instance uatdb1 is running on node doyenhost01

Instance uatdb2 is not running on node doyenhost02

[oracle@doyenhost01 ~]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): doyenhost01,doyenhost02

[oracle@doyenhost01 ~]$ srvctl stop instance -i uatdb1 -d uatdb

[oracle@doyenhost01 ~]$ srvctl status database -d uatdb

Instance uatdb1 is not running on node doyenhost01

Instance uatdb2 is not running on node doyenhost02

[oracle@doyenhost01 ~]$ srvctl stop listener -n doyenhost01

[oracle@doyenhost01 ~]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): doyenhost02

[oracle@doyenhost01 ~]$ srvctl start listener -n doyenhost01

[oracle@doyenhost01 ~]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): doyenhost01,doyenhost02

[oracle@doyenhost01 ~]$ srvctl start instance -i uatdb1 -d uatdb

[oracle@doyenhost01 ~]$ srvctl status database -d uatdb

Instance uatdb1 is running on node doyenhost01

Instance uatdb2 is not running on node doyenhost02

[oracle@doyenhost01 ~]$

Step 7:Create the wallet and Set the Master Encryption Key.

Initiate a new SQL*Plus session.

This causes the changes to sqlnet.ora and the environment variable to be picked

up by the new session.

SQL> select name from v$database;

NAME

———

uatdb

SQL> select status from v$instance;

STATUS

————

OPEN

Set the Master Encryption Key.

Check below:

==========

SQL> select instance_name,status,database_status from gv$instance;

INSTANCE_NAME STATUS DATABASE_STATUS

—————- ———— —————–

uatdb1 OPEN ACTIVE

SQL> select * from gv$encryption_wallet order by inst_id;

INST_ID WRL_TYPE

———- ——————–

WRL_PARAMETER

——————————————————————————–

STATUS

——————

1 file

/home/oracle/wallet/$ORACLE_SID

CLOSED

(or)

select * from v$encryption_wallet;

SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY “Mywork8t5_mydbacomp”;

System altered.

NOTE: Ensure that the password string is contained in double quotation marks (” “).

Step 8: Shutdown & Startup the database normally, ensuring that the wallet is open:

[oracle@doyenhost01 ~]$ sqlplus “/as sysdba”

SQL> select name from v$database;

NAME

———

uatdb

SQL>shut immediate

SQL*Plus: Release 11.2.0.4.0 Production on Fri Apr 19 20:31:26 2019

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.2527E+10 bytes

Fixed Size 2264856 bytes

Variable Size 7012876520 bytes

Database Buffers 5502926848 bytes

Redo Buffers 8658944 bytes

Database mounted.

SQL> alter system set encryption wallet open identified by “Mywork8t5_mydbacomp”;

System altered.

SQL> alter database open;

Database altered.

SQL> select instance_name,status,database_status from gv$instance;

INSTANCE_NAME STATUS DATABASE_STATUS

—————- ———— —————–

uatdb1 OPEN ACTIVE

SQL>

Step 9:To configure auto login for wallet (optional), do the following

Command: orapki wallet create -wallet “/home/oracle/wallet/$ORACLE_SID” -auto_login

[oracle@doyenhost01 admin]$ orapki wallet create -wallet “/home/oracle/wallet/$ORACLE_SID” -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: Mywork8t5_mydbacomp

[oracle@doyenhost01 admin]$

Verify autostart of Database is working as expected:

=======================================

[oracle@doyenhost01 admin]$ srvctl status database -d uatdb

Instance uatdb1 is running on node doyenhost01

Instance uatdb2 is not running on node doyenhost02

[oracle@doyenhost01 admin]$ srvctl stop instance -i uatdb1 -d uatdb

[oracle@doyenhost01 admin]$ srvctl status database -d uatdb

Instance uatdb1 is not running on node doyenhost01

Instance uatdb2 is not running on node doyenhost02

[oracle@doyenhost01 admin]$ srvctl start instance -i uatdb1 -d uatdb

[oracle@doyenhost01 admin]$ srvctl status database -d uatdb

Instance uatdb1 is running on node doyenhost01

Instance uatdb2 is not running on node doyenhost02

Step 10:Backup the wallet

Using ‘cp’ command copy the wallet files in separate directory and verify it.

[oracle@doyenhost01 uatdb1]$ pwd

/home/oracle/DBA/backup/wallet/uatdb1

[oracle@doyenhost01 uatdb1]$ ls -ltr

total 8

-rw——-. 1 oracle oinstall 2917 Apr 19 21:00 cwallet.sso

-rw-r–r–. 1 oracle oinstall 2840 Apr 19 21:00 ewallet.p12

[oracle@doyenhost01 uatdb1]$

Step 11: Copy the wallet to DB node2 in the same location as DB node1.

Copy the wallet to DB node2 in the same location as DB node1 and make sure in the sqlnet.ora and sqlnet_ifile.ora file has the same location defined.

$ORACLE_HOME/network/admin is the location where you find network configuration files( sqlnet.ora or sqlnet_ifile.ora(In Oracle ERP) needs to be modified).

sqlnet.or or sqlnet_ifile.ora contents :

ENCRYPTION_WALLET_LOCATION =

(SOURCE = (METHOD = FILE)

(METHOD_DATA =

(DIRECTORY = /home/oracle/wallet/$ORACLE_SID)

)

)

oracle@doyenhost01 uatdb1]$ pwd

/home/oracle/wallet/uatdb1

[oracle@doyenhost01 uatdb1]$ ls -ltr

total 8

-rw-r–r–. 1 oracle asmadmin 2840 Apr 19 20:17 ewallet.p12

-rw——-. 1 oracle oinstall 2917 Apr 19 20:47 cwallet.sso

[oracle@doyenhost01 uatdb1]$ scp ewallet.p12 cwallet.sso oracle@doyenhost02:/home/oracle/wallet/uatdb2

ewallet.p12 100% 2840 2.8KB/s 00:00

cwallet.sso 100% 2917 2.9KB/s 00:00

[oracle@doyenhost01 uatdb1]$

Step 12:Verify DB Instances and Services on DB node1 & DB node2.

Now you can start the instance on Database Instance and Database services on DB node2 and verify it is running.

[oracle@doyenhost01 admin]$ srvctl status database -d uatdb

Instance uatdb1 is running on node doyenhost01

Instance uatdb2 is running on node doyenhost02

[oracle@doyenhost01 ~]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): doyenhost01,doyenhost02

(OR)

For Testing purpose only

We can also verify DB node2 is started after copying ewallet.p12 and cwallet.sso:

===========================================================

[oracle@doyenhost02 uatdb2]$ ls -ltr

total 8

-rw-r–r–. 1 oracle oinstall 2840 Apr 19 21:08 ewallet.p12

-rw——-. 1 oracle oinstall 2917 Apr 19 21:08 cwallet.sso

[oracle@doyenhost02 uatdb2]$ srvctl stop instance -i uatdb1 -d uatdb

[oracle@doyenhost02 uatdb2]$ srvctl status database -d uatdb

Instance uatdb1 is not running on node doyenhost01

Instance uatdb2 is not running on node doyenhost02

[oracle@doyenhost02 uatdb2]$ srvctl start instance -i uatdb2 -d uatdb

[oracle@doyenhost02 uatdb2]$ srvctl status database -d uatdb

Instance uatdb1 is not running on node doyenhost01

Instance uatdb2 is running on node doyenhost02

[oracle@doyenhost02 uatdb2]$

Make sure all the Database Instances and Services are Up before proceeding next step.

Verify as below:

[oracle@doyenhost01 admin]$ srvctl status database -d uatdb

Instance uatdb1 is running on node doyenhost01

Instance uatdb2 is running on node doyenhost02

[oracle@doyenhost01 ~]$ srvctl status listener

Listener LISTENER is enabled

Listener LISTENER is running on node(s): doyenhost01,doyenhost02

Step 13: Find out all the Temporary and Undo Tablespaces in the Database

SQL> select name from v$database;

NAME

———

uatdb

SQL> select tablespace_name from dba_tablespaces where contents=’TEMPORARY’ and STATUS=’ONLINE’;

TABLESPACE_NAME

——————————

TEMP

PG_TEMP

MG_TEMP

TG_TEMP

TMP

SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’ and STATUS=’ONLINE’;

TABLESPACE_NAME

——————————

UNDOTBS1

UNDOTBS2

Step 14: Create a script called tbsp_offline.sql script to bring tablespaces

other than system, sysaux, temp and undo offline:

cd /home/oracle/DBA/scripts/tde_scripts

$ sqlplus / as sysdba

SQL>set heading off

SQL>set linesize 150

SQL>spool tbsp_offline.sql

SQL>select ‘alter tablespace ‘||tablespace_name|| ‘ offline;’ from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’TEMP’,’PG_TEMP’,’MG_TEMP’,’TG_TEMP’,’TMP’,’UNDOTBS1′,’UNDOTBS2′,’MY_TEMP’);

SQL>exit

Now edit the tbsp_offline.sql script to remove all lines other than alter tablespace commands.

(uatdb):

——————-

Don’t delete line just comment extra lines as done below:

[oracle@doyenhost01 tde_scripts]$ ls -ltr

total 36

-rw-r–r–. 1 oracle oinstall 2181 Apr 17 15:00 tbsp_offline_orig_17Apr2019.sql

-rw-r–r–. 1 oracle oinstall 12025 Apr 17 15:07 datafiles_encrypt_orig_17Apr2019.sql

-rw-r–r–. 1 oracle oinstall 2175 Apr 17 15:21 tbsp_offline.sql

-rw-r–r–. 1 oracle oinstall 2150 Apr 17 15:21 tbsp_online.sql

-rw-r–r–. 1 oracle oinstall 12031 Apr 17 15:23 datafiles_encrypt.sql

[oracle@doyenhost01 tde_scripts]$ cat tbsp_offline.sql

–SQL> select ‘alter tablespace ‘||tablespace_name|| ‘ offline;’ from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’TEMP’,’PG_TEMP’,’MG_TEMP’,’TG_TEMP’,’TMP’,’UNDOTBS1′,’UNDOTBS2′,’MY_TEMP’);

alter tablespace USERS offline;

alter tablespace TEST1_TS offline;

alter tablespace TEST2_TS offline;

alter tablespace TEST3_TS offline;

alter tablespace TEST4_TS offline;

alter tablespace TEST5_TS offline;

alter tablespace TEST6_TS offline;

–7 rows selected.

Step 15: Create script for to Encrypt datafiles of Tablespaces

Create a script called datafiles_encrypt.sql containing the commands to encrypt your datafiles,

except system, sysaux, temp and undo.Include all TEMP and UNDO tablespace names from the database in your uatdb instance, in the exclusion list.

$ sqlplus / as sysdba

SQL>set heading off

SQL>set linesize 150

SQL>spool datafiles_encrypt.sql

SQL>select ‘alter database datafile ”’ || file_name ||”’ encrypt;’ from dba_data_files where tablespace_name not in ((‘SYSTEM’,’SYSAUX’,’TEMP’,’PG_TEMP’,’MG_TEMP’,’TG_TEMP’,’TMP’,’UNDOTBS1′,’UNDOTBS2′,’MY_TEMP’);

SQL>exit

Verify the Script.

[oracle@doyenhost01 tde_scripts]$ cat datafiles_encrypt.sql

–SQL> select ‘alter database datafile ”’ || file_name ||”’ encrypt;’ from dba_data_files where tablespace_name not in –(‘SYSTEM’,’SYSAUX’,’TEMP’,’PG_TEMP’,’MG_TEMP’,’TG_TEMP’,’TMP’,’UNDOTBS1′,’UNDOTBS2′,’MY_TEMP’);

alter database datafile ‘+DATA/uatdb/datafile/user_ts_ts01.dbf’ encrypt;

alter database datafile ‘+DATA/uatdb/datafile/test_01_ts.dbf’ encrypt;

alter database datafile ‘+DATA/uatdb/datafile/test_02_ts.dbf’ encrypt;

alter database datafile ‘+DATA/uatdb/datafile/test_03_ts.dbf’ encrypt;

alter database datafile ‘+DATA/uatdb/datafile/test_04_ts.dbf’ encrypt;

alter database datafile ‘+DATA/uatdb/datafile/test_05_ts.dbf’ encrypt;

alter database datafile ‘+DATA/uatdb/datafile/test_06_ts.dbf’ encrypt;

–7 rows selected.

Step 16: Create a script called tbsp_online.sql script to bring Encrypted tablespaces online now

$ sqlplus / as sysdba

SQL>set heading off

SQL>set linesize 150

SQL>spool tbsp_online.sql

SQL> select ‘alter tablespace ‘||tablespace_name|| ‘ online;’ from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’TEMP’,’PG_TEMP’,’MG_TEMP’,’TG_TEMP’,’TMP’,’UNDOTBS1′,’UNDOTBS2′,’MY_TEMP’);

–SQL> spool off

alter tablespace USERS online;

alter tablespace TEST1_TS online;

alter tablespace TEST2_TS online;

alter tablespace TEST3_TS online;

alter tablespace TEST4_TS online;

alter tablespace TEST5_TS online;

alter tablespace TEST6_TS online;

–7 rows selected.

[oracle@doyenhost01 tde_scripts]$

Step 17: Execute the script for bringing the tablespaces offline

cd /home/oracle/DBA/scripts/tde_scripts

sqlplus “/as sysdba”

SQL> select name from v$database;

NAME

———

uatdb

SQL> @tbsp_offline.sql

Tablespace altered.

Step 18: Execute the script for Encrypting the Datafiles of the tablespaces.

cd /home/oracle/DBA/scripts/tde_scripts

sqlplus “/as sysdba”

SQL> select name from v$database;

NAME

———

uatdb

SQL> @datafiles_encrypt.sql

Database altered.

Note: If we have a large number of datafiles , we can parallelize their encryption by creating sub-scripts and running the sub-scripts from parallel SQL*Plus sessions.

Step 19: Execute the script for bringing the tablespaces online

cd /home/oracle/DBA/scripts/tde_scripts

sqlplus “/as sysdba”

SQL> select name from v$database;

NAME

———

uatdb

SQL> @tbsp_online.sql

Tablespace altered.

Note: Some tablespaces may take time to show as online. These are probably tablespaces that are encrypted.

*Check the status of tablespace encryption by connecting to SQL*Plus / as sysdba

and running the query shown:

$ sqlplus / as sysdba

SQL>select tablespace_name, encrypted from dba_tablespaces;

Note: Be aware that unless an auto login keystore is created, every time the database is started up the wallet will need to be opened.

Note: In Oracle 12c we can use below command to create auto login

$ sqlplus / as sysdba

$ administer key management create AUTO_LOGIN keystore from keystore “” identified by “”;

Please refer below link for more details on it:

https://oracle-base.com/articles/12c/multitenant-transparent-data-encryption-tde-12cr1

Step 20: Bounce database and listener , verify auto login is working .

Verify the Database , Listener and Do Complete Health check of the Database.

Start the Applications.

Validate Database connectivity with TOAD, SQL Developer and every component is working as expected.

Make sure all the instances and Database services are up & running. If you have DR Database copy the wallet to same location as Primary Database and Sync it and Verify the DR DB is in sync with Primary.

sqlplus “/as sysdba”

SQL*Plus: Release 12.1.0.2.0 Production on Tue Mar 5 17:12:22 2019

Copyright (c) 1982, 2017, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 5167382528 bytes

Fixed Size 3056376 bytes

Variable Size 1056967944 bytes

Database Buffers 4093640704 bytes

Redo Buffers 13717504 bytes

Database mounted.

Database opened.

SQL> select tablespace_name, encrypted from dba_tablespaces;

TABLESPACE_NAME ENC

—————————— —

SYSTEM NO

SYSAUX NO

UNDOTBS1 NO

TEMP NO

USERS YES

SQL> SET LINESIZE 200

SQL> COLUMN wrl_parameter FORMAT A50

SQL> SELECT * FROM gv$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR FULLY_BAC CON_ID

——————– ————————————————– —————————— ——————– ——— ——— ———-

FILE /home/oracle/wallet/uatdb1 OPEN SINGLE NO 0

FILE /home/oracle/wallet/uatdb2 OPEN

SQL> select active_state,instance_Name,status from gv$instance;

ACTIVE_ST INSTANCE_NAME STATUS

——— —————- ————

NORMAL uatdb1 OPEN

NORMAL uatdb2 OPEN

SQL> select open_mode from v$database;

OPEN_MODE

——————–

READ WRITE