Infrastructure at your Service

Franck Pachot

Oracle 12cR2: Online tablespace encryption

By Franck Pachot

.
By default, all data is visible in the datafiles. Transparent Tablespace Encryption (TDE) can be used to get them encrypted. It requires Enterprise Edition plus Advanced Security Option. Except in the Oracle Cloud when it is available – and mandatory – in all editions. And we can foresee that security policies will be enforced in the future years, by law or because companies realize their files can be stolen. This means that lot of databases will have to be encrypted, and this may take too long to do it during a maintenance window. In 12.2 we can encrypt online. Online means that we can do it while our application is running, but of course there is a performance overhead on the system.

I’ve run a SLOB workload with reads and writes (PCT_UPDATE=25). Four times the same workload:

  • during the first one, I encrypted the tablespace online
  • the second one is running on the encrypted tablespace
  • during the third one, I decrypted the tablespace online
  • the fourth one is running on the decrypted tablespace

Here is the ASH visualized with Orachrome Lighty:

Online_encryption_ASH_Response_Time

The dark blue is ‘db file sequential read’ is ny 4 SLOB sessions activity. Light blue is all background activity (DBWR, LGWR) and the encrypt/decrypt (db file parallel write).
The green is CPU activity. The brown is free buffer gets: DBWR can’t keep up with the rate of changes we are doing, while encrypting the tablespace.

You may wonder how I was able to have un-encrypted tablespaces on 12.2 which is available only on the Oracle Cloud where encryption is mandatory. This is explained in Oracle Public Cloud 12cR2: TDE is not an option. This means that I created the SLOB database and I have created the wallet.

Configure the TDE keystore

I’ve created the directory

mkdir -p /u01/app/oracle/admin/SLOB/tde_wallet

I declared it in sqlnet.ora

ENCRYPTION_WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/admin/SLOB/tde_wallet)))

I created the wallet

administer key management create keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;

Opened it

administer key management set keystore open identified by oracle;

Created the master key

administer key management set key identified by oracle with backup;

Optionally created an auto-login wallet

administer key management create auto_login keystore from keystore '/u01/app/oracle/admin/SLOB/tde_wallet' identified by oracle;

Statspack

I’ll show some statistics for the following runs: Inital (when not encrypted), Encryption (when encryption is running concurrently), Encrypted (when tablespace encryption has been completed), Decryption (when decrypt is running concurrently) and Decrypted (once decryption is completed).

Run on non-encrypted tablespace

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                4.0                0.1        0.02        6.39
       DB CPU(s):                0.4                0.0        0.00        0.68
       Redo size:        1,064,743.9           18,829.0
   Logical reads:           15,635.7              276.5
   Block changes:            7,293.4              129.0
  Physical reads:            9,451.4              167.1
 Physical writes:            3,303.2               58.4
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read                        817,591       1,000      1   48.9
log file parallel write                         12,258         408     33   20.0
db file parallel write                          18,284         217     12   10.6
CPU time                                                       128           6.3
db file parallel read                           46,263         121      3    5.9
Time Model System Stats  DB/Inst: SLOB/SLOB  Snaps: 26-27
-> Ordered by % of DB time desc, Statistic name
 
Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time                         1,201.2     100.0
DB CPU                                             127.8      10.6
PL/SQL execution elapsed time                        1.7        .1
parse time elapsed                                   0.0        .0
connection management call elapsed                   0.0        .0
hard parse elapsed time                              0.0        .0
Tablespace encryption elapsed time                   0.0        .0
repeated bind elapsed time                           0.0        .0
DB time                                          1,201.7

Run during tablespace encryption

As soon as I started this SLOB run, I started the encryption:

alter tablespace IOPS encryption encrypt;
Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                4.8                0.1        0.03        8.27
       DB CPU(s):                0.4                0.0        0.00        0.72
       Redo size:          644,447.5           18,839.1
   Logical reads:            9,441.5              276.0
   Block changes:            4,412.8              129.0
  Physical reads:            5,702.6              166.7
 Physical writes:            1,952.0               57.1
Time Model System Stats  DB/Inst: SLOB/SLOB  Snaps: 28-29
-> Ordered by % of DB time desc, Statistic name
 
Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time                         1,455.0      99.9
DB CPU                                             126.9       8.7
Tablespace encryption elapsed time                  15.4       1.1
Tablespace encryption cpu time                      12.1        .8

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read                        546,294         660      1   30.8
free buffer waits                               30,704         325     11   15.2
log file parallel write                          8,057         304     38   14.2
db file parallel write                           9,929         260     26   12.1
db file async I/O submit                         6,304         185     29    8.7
Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads                      2,860,788        9,441.5        276.0
consistent gets                            2,154,358        7,110.1        207.9
blocks decrypted                             850,557        2,807.1         82.1
blocks encrypted                           1,042,777        3,441.5        100.6

Run on encrypted tablespace

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                4.0                0.1        0.02        6.95
       DB CPU(s):                0.5                0.0        0.00        0.95
       Redo size:        1,057,446.8           18,806.5
   Logical reads:           15,534.1              276.3
   Block changes:            7,248.4              128.9
  Physical reads:            9,415.8              167.5
 Physical writes:            3,266.7               58.1
Time Model System Stats  DB/Inst: SLOB/SLOB  Snaps: 30-31
-> Ordered by % of DB time desc, Statistic name
 
Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time                         1,201.1      99.9
DB CPU                                             164.7      13.7
Tablespace encryption elapsed time                  19.0       1.6
Tablespace encryption cpu time                      10.1        .8
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read                        824,329         958      1   47.3
log file parallel write                         12,207         416     34   20.5
db file parallel write                          17,405         202     12   10.0
CPU time                                                       166           8.2
db file parallel read                           46,394         113      2    5.6
Statistic                                      Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads                      4,706,832       15,534.1        276.3
consistent gets                            3,546,519       11,704.7        208.2
blocks decrypted                           2,852,666        9,414.7        167.4
blocks encrypted                             989,254        3,264.9         58.1

Run during tablespace decryption

As soon as I started this SLOB run, I started the decryption:

alter tablespace IOPS encryption decrypt;
Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                4.9                0.2        0.04        7.56
       DB CPU(s):                0.4                0.0        0.00        0.61
       Redo size:          606,680.3           19,111.0
   Logical reads:            8,817.1              277.8
   Block changes:            4,121.4              129.8
  Physical reads:            5,294.9              166.8
 Physical writes:            1,827.2               57.6
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read                        515,429         629      1   25.9
free buffer waits                               34,335         362     11   14.9
log file parallel write                          7,287         293     40   12.1
direct path write                                7,703         275     36   11.3
db file parallel write                           9,966         270     27   11.1
Time Model System Stats  DB/Inst: SLOB/SLOB  Snaps: 32-33
-> Ordered by % of DB time desc, Statistic name
 
Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time                         1,464.6      99.9
DB CPU                                             117.9       8.0
Tablespace encryption elapsed time                   9.4        .6
Tablespace encryption cpu time                       4.6        .3
Statistic                                Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads                      2,662,776        8,817.1        277.8
consistent gets                            2,001,129        6,626.3        208.7
blocks decrypted                           1,026,940        3,400.5        107.1
blocks encrypted                             696,105        2,305.0         72.6

Run on decrypted tablespace

Load Profile              Per Second    Per Transaction    Per Exec    Per Call
~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------
      DB time(s):                4.0                0.1        0.02        6.79
       DB CPU(s):                0.4                0.0        0.00        0.72
       Redo size:        1,060,856.5           18,876.7
   Logical reads:           15,565.8              277.0
   Block changes:            7,258.6              129.2
  Physical reads:            9,418.8              167.6
 Physical writes:            3,330.5               59.3
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file sequential read                        818,717         999      1   42.1
log file parallel write                         11,799         421     36   17.8
direct path write                                8,887         299     34   12.6
db file parallel write                          18,817         222     12    9.4
CPU time                                                       129           5.4
Time Model System Stats  DB/Inst: SLOB/SLOB  Snaps: 34-35
-> Ordered by % of DB time desc, Statistic name
 
Statistic                                       Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time                         1,201.1     100.0
DB CPU                                             127.1      10.6
PL/SQL execution elapsed time                        1.7        .1
parse time elapsed                                   0.0        .0
connection management call elapsed                   0.0        .0
Tablespace encryption cpu time                       0.0        .0
Tablespace encryption elapsed time                   0.0        .0
Statistic                                Total     per Second    per Trans
--------------------------------- ------------------ -------------- ------------
session logical reads                      4,685,294       15,565.8        277.0
consistent gets                            3,528,610       11,723.0        208.6
blocks decrypted                                 271            0.9          0.0
blocks encrypted                                  10            0.0          0.0

Observations

During encryption and decryption, we have contention on ‘free buffer waits’. When running a workload that is I/O bound, and with updates, the DBWR cannot keep-up when encryption/decryption is running in parallel. Online encryption works like online datafile move: there is a double write, one the the current file and one to the file encrypted one. Only when completed, the reads and writes are directed to ne new file and the old one is removed.

The statistics ‘blocks decrypted’ and ‘block encrypted’ are related to reads and writes from an encrypted tablespace.

The Time Model ‘Tablespace encryption’ statistics are significant only when the tablespace is encrypted, or during encryption/decryption. But the time is not so significant: 1% of DB Time. I’m not completely sure about how to interpret it and it is not yet documented. From my test, it looks like it measures the overhead of reading from encrypted tablespaces.

But fore sure, having the tablespaces encrypted is not a big overhead, and online encryption can be useful to avoid a large maintenance window (it can take few hours to encrypt hundred of GB) but don’t run it at a time where you have lot of modifications.

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
 Oracle ACE Director
 Oracle Database OCM 12c certified
 AWS Database Specialty certified
 Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn: www.linkedin.com/in/franckpachot
Podcast en français: DBPod