8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

SecureFiles : Large Objects (LOBs) in Oracle 11g Database Release 1

The SecureFiles functionality is a complete redesign of the implementation of large object (LOB) storage in Oracle 11g. The original LOB storage, now known as BASICFILE, is still the default storage method, but the SECUREFILE keyword enables the new storage method, which allows encryption for security and space savings using compression and deduplication.

Initialization Parameters
Creating SecureFile LOBs
PL/SQL APIs
Migrating to SecureFiles
COMPRESS LOW - 11gR2
Database File System (DBFS) - 11gR2

Initialization Parameters

The SecureFile functionality is available once the COMPATIBLE initialization parameter is set to 11.0.0.0.0 or higher.

The DB_SECUREFILE initialization parameter controls the default action of the database with regards to LOB storage. The allowable values are:

ALWAYS - All LOBs in ASSM tablespaces are created as SecureFile LOBs. LOBs in non-ASSM tablespaces are created as BasicFile LOBs unless explicitly specified as SecureFiles. BasicFile storage options are ignored, and SecureFile default storage options are used for any unspecified options.
FORCE - All LOBs are created as SecureFile LOBs. If the LOB is being created in a non-ASSM tablespace, an error is thrown. BasicFile storage options are ignored, and SecureFile default storage options are used for any unspecified options.
PERMITTED - The default setting prior to 12c, which allows SecureFile LOB storage when the SECUREFILE keyword is used. The default storage method is BASICFILE.
PREFERRED - The default setting from 12c onward, which uses SecureFile LOB storage in all cases where LOB storage would otherwise default to BasicFile.
NEVER - Creation of SecureFile LOBs is not permitted.
IGNORE - Prevent creation of SecureFile LOBs, and ignore any errors associated with SecureFile storage options.

Depending on the database version the list may vary a little, and the FORCE option is not documented in the reference guide, but it is visible if you try to set an incorrect value.

SQL> ALTER SYSTEM SET db_securefile = 'BANANA';
ALTER SYSTEM SET db_securefile = 'BANANA'
*
ERROR at line 1:
ORA-00096: invalid value BANANA for parameter db_securefile, must be from among
FORCE, PREFERRED, ALWAYS, PERMITTED, IGNORE, NEVER

SQL>

The parameter is dynamic, so it can be set using the ALTER SYSTEM command.

SQL> ALTER SYSTEM SET db_securefile = 'FORCE';

System altered.

SQL>  ALTER SYSTEM SET db_securefile = 'PERMITTED';

System altered.

SQL>

The following examples assume the DB_SECUREFILE initialization parameter is set to the default value of PERMITTED.

Creating SecureFile LOBs

The Basics

In their simplest form, SecureFile LOBs are created by adding the SECUREFILE keyword to the LOB storage clause. The following code shows the creation of two tables. The first using the original storage method and the second using the SecureFile storage method.

CREATE TABLE bf_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS BASICFILE;

INSERT INTO bf_tab VALUES (1, 'My CLOB data');
COMMIT;

CREATE TABLE sf_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE;

INSERT INTO sf_tab VALUES (1, 'My CLOB data');
COMMIT;

LOB Deduplication

The DEDUPLICATE option of SecureFiles allows decuplication of entries within a LOB at the table or partition level. As you would expect, the processing associated with preventing duplication incurs an overhead. The KEEP_DUPLICATES options explicitly prevents deduplication. The example below compares the space usage of a regular SecureFile and a deduplicated SecureFile.

CREATE TABLE keep_duplicates_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE keepdup_lob(
  KEEP_DUPLICATES
);

CREATE TABLE deduplicate_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE dedup_lob (
  DEDUPLICATE
);

DECLARE
  l_clob CLOB := RPAD('X', 10000, 'X');
BEGIN
  FOR i IN 1 .. 1000 LOOP
    INSERT INTO keep_duplicates_tab VALUES (i, l_clob);
  END LOOP;
  COMMIT;

  FOR i IN 1 .. 1000 LOOP
    INSERT INTO deduplicate_tab VALUES (i, l_clob);
  END LOOP;
  COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'keep_duplicates_tab');
EXEC DBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');

COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM   user_segments
WHERE  segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');

SEGMENT_NAME                        BYTES
------------------------------ ----------
DEDUP_LOB                          262144
KEEPDUP_LOB                      19267584

2 rows selected.

SQL>

Notice how much smaller the deduplicated segment is. The space savings depend on the extent of the duplication within the LOB segment.

The duplication mode can be reset using the ALTER TABLE command.

ALTER TABLE deduplicate_tab MODIFY LOB(clob_data) (
  KEEP_DUPLICATES
);

EXEC DBMS_STATS.gather_table_stats(USER, 'deduplicate_tab');

COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM   user_segments
WHERE  segment_name IN ('KEEPDUP_LOB', 'DEDUP_LOB');

SEGMENT_NAME                        BYTES
------------------------------ ----------
DEDUP_LOB                        76808192
KEEPDUP_LOB                      68091904

2 rows selected.

SQL>

LOB Compression

The COMPRESS option of SecureFiles enables compression of LOB contents at table or partition level. The level of compression is indicated using the optional MEDIUM and HIGH keywords. If no compression level is specified, MEDIUM is used. There is an overhead associated with compressing the LOB contents, so using a high level of compression may be counterproductive to system performance. The compression in SecureFiles does not affect table compression, and vice versa. The example below compares the space usage of a regular and a compressed SecureFile LOB.

CREATE TABLE nocompress_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE nocompress_lob(
  NOCOMPRESS
);

CREATE TABLE compress_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE compress_lob (
  COMPRESS HIGH
);

DECLARE
  l_clob CLOB := RPAD('X', 10000, 'X');
BEGIN
  FOR i IN 1 .. 1000 LOOP
    INSERT INTO nocompress_tab VALUES (i, l_clob);
  END LOOP;
  COMMIT;

  FOR i IN 1 .. 1000 LOOP
    INSERT INTO compress_tab VALUES (i, l_clob);
  END LOOP;
  COMMIT;
END;
/

EXEC DBMS_STATS.gather_table_stats(USER, 'nocompress_tab');
EXEC DBMS_STATS.gather_table_stats(USER, 'compress_tab');

COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM   user_segments
WHERE  segment_name IN ('COMPRESS_LOB', 'NOCOMPRESS_LOB');

SEGMENT_NAME                        BYTES
------------------------------ ----------
COMPRESS_LOB                       131072
NOCOMPRESS_LOB                   71565312

2 rows selected.

SQL>

We can see the compressed LOB segment is significantly smaller than the uncompressed segment. The level of space saving depends on the type of data stored in the LOB segement.

The compression mode can be reset using the ALTER TABLE command.

ALTER TABLE compress_tab MODIFY LOB(clob_data) (
  NOCOMPRESS
);

EXEC DBMS_STATS.gather_table_stats(USER, 'compress_tab');

COLUMN segment_name FORMAT A30
SELECT segment_name, bytes
FROM   user_segments
WHERE  segment_name IN ('COMPRESS_LOB', 'NOCOMPRESS_LOB');

SEGMENT_NAME                        BYTES
------------------------------ ----------
COMPRESS_LOB                     76808192
NOCOMPRESS_LOB                   71630848

2 rows selected.

SQL>

LOB Encryption

Ecryption of SecureFile LOBs relies on a wallet, or Hardware Security Model (HSM), to hold the encryption key. The wallet setup is the same as that described for Transparent Data Encryption (TDE) and Tablespace Encryption, so complete that before trying the following examples.

The ENCRYPT option is available in SecureFiles to perform block-level encryption of LOB contents. An optional USING clause defines which encryption algorithm is used (3DES168, AES128, AES192, or AES256), the default being 'AES192'. The NO SALT option is not available for SecureFile encryption. The encryption is applied on a per-column basis, so it affects all partitions within a LOB. The DECRYPT option is used to explicitly prevent encryption. The example below shows creation of a table with an encrypted SecureFile LOB.

CREATE TABLE encrypt_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE encrypt_lob(
  ENCRYPT USING 'AES256'
);

The ALTER TABLE command is used to encrypt or decrypt an existing column. To switch between encryption algorithms you must use the REKEY option.

ALTER TABLE encrypt_tab MODIFY (
  clob_data  CLOB DECRYPT
);

ALTER TABLE encrypt_tab MODIFY (
  clob_data  CLOB ENCRYPT USING '3DES168'
);

ALTER TABLE encrypt_tab REKEY USING 'AES192';

Encryption is not supported by the original import and export utilities or by transportable tablespaces, so data must be transfered using the Data Pump import and export utilities.

LOB Cache and Logging

Both Basicfile and SecureFile LOBs share some basic caching and logging options. The common caching options are:

CACHE - LOB data is placed in the buffer cache.
CACHE READS - LOB data is only placed in the buffer cache during read operations, not write operations.
NOCACHE - LOB data is not placed in the buffer cache. This is the default option for BasicFile and SecureFile LOBs.

The basic logging options are:

LOGGING - LOB creation and changes generate full redo. This is the default setting.
NOLOGGING - The operations are not logged in the redo logs and are therefore not recoverable. This is useful during initial creation and during large loads that can be replayed in the event of failure.

In addition, SecureFile LOBs can have a logging option of FILESYSTEM_LIKE_LOGGING, where only the metadata is logged, which still allows recovery of the segment in the event of failure.

The CACHE option implies LOGGING, so you cannot use CACHE in combination with NOLOGGING or FILESYSTEM_LIKE_LOGGING.

The following code shows an example of explicitly setting the caching and logging options during and after table creation.

CREATE TABLE caching_and_logging_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE(
  NOCACHE
  FILESYSTEM_LIKE_LOGGING
);

ALTER TABLE caching_and_logging_tab MODIFY LOB(clob_data) (
  CACHE
);

PL/SQL APIs

The DBMS_LOB package is used to access both BasicFile and SecureFile LOBs. General LOB interaction is not affected by the move to SecureFiles. The SETOPTIONS procedure and GETOPTIONS function allow compression, encryption and deduplication options to be set and retrieved on a per-LOB basis.

CREATE TABLE securefile_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE securefile_lob(
 encrypt
 compress
);

INSERT INTO securefile_tab VALUES (1, 'ONE');
INSERT INTO securefile_tab VALUES (2, 'TWO');
COMMIT;

SET SERVEROUTPUT ON
DECLARE
  l_clob  CLOB;
BEGIN
  SELECT clob_data
  INTO   l_clob
  FROM   securefile_tab
  WHERE  id = 1
  FOR UPDATE;

  DBMS_OUTPUT.put_line('Compression  : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_compress));
  DBMS_OUTPUT.put_line('Encryption   : ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_encrypt));
  DBMS_OUTPUT.put_line('Deduplication: ' || DBMS_LOB.getoptions(l_clob, DBMS_LOB.opt_deduplicate));

  ROLLBACK;
END;
/

The DBMS_SPACE package includes an overload of the SPACE_USAGE procedure to return information about the amount of disk space in blocks used by all the LOBs in the LOB segment. This procedure can only be used against ASSM tablespaces.

SET SERVEROUTPUT ON
DECLARE
  l_segment_size_blocks  NUMBER;
  l_segment_size_bytes   NUMBER;
  l_used_blocks          NUMBER;
  l_used_bytes           NUMBER;
  l_expired_blocks       NUMBER;
  l_expired_bytes        NUMBER;
  l_unexpired_blocks     NUMBER;
  l_unexpired_bytes      NUMBER;
BEGIN
  DBMS_SPACE.SPACE_USAGE(
    segment_owner         => 'TEST',
    segment_name          => 'SECUREFILE_LOB',
    segment_type          => 'LOB',
    segment_size_blocks   => l_segment_size_blocks,
    segment_size_bytes    => l_segment_size_bytes,
    used_blocks           => l_used_blocks,
    used_bytes            => l_used_bytes,
    expired_blocks        => l_expired_blocks,
    expired_bytes         => l_expired_bytes,
    unexpired_blocks      => l_unexpired_blocks,
    unexpired_bytes       => l_unexpired_bytes);

  DBMS_OUTPUT.put_line('segment_size_blocks:' || l_segment_size_blocks);
  DBMS_OUTPUT.put_line('segment_size_bytes :' || l_segment_size_bytes);
  DBMS_OUTPUT.put_line('used_blocks        :' || l_used_blocks);
  DBMS_OUTPUT.put_line('used_bytes         :' || l_used_bytes);
  DBMS_OUTPUT.put_line('expired_blocks     :' || l_expired_blocks);
  DBMS_OUTPUT.put_line('expired_bytes      :' || l_expired_bytes);
  DBMS_OUTPUT.put_line('unexpired_blocks   :' || l_unexpired_blocks);
  DBMS_OUTPUT.put_line('unexpired_bytes    :' || l_unexpired_bytes);
END;
/

Migrating to SecureFiles

There is no automatic method for migrating a column from a BasicFile to a SecureFile LOB. Instead, you must manually convert the data using one of the following methods:

CREATE TABLE ... AS SELECT ...
INSERT INTO ... SELECT ...
Online table redefintion.
Export/Import
Create a new column, update the new column with the values in the original column, then drop the old column.
Create a new column, update the new column with the values in the original column, rename the table and create a view with the original name that only references the new column.

With the exception of the export/import method, all the options will require considerable amounts of disk space when converting LOBs containing large amounts of data.

Oracle Streams does not support SecureFiles in Oracle 11gR1, so avoid migrating LOBs that are used in conjunction with streams. Oracle Streams does support SecureFiles in Oracle 11gR2, provided the LOBs are not deduplicated.

COMPRESS LOW - 11gR2

Oracle Database 11g Release 2 (11.2) introduced support for a new type of compression (COMPRESS LOW) that provides reduced levels of compression for a lower CPU overhead.

CREATE TABLE compress_tab (
  id         NUMBER,
  clob_data  CLOB
) 
LOB(clob_data) STORE AS SECUREFILE compress_lob (
  COMPRESS LOW
);

It is especially interesting when compared to the old BasicFile architecture, since the documentation suggests it uses less CPU that using the uncompressed BasicFile mode, and due to the compression uses less storage and as a result produced better I/O throughput.

Database File System (DBFS) - 11gR2

Oracle Database 11gR2 introduced a feature called the Database File System (DBFS), which allows you to store files in the BLOBs in the database and present them to the outside world as a regular file system. This functionality is covered in separate articles here.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.