Oracle Securefile and options (compression, deduplication, and encryption)

Oracle has introduced LOBs for managing large unstructured data insite the Oracle database.
The introduction of LOBS suffers from several drawbacks in Oracle version before Oracle 11g:

• The LOBs where created for mostly " write once, read many times" operations and couldn't handle frequent updates
• LOBs assumed low concurrent usage data
• Undo retianed by setting the retention and pctversion parameters, addidition management burden
• LOBs weren't expected to be very large in size
• LOBs size is uniform, max chunk size could only be 32KB
• LOBs were not planned with concurrent usage requirements in Oracle RAC in mind

The reality has changed en today LOBs are quite large, and comes in all sizes. Oracle 11g introduced a complete new way of handeling unstructured data to address the concerns resulting from the way LOBs are currently implemented.
The new introduced LOB data type is called : Oracle Securefiles. The old LOB data type wil be referenced by Oracle as BasicFiles. The New created LOB data type offers intelligent compression, transparent encryption capabilities, deduplication, and improved preformance.
Oracle securefile offers now:

• Variable chunksize and can be as large as 64MB
• Relieve user from version control task by determining full redo record or generate opnly for changed data
• Read and Write performance improved by new client/server nertwork layer
• New maintain internal memory and space usage statistics that enabled the database to maintain the SecurewFiles

How to enable Oracle Securefiles:

To use the functionality of Securefiles the initialization parameter must be set at least 11.0.0.0. and useages of a tablespaces with ASSM. Setting this parameter to 11.0.0.0. still offers you to use to use both lob types BasicFiles and Securefiles in the samme database. You can control the functionality of SecureFILES by setting the db_securefile initialization parameter.
The parameter can have the following values:

• ALWAYS    Create all LOBS as SecureFile LOBs. If tablespaces not enabled for ASSM then oracle will create the tradional BasicFile LOBs
• FORCE     Create all LOBs as SecureFile LOBS. Will generate an error when used a not enabled ASSM tablespace
• PERMITTED    Default setting and allows the creation of SecureFiles
• NEVER     The database won't allow the creation of new SecureFiles LOBs
• IGNORE    The database won't allow the creation of new SecureFiles LOBs ans generate errors by creating BasicFile with SecureFile options ( encryption, complression,deduplication)

Capabilities of SecureFile:

Implementing Oracle Securefile can take advantage of three new advanced compatibilities: compression,deduplication and encryption.

• COMPPRESSION    Compress SecureFile. Uncompress only those blocks that are necessary for read and write operations.
• DEDUPLICATION    Automatically detects duplicate data and saves only one copy of any duplicated data. This can be enabled at table or partition level.
• ENCRYPTION    Offers transparent encryption.

These advance new features -deduplication, compression ,and encryption - can be setup either independently or together. Be activating all the featues of Securefile at ones Oracle will first perform deduplication then compress and at last encryption of the data.

Storage Options for SecureFiles:

The BasicFile used storage clauses CHUNK, PCTVERSION,FREEPOOLS,FREELISTS and FREELISTGROUPS. When using the SecureFiles you don't have to specified any of these storage clauses. When still used by Securefiles the the database will parse but not interpret the clauses.

With Oracle Securefiles you now have the following new storage related clauses:

• MAXSIZE    Specifies the maximun LOB segment size.
• RETENTION    Specifies version control policy by which versions it must retain..
• MAX    Specified that the database start reclaining th old version once a segment reaches its maxsize value.
• MIN    Specifies that the database retain old versions for at least the minimum time spcified ( in seconds).
• AUTO  Automatically determine the retention time ( This is the default setting).
• none    Specifies that the database use old versions as much as possible.

Here's the example.With Oracle Securefiles and compression

First create a table with data:

create table securefile_tab
 (contract_id number(12)
 ,contract_name varchar2(80)
 ,file_size number
 ,clob_data clob
 ) tablespace users
   lob (clob_data) store as securefile sf_clob_data
   (compress high
     tablespace users
   );

select l.owner,l.segment_name, s.bytes/1024/1024 Mb,l.compression,l.securefile
  from dba_lobs l,
           dba_segments s
    where (l.owner||l.segment_name)=(s.owner||s.segment_name)
        and  l.owner ='JVDOORD';

OWNER                          SEGMENT_NAME                 MB   COMP SEC
------------------------------ -------------------- ---------- ------ ---
JVDOORD                        SF_CLOB_DATA              0.125   HIGH YES

DECLARE
  l_clob CLOB := RPAD('Y', 2000, 'Y');
BEGIN
  FOR i IN 1 .. 1000 LOOP
    if i=123
      then
      INSERT INTO securefile_tab(contract_id,contract_name,file_size,clob_data )
       VALUES (i, 'Contract'||i,null,RPAD('X', 100000, 'X'));
    ELSE
    INSERT INTO securefile_tab(contract_id,contract_name,file_size,clob_data )
       VALUES (i, 'Contract'||i,null,l_clob);
  end  if;
  END LOOP;
  COMMIT;
END;
/

Begin
   FOR a in 1..12
            LOOP
              update    securefile_tab
              SET  clob_data = clob_data||clob_data
              WHERE  CONTRACT_ID = 123;
              commit;
        END LOOP;
   update securefile_tab
         set file_size =dbms_lob.getlength(clob_data);
   commit;
END;
/

See here the results of adding rows into the compressed lob:

OWNER                          SEGMENT_NAME                 MB   COMP SEC
------------------------------ -------------------- ---------- ------ ---
JVDOORD                        SF_CLOB_DATA              0.375   HIGH YES

Now set at ROW level the lob compression off for the row with CONTRACT_ID=123

DECLARE
  l_clob  CLOB;
BEGIN
  SELECT clob_data
  INTO   l_clob
  FROM   securefile_tab
  WHERE  CONTRACT_ID = 123
  FOR UPDATE;
      DBMS_LOB.setoptions(l_clob,DBMS_LOB.opt_compress,0);
  COMMIT;
END;
/

OWNER                          SEGMENT_NAME                 MB   COMP SEC
------------------------------ -------------------- ---------- ------ ---
JVDOORD                        SF_CLOB_DATA             35.375   HIGH YES

How to detect the lob compression or other setting at row level. Oracle has introduce new functions in the DBMS_LOB package for setting the attributes on ROW level. The following attributes for the option are possible for Oracle Securefile:


Option

• opt_compress - can use value = 1
• opt_encrypt - can use value = 2
• opt_deduplicate - can use value = 4

To Enable or Disable the functionality of the lob options at row level:

• compress_off - use value = 0
• compress_on - use value = 1
• encrypt_off - use value = 0
• encrypt on - use value = 2
• deduplicate_off  - use value = 0
• deduplicate_on - use value = 4

Here is the PL/SQL block to validate the Oracle Securefile status at ROW level of the object:

set outputserver on
DECLARE
  l_clob  CLOB;
BEGIN
  SELECT clob_data
  INTO   l_clob
  FROM   securefile_tab
  WHERE  CONTRACT_ID = 123
  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;
/

or use the numbers of the attributes

DECLARE
  l_clob  CLOB;
BEGIN
  SELECT clob_data
  INTO   l_clob
  FROM   securefile_tab
  WHERE  CONTRACT_ID = 123
  FOR UPDATE;
     DBMS_OUTPUT.put_line('Compression  : ' || DBMS_LOB.getoptions(l_clob, 1));
     DBMS_OUTPUT.put_line('Encryption   : ' || DBMS_LOB.getoptions(l_clob, 2));
     DBMS_OUTPUT.put_line('Deduplication: ' || DBMS_LOB.getoptions(l_clob, 4));
  ROLLBACK;
END;
/

The above PL/SQL gives the following output:

Compression: 0
Encryption : 0
Deduplication : 0

Now the same PL/SQL block but now for CONTRACT_ID=100

Compression: 1
Encryption : 0
Deduplication : 0

Execute the following statement will solve the uncompressed data of possible lobs of the object:

alter table securefile_tab modify lob(clob_data) (compress high);

Summary options OracleSecurefile

In the meta data you see that the option is activated. This will not guarantee that this is the state for every row in the table for the Oracle SecureFiles. To be sure you have to execute the alter table statement or a validate statement on every row in the table.