Friday, November 30, 2018

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

This is the second part in a series on implementing TDE and what happens to the sizing.

At first my plan was to encrypt the dataset I created in my first post, but instead I compressed it.

At this point (and throughout this post), I am working with an un-encrypted dataset.

One of the first things to understand about Encryption is that encrypted data DOES NOT compress.
This is critical when understanding what happens when you implement TDE.

One way to save storage when implementing TDE is to implement encryption AND compression together.

In order to break down the affects of encryption on compressed data VS uncompressed data, I took my dataset (the SOE dataset from swing bench) and I compressed it.  I implemented Advanced compression on the tables, and I compressed the indexes and rebuilt them.

I now have 2 copies of the same dataset. 1 is compressed, and 1 is not.

Now let's take a look at the sizing of the Data sets and I will go through the same backup procedures and see what happens.

SEGMENT_NAME              SEGMENT_TYPE Space Used uncompressed   Space Used Compressed SPACE_SAVINGS
------------------------- ------------ ------------------------- -------------------- -------------
ADDRESSES                 TABLE           3,392 MB                  3,264 MB                      3
ADDRESS_CUST_IX           INDEX             703 MB                    728 MB                     -3
ADDRESS_PK                INDEX             662 MB                    888 MB                    -34
CARDDETAILS_CUST_IX       INDEX             703 MB                    562 MB                     20
CARD_DETAILS              TABLE           2,048 MB                  1,600 MB                     21
CARD_DETAILS_PK           INDEX             662 MB                      0 MB                    100
CUSTOMERS                 TABLE           3,328 MB                  2,880 MB                     13
CUSTOMERS_PK              INDEX             443 MB                      0 MB                    100
CUST_ACCOUNT_MANAGER_IX   INDEX             417 MB                    272 MB                     34
CUST_DOB_IX               INDEX             528 MB                    280 MB                     47
CUST_EMAIL_IX             INDEX             975 MB                    280 MB                     71
CUST_FUNC_LOWER_NAME_IX   INDEX             683 MB                    280 MB                     58
INVENTORIES               TABLE             176 MB                    176 MB                      0
INVENTORY_PK              INDEX              18 MB                      0 MB                    100
INV_PRODUCT_IX            INDEX              16 MB                     12 MB                     24
INV_WAREHOUSE_IX          INDEX              16 MB                     12 MB                     24
ITEM_ORDER_IX             INDEX           2,000 MB                  1,770 MB                     11
ITEM_PRODUCT_IX           INDEX           1,768 MB                  1,301 MB                     26
LOGON                     TABLE           1,728 MB                  1,728 MB                      0
ORDERENTRY_METADATA       TABLE               0 MB                      0 MB                      0
ORDERS                    TABLE           3,968 MB                  2,816 MB                     29
ORDER_ITEMS               TABLE           6,976 MB                  4,992 MB                     28
ORDER_ITEMS_PK            INDEX           2,234 MB                      0 MB                    100
ORDER_PK                  INDEX             632 MB                      0 MB                    100
ORD_CUSTOMER_IX           INDEX             671 MB                    480 MB                     28
ORD_ORDER_DATE_IX         INDEX             752 MB                    439 MB                     41
ORD_SALES_REP_IX          INDEX             594 MB                    438 MB                     26
ORD_WAREHOUSE_IX          INDEX             709 MB                    438 MB                     38
PRD_DESC_PK               INDEX               0 MB                      0 MB                    100
PRODUCT_DESCRIPTIONS      TABLE               0 MB                      0 MB                      0
PRODUCT_INFORMATION       TABLE               0 MB                      0 MB                      0
PRODUCT_INFORMATION_PK    INDEX               0 MB                      0 MB                    100
PROD_CATEGORY_IX          INDEX               0 MB                      0 MB                      0
PROD_NAME_IX              INDEX               0 MB                      0 MB                      0
PROD_SUPPLIER_IX          INDEX               0 MB                      0 MB                   -100
WAREHOUSES                TABLE               0 MB                      0 MB                      0
WAREHOUSES_PK             INDEX               0 MB                      0 MB                    100
WHS_LOCATION_IX           INDEX               0 MB                      0 MB                   -100


Here is the total savings by compressing both tables and indexes with advanced compression.

Space Used uncompressed   Space Used Compressed  SPACE_SAVINGS
------------------------- ---------------------- -------------
  36,804 MB                 25,636 MB                       30



Now to compare this with the previous data uncompressed I am going to backup by tablespace.  Below is the sizing of the backups. I used a tag to identify the backups.


-rw-rw----. 1 oracle oracle 26773323776 Nov 29 17:02 COMPRESSED_SOE.bkp
-rw-rw----. 1 oracle oracle 38441140224 Nov 29 17:04 UNCOMPRESSED_SOE.bkp
-rw-rw----. 1 oracle oracle 10987765760 Nov 29 18:35 BASIC_COMPRESSED_SOE.bkp
-rw-rw----. 1 oracle oracle 11135655936 Nov 29 18:36 BASIC_COMPRESSED_SOE_COMPRESSED.bkp
-rw-rw----. 1 oracle oracle 13360308224 Nov 29 20:12 MEDIUM_COMP_SOE_COMPRESSED.bkp
-rw-rw----. 1 oracle oracle 14383603712 Nov 29 20:12 MEDIUM_COMPRESSED_SOE_.bkp
-rw-rw----. 1 oracle oracle  9420791808 Nov 30 00:12 HIGH_COMP_SOE_COMPRESSED.bkp
-rw-rw----. 1 oracle oracle  9112944640 Nov 30 00:23 HIGH_COMPRESSED_SOE.bkp


Now I'm going to put that in a table and a chart to compare..

First the table of sizes



Now the chart


Now by looking at the chart it is apparent what happens with compression and the data.


  • Compression in the database reduced the size of the data by 30%
  • An uncompressed backupset matched the size of the data
  • Once I compressed the backupset, the difference is size was minimal.

** Bottom line - Compressing the data in the database saved on the uncompressed backupsize. Once the backupset is compressed the final size is about the same.

** Final conclusion -- Most modern backup appliances (ZDLRA, ZFS, DD) compress the backups.  When using those appliances with unencrypted data, the final size is the same regardless of whether the data is compressed in the Database.

Now that I've looked at both compressed and uncompressed data at the DB and backupset I am going to compress the data.  Next post.


Thursday, November 15, 2018

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.