TDE (Transparent Data Encryption)

Oracle has introduced TDE from 10g and has come through various enhancements till 12c release.


Encryption requires at least two things: an encryption key and an algorithm. TDE uses what is known as a two-tier key architecture: both column and tablespace encryption keys are stored in the database but are encrypted with another key called the master key . The master key is stored outside the database in a special container called an external security module , which can be something as easy to set up as an Oracle wallet or as sophisticated as a hardware security module device. The Oracle wallet is a file formatted according to Public Key Cryptography Standard No. 12 and encrypted with a password. For using the wallet as the external security module, a password must be provided to make the master key accessible to the database. Unless the right password is supplied, the wallet can’t be opened and the encrypted data can’t be retrieved. The wallet is automatically closed when the database instance is shut down and must be reopened by a security officer when the instance starts. So although thieves might be able to restore a database from tapes, without the wallet and the password, they will not be able to view the encrypted data. (In the hardware security module case, the hardware device must be made available to the database in a manner specified by the vendor of the device.)



Tablespace Encryption Setup
Let’s look at how to set up TDE tablespace encryption, using a file-based wallet. Note that the compatibility of the database must be set to 11.1 or higher. First, if you don’t have one, create the wallet:

1. Make sure the ORACLE_BASE variable has been set. If it has not, set it by issuing



$ export ORACLE_BASE=/opt/oracle



2. Change to the ORACLE_BASE directory and then to the admin subdirectory for that instance. In my case, the instance is named prolin1, so I issue



$ cd $ORACLE_BASE/admin/prolin1



3. Create a directory called “wallet” to hold the wallet:



$ mkdir wallet



4. Create the wallet, along with a secure password, preferably containing a mix of alphanumeric characters, such as “T45rustMe54”:



$ sqlplus / as sysdba
SQL> alter system set encryption key 
identified by "T45rustMe54";



The password is case-sensitive.

The preceding step will create the wallet as well as open it. You need to create the wallet only once. After the database is opened, the wallet remains open until either the wallet is explicitly closed or the database is shut down, at which time the wallet automatically closes. You can reopen this wallet after the database is restarted, by using



SQL> alter system set wallet open 
identified by "T45rustMe54";
System altered.



Now that the wallet is set up, you can create the encrypted tablespace.

1. The following code sets up an encrypted tablespace named enc128_ts:




create tablespace enc128_ts
datafile '/u01/oracle/database/
enc128_ts.dbf'
size 1M autoextend on next 1M
encryption using 'AES128'
default storage (encrypt)
/



Note the special encryption using 'AES128’ clause, which indicates that the AES algorithm is to be used with a 128-bit key. You can also use the values AES192 and AES256 (in place of AES128, the default value) to use 192- and 256-bit keys, respectively.

2. Once the tablespace is created, you can create objects in it. For instance, the following code creates a table called ACCOUNTS_ENC:



create table accounts_enc (
  ACC_NO        NUMBER           NOT NULL,
  FIRST_NAME  VARCHAR2(30)  NOT NULL,
                               
... other columns ...
)
tablespace enc128_ts;
                            



That’s it; no special clause is required. All the columns of the table (or anything else created in this tablespace) will be encrypted.





-------------



Steps to Configure Transparent Data Encryption (TDE)

Lets walk through the step by step process for implementing Transparent Data Encryption (TDE) in Oracle Database 12c.

Demonstration (step 1):

Configure Key store location in database for Oracle 12c Setup :

[oracle@labserver ~]$ echo $ORACLE_SID

prodcdb



[oracle@labserver ~]$ cd $ORACLE_HOME/network/admin



[oracle@labserver admin]$ pwd

/app/oracle/db/12.1.0.1/network/admin



[oracle@labserver admin]$ vi sqlnet.ora (append the following lines in SQLNET.ORA)

ENCRYPTION_WALLET_LOCATION=

 (SOURCE=

  (METHOD=FILE)

   (METHOD_DATA=

    (DIRECTORY=+DATA/PRODCDB/WALLET)

   )

 )

ENCRYPTION_WALLET_LOCATION.

[oracle@labserver ~]$ echo $ORACLE_SID

+ASM



[oracle@labserver ~]$ asmcmd

ASMCMD>

ASMCMD> cd +DATA/PRODCDB

ASMCMD> mkdir WALLET

ASMCMD> cd WALLET/

ASMCMD> pwd

+DATA/PRODCDB/WALLET

Demonstration (step 2):



sys@PRODCDB> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATA/PRODCDB/WALLET' IDENTIFIED BY encWallet;



keystore altered.

Once we create the Keystore from the database, we can see the file 'ewallet.p12' gets created in the Keystore Location.

ASMCMD> pwd

+DATA/PRODCDB/WALLET

ASMCMD> ls -l

Type       Redund  Striped  Time             Sys  Name

KEY_STORE  MIRROR  COARSE   MAR 28 15:00:00  N    ewallet.p12 => +DATA/PRODCDB/KEY_STORE/ewallet.338.875546829

ASMCMD>

Demonstration (step 3):

Open the Keystore:

sys@PRODCDB> show con_name



CON_NAME

------------------------------

CDB$ROOT



sys@PRODCDB> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY encWallet;



keystore altered.

We can optionally query the V_$ENCRYPTION_WALLET view to check the STATUS of the Keystore as shown below.

sys@PRODCDB> select * from V_$ENCRYPTION_WALLET;



WRL_TYPE   WRL_PARAMETER             STATUS                    WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID

---------- ------------------------- ------------------------- --------------- --------- --------- ----------

ASM        +DATA/PRODCDB/WALLET      OPEN_NO_MASTER_KEY        PASSWORD        SINGLE    UNDEFINED          0

To enable the Keystore to open automatically, use the following command.

Demonstration (Enable Auto Login for keystore):

Here, I am enabling Auto-Login for the Keystore defined for my Oracle 12c CDB database 'prodcdb'.

sys@PRODCDB> show con_name



CON_NAME

------------------------------

CDB$ROOT



sys@PRODCDB> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '+DATA/PRODCDB/WALLET' IDENTIFIED BY encWallet;



keystore altered.



Once, we enable the Auto-Login for the Keystore, we can see a new file 'cwallet.sso' gets created in the Keystore location.

[oracle@labserver ~]$ asmcmd

ASMCMD> cd +DATA/PRODCDB/WALLET

ASMCMD> ls -l

Type                 Redund  Striped  Time             Sys  Name

AUTOLOGIN_KEY_STORE  MIRROR  COARSE   --- -- 08:00:00  N    cwallet.sso => +DATA/PRODCDB/AUTOLOGIN_KEY_STORE/cwallet

We can also observe that WALLET_TYPE is set to AUTOLOGIN when querying V$ENCRYPTION_WALLET view.

sys@PRODCDB> select * from v$encryption_wallet;



WRL_TYPE             WRL_PARAMETER             STATUS     WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID

-------------------- ------------------------- ---------- -------------------- --------- --------- ----------

ASM                  +DATA/PRODCDB/WALLET      OPEN       AUTOLOGIN            SINGLE    NO                 0



sys@PRODCDB> select * from v$encryption_wallet;



WRL_TYPE             WRL_PARAMETER             STATUS               WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID

-------------------- ------------------------- -------------------- -------------------- --------- --------- ----------

ASM                  +DATA/PRODCDB/WALLET      OPEN_NO_MASTER_KEY   AUTOLOGIN            SINGLE    UNDEFINED          0

Demonstration (step 4):

Activating the Master Encryption Key :



sys@PRODCDB>  ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY encWallet WITH BACKUP;



keystore altered.

Once a Master Encryption Key is created, we can query the V_$ENCRYPTION_KEYS to check the status of key as follows.

sys@PRODCDB> select CON_ID,KEY_ID,KEYSTORE_TYPE,CREATOR_DBNAME,CREATOR_PDBNAME from v$encryption_keys;



    CON_ID KEY_ID                                                KEYSTORE_TYPE     CREATOR_DBNAME  CREATOR_PDBNAME

---------- ----------------------------------------------------- ----------------- --------------- ---------------      

         0 AcxA2N2N5k/Wv80Sy7NkDHkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA  SOFTWARE KEYSTORE prodcdb         CDB$ROOT



Once the Master Encryption Key is created, the STATUS of the Keystore also gets changed from OPEN_NO_MASTER_KEY to OPEN as shown below.

sys@PRODCDB> select * from V_$ENCRYPTION_WALLET;



WRL_TYPE   WRL_PARAMETER             STATUS                    WALLET_TYPE     WALLET_OR FULLY_BAC     CON_ID

---------- ------------------------- ------------------------- --------------- --------- --------- ----------

ASM        +DATA/PRODCDB/WALLET      OPEN                      PASSWORD        SINGLE    NO                 0

Step 5: Encrypt the Tablespace:

Once the Keystore is created and opened with an active Master Encryption Key, we are all set to start encrypting the data. We can either ENCRYPT individual table columns in the database or ENCRYPT a entire tablespace.

Encrypt Tablespace:

We can encrypt a tablespace using TDE while creating it using CREATE TABLESPACE statement by means of ENCRYPTION clause.

Demonstration (Encrypt Tablespace):



sys@PRODCDB> create tablespace enc_data

  2  datafile '+DATA'

  3  encryption

  4  default storage (ENCRYPT)

  5  ;



Tablespace created.

TDE Encryption Algorithms:

By default, TDE uses the AES encryption algorithm with a 192-bit key length (AES192). If we encrypt a table column without specifying a encryption algorithm, then the column is encrypted using the AES192 algorithm. TDE also enables us to specify a non-default encryption algorithm. We can choose from one of the following encryption algorithms for TDE:

3DES168

AES128

AES192 (default)

AES256

Restrictions using TDE:

Restrictions on Data Types:

Transparent Data Encryption (TDE) supports the following list of data types.

BINARY_DOUBLE

BINARY_FLOAT

CHAR

DATE

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

NCHAR

NUMBER

NVARCHAR2

RAW (legacy or extended)

TIMESTAMP (includes TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE)

VARCHAR2 (legacy or extended) 

SQL statement parsing stages

SQL Parsing Flow Diagram:

 

NOTES

1. A cursor is an address on the client that points to the memory location of a SQL statement on the server. Multiple-client cursors may point at the same address on the server.

2. Remember that 'Client' and 'Server' sides may reside on the same machine - in which case Client/Server is a logical distinction.

3. If a cursor is open, then the statement will be in the sql_area, so no parsing is necessary. This is why locks may remain when a client is terminated abnormally (such as a PC Client being turned off without closing open cursors).

4. SESSION_CACHED_CURSORS is the initialisation parameter that specifies how many cursors to hold open for a particular session.
The open cursor request will still be sent to the server but it will not be executed once a matching cursor is found in the session cursor cache.

5. HOLD_CURSOR is an precompiler parameter that specifies that an individual cursor should be held open. See:

Change value of parameter to all connected sessions

The ALTER SYSTEM SET clause for this parameter is only valid with the SCOPE=SPFILE option, so you can not change the parameter value with this dynamically.

You can change the value for all other sessions, e.g as below:

begin
for c in (select sid, serial# from v$session) loop
   dbms_system.set_int_param_in_session(c.sid,c.serial#,'session_cached_cursors', 100);
end loop;
end;
/

Histogram: Oracle 12c

Histogram are ways to store detailed information about column data for CBO optimizer to interpret the optimal access path for the query.

If there are no Histogram or table statistics are gathered with method_opt=>'FOR ALL COLUMNS SIZE 1', the optimizer checks the dictionary for:

1. High/Low (USER_TAB_COL_STATISTICS.LOW_VALUE and HIGH_VALUE) of column value,

2. Number of Distinct value (NDV),

3. The number of NULL and number of rows.



CBO generates the column selectivity of 1/NDV.



if there are a non-uniform distribution of data in the column, then CBO using default way of column selectivity cause performance degradation.



In terms of implementation, we could choose to store every distinct value together with the number of rows for that value.



For a small number of values this is efficient and 'width balanced' histograms are used.

For a higher number of distinct values, we should use Height Balanced Histogram





If the number of distinct values is less than or equal to the number of histogram buckets specified (up to 254) then a Frequency Histogram is created.

If the number of distinct values is greater than the number of histogram buckets specified, a Height Balanced Histogram is created.



Frequency Histograms:

These use buckets to record the row count for each distinct value.



Height Balanced Histograms:

These are implemented by dividing the data up into different 'buckets' where

each bucket contains the same number of values. The highest value in each bucket (or END_POINT) is recorded together with the lowest value in the "zero" bucket.



Once the data is recorded in buckets we recognize 2 types of data value - Non-popular values and popular values.



Non-popular values - are those that do not occur multiple times as endpoints.

Popular values - occur multiple times as end points.

We can use Popular and Non-Popular Values to provide users with various statistics. Since we know how many values there are in a bucket we can use this information to estimate the number of rows in total that is covered by Popular and Non-Popular values.



The selectivity for popular values can be obtained by calculation the proportion of bucket endpoints filled by that popular value.

The selectivity for nonpopular values can now be calculated as 1/number non-popular bucket endpoints, so we can now be more accurate about selectivities than the original 1/NDV because we have removed the popular values from the equation.



ALL_TAB_HISTOGRAMS describes histograms on tables and views accessible to the current user.
The ALL_TAB_HISTOGRAMS view contains a one-bucket histogram, which in fact signifies "No histogram" to the Oracle Database software. 

Therefore, it should not be queried to indicate the presence or absence of a histogram on a particular column.  Instead, query the value of column HISTOGRAM in the ALL_TAB_COL_STATISTICS view.


Density is a column statistic and provides selectivity estimates for
equi-join predicates (e.g. and A.COL1 = B.COL1) and equality predicates
(e.g. COL1 = 'Y').

The density is expressed as a decimal number between 0 and 1.
Values close to 1 indicate that this column is unselective
Values close to 0 indicate that this column is highly selective

The more selective a column, the less rows are likely to be returned by a
query referencing this column in its predicate list.

The column selectivity is part of the equation used to decide on the best
path for a query to take to retrieve the data required in the most effective
manner and hence impacts the final cost value for the query.

Smart Scan

What is Smart Scan ?
The data search and retrieval processing can be offloaded to the Exadata Storage Servers. This feature is called Smart Scan. Using this Smart Scan, Oracle Database can optimize the performance of operations that perform table and index scans by performing the scans inside Exadata Storage Server, rather than transporting all the data to the database server.

Smart Scan capabilities includes :-

1) Predicate Filtering

2) Column filtering

3) Join Processing



What is predicate filtering and what all conditional operators are supported by predicate filtering ?
 Exadata Storage Server enables predicate filtering for table scans. Rather than returning all the rows for the database to evaluate, Exadata Storage Server returns only the rows that match the filter condition.

 In addition, many common SQL functions can be evaluated by Exadata Storage Server during predicate filtering.

The definitive list of which functions are offloadable for your particular version is contained in V$SQLFN_METADATA.
   SQL> select * from v$sqlfn_metadata where offloadable = 'YES';

A list of conditional operators that are supported by predicate filtering include =, !=, <, >, <=, >=, IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, IS OF type, NOT, AND, OR.



What is Bloom Filter ?
Offloaded joins are accomplished by creating what is called a bloom filter.

A Bloom filter, conceived by Burton Howard Bloom in 1970, is a space-efficient probabilistic data structure that is used to test whether an element is a member of a set. The properties of a Bloom filter make is a very efficient way of determining which values are not in a set. This is very useful for processing join conditions where a significant proportion of the data does not fulfill the join criteria.

Oracle Database 10g Release 2 first used Bloom filters to optimize parallel join operations. When two tables are joined via a hash join, the first table (typically the smaller table) is scanned and the rows that satisfy the WHERE clause predicates (for that table) are used to create a hash table. During the hash table creation, a Bloom filter bit string is also created based on the join column. The bit string is then sent as an additional predicate to the second table scan. After the WHERE clause predicates relating to the second table are applied, the resulting rows are tested using the Bloom filter. Any rows rejected by the Bloom filter must fail the join criteria and are discarded. Any rows that match using the Bloom filter are sent to the hash join.

With Exadata, the Bloom filter is passed to the storage servers as an additional predicate. Processing the Bloom filter inside Exadata Storage Server can reduce the amount of data transported to the database server to process a join, which in turn can speed up query performance.



Will bloom filter work with all type of joins?
No, bloom filter works only with hash joins.



Does Smart Scan work with encrypted and compressed data ?
Yes, smart scan works with encrypted and compressed data.

Exadata Storage Server performs Smart Scans on encrypted tablespaces and encrypted columns. For encrypted tablespaces, Exadata Storage Server can decrypt blocks and return the decrypted blocks to Oracle Database, or it can perform row and column filtering on encrypted data. Significant CPU savings can be made within the database server by offloading the CPU-intensive decryption task to Exadata cells.

Smart Scan works in conjunction with Exadata Hybrid Columnar Compression so that column projection and row filtering can be executed along with decompression at the storage level to save CPU cycles on the database servers.



What are prerequisites for Smart Scan to occur ?
There are 4 basic requirements that must be met for Smart Scans to occur :
 1) There must be a full scan of an object; that is, full table scans, fast full index scans and fast full bitmap index scans.

 2)The scan must use Oracle’s Direct Path Read mechanism. Direct path reads are generally used by Oracle when reading directly into PGA memory (as opposed to into the buffer cache).
      - Direct-path reads are automatically used for parallel queries
      - Direct-path reads may be used for serial queries
             o Not used by default for serial small table scans
             o Use _serial_direct_read=TRUE to force direct path reads

3) Each segment being scanned must be on a disk group that is completely stored on Exadata cells. The disk group must also have the disk group attribute settings as below :-
        'compatible.rdbms' = 11.2.0.0.0' (or later)
        'compatible.asm' = 11.2.0.0.0' (or later)
        ' cell.smart_scan_capable' = TRUE

4) The CELL_OFFLOAD_PROCESSING initialization parameter enables or disables Smart Scan.

   The default value of the parameter is TRUE, meaning that Smart Scan is enabled by default. If it is set to FALSE, Smart Scan is disabled and the database uses Exadata storage to serve data blocks similar to traditional storage.



What are the situations that prevent Smart Scan to happen?
Scan on a clustered table
Scan on an index-organized table
Fast full scan on a compressed index
Fast full scan on a reverse key indexes
The table has row-level dependency tracking enabled
The ORA_ROWSCN pseudocolumn is being fetched
The optimizer wants the scan to return rows in ROWID order
The command is CREATE INDEX using NOSORT
A LOB or LONG column is being selected or queried
A SELECT .. VERSIONS flashback query is being executed
To evaluate a predicate based on a virtual column
More than 255 columns are referenced in the query
The data is encrypted and cell-based decryption is disabled
If table has CACHE property
If _serial_direct_read is turned off (NEVER)
If the partitioned object's size is less than _small_table_threshold.
Offloading is not done on serial DMLs.
Serial Direct read is not applied for these cases (and thus no smart scan) :
     - sql from a table function
     - sql from dbms_sql
     - sql from plsql trigger
Smart scan is not enabled for the sql in plsql when plsql package is called by 'CALL plsql‘
Does not work on serial queries issued from shared servers
The SQL in question must not be quarantined.
High CPU usage on the Storage Cells


What are Storage Indexes ?
Storage Indexes is a very useful Exadata feature which is transparent to database and are maintained automatically.
They are not indexes that are stored in the database like Oracle’s traditional B-Tree or bitmapped indexes. They are not capable of identifying a set of records that has a certain value in a given column. Rather, they are a feature of the storage server software that is designed to eliminate disk I/O.
They work by storing minimum and maximum column values for disk storage units, which are 1 Megabyte (MB) by default and are called region indexes.
Because SQL predicates are passed to the storage servers when Smart Scans are performed, the storage software can check the predicates against the Storage Index metadata (maximum and minimum values) before doing the requested I/O. Any storage region that cannot possibly have a matching row is skipped.

Since the indexes are in-memory on the cell server, if a cell server is restarted the SIs are lost and must be rebuilt.
They are generally created during the first smart scan that references a given column after a storage server has been restarted.
They can also be created when a table is created via a CREATE TABLE AS SELECT statement, or during other direct-path loads.



Under what conditions Storage Index would be used ?
In order for a storage index to be used, a query must include or make use of all the following :

1. Storage Indexes can only be used with statements that do Smart Scans. The main requirements are that the optimizer must choose a full scan and that the I/O must be done via the direct path read mechanism.
2. In order for a statement to use a Storage Index, there must be a WHERE clause with at least one predicate.
3. Storage Indexes can be used with the following set of operators: =, <, >, BETWEEN, >=, <=, IN, IS NULL, IS NOT NULL


What conditions prevent the use of Storage Indexes ?
1. Storage Indexes are not created on CLOBs.
2. Storage Indexes do not work with predicates that use the != comparison operator.
3. Storage Indexes do not work on predicates that use the % wildcard.
4. Storage Indexes are created and maintained for eight-columns per table.
5. Customer had set either of the following init.ora parameter

"_smu_debug_mode=134217728"

or alternatively

"_enable_minscn_cr"=false





What are the statistics related to Storage Indexes ?
There is only one database statistic related to storage indexes. The statistic, 'Cell Physical IO Bytes
Saved by Storage Index', keeps track of the accumulated I/O that has been avoided by the use of Storage Indexes.
Since the statistic is cumulative, it must be checked before and after a given SQL statement in order to
determine whether Storage Indexes were used on that particular statement.

Run the following query to check the statistics for Storage Indexes :
select name, value from v$sysstat where name like '%storage%';


How to diagnose Smart Scan causing wrong results ?
Refer Note 1260804.1 - Exadata: How to diagnose smart scan and wrong results.