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
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.
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.
Now let’s look at what happens when I compress the backup of this database
My next Blog will cover taking this data set and compressing it.
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).
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.
Here is the total size for the dataSEGMENT_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 MBTOTAL 36,804 MB
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]$
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
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.
