# Next-Generation LOBs - Oracle 11g

Oracle Database 11g breaks the barrier of large object (LOB) storage in the database.Though once perceived to be something handled only outside the database, Oracle Database 11g proves that files can easily be stored in the database with equal or better performance than in a traditional file system.

Although Oracle Database 10g provides file storage in the database using various utilities and applications,this technology was not well embraced by the Oracle community as a whole. Before Oracle Database 11g,LOBs were classified as either character LOBs (CLOBs) or binary LOBs(BLOBs).The only distinguishing factor between the two is that one is used to store large variable character data,and the other is used to store binary data.Oracle Database 11g completely reengineers LOB storage to what they call SecureFiles.The primary focus of the SecureFiles feature is to do the following:

• Improve performance
• Reduce space consumption
• Enhance security

SecureFiles vastly improves the management of large objects by introducing critical new functionality. First,LOB compression saves disk space for data at rest.Second,LOB encryption provides for data security at rest.Oracle offers the capability to deduplicate LOB segments and provides additional LOB APIs. For performance needs,LOB data path optimizations are included in Oracle Database 11g.We will be discussing all of these features in detail in the following sections.

SecureFiles Requirements

To take advantage of Oracle SecureFiles, you must satisfy a couple of requirements. First,the tablespace that houses the secure file must be in automatic segment space management (assm).Luckily, Oracle adopted the assm tablespace as a default feature when it was introduced in Oracle Database 10g and continues to be in Oracle Database 11g. Second, the compatibility for the database must be set to 11.0.0.0.0 or higher.

BasicFiles vs.SecureFiles

Oracle distinguishes the legacy large object storage from its next-generation large objects,referring to the legacy large objects now as Basic Files.BasicFiles comprises all the CLOB and BLOB functionality as you know it up to Oracle Database 10g Release 2.Starting from Oracle Database 11g,the next-generation large objects are distinguished as Secure Files.Let’s drill down to the specific differences of SecureFiles.

What differentiates SecureFiles from BasicFiles is the storage parameter used when creating the table or adding the column. To leverage SecureFiles, you must specify the reserved store as securefile keyword of the LOB clause. Here’s an example showing how to create a table leveraging the Secure Files storage for the BLOB_CONTENT column in a table called SECURE_DOCS:

create table secure_docs
( document_id number not null primary key,
name varchar2(255) not null,
edba_knowledgebase_category_id number not null,
mime_type varchar2(128),
doc_size number,
last_updated date,
content_type varchar2(128),
blob_content blob
)
tablespace tools
lob (blob_content) store as securefile (
tablespace tools enable storage in row chunk 8192 pctversion 10
nocache logging)
/

By default,BasicFiles is created if the store as securefile clause is not specified.The following example creates a table named DOCS with a BasicFiles column named BLOB_CONTENT:

create table docs
( document_id number not null primary key,
name varchar2(255) not null,
edba_knowledgebase_category_id number,
mime_type varchar2(128),
doc_size number,
last_updated date,
content_type varchar2(128),
blob_content blob
)
tablespace tools
lob (blob_content) store as basicfile (
tablespace tools enable storage in row chunk 8192 pctversion 10
nocache logging)
/

Note The term FastFiles is synonymous with SecureFiles.

LOB Performance

One of the promising features of SecureFiles is that the feature is highly performatory and capable of higher throughput than the file system.There are numerous performance optimizations for Secure Files,including the following:

• SecureFiles allows for dynamic cache and nocache options.With Oracle Database 11g compatibility, you can provide a logging clause after the cache parameter.Oracle will determine when to cache or not cache the LOB into the buffer cache for performance.The logging setting is similar to the data journaling capabilities of file systems.
• The locking mechanism for SecureFiles uses the new distributed lock management semantics for LOB blocks to avoid cache fusion locks.
• The writing mechanism of LOBs is similar to the file system,in that LOB IOs are now capable of dirty cache writes on file servers.Similar to the file system,Oracle is capable of coalescing several IO requests into a single large IO call to the database,thus reducing the overhead from what it would take to manage several small IO requests.Moreover,Oracle Database 11g provides the option of prefetching LOBs at the OCI layer.
• The new SecureFiles storage option called filesystem_like_logging allows the logging of only the metadata changes.This is similar to the metadata journaling of file systems. filesystem_like_logging reduces the mean time to recovery.

Deduplication

Oracle Database 11g SecureFiles adds to the LOB paradigm the ability to detect duplicate LOB data and eliminate duplicates by using a secure hash SHA1 algorithm.Elimination of duplicate data is achieved by using the deduplicate LOB syntax.Deduplication allows companies to save storage without compromising performance.

By contrast, you can use the keep_duplicates option to continue to allow duplicate values in the same LOB segment to be stored in a table.The
keep_duplicates option remains the default behavior for the deduplication feature in Oracle Database 11g.Continuing to utilize the documents table from the previous example,we’ll show how you can implement this feature.First we’ll create a table that allows for duplicate LOB segments.In the following example,we’ll create a table called DEFAULT_DOCS with the keep_duplicates keyword:

create table default_docs
( document_id number not null primary key,
name varchar2(255) not null,
edba_knowledgebase_category_id number,
mime_type varchar2(128),
doc_size number,
last_updated date,
content_type varchar2(128),
blob_content blob
)
tablespace tools
lob (blob_content) store as securefile (
keep_duplicates
tablespace tools enable storage in row chunk 8192 pctversion 10
nocache logging)
/
Table created.

Using a simple shell script to load a 189KB Microsoft Word document into the database, you can easily prove the usefulness of the deduplicationfunctionality.The following scripts,the load.ksh shell script and a load.ctl SQL*Loader controlfile, are provided.The function of the load.ksh script is to simply increment the sequence number in the load.ctl file and invoke the SQL*Loader utility.Here’s the content of the load.ksh shell script:

export filenum=$(cat$counter_file)
(( NUM = $filenum + 1 )) echo$NUM > $counter_file cat load.ctl | sed -e "s/NUMBER_TO_REPLACE###/$NUM/g" > $controlfile sqlldr control=$controlfile userid=rodba/oracle123

The load.ctl file is the SQL*Loader controlfile to load a Microsoft Word document in the database.The size of the Word document is approximately 189KB.

append
into table docs
fields terminated by '|'
(
document_id,
name,
blob_content lobfile(name) TERMINATED BY EOF
)
begindata
NUMBER_TO_REPLACE###|Chapter9-ASM.doc||

The load.ksh script was invoked 100 times to load 100 copies of the same document into the DEFAULT_DOCS table.By using the DBMS_SPACE.USED_SPACE procedure,the total bytes allocatedfor the LOB segment can be retrieved. The following simple PL/SQL script was created to retrieve the LOB segment size:

This script takes an input parameter of the table name.Based on the table name,the PL/SQL logic queries the DBA_LOBS view and retrieves the LOB segment name associated with the table.This code example assumes that a table will have only one LOB segment.The LOB segment name is passed to the DBMS_SPACE. USED_SPACE procedure as one of the arguments.Using DBMS_OUTPUT, the total number of bytes used for the LOB segment is displayed.

We will save this script to a file named space.sql.We will use the space.sql script to determine the size of the SecureFiles LOB segments repeatedly throughout this chapter.In this particular example, the DEFAULT_DOCS LOB segment size is obtained using the space.sql script:

SQL> @space default_docs
old 12: and table_name=upper('&v_tab');
new 12: and table_name=upper('default_docs');
KB used: 19352
PL/SQL procedure successfully completed.

You can see from this output that the size of the LOB segment in the DEFAULT_DOCS table consumes about 19MB.

Next,let’s create a table that is identical to the DEFAULT_DOCS table except with the deduplicate reserved syntax.Before we create the table,let’s isolate this new table into a separate tablespace.Just to prove a point, we will create a tablespace called secure_docs_d with a single datafile 1MB in size:

SQL>create tablespace secure_docs_d data file '+data' size 1m;

Tablespace created.

A table that is identical to DEFAULT_DOCS will be called SECURE_DOCS but will be created with the deduplication option, as shown here:

create table secure_docs
( document_id number not null primary key,
name varchar2(255) not null,
edba_knowledgebase_category_id number,
mime_type varchar2(128),
doc_size number,
last_updated date,
content_type varchar2(128),
blob_content blob
)
tablespace secure_docs_d
lob (blob_content) store as securefile (
deduplicate lob
tablespace secure_docs_d enable storage in row chunk 8192 pctversion 10
nocache logging)
/

Using SQL*Loader again, we are able to load the same 189KB document 100 times.The deduplication option is truly working since the 100 inserts of the document are resident in the 1MB tablespace.Let’s see the actual size of the Secure Files LOB segment using the same space.sql script created earlier:

SQL> @space secure_docs
old 12: and table_name=upper('&v_tab');
new 12: and table_name=upper('secure_docs');
KB used: 200
PL/SQL procedure successfully completed.

Amazingly, LOBs in the SECURE_DOCS table,using the deduplication option, consume only 200KB of space compared to the LOB column in the BASIC_DOCS table, which consumes about 19MB of storage.

By default, Oracle Database 11g allows duplicate LOB data to be loaded inside the database.When you query the DBA_LOBS view, you will see several distinct values for the DEDUPLICATION column, as shown here:

SQL> select table_name, column_name, deduplication, securefile
2 from dba_lobs
3* where table_name in ('DEFAULT_DOCS', 'SECURE_DOCS', 'DOCS')
SQL> /
TABLE_NAME COLUMN_NAME DEDUPLICATION SEC
--------------- ------------- ------------- ------
DEFAULT_DOCS BLOB_CONTENT NO YES
SECURE_DOCS BLOB_CONTENT LOB YES
DOCS BLOB_CONTENT NONE NO

SecureFiles LOB segments that do not allow for duplicate values will have a value of LOB in the DEDUPLICATION column.You can also see that the value of NONE is provided for BasicFiles LOB segments.

In addition to specifying deduplication when creating a LOB, you can also modify the deduplication attribute of a SecureFiles LOB column using the alter table command:

SQL> alter table default_docs modify lob(blob_content) (deduplicate lob);

Table altered.

When you issue such a command, Oracle Database will immediately read all values in the LOB column and remove duplicates.

DBMS_LOB.SETOPTIONS

Oracle also provides the capability to enable or disablededuplication/ encryption/ compression of LOBs at the per-LOB level using PL/SQL and using the LOB Locator API.The DBMS_LOB.SETOPTIONS stored procedure provides this granularity of control.Here’s a description of the setoptions procedure:

procedure setoptions
argument name type in/out default?
----------------- ------------------- ---------------
lob_loc blob in/out
option_types binary_integer in
options binary_integer in

Encryption

LOB encryption is another superb enhancement to Oracle Database 11g Secure Files.Now,you can encrypt LOB segments using Transparent Data Encryption (TDE).TDE requires an encryption key to be established and stored in Oracle’s TDE wallet.

Continuing with our example from earlier with the SECURE_DOCS table, you can modify this table to be encrypted using the alter table encrypt clause. The following example shows how you can enable encryption for SecureFiles LOBs:

SQL> alter table secure_docs modify (blob_content encrypt using 'AES192');

Table altered.

Multiple levels of encryption are available in Oracle Database11g forSecure Files.The AES192-bit encryption algorithm happens to be the default encryption method. Other available encryption algorithms are as follows:

• 3DES168
• AES128
• AES256

Encryption for SecureFiles must use SALT.The option for NOSALT is notsupported.SALT is a random value added to the data before it is encrypted. SALT in hashing algorithms strengthens the security of encrypted data bymaking it more difficult for hackers to crack using standard pattern-matching techniques.Encryption of the SecureFiles LOB segments occurs at the block level.You can encrypt LOBs on a per-column or on a per-partition basis.

You can query the USER_/DBA_ENCRYPTED_COLUMNS view to confirm that LOB columns are encrypted.You can also confirm that SALT is enabled by default with Secure Files.Here’s an example showing you that the default encryption is AES192-bit encryption and that it applies SALT:

SQL> select table_name, column_name, encryption_alg, salt
from user_encrypted_columns
SQL> /
TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL
----------- ------------- ---------------- ---
SECURE_DOCS BLOB_CONTENT AES 192 bits key YES

Once a SecureFiles LOB column is encrypted, you can disable encryption using the decrypt keyword.The decrypt option will convert the encrypted column to clear text,as shown here:

SQL> alter table secure_docs modify (blob_content decrypt)
SQL> /
Table altered.

Compression

Customers with terabytes (or even petabytes) of storage allocated can greatly benefit from the compression option of Oracle Database 11lob (blob_content) store as secure file (compress high)

There are two valid values for the compression option: medium and high.The default compression is set to medium.We’ll demonstrate the medium and high compression options in this section to prove the enormous potential in space savings.SecureFiles with compression is the logical choice if your database has a large number of LOBs inside the database and is not updated frequently.To be able to demonstrate the compression capabilities,the keep_duplicate option is enabled so that Oracle will load the 100 LOB documents from the DEFAULT_DOCS table.Next,we will insert the same 100 documents into a table called SECURE_DUP_MEDIUM that has mediumlevel compression enabled:

SQL> insert into secure_docs_medium select * from default_docs;

The same space.sql script, using the DBMS_SPACE.USED_SPACE procedure, can be used to determine how much space medium-level compression saves.With medium compression,the LOB segment consumes about 6.4MB of disk space,as shown here:

SQL> @space secure_docs_medium
old 12: and table_name=upper('&v_tab');
new 12: and table_name=upper('secure_docs_medium');
KB used: 6408
PL/SQL procedure successfully completed.

Another table with high compression is also created to show the compression capabilities of Oracle Database 11g Secure Files.In this particular example,the LOB segment consumes about 5.6MB of disk space, as shown here:

SQL> @space secure_docs_high
old 12: and table_name=upper('&v_tab');
new 12: and table_name=upper('secure_docs_high');
KB used: 5608
PL/SQL procedure successfully completed.

You can change the compression algorithm by specifying the compress high syntax to thealter table command, as shown here:

SQL> alter table secure_dup_compressed_docs
modify lob (blob_content) (compress high);
Table altered.

In this particular example,the difference between high and mediumcompression is not that noticeable since we are storing the same document 100 times.Obviously, the higher the degree of compression,the greater the CPU utilization will be to decompress the data.That higher CPU utilization will be reflected in the latency to retrieve and store the data.The gain of space saved in storage will be directly correlated with the penalty of retrieval and insertion times. For LOBs that are frequently updated,Secure Files compression can save double, triple,quadruple or more the storage capacity for large corporations that have massive documents stored inside the database. Another factor that will determine the amount of compression will be the type of file. If the file is already compressed such as with JPEG or ZIP files,Oracle ’s SecureFile LOB compression will have little or no effect.

If you need to uncompress the LOB segment for one reason or another, you can use the nocompress keyword to achieve this.Here’s an example of such a command:

SQL> alter table secure_docs_high modify lob (blob_content) (nocompress)
SQL> /
Table altered.

Note Please exercise caution with the compress and nocompress options because compression decompression occurs immediately and can be extremely time consuming for large LOB columns.

After the decompression of the LOB that was compressed with the high option, the size ofthe LOB is back up to 19MB, as shown here:

SQL> @space secure_docs_high
old 12: and table_name=upper('&v_tab');
new 12: and table_name=upper('secure_docs_high');
KB used: 19208
PL/SQL procedure successfully completed.

LOB compression can be enabled at the partition level.One partition can have LOB compression enabled while another partition does not.Likewise, a partition can have high compression for a partition and medium-level compression for another.See the following example that demonstrates the different compression options for partitions:

SQL> create table docs_compressed_mix2
2 (document_id number,
3 blob_content blob,
4 document_category varchar2(55))
5 partition by list (document_category)
6 (
7 partition p_dba1 values ('Data Guard') lob(blob_content)
8 store as securefile(compress high),
9 partition p_dba2 values ('ASM') lob(blob_content)
10 store as securefile(compress medium),
11 partition p_dba3 values ('Java') lob(blob_content)
12 store as securefile(nocompress),
13 partition p_dba4 values (default) lob(blob_content)
14 store as securefile(compress high)
15* )
SQL> /
Table created.

db_securefile Initialization Parameter

Oracle Database 11g implements a new parameter called db_secure file to provide you, the DBA,with control over the creation of LOB columns in a database.This parameter is dynamic and can be modified with the alter system command.There are five possible values for this parameter:

• always: This creates all LOBs as SecureFiles, but if the target tablespace is not ASSM, LOBs are created as BasicFiles.BasicFiles storage parameters are ignored.SecureFiles default parameters apply when storage options are not specified.
• force: Going forward, this forces all LOB objects to be created as SecureFiles.If the target tablespace is not ASSM, an exception will be reported to the user.BasicFiles storage parameters are ignored, and default Secure Files storage parameters are used for parameters that are not specified.
• permitted: This is the default setting. SecureFiles LOBs are allowed to be created. By default, BasicFiles is created if the store as securefile clause is not specified.
• never: Going forward, this does not allow SecureFiles to be created. LOBs that are characterized as Secure Files LOBs are created as BasicFiles LOBs.All storage parameters that are specific to SecureFiles will generate an exception error.If storage parameters do not exist,this defaults to the basic LOB defaults.
• ignore: This disallows SecureFiles storage parameters and forces BasicFiles.SecureFiles storage options are ignored.

Here’s an example of modifying the db_securefile initialization parameter using the alter system command:

SQL> alter system set db_securefile='ALWAYS'; System altered.

Migrate from BasicFiles to Secure Files

There are multiple approaches to migrate Basic Files LOB segments toSecureFiles. Unfortunately,to take advantage of SecureFiles,the data must be completely rewritten into the new LOB.The most common approach is probably create table as (CTAS) or inserting all the rows from one table to another. Another option is to create a dummy column and update the data.Once you are done, drop the old BasicFiles LOB column and rename the SecureFiles LOB column to the original column name.

The biggest advantage to this approach is that it requires the least amount of overhead in space consumption.You can use PL/SQL to perform batch inserts and commit every 10,000 rows or so to validate the progress along the way.

Another method would be to export and import the data.This still requires double the storage, one for the table and one for the dump file.Lots of companies would prefer to keep two copies of the data for a week or so before deleting the backup copies.By introducing export/import, you may be looking at triple storage to migrate to SecureFiles.

Online redefinition is another approach to migrate from Basic Files toSecureFiles.

The greatest benefit would be reduced or no downtime to perform the task. With online redefinition,the table can stay online during the entire duration of the migration,and the procedure can be performed in parallel.Again, double storage is required to perform an online redefinition.Here’s a simple example of performing an online definition for the DOCS table to the SECURE_DOCS table:

declare
error_count pls_integer
begin
dbms_redefinition.start_redef_table('sh','docs','secure_docs',
'document_id, name, edba_knowledgebase_category_id,
last_updated, content_type, blob_content');
dbms_redefinition.finish_redef_table('sh','docs','secure_docs');
end;
/

Note As of Oracle Database 11g Release 1, Oracle Streams does not support SecureFiles, so this technologycannot be leveraged.

If the table being redefined happens to be a partitioned table,another great time-saving technique will be to use the partition exchange option. During this process,Oracle recommends using the nologging option of the SecureFiles LOB storage option.You can reenable logging once the migration is complete.Using the documents table that is provided in the previous example, you can perform a partition-by-partition exchange and migrate from BasicFiles to SecureFiles LOB.First create a new table called DOCS2 with just one of the subsets of the range partition key. In the following example,the table DOCS2 is created as a SecureFiles using the CTAS approach:

SQL> create table docs2
2 lob(blob_content) store as securefile (nocache nologging)
3 tablespace tools
4 as
5 select * from documents_parted
6* where edba_knowledgebase_category_id=2
SQL> /
Table created.

Now,once the partition data is converted into Secure Files,you can perform the exchange.You can use the following script to exchange the second partition (DOCUMENTS_P2) with the temporary table (DOCS2)created earlier.The outage is incurred only for the single partition in question.

SQL> alter table documents_parted exchange partition documents_p2
with table docs2 without validation;

Table altered.

LOB Prefetch

Oracle Database 11g improves OCI access of smaller LOB and BFILES. LOB data can be prefetched and cached while also fetching the locator.Now, you can customize the setting for the oci_attr_default_lobprefetch_size variable in your OCI programs.This value determines the prefetch data size for LOB locators.You can set this as an attribute of your OCI environment or set it within your OCI program.The default value for this attribute is zero,which means there is no prefetching of LOB data.