Install Oracle Database 11g.

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

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

mkdir $ORACLE_HOME/wallet

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)))

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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.

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

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.

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.

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.

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.

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

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).

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;

Drop the OBE_TBS1 and OBE_TBS2 tablespaces.

DROP TABLESPACE obe_tbs1 INCLUDING CONTENTS;

DROP TABLESPACE obe_tbs2 INCLUDING CONTENTS;