Sunday, April 5, 2020

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

Hi DBAs,

It is important we share what we do in DBAs life to help ourself and others. I've done many implementation projects recently related with Database Security. One of the them was TDE which was pending in my blog posts . I've decided to share the steps involve in implementing Transparent Data Encrption(TDE) using fast offline conversion method for entire Oracle Database. We can follow the similar steps in Oracle RAC or Standalone Database Except few additional things need to done for Oracle RAC Database.


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@dbhost01 bin]# su - oracle
[oracle@dbhost01 ~]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost01,dbhost02
[oracle@dbhost01 ~]$ srvctl stop instance -i testdb1 -d testdb
[oracle@dbhost01 ~]$ srvctl status database -d testdb
Instance testdb1 is not running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 ~]$ srvctl stop listener -n dbhost01
[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost02
[oracle@dbhost01 ~]$ srvctl start listener -n dbhost01
[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost01,dbhost02
[oracle@dbhost01 ~]$ srvctl start instance -i testdb1 -d testdb
[oracle@dbhost01 ~]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 ~]$


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
---------
testdb

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
---------------- ------------ -----------------
testdb1        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@dbhost01 ~]$ sqlplus "/as sysdba"

SQL> select name from v$database;

NAME
---------
testdb

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
---------------- ------------ -----------------
testdb1        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@dbhost01 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@dbhost01 admin]$

Verify autostart of Database is working as expected:
=======================================
[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 admin]$ srvctl stop instance -i testdb1 -d testdb
[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is not running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost01 admin]$ srvctl start instance -i testdb1 -d testdb
[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is not running on node dbhost02

Step 10:Backup the wallet

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

[oracle@dbhost01 testdb1]$ pwd
/home/oracle/DBA/backup/wallet/testdb1
[oracle@dbhost01 testdb1]$ 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@dbhost01 testdb1]$


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@dbhost01 testdb1]$ pwd
/home/oracle/wallet/testdb1
[oracle@dbhost01 testdb1]$ 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@dbhost01 testdb1]$ scp ewallet.p12 cwallet.sso oracle@dbhost02:/home/oracle/wallet/testdb2
ewallet.p12                                                                           100% 2840     2.8KB/s   00:00
cwallet.sso                                                                           100% 2917     2.9KB/s   00:00
[oracle@dbhost01 testdb1]$


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@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is running on node dbhost02


[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost01,dbhost02

(OR)

For Testing purpose only

We can also verify DB node2 is started after copying ewallet.p12 and cwallet.sso:
===========================================================
[oracle@dbhost02 testdb2]$ 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@dbhost02 testdb2]$ srvctl stop instance -i testdb1 -d testdb
[oracle@dbhost02 testdb2]$ srvctl status database -d testdb
Instance testdb1 is not running on node dbhost01
Instance testdb2 is not running on node dbhost02
[oracle@dbhost02 testdb2]$ srvctl start instance -i testdb2 -d testdb
[oracle@dbhost02 testdb2]$ srvctl status database -d testdb
Instance testdb1 is not running on node dbhost01
Instance testdb2 is running on node dbhost02
[oracle@dbhost02 testdb2]$


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

Verify as below:
[oracle@dbhost01 admin]$ srvctl status database -d testdb
Instance testdb1 is running on node dbhost01
Instance testdb2 is running on node dbhost02


[oracle@dbhost01 ~]$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): dbhost01,dbhost02



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


SQL> select name from v$database;

NAME
---------
TESTDB

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.

(testdb):
-------------------
Don't delete line just comment extra lines as done below:

[oracle@dbhost01 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@dbhost01 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 TESTDB 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@dbhost01 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/testdb/datafile/user_ts_ts01.dbf' encrypt;                                     
alter database datafile '+DATA/testdb/datafile/test_01_ts.dbf' encrypt;                                           
alter database datafile '+DATA/testdb/datafile/test_02_ts.dbf' encrypt;                                         
alter database datafile '+DATA/testdb/datafile/test_03_ts.dbf' encrypt;                                           
alter database datafile '+DATA/testdb/datafile/test_04_ts.dbf' encrypt;                                           
alter database datafile '+DATA/testdb/datafile/test_05_ts.dbf' encrypt;                                         
alter database datafile '+DATA/testdb/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@dbhost01 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
---------
testdb

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
---------
testdb

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
---------
testdb

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 "<Wallet Path>" identified by "<Wallet Password>";

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/testdb1 OPEN                                    SINGLE    NO                 0
FILE                 /home/oracle/wallet/testdb2 OPEN 
                   
SQL> select active_state,instance_Name,status from gv$instance;

ACTIVE_ST INSTANCE_NAME    STATUS
--------- ---------------- ------------
NORMAL    testdb1          OPEN
NORMAL    testdb2          OPEN

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


Enjoy DBA learning and doing tasks....


Thanks,

Rafi