Oracle 11g: SecureFiles

  Starting from Oracle 11g, there is a new feature which is called SecureFiles.  Although it is very useful feature, less people is aware about it or implemented it. I don't know why ;)


 SecureFiles is a completely new architecture inside the Oracle Database 11g for handling file or unstructured data – word, pdf, image, video and etc. Traditionally, we stored unstructured data in data type of LOB/BLOB or in OS with reference to database. Before Oracle 11g, there are many overheads if you store your unstructured data in OS/LOB data type, like no smart backup mechanism, no storage saving, no transaction/read consistency algorithm, no fine-grained security control, no auditing and etc. In general: most of the database features cannot be applied on it.


But now – with SecureFiles, the advanced features are available only for SecureFiles and do not apply to older LOBs or BasicFiles. The new features in SecureFiles - Deduplication, Compression and Encryption - can be setup independently or as a combination of one or more features. 


Using SecureFiles:
First of all, there is a new initialization parameter enables DBA to specify SecureFIles usage inside database. From doc: http://docs.oracle.com/cd/B28359_01/appdev.111/b28393/adlob_smart.htm#BABJFIBC
Parameter Name: db_securefile
Parameter Type: text
Allowable Values: { ALWAYS | FORCE | PERMITTED | NEVER | IGNORE }
Default Value: PERMITTED
Description: This parameter enables the database administrator to either allow SECUREFILE LOBs to be created (PERMITTED), disallow SECUREFILE LOBs from being created going forward (NEVER), force all LOBs created going forward to be SECUREFILE LOBs (FORCE), attempt to create SECUREFILE LOBs but fall back to BASICFILE LOBs (ALWAYS), or disallow SECUREFILE LOBs and ignore any errors that would otherwise be caused by forcing BASICFILE LOBs with SECUREFILE options (IGNORE).  Pls, read more details on this parameter from documentation.


Example:


Prior to Oracle 11g, we would have defined the table as follows:


CREATE TABLE basic_tab (
  id         NUMBER,
  myblob  BLOB
) 
LOB(myblob)(tablespace users);


In Oracle 11g, as for backward compatibility, you would have define it as below:


CREATE TABLE basic_tab (
  id         NUMBER,
  myblob  BLOB
) 
LOB(myblob) store as basicfile (tablespace users);




If you want to store the LOB as a SecureFile, all you have to do is place a clause "store as securefile" in the table creation, as shown below:


CREATE TABLE secure_tab (
  id         NUMBER,
  myblob  BLOB
) 
LOB(myblob) STORE AS SECUREFILE (tablespace users);






SQL> CREATE TABLE basic_tab (
  2  id number,
  3  myblob BLOB
  4  )
  5  LOB(myblob) store as BASICFILE (tablespace users)
  6  /


Table created.


SQL> CREATE TABLE secure_tab(
  2  id number,
  3  myblob BLOB
  4  )
  5  LOB(myblob) store as SECUREFILE (tablespace users)
  6  /


Table created.


SQL> 




Note: The tablespace where you are creating the securefile must be Automatic Segment Space Management (ASSM) enabled, otherwise you will meet with below error:


SQL> CREATE TABLESPACE mytbs1
  2  DATAFILE '/u01/app/oracle/oradata/ocmdb/mytbs01.dbf' SIZE 10M
  3  EXTENT MANAGEMENT LOCAL
  4  SEGMENT SPACE MANAGEMENT MANUAL;


Tablespace created.


SQL> CREATE TABLE secure_tab_ms(
  2  id number, 
  3  myblob BLOB
  4  )
  5  LOB(myblob) store as securefile (tablespace mytbs1);
CREATE TABLE secure_tab_ms(
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "MYTBS1"




Your applications do not need to be changed in order to load data to SecureFile LOBs, the same as you did for pre-11g LOB (BasicFile).




Deduplication:


In my opinion, deduplication is the most popular feature in SecureFiles. Imagine you have 10 records each with BLOB. 6 of the BLOBs are identical. Is it possible to store the BLOB only once and store only the reference to that copy on other 5 records, it would reduce the space consumption. This is possible in OS files but would not have been possible in Oracle Database 10g LOBs. But with SecureFiles it's actually trivial via a property called deduplication. You can specify it during the table creation or modify it later. Here, I will insert the same number of records to 2 tables: one with DEDUPLICATE option, another one with KEEP_DUPLICATES. Later, we will see how much space used in each case:


CREATE TABLE keep_duplicates_tab (
  id         NUMBER,
  myblob  CLOB
) 
LOB(myblob) STORE AS SECUREFILE (KEEP_DUPLICATES tablespace users);


CREATE TABLE deduplicate_tab (
  id         NUMBER,
  myblob   CLOB
) 
LOB(myblob) STORE AS SECUREFILE (DEDUPLICATE tablespace users);






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


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




SQL> select l.table_name, l.segment_name, s.bytes  from dba_lobs l, user_segments s where l.table_name IN ('DEDUPLICATE_TAB', 'KEEP_DUPLICATES_TAB') AND S.SEGMENT_NAME = L.SEGMENT_NAME;


TABLE_NAME                     SEGMENT_NAME                        BYTES
------------------------------ ------------------------------ ----------
KEEP_DUPLICATES_TAB            SYS_LOB0000071365C00002$$        71303168
DEDUPLICATE_TAB                SYS_LOB0000071368C00002$$          327680




You can also reverse the deduplication process:


SQL> alter table deduplicate_tab modify lob(myblob) (keep_duplicates);


Table altered.


SQL> select l.table_name, l.segment_name, s.bytes  from dba_lobs l, user_segments s where l.table_name IN ('DEDUPLICATE_TAB', 'KEEP_DUPLICATES_TAB') AND S.SEGMENT_NAME = L.SEGMENT_NAME;


TABLE_NAME                     SEGMENT_NAME                        BYTES
------------------------------ ------------------------------ ----------
KEEP_DUPLICATES_TAB            SYS_LOB0000071365C00002$$        71303168
DEDUPLICATE_TAB                SYS_LOB0000071368C00002$$         9764864






LOB Compression




Anothjer useful feature of SecureFile is COMPRESS. This 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:






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


CREATE TABLE compress_tab (
  id         NUMBER,
  myblob  CLOB
) 
LOB(myblob) STORE AS SECUREFILE (COMPRESS HIGH tablespace users);


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


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




SQL> select l.table_name, l.segment_name, s.bytes  from dba_lobs l, user_segments s where l.table_name IN ('COMPRESS_TAB', 'NOCOMPRESS_TAB') AND S.SEGMENT_NAME = L.SEGMENT_NAME;


TABLE_NAME                     SEGMENT_NAME                        BYTES
------------------------------ ------------------------------ ----------
NOCOMPRESS_TAB                  SYS_LOB0000071389C00002$$                    71303168
COMPRESS_TAB                   SYS_LOB0000071385C00002$$          131072




You can also reverse the compression process:




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


SQL>  select l.table_name, l.segment_name, s.bytes  from dba_lobs l, user_segments s where l.table_name IN ('COMPRESS_TAB', 'NOCOMPRESS_TAB') AND S.SEGMENT_NAME = L.SEGMENT_NAME;


TABLE_NAME                     SEGMENT_NAME                        BYTES
------------------------------ ------------------------------ ----------
NOCOMPRESS_TAB                  SYS_LOB0000071389C00002$$                    71303168
COMPRESS_TAB                   SYS_LOB0000071385C00002$$        76808192