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
