TDE–How to implement TDE in your database and what to think about (part 1)

This is the first part in a series of blog posts on TDE.
Many organizations are moving to TDE, and this can have a dramatic affect on your systems.
TDE impacts 2 areas
1) Post encryption compression goes away.  Encrypted data can’t be compressed.  Now why do I mention “Post encryption” ? This is because data can be compressed before encrypting.  Compressed data in your database (HCC, OLTP, basic etc.) is compressed PRIOR to encryption.  Utilizing compression in your database not only saves you disk space on your storage system, but it also saves you disk space for your backups.  The loss of compression post encryption can have many consequences you might not immediately think of

• if you are using SSD storage that compresses blocks, you need to take into account the extra storage needed
• If you are using a De-duplication appliance you will lose most of the benefits of de-duplication.
• If you are compressing your backups, you will lose the benefits gained from compression (small backups and lowered network traffic).

2) Moving to TDE requires more space during the migration. Rebuilding the tablespaces with a newly encrypted copy is done by creating a second copy of each datafile (encrypted), and then removing the pre-encrypted copy.  The database switches to the new datafile when the process is complete.  This utilizes additional storage for the second copy of the datafiles.  The other migration impact is an increase in backup storage.  After encrypting tablespaces, a new level 0 backup is needed to ensure you are restoring to an encrypted copy of the data. Remember the encryption process changes all the blocks in the datafiles. I will discuss the backup implications more later.

Now I’m going to start by describing the dataset I used for testing.
In order to create this dataset I used the oewizard from Swingbench

Here are the objects and the sizes.

SEGMENT_NAME              SEGMENT_TYPE TABLESPACE_NAME SPACE_USED
------------------------- ------------ --------------- ------------
ADDRESSES                 TABLE        SOE                3,392 MB
ADDRESS_CUST_IX           INDEX        SOE                  703 MB
ADDRESS_PK                INDEX        SOE                  662 MB
CARDDETAILS_CUST_IX       INDEX        SOE                  703 MB
CARD_DETAILS              TABLE        SOE                2,048 MB
CARD_DETAILS_PK           INDEX        SOE                  662 MB
CUSTOMERS                 TABLE        SOE                3,328 MB
CUSTOMERS_PK              INDEX        SOE                  443 MB
CUST_ACCOUNT_MANAGER_IX   INDEX        SOE                  417 MB
CUST_DOB_IX               INDEX        SOE                  528 MB
CUST_EMAIL_IX             INDEX        SOE                  975 MB
CUST_FUNC_LOWER_NAME_IX   INDEX        SOE                  683 MB
INVENTORIES               TABLE        SOE                  176 MB
INVENTORY_PK              INDEX        SOE                   18 MB
INV_PRODUCT_IX            INDEX        SOE                   16 MB
INV_WAREHOUSE_IX          INDEX        SOE                   16 MB
ITEM_ORDER_IX             INDEX        SOE                2,000 MB
ITEM_PRODUCT_IX           INDEX        SOE                1,768 MB
LOGON                     TABLE        SOE                1,728 MB
ORDERENTRY_METADATA       TABLE        SOE                    0 MB
ORDERS                    TABLE        SOE                3,968 MB
ORDER_ITEMS               TABLE        SOE                6,976 MB
ORDER_ITEMS_PK            INDEX        SOE                2,234 MB
ORDER_PK                  INDEX        SOE                  632 MB
ORD_CUSTOMER_IX           INDEX        SOE                  671 MB
ORD_ORDER_DATE_IX         INDEX        SOE                  752 MB
ORD_SALES_REP_IX          INDEX        SOE                  594 MB
ORD_WAREHOUSE_IX          INDEX        SOE                  709 MB
PRD_DESC_PK               INDEX        SOE                    0 MB
PRODUCT_DESCRIPTIONS      TABLE        SOE                    0 MB
PRODUCT_INFORMATION       TABLE        SOE                    0 MB
PRODUCT_INFORMATION_PK    INDEX        SOE                    0 MB
PROD_CATEGORY_IX          INDEX        SOE                    0 MB
PROD_NAME_IX              INDEX        SOE                    0 MB
PROD_SUPPLIER_IX          INDEX        SOE                    0 MB
WAREHOUSES                TABLE        SOE                    0 MB
WAREHOUSES_PK             INDEX        SOE                    0 MB
WHS_LOCATION_IX           INDEX        SOE                    0 MB


TOTAL                                                                                 36,804 MB

Here is the total size for the data

TABLESPACE_NAME   FILE_ID FILE_NAME            SPACE_USED   TOTAL_ALLOCATED
--------------- --------- -------------------- ------------ --------------------
SYSTEM                  1 system01.dbf              819 MB       830 MB
SYSAUX                  3 sysaux01.dbf              809 MB       860 MB
UNDOTBS1                4 undotbs01.dbf             369 MB    29,180 MB
SOE                     5 soe_1.dbf               3,600 MB     5,120 MB
USERS                   7 users01.dbf                 5 MB         5 MB
SOE                     8 soe_2.dbf               3,841 MB     5,120 MB
SOE                     9 soe_3.dbf               3,822 MB     5,120 MB
SOE                    10 soe_4.dbf               3,825 MB     5,120 MB
SOE                    11 soe_5.dbf               3,806 MB     5,120 MB
SOE                    12 soe_6.dbf               3,728 MB     5,120 MB
SOE                    13 soe_7.dbf               3,781 MB     5,120 MB
SOE                    14 soe_8.dbf               3,442 MB     5,120 MB
SOE                    15 soe_9.dbf               3,464 MB     5,120 MB
SOE                    16 soe_10.dbf              3,495 MB     5,120 MB


===================================================================================================================

Total                                   38,303 MB      60,820 MB      22,517 MB

From above I can see that I am using 38 GB of space, out of the 61 GB of space allocated.
Now I created a backup set . With no compression the size of the backup set is about the size data used.

[oracle@oracle-server]$ ls -al
total 38910628
drwxrwx---. 2 oracle oracle          58 Nov 15 16:34 .
drwxrwx---. 3 oracle oracle          24 Nov 15 16:23 ..
-rw-rw----. 1 oracle oracle 39844478976 Nov 15 16:37 o1_mf_nnnd0_TAG20181115T163432_fyvsm8rz_.bkp
[oracle@oracle-server]$

Just to save my spot .I’m going to create a restore point to make this the starting point of all my testing.

SQL> create restore point new_database;

Restore point created.

Now let’s look at what happens when I compress the backup of this database

 oracle oracle 39844478976 Nov 15 16:37 o1_mf_nnnd0_TAG20181115T163432_fyvsm8rz_.bkp   ---> Original backup
 oracle oracle 11424759808 Nov 15 17:09 o1_mf_nnndf_TAG20181115T165247_fyvtoj7m_.bkp   ---> Basic Compression
 oracle oracle  9468592128 Nov 15 18:33 o1_mf_nnndf_TAG20181115T174452_fyvxq4s2_.bkp   ---> High Compression
 oracle oracle 14488240128 Nov 15 18:44 o1_mf_nnndf_TAG20181115T183319_fyw0l08k_.bkp   ---> Medium Compression

Finally I took an incremental merge backup to see what happens with that.

ls -al
total 62300276
drwxrwx---. 2 oracle oracle       4096 Nov 16 09:47 .
drwxrwx---. 7 oracle oracle         92 Nov 14 13:24 ..
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:38 o1_mf_soe_fyxolco8_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:39 o1_mf_soe_fyxon2o4_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:40 o1_mf_soe_fyxoohtl_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:41 o1_mf_soe_fyxopwy8_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:41 o1_mf_soe_fyxorb2f_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:42 o1_mf_soe_fyxosq7c_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:43 o1_mf_soe_fyxov49x_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:44 o1_mf_soe_fyxowklr_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:44 o1_mf_soe_fyxoxyl8_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:45 o1_mf_soe_fyxozcq9_.dbf
-rw-rw----. 1 oracle oracle 5368717312 Nov 16 09:46 o1_mf_soe_fyxp0rwd_.dbf
-rw-rw----. 1 oracle oracle  692068352 Nov 16 09:47 o1_mf_sysaux_fyxp3gc4_.dbf
-rw-rw----. 1 oracle oracle  859840512 Nov 16 09:46 o1_mf_system_fyxp2z72_.dbf
-rw-rw----. 1 oracle oracle 3187679232 Nov 16 09:46 o1_mf_undotbs1_fyxp2666_.dbf

Backup Method	Backup Size
Image Copy	62 GB
No Compression	40 GB
Basic Compression	11 GB
Medium Compression	14 GB
High Compression	95 GB

My next Blog will cover taking this data set and compressing it.