Use and access SecureFile LOBS

SecureFiles is a Oracle database 11g New Feature to deliver scalability and high performance for unstructured or file data inside oracle. Its best of both world( file system and database). SecureFiles data can be updated/retrieved at same performance as traditional file system plus advantages of database(11g).

SecureFiles supported Features :

• Deduplication - detecting identical securefile data, and storing only one copy
• Compression - Saves storage, I/O, redo and encryption overhead.
• Encryption - Uses TDE syntax, and current encryption algorithms.
• Journaling - File system-like logging.

How to Configure/Use SecureFiles LOBS?

SecureFiles functionality is available in Oracle Database 11g by init.ora parameter db_securefile.

Default Value is "PERMITTED" - Allow SecureFile Creation.

Other Values are :

ALWAYS - Attempt to create SecureFile, if not possible, creates BasicFile LOB

FORCE  - Forces all LOBs created from that point forward to be SecureFiles

NEVER -  Disallows SecureFile LOB creation

IGNORE - Disallows SecureFile LOBs, and ignores errors if a BasicFile LOB is created with forced SecureFile optio

db_scurefile is a dynamic parameter and can be changed with alter system command.

for example, to force all LOBs creation in Securefiles.

SQL > alter system set db_securefile='FORCE' scope=both sid='*';

alter system set db_securefile='PERMITTED' SCOPE=BOTH;

create table SECURE_FILES_M_PERMITTED

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS01_MANUAL);

create table SECURE_FILES_M_PERMITTED

*

ERROR at line 1:

ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "TBS01_MANUAL"

create table SECURE_FILES_A_PERMITTED

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS02_AUTO);

Table created.

alter system set db_securefile='FORCE' SCOPE=BOTH;

create table SECURE_FILES_M_FORCE

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS01_MANUAL);

create table SECURE_FILES_M_FORCE

*

ERROR at line 1:

ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "TBS01_MANUAL"

create table SECURE_FILES_A_FORCE

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS02_AUTO);

Table created.

alter system set db_securefile='ALWAYS' SCOPE=BOTH;

create table SECURE_FILES_M_ALWAYS

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS01_MANUAL);

create table SECURE_FILES_M_ALWAYS

*

ERROR at line 1:

ORA-43853: SECUREFILE lobs cannot be used in non-ASSM tablespace "TBS01_MANUAL"

create table SECURE_FILES_A_ALWAYS

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS02_AUTO);

Table created.

alter system set db_securefile='NEVER' SCOPE=BOTH;

create table SECURE_FILES_M_NEVER

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS01_MANUAL);

Table created.

create table SECURE_FILES_A_NEVER

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS02_AUTO);

Table created.

alter system set db_securefile='IGNORE' SCOPE=BOTH;

create table SECURE_FILES_M_IGNORE

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS01_MANUAL);

Table created.

create table SECURE_FILES_A_IGNORE

(FILE_NAME VARCHAR2(30),

FILE_ID NUMBER,

FILE_DATA BLOB)

LOB(FILE_DATA) STORE AS SECUREFILE (TABLESPACE TBS02_AUTO);

Table created.

Analysis report based on above test. 

Above table can be used to understand the combination of parameters value/Storage management type and LOB type creation impact.

Conclusion :

So if you want to create SecureFile Storage management must be ASSM and value of db_scurefile parameter must be "ALWAYS|FORCE|PERMITTED".