Using SecureFiles to Improve Performance, Maximize Storage, and Enhance Security

Purpose

This tutorial shows you how to implement the new Database 11g Large Object (LOB) storage format called SecureFiles. You learn to migrate the pre-Database 11g LOB storage format (called BasicFile LOB format) to the SecureFiles LOB format, with deduplication (storage sharing) and compression enabled at table creation time. You also compare the performance of LOBs stored in the BasicFile format versus the SecureFiles format. Finally, you enable SecureFiles LOB encryption.

Time to Complete

Approximately 20 minutes

Topics

This tutorial covers the following topics:

 Overview
 Prerequisites
 Set Up Your Database and Environment for the OBE
 Sample the Performance of BasicFile LOB
 Verify the Space Usage of BasicFile LOB
 Migrate BasicFile LOB to SecureFiles LOB
 Verify the Space Usage of SecureFiles LOB
 Sample the Performance of SecureFiles LOB
 Enable Encryption
 Clean Up
 Summary

Viewing Screenshots

 Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

What Is SecureFiles?

SecureFiles is a Database 11g feature that introduces a completely re-engineered storage format for large object (LOB) data types to improve performance, reduce space usage, and enhance security, all without compromising the ease of application development. This new implementation offers advanced, next-generation functionality such as deduplication (also referred to as LOB sharing), intelligent compression, and transparent encryption. SecureFiles significantly strengthens the native content management capabilities of Oracle Database.

Back to Topic List

Prerequisites

Before you perform this tutorial, you should:

1.

Install Oracle Database 11g.

2.

Download and unzip the obe.zip file into your working directory (i.e.wkdir)

Back to Topic List

Set Up Your Database and Environment for the OBE

To set up your database and environment for the OBE, perform the following steps:

1.

Create a directory to store the Transparent Data Encryption (TDE) wallet. This is required for the SecureFiles LOB encryption.

mkdir $ORACLE_HOME/wallet

 

2.

Edit the $ORACLE_HOME/network/admin/sqlnet.ora file to indicate the location of the TDE wallet.

ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=/u01/app/oracle/product/11.1.0/db_1/wallet)))

 

3.

Using SQL*Plus, connect to the database as SYS and run the obe_setup.sql script.

cd /wkdir/files < or wherever you have unzipped the contents of the obe.zip file >
$ORACLE_HOME/bin/sqlplus / as sysdba
@obe_setup.sql

The first step is to create an encryption key and store it in the TDE wallet. You will use this encryption key later to encrypt the SecureFiles LOB.

Press [Enter] to continue to the next step in the script.

 

4.

The next step in the script is to create a database schema for this OBE. The schema is called SF_DEMO. It is granted the CONNECT and RESOURCE roles. It also requires the EXECUTE ANY PROCEDURE and CREATE ANY DIRECTORY system privileges.

Press [Enter] to continue to the next step in the script.

 

5.

Next, the script creates two 150MB tablespaces. The OBE_TBS1 tablespace will be used to store the LOB in BasicFile format, and the OBE_TBS2 tablespace will be used to store the LOB in the new SecureFiles format.

Automatic Segment Space Management (ASSM) needs to be enabled for the tablespace to support SecureFiles.

Press [Enter] to continue to the next step in the script.

 

6.

The rest of the script is to create objects in the SF_DEMO schema. First a table called RESUMES is created. This table has a BLOB column ( RESUME) that is stored in BasicFile format.

Press [Enter] to continue to the next step in the script.

 

7.

A DIRECTORY object is required to store the path name of the MS Word files which will be retrieved and stored in the LOB column.

When prompted for the directory_name, enter the full path where you have unzipped the contents of obe.zip. For example, the path name may be /home/oracle/wkdir.

Press [Enter] to continue to the next step in the script.

 

8.

To read the MS Word files on the file system and store them into into the LOB column, a procedure called LOADLOBFROMBFILE_PROC is created. This procedure calls the DBMS_LOB package. Note that this same procedure will be used for both BasicFile as well as SecureFiles LOB storage formats.

Press [Enter] to continue to the next step in the script.

 

9.

To insert records into the RESUMES table, including the LOB values, a procedure called WRITE_LOB is created. This procedure calls the LOADLOBFROMBFILE_PROC procedure created in the previous step.

Press [Enter] to continue to the next step in the script.

 

10.

To retrieve records from the RESUMES table, including the LOB values, a procedure called READ_LOB is created.

Press [Enter] to continue to the next step in the script.

 

11.

A procedure called CHECK_SPACE_BF is created to display the space usage in the disk blocks allocated to the BasicFile LOB segment in the RESUMES table. This procedure calls the DBMS_SPACE package.

Note: The DBMS_SPACE.SPACE_USAGE procedure has been overloaded in Oracle Database 11g. There are now two DBMS_SPACE.SPACE_USAGE procedures. The two cannot be used interchangeably. The invocation of the DBMS_SPACE.SPACE_USAGE procedure in CHECK_SPACE_BF is ONLY valid for use against BasicFile LOBs.

 

12.

A procedure called CHECK_SPACE_SF is created to display the space usage in the disk blocks allocated to the SecureFiles LOB segment in the RESUMES table. This procedure calls the DBMS_SPACE package.

Note: The DBMS_SPACE.SPACE_USAGE procedure has been overloaded in Oracle Database 11g. There are now two DBMS_SPACE.SPACE_USAGE procedures. The two cannot be used interchangeably. The invocation of the DBMS_SPACE.SPACE_USAGE procedure in CHECK_SPACE_SF is ONLY valid for use against SecureFiles LOBs.

 

Back to Topic List

Sample the Performance of BasicFile LOB Storage

To capture write and read performance statistics for BasicFile LOB storage, perform the following steps:

1.

Using SQL*Plus, connect to the database as sf_demo/oracle. Run the write_data.sql script.

$ORACLE_HOME/bin/sqlplus sf_demo/oracle
@write_data.sql

This script inserts 90 records into the RESUMES table. Each record includes a LOB between 35KB to 65KB in size. A SQL*Plus timer is started to capture the total elapsed time for the insert.

When the script completes write down the elapsed time for loading LOBs in BasicFile format.

Note: Depending on your hardware configuration (for example, CPU speed, amount of memory, and so on), the elapsed time you observe may differ.

 

2.

Now retrieve the records that were inserted in the previous step. Run the read_data.sql script.

@read_data.sql

This script reads back the 90 records from the RESUMES table. For each record, the size of the LOB value plus the first 200 characters of the LOB are displayed on screen. A SQL*Plus timer is started to capture the total elapsed time for the retrieval.

When the script completes write down the elapsed time for reading LOBs in BasicFile format.

Note: Depending on your hardware configuration (for example, CPU speed, amount of memory, and so on), the elapsed time you observe may differ.

 

Back to Topic List

Verify the Space Usage of BasicFile LOB

To verify the space usage of BasicFile LOBs, perform the following steps:

1.

Using SQL*Plus, connect to the database as sf_demo/oracle. Run the space_usage.sql script.

$ORACLE_HOME/bin/sqlplus sf_demo/oracle
@space_usage.sql

When prompted for the LOB type, enter BF . When the script completes, write down the number of Full Blocks , and the Full Blocks Bytes .

 

Back to Topic List

Migrate BasicFile LOB to SecureFiles LOB

To migrate BasicFile LOB storage to SecureFiles LOB storage, perform the following steps:

1.

Using SQL*Plus, connect to the database as SYS. Run the mig_bf_to_sf.sql script.

$ORACLE_HOME/bin/sqlplus / as sysdba
@mig_bf_to_sf.sql

First, query the DBA_SEGMENTS view to see the LOB segment subtype name for BasicFile LOB storage. Note that it says ASSM.

Press [Enter] to continue to the next step in the script.

 

2.

The migration from BasicFile to SecureFiles LOB storage format is done online. This means that the RESUMES table will continue to be accessible during the migration. This type of operation is called online redefinition.

Online redefinition requires an interim table for data storage.

In this step of the script, the interim table is defined with the SecureFiles LOB storage format, and the LOB is stored in the OBE_TBS2 tablespace. Once the migration completes, the RESUME LOB is stored in the OBE_TBS2 tablespace.

Note: It is recommended practice to enable compression and deduplication at table creation time. Also, the ALTER TABLE SHRINK operation is not yet supported for SecureFiles LOBs in this release of Oracle Database.

Press [Enter] to continue to the next step in the script.

 

3.

The next step in the script calls the DBMS_REDEFINITION package to perform the online redefinition operation.

Press [Enter] to continue to the next step in the script.

 

4.

Once the online redefinition operation completes, the interim table can be dropped.

Press [Enter] to continue to the next step in the script.


5.

The migrated LOB data now resides in the obe_tbs2 tablespace. This step in the script queries the DBA_SEGMENTS view to see the LOB segment subtype name for SecureFiles LOB storage. Note that it says SECUREFILE.

 

6.

The final step in the script queries the DBA_LOBS view to see the compression and deduplication settings for the SecureFiles LOB segment.

 

Back to Topic List

Verify the Space Usage of SecureFiles LOB

To verify the space usage of SecureFiles LOBs, perform the following steps:

1.

Using SQL*Plus, connect to the database as sf_demo/oracle. Run the space_usage.sql script.

$ORACLE_HOME/bin/sqlplus sf_demo/oracle
@space_usage.sql

When prompted for the LOB type, enter SF . When the script completes, write down the number of Used Blocks , and the Used Blocks Bytes .

 

2.

Compare the BasicFile LOB Full Blocks number and bytes with the SecureFiles LOB Used Blocks number and bytes.

Notice the dramatic decrease in space usage for the SecureFiles LOB with compression and deduplication enabled.

 

Back to Topic List

Sample the Performance of SecureFiles LOB Storage

To capture read and write performance statistics for SecureFiles LOB storage, perform the following steps:

1.

Unlike the previous section on sampling performance of BasicFile LOB storage, you start with a read performance test first for SecureFiles LOB storage. This is to ensure a valid read performance comparison between BasicFile and SecureFiles -- you need to perform the read test on the same set of records, right after they have been migrated from BasicFile to SecureFiles.

Using SQL*Plus, connect to the database as sf_demo/oracle. Run the read_data.sql script.

$ORACLE_HOME/bin/sqlplus sf_demo/oracle
@read_data.sql

This script reads back the 90 records from the RESUMES table. For each record, the size of the LOB value plus the first 200 characters of the LOB are displayed on screen. A SQL*Plus timer is started to capture the total elapsed time for the retrieval.

When the script completes write down the elapsed time for reading LOBs in SecureFiles format.

Note: Depending on your hardware configuration (for example, CPU speed, amount of memory, and so on), the elapsed time you observe may differ.

 

2.

Now insert additional records. Run the write_data.sql script.

@write_data.sql

This script inserts 90 records into the RESUMES table. Each record includes a LOB between 35KB to 65KB in size. A SQL*Plus timer is started to capture the total elapsed time for the insert.

When the script completes write down the elapsed time for writing LOBs in SecureFiles format.

Note: Depending on your hardware configuration (for example, CPU speed, amount of memory, and so on), the elapsed time you observe may differ.

 

Back to Topic List

Enable Encryption

To encrypt LOB stored in SecureFiles format, perform the following steps:

1.

Using SQL*Plus, connect to the database as sf_demo/oracle. Run the encrypt_lob.sql script.

$ORACLE_HOME/bin/sqlplus sf_demo/oracle
@encrypt_lob.sql

 

2.

Verify that the LOB is now encrypted. Run the check_encrypt.sql script.

@check_encrypt.sql

Note: The column encryption operation essentially updates all existing LOB values. However, if free space is available in the data block, the original unencrypted form of the LOB value may still exist. To ensure that no unencrypted values are on disk, it is best to enable encryption at table creation time (i.e., before any values are inserted).

Back to Topic List

Clean Up

To clean up the database accounts and objects created for this OBE, perform the following steps:

1.

Using SQL*Plus, connect to the database as SYS. Drop the SF_DEMO schema.

$ORACLE_HOME/bin/sqlplus / as sysdba

DROP USER sf_demo CASCADE;

 

2.

Drop the OBE_TBS1 and OBE_TBS2 tablespaces.

DROP TABLESPACE obe_tbs1 INCLUDING CONTENTS;

DROP TABLESPACE obe_tbs2 INCLUDING CONTENTS;

 

Summary

In this tutorial, you learned how to:

 Measure the write and read performance of BasicFile versus SecureFiles LOB storage formats.
 Compare the space usage of BasicFile versus SecureFiles LOB storage formats.
 Migrate BasicFile LOB storage format to SecureFiles LOB storage format.
 Verify that the same DBMS_LOB API used against the BasicFile LOB storage format (i.e., the pre-11g format) also works against the SecureFiles LOB storage format.
 Enable encryption for SecureFiles LOB storage.

Back to Topic List

 Place the cursor over this icon to hide all screenshots.

 

 

 

Left Curve
Popular Downloads
Right Curve
Untitled Document