Oracle12c-Transparent Data Encryption (TDE) Tips And Tricks

Transparent Data Encryption :-

  • TDE is an encryption mechanism present in Oracle database used to encrypt the data stored in a table column or tablespace. It protects the data stored on database files (DBF) by doing an encryption in case the file is stolen or hacked.
  • Transparent Data Encryption (TDE) provides mechanism to encrypt the data stored in the OS data files. TDE enables the encryption of data at the storage level to prevent data tempering from outside of the database.
  • When using transparent encryption, the Oracle encryption wallet must be created and the wallet should be opened every time the database starts.

TDE supports two levels of encryption

  • Columns Level Encryption: Encrypt the table column data.
  • Tablespace Level Encryption: Encrypt all the data in a tablespace.

Oracle database 12c introduced a new way to manage keystores, encryption keys and secrets using the ADMINISTER KEY MANAGEMENT command. This replaces the ALTER SYSTEM SET ENCRYPTION KEY and ALTER SYSTEM SET ENCRYPTION WALLET commands for key and wallet administration from previous releases.

Steps to create wallet and enable encryption for table column and tablespace:

  • Create a wallet location :
[oracle@orcl:~ ] mkdir -p /home/oracle/wallet
  • Update the wallet/keystore location in SQLNET.ORA :

A keystore must be created to hold the encryption key.Add the below entry to SQLNET.ORA,

[oracle@orcl:~ orcldemo] cat $ORACLE_HOME/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /oradb/app/oracle/product/12.2.0.1/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /home/oracle/wallet)))
  • Create a keystore :
SQL> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/home/oracle/wallet' IDENTIFIED BY wallet$123;

keystore altered.

SQL> HOST ls /home/oracle/wallet
ewallet.p12
  • Open the keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet$123;

keystore altered.
  • Check the v$encryption_keys view to see the key activated:
SQL> SELECT con_id, key_id FROM v$encryption_keys;

no rows selected
  • Set the encyrption key :
SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY wallet$123 WITH BACKUP;

keystore altered.

WITH BACKUP creates a backup of the software keystore.It also creates a backup of the keystore before creating the new master encryption key.

Now,check the v$encryption_keys view to check the keystore is enabled.

SQL> SELECT con_id, key_id FROM v$encryption_keys;

 CON_ID          KEY_ID
---------- ----------------------------------------------------
0          AVRRh/0Uok8dvyvCAPMtZhwAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Check keystore information from v$encryption_wallet view.

The wallet type is password-based software keystore: As names suggests, this type of keystore is protected by a password, and password is required to open the keystore to retrieve the encryption keys.

TDE Implementation in Oracle 12c database :

Table creation with encrypted wallet :

SQL> CREATE TABLE student (name VARCHAR2(30),rollno NUMBER,dept VARCHAR2(30) ENCRYPT);

Table created.

SQL> INSERT INTO student values('hari',101,'MCA');

1 row created.

SQL> commit;

Commit complete.

SQL> select owner,table_name,column_name,encryption_alg from dba_encrypted_columns where table_name='STUDENT';

OWNER      TABLE_NAME COLUMN_NAME          ENCRYPTION_ALG
---------- ---------- -------------------- -----------------
HARI       STUDENT    DEPT                 AES 192 bits key
  • Restart the database,
SQL> startup force
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 8628936 bytes
Variable Size 603981112 bytes
Database Buffers 1526726656 bytes
Redo Buffers 8146944 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.
  • After restart database,check the encrypted table column data,
SQL> SELECT * FROM hari.student;
SELECT * FROM hari.student;
                   *
ERROR at line 1:
ORA-28365: wallet is not open
  • Reopen the keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY wallet$123; 

keystore altered.

Here the wallet_type is PASSWORD , i.e every time we restart the database, we need to open the key/wallet separately.To overcome this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.

Auto-login software keystore: 

This kind of keystores are protected by system-generated password, and does not need to opened explicitly because these keystores open automatically.

  • Enable AUTOLOGIN  wallet type :
SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER    STATUS WALLET_TYP WALLET_OR  FULLY_BAC CON_ID
-------- -------------    ------ ---------- ---------- --------- ------
FILE     /home/oracle/wallet/ OPEN PASSWORD SINGLE     NO        0
SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/home/oracle/wallet' IDENTIFIED BY wallet$123;

keystore altered.
SQL> HOST ls /home/oracle/wallet
cwallet.sso ewallet.p12 ewallet_2018111618242920.p12 ewallet_2018111618410185.p12

As soon as we execute above statement, we will see cwallet.sso file gets created under keystore location directory. Once we have AUTOLOGIN keystore, there is no need to open keystore for individual pluggable databases because auto-login keystore would open automatically for all pluggable databases as well.

  • Restart the database,
SQL> startup force
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 8628936 bytes
Variable Size 603981112 bytes
Database Buffers 1526726656 bytes
Redo Buffers 8146944 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.
  • After restarting database,check the wallet type:
SQL> SELECT * FROM v$encryption_wallet;

WRL_TYPE WRL_PARAMETER STATUS WALLET_TYP WALLET_OR FULLY_BAC CON_ID
-------- ------------- ------ ---------- ---------- --------- ------
FILE /home/oracle/wallet/ OPEN AUTOLOGIN SINGLE NO 0
  • Now,the encrypted table data and column name ‘DEPT’,
SQL> SELECT * FROM hari.student;

NAME  ROLLNO  DEPT
----- ------- -----
hari  101      MCA
  • Create Encrypted Tablespace :
SQL> CREATE TABLESPACE encrypt_ts DATAFILE '/oradb/app/oracle/oradata/ORCLDEMO/datafile/encrypt_ts.dbf' SIZE 2G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.


SQL> CREATE TABLE student_demo (name VARCHAR2(30),rollno NUMBER,dept VARCHAR2(30)) tablespace encrypt_ts ;

Table created.

SQL> INSERT INTO student_demo values('hari',101,'MCA'); 

1 row created. 

SQL> commit; 

Commit complete.

SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='ENCRYPT_TS';

TABLESPACE_NAME   ENC
---------------   ---
ENCRYPT_TS        YES
  • Restart the database,
SQL> startup force
ORACLE instance started.

Total System Global Area 4294967296 bytes
Fixed Size 8628936 bytes
Variable Size 603981112 bytes
Database Buffers 1526726656 bytes
Redo Buffers 8146944 bytes
In-Memory Area 2147483648 bytes
Database mounted.
Database opened.
  • Check the table data present in tablespace ‘ENCRYPT_TS’        
SQL> SELECT * FROM hari.student_demo;

NAME  ROLLNO  DEPT
----- ------- ----
hari  101     MCA

Local auto-login software keystores:

 This type of keystores have auto-login functionality on the computer where these are created and these cannot be opened from any other computer.

  • To create a local auto-login keystore, use following syntax
SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE 'wallet location' IDENTIFIED BY password;

But for creation of LOCAL AUTO_LOGIN keystore,the wallet type before has to be PASSWORD ( password-based keystore).

  • Close the Password based software keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY wallet$123;

keystore altered.

This above option can be enable when the wallet_type is PASSWORD.

To close a password-based software keystore or a hardware keystore, specify the Keystore password.

  • Close the Autologin based software keystore :
SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE;

keystore altered.

To close an auto-login keystore, do not specify Keystore password.Before you close an auto-login keystore, check the WALLET_TYPE  column of the v$encryption_wallet view. If it returns  AUTOLOGIN, then you can close the keystore. Otherwise, if you attempt to close the keystore, then an error occurs.

 

Catch Me On:- Hariprasath Rajaram 

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba                        FB Group:https://www.facebook.com/groups/894402327369506/             FB Page :https://www.facebook.com/dbahariprasath/?                 Twitter :https://twitter.com/hariprasathdba

Oracle Trace File Analyzer Tips And Tricks

TFA

Trace File Analyzer is new utility which can be installed on the database nodes either stand alone or cluster nodes.

In order to collect the diagnostics of 8 node for example, then we have to review alert log, listener logs whatever on all the nodes that become very lengthy procedure and also not easy to merge the information based on the all nodes.

Hence Oracle introduced TFA – Trace file analyzer and this made easy job for DBA’s, TFA utility or bundle can be downloaded from MOS.

TFACTL INSTALLATION:

 

[root@test18c ~]# cd /u01/app/oracle/tfa/bin/
[root@test18c bin]# ./tfactl
tfactl> orachk
Using Orachk : /u01/app/oracle/tfa/test18c/tfa_home/ext/orachk/orachk


Running orachk
----------------------------------------------------------
PATH : /u01/app/oracle/tfa/test18c/tfa_home/ext/orachk
VERSION : 18.3.0_20180808
COLLECTIONS DATA LOCATION : /u01/app/oracle/tfa/repository/suptools/test18c/orachk/root
----------------------------------------------------------

List of running databases

1. test18c
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1]. 1
. .
. .

Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

. . . . . .
. . . . . . . . .
-------------------------------------------------------------------------------------------------------
Oracle Stack Status 
-------------------------------------------------------------------------------------------------------
Host Name CRS Installed ASM HOME RDBMS Installed CRS UP ASM UP RDBMS UP DB Instance Name
-------------------------------------------------------------------------------------------------------
test18c No No Yes No No Yes test18c
-------------------------------------------------------------------------------------------------------


Copying plug-ins

. .
. . . . . .

*** Checking Best Practice Recommendations ( PASS / WARNING / FAIL ) ***


.

Collections and audit checks log file is
/u01/app/oracle/tfa/repository/suptools/test18c/orachk/root/orachk_test18c_test18c_111318_034400/log/orachk.log

============================================================
Node name - test18c
============================================================

Collecting - Database Parameters for test18c database
Collecting - Database Undocumented Parameters for test18c database
Collecting - RDBMS Feature Usage for test18c database
Collecting - CPU Information
Collecting - Disk I/O Scheduler on Linux
Collecting - DiskMount Information
Collecting - Kernel parameters
Collecting - Maximum number of semaphore sets on system
Collecting - Maximum number of semaphores on system
Collecting - Maximum number of semaphores per semaphore set
Collecting - Memory Information
Collecting - OS Packages
Collecting - Operating system release information and kernel version
Collecting - Patches for RDBMS Home
Collecting - Table of file system defaults
Collecting - number of semaphore operations per semop system call
Collecting - Disk Information
Collecting - Linux Operating system health check using vmpscan.sh
Collecting - Root user limits
Collecting - Verify no database server kernel out of memory errors


Data collections completed. Checking best practices on test18c.
------------------------------------------------------------

CRITICAL => Bash is vulnerable to code injection (CVE-2014-6271)
WARNING => Linux swap configuration does not meet recommendation
INFO => Important Storage Minimum Requirements for Grid & Database Homes
WARNING => Non-AWR Space consumption is greater than or equal to 50% of total SYSAUX space. for test18c
INFO => Most recent ADR incidents for /u01/app/oracle/product/18.0.0/dbhome_1
INFO => Oracle GoldenGate failure prevention best practices
INFO => user_dump_dest has trace files older than 30 days for test18c
FAIL => Database parameter DB_BLOCK_CHECKSUM is not set to recommended value on test18c instance
FAIL => Database parameter DB_LOST_WRITE_PROTECT is not set to recommended value on test18c instance
WARNING => Database parameter DB_BLOCK_CHECKING on PRIMARY is NOT set to the recommended value. for test18c
INFO => Operational Best Practices
INFO => Database Consolidation Best Practices
INFO => Computer failure prevention best practices
INFO => Data corruption prevention best practices
INFO => Logical corruption prevention best practices
INFO => Database/Cluster/Site failure prevention best practices
INFO => Client failover operational best practices
WARNING => Duplicate objects were found in the SYS and SYSTEM schemas for test18c
WARNING => Oracle clusterware is not being used
WARNING => RAC Application Cluster is not being used for database high availability on test18c instance
WARNING => DISK_ASYNCH_IO is NOT set to recommended value for test18c
FAIL => Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for test18c
FAIL => Flashback on PRIMARY is not configured for test18c
INFO => Database failure prevention best practices
WARNING => fast_start_mttr_target has NOT been changed from default on test18c instance
WARNING => Database Archivelog Mode should be set to ARCHIVELOG for test18c
FAIL => Primary database is not protected with Data Guard (standby database) for real-time data protection and availability for test18c
FAIL => Active Data Guard is not configured for test18c
INFO => Parallel Execution Health-Checks and Diagnostics Reports for test18c
INFO => Oracle recovery manager(rman) best practices
WARNING => Linux Disk I/O Scheduler should be configured to Deadline
WARNING => Consider investigating changes to the schema objects such as DDLs or new object creation for test18c
WARNING => Consider investigating the frequency of SGA resize operations and take corrective action for test18c
Best Practice checking completed. Checking recommended patches on test18c
--------------------------------------------------------------------------------
Collecting patch inventory on ORACLE_HOME /u01/app/oracle/product/18.0.0/dbhome_1
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
1 Recommended RDBMS patches for 180000 from /u01/app/oracle/product/18.0.0/dbhome_1 on test18c
--------------------------------------------------------------------------------
Patch# RDBMS ASM type Patch-Description 
--------------------------------------------------------------------------------
28090523 yes merge DATABASE RELEASE UPDATE 18.3.0.0.0
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
RDBMS homes patches summary report
--------------------------------------------------------------------------------
Total patches Applied on RDBMS Applied on ASM ORACLE_HOME
--------------------------------------------------------------------------------
1 1 0 /u01/app/oracle/product/18.0.0/dbhome_1
--------------------------------------------------------------------------------

------------------------------------------------------------
Detailed report (html) - /u01/app/oracle/tfa/repository/suptools/test18c/orachk/root/orachk_test18c_test18c_111318_034400/orachk_test18c_test18c_111318_034400.html

UPLOAD [if required] - /u01/app/oracle/tfa/repository/suptools/test18c/orachk/root/orachk_test18c_test18c_111318_034400.zip


tfactl>

 

Starting from 12.2 this bundle is included with RDBMS software and again this is optional when we run the root.sh script, still we can skip this if it’s not required.

we will see how to initiate the TFA when we run the root.sh of RDBMS.

If we crack the log file then we can see very much detailed information such as scanned trace directories, number of hosts and with the help preview.

 

 

tfactl> summary
LOGFILE LOCATION : /u01/app/oracle/tfa/repository/suptools/test18c/summary/root/20181113030332/log/summary_command_20181113030332_test18c_4674.log

Component Specific Summary collection :
- Collecting ACFS details ... Done.
- Collecting DATABASE details ... Done.
- Collecting PATCH details ... Done.
- Collecting LISTENER details ... Done.
- Collecting NETWORK details ... Done.
- Collecting OS details ... Done.
- Collecting TFA details ... Done.
- Collecting SUMMARY details ... Done.

Prepare Clusterwide Summary Overview ... 
tfactl>
tfactl>

Example of Collecting Diagnostic Data

We can access the list of commands using the “tfactl <command> -help“, in this example we will collect sample diagnostic data using TFA.

 

How to check whether TFA is running or not?

This is simple by grepping the word “tfa” from host level as

 

MENU:



 

Basic TFACTL commands include:

tfactl start: Starts the Oracle Trace File Analyzer daemon on the local node.

tfactl stop: Stops the Oracle Trace File Analyzer daemon on the local node.

tfactl enable: Enables automatic restart of the Oracle Trace File Analyzer daemon after a failure or system reboot.

tfactl disable: Stops any running Oracle Trace File Analyzer daemon and disables automatic restart.

tfactl uninstall: Removes Oracle Trace File Analyzer from the local node.

tfactl syncnodes: Generates and copies Oracle Trace File Analyzer certificates from one Oracle Trace File Analyzer node to other nodes.

tfactl restrictprotocol: Restricts the use of certain protocols.

tfactl status: Checks the status of an Oracle Trace File Analyzer process. The output is same as tfactl print status.

tfactl diagnosetfa: Use the tfactl diagnosa tfa command to collect Oracle Trace File Analyzer diagnostic data from the local node to help diagnose issues with Oracle Trace File Analyzer.

tfactl host: Use the tfactl host command to add hosts to, or remove hosts from the Oracle Trace File Analyzer configuration.

tfactl set: Use the tfactl set command to enable or disable, or modify various Oracle Trace File Analyzer functions.

tfactl access: Use the tfactl access command to allow non-root users to have controlled access to Oracle Trace File Analyzer and to run diagnostic collections.

OSWatcher: http://www.oracledbwr.com/tuning/analyze-oracle-server-diagnostic-information-using-oswatcher-tool/

OraChk : http://www.oracledbwr.com/tuning/health-checks-for-the-oracle-stack-using-orachk/

 

Catch Me On:- Hariprasath Rajaram

LinkedIn:       https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:       https://www.facebook.com/HariPrasathdba
Facebook Group: https://www.facebook.com/groups/894402327369506/
Facebook Page:  https://www.facebook.com/dbahariprasath/?
Twitter:        https://twitter.com/hariprasathdba