With the whirling phases of application development, its content has to be efficient to handle both relational and unstructured data in accessible form. Relational content looks fine and flexible to follow even crude strategies of storage but unstructured data like documents of varied sort, audios, videos, and many more are add up the challenges for designers and architects. Since the data has to be physically stored within the database, its security and ‘Pull out’ performance are the decisive factors in design and philosophy.

Oracle database treats the unstructured data as large objects, which would be stored either as CLOB (Character large objects) or BLOB (Binary large objects). While a CLOB column is capable of storing documents and character files, a BLOB column holds system binary files. The earlier storage layer worked well till the areas of its improvements were discovered. The areas in focus were data security, retrieval performance and the space consumed over the disk.

Oracle 11g saw an enhanced version of unstructured data storage, known as SecureFiles. While the earlier LOB storage concept was archived as BasicFiles, the latter was named as SecureFiles. The tutorial shall list the key considerations of SecureFiles over BasicFiles and justify with the help of illustrations.

Understanding SecureFiles

Database paradigms stood still for structured and relational data. Operations on unstructured data have always been an ‘out of box’ activity in Oracle. LOB operations, context indexing, segment management and many other features categorize the initiatives taken to improvise upon the unstructured data storage. SecureFile is the latest enhancement, which comes as topping over the existing storage phenomenon. It has unified the storage and security model for both relational and unstructured data, especially large media files.

Introduction of SecureFiles doesn’t imply the extinction of earlier LOBs. Applications where LOBs were in use, can continue to work with them as BasicFiles, might be at the cost of storage and performance. Optionally, the existing LOB formats can be moved/migrated/upgraded to new version by conventional export/import method or Redefinition utility package.

Few of the differentiating factors between BasicFiles and SecureFiles

• Effective Space allocation
• Storage Philosophy
• Read/ write Performance

Another important aspect of the introduction of SecureFiles is that only the Data layer has been modified. The interface layer still remains the same as earlier. In a more detailed way, data layer defines the storage principles and presents high level design of space management. Interface layer formulates the read/write actions on the data layer.

SecureFiles Usage Notes

• SecureFile is an architecture which directs the database to upgrade its LOB management strategy.
• SecureFiles can be created only on ASSM tablespace.
• Can be accessed from database as well as client interfaces (Using JDBS/ODBC)

SecureFile Enhancements

As the name suggests, SecureFile rolls up the security of the data to multiple folds, along with the intelligent space management during read/write operations.

We shall discuss these enhancements in detail as below

Effective Space management – The intelligent space manager effectively manages the space allocation for the large object data. During read and write operation, it maintains the disk block contention based on the call in queue method. During delete operation, it releases the freed up memory and keeps it ready for the next call. For the same reason, SecureFiles segments can only exist on ASSM tablespaces.

Dynamic disk allocation – Prior to Oracle 11g release, data allocation was based on a preset chunk size value. This value was updatable in a session but constant per operation, which at times, used to fragment larger data under the current operation. With Oracle 11g release, the CHUNK size parameter is only an advisory one; in the real time write operation, SecureFile uses dynamic chunk size to maximize contiguous disk allocations. This boosts up the performance during write operation dealing with large volume files and relatively with normal files.

Additionally, the ‘Pre-fetch’ behavior of SecureFile effectively enhances the performance during retrieval of large files.

New protocol to communicate with the network layer – SecureFile uses a new protocol which communicates to the network layer, to facilitate the flow of data directly from the network socket. This factor too boosts up the performance.

Another interesting feature of SecureFile is that it does not depend on LOB index to track a file during retrieval. In older LOBs, maintaining LOB index was identified as a performance overhead during LOB operations. SecureFiles have separate set of blocks which contain the logical-physical block mapping information. It uses these ‘self owned’ blocks for LOB operations and yield better performance.

Specification and Syntax

The LOB column in the table has to be directed to store the data as SecureFile using the SECUREFILE keyword. For older LOBs, which are from Oracle 10g and before, the keyword BASICFILE can be specified against the column. Note that these specifications have to be made during creation of the table.

System’s behavior depends upon the value set for db_securefile parameter. It is a newly introduced parameter, whose value can be any one of ALWAYS, FORCE, PERMITTED, NEVER and IGNORE. I shall briefly describe them as below.

• PERMITTED – It allows DBA to create SecureFiles in the system of appropriate compatibility i.e. 11.1 and higher.
• ALWAYS – Apart from normal SecureFiles, all BasicFiles on ASSM tablespaces are also treated as SecureFiles. But BasicFiles, which are created on a non ASSM tablespace, are still BasicFiles.
• FORCE– All LOB columns (both with SecureFile and BasicFile specification) are forced to be created as SecureFiles only. It does not allow any LOB column to be created on non ASSM tablespace.
• NEVER – Restricts the creation of SecureFiles.
• IGNORE – Ignore the creation of SecureFiles. All LOB columns are created as BasicFiles.

The parameter can be set using ALTER [SYSTEM | SESSION] command.

The LOB column storage clause syntax is as below

LOB(COLUMN_NAME) 
STORE AS [SECUREFILE | BASICFILE]
{STORAGE PARAMETERS [DUPLICATE | COMPRESS | ENCRYPTION]}

Additional Storage Parameters

Below are the additional parameters which value up SecureFiles ahead of BasicFiles. These are optional parameters where Oracle follows a defined protocol of application. The logical protocol states that in situation, where all the three factors are active for a SecureFile, oracle will first perform Deduplication, then Compression and Encryption at the end.

DEDUPLICATION – Restricts the storage of content based redundant SecureFiles. For ‘Deduplicated SecureFiles’, number of bytes for the LOB segment is less than the total LOB size. Oracle maintains a secure hash to map each data, thus demonstrating the intelligence and space management of LOB manager. It has to be specified as a storage parameter, whose value can be [DEDUPLICATE | KEEP_DUPLICATES].

COMPRESSION – Based on the background analysis, LOB manager compresses the LOB content. Surely, compression is an effective technique to save space on the disk, but it depends a lot on the stringent analysis done by SecureFile. If the compression doesn’t yields better results, SecureFile puts off compression for the file under action. Note that SecureFile compression is different from table compression.

Compression degree can be set as [HIGH | MEDIUM (default)]. User can explicitly set off the compression by specifying NOCOMPRESS in the storage clause.

{COMPRESS [HIGH | MEDIUM] | NOCOMPRESS}

ENCRYPTION – Encryption is one of the security features of Oracle 11g SecureFiles. It is a type of transparent data encryption, where the SecureFiles can be encrypted or decrypted on the disk, as per the below encryption algorithms.

3DES168 – Triple data encryption standard with 168-bit key size
AES128 – Advanced data encryption standard with 128 bit key size
AES192 – Advanced data encryption standard with 192 bit key size
AES256 – Advanced data encryption standard with 256 bit key size

Out of the above encryption algorithms, AES128 is the default one. If the SecureFile is owned by the SYS user, it cannot be encrypted. The action raises exception ‘ORA-28336: cannot encrypt SYS owned objects’. This restriction has been made for transparency purpose.

Examples and Illustrations

We shall see the SecureFile behavior with respect to different values of DB_SECUREFILE parameter. The SecureFile mode can be PERMITTED, ALWAYS, FORCE, IGNORE and NEVER. PERMITTED is the default value of the parameter.

Below is the default information of the ‘DB_SECUREFILE’ parameter.

SQL> SHOW parameter db_securefile
 
NAME                              TYPE        VALUE
--------------------------------- ----------- ------------------------------
db_securefile                     string      PERMITTED

Since ‘tablespace segment space management’ plays a vital role in the creation of a SecureFile, below screen dump shows the ASSM property of each tablespace.

----Set the DB_SECUREFILE parameter as PERMITTED----
SQL> ALTER SYSTEM SET DB_SECUREFILE=PERMITTED;
 
System altered.
 
Now, we shall CREATE TABLES WITH SecureFiles AND BasicFiles ON ASSM AND non ASSM tablespaces respectively AND observe the results.
 
----Create table with SecureFile column on ASSM tablespace----
SQL> CREATE TABLE PERMITTED_SECUREFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE USERS;
 
TABLE created.
 
----Create table with SecureFile column on non ASSM tablespace----
SQL> CREATE TABLE PERMITTED_SECUREFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE SYSTEM;
CREATE TABLE PERMITTED_SECUREFILE_2
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used IN non-ASSM tablespace "SYSTEM"
 
----Create table with BasicFile column on ASSM tablespace----
SQL> CREATE TABLE PERMITTED_BASICFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE USERS;
 
TABLE created.
 
----Create table with BasicFile column on non ASSM tablespace----
SQL> CREATE TABLE PERMITTED_BASICFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE SYSTEM;
 
TABLE created.

----Set the DB_SECUREFILE parameter as ALWAYS----
SQL> ALTER SYSTEM SET DB_SECUREFILE=ALWAYS;
 
System altered.
 
----Create table with SecureFile column on ASSM tablespace----
SQL> CREATE TABLE ALWAYS_SECUREFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE USERS
  5  /
 
TABLE created.
 
Still, a SecureFile cannot be created ON non ASSM tablespace.
 
----Create table with SecureFile column on non ASSM tablespace----
SQL> CREATE TABLE ALWAYS_SECUREFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE SYSTEM
  5  /
CREATE TABLE ALWAYS_SECUREFILE_2
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used IN non-ASSM tablespace "SYSTEM"
 
----Create table with BasicFile column on ASSM tablespace----
SQL> CREATE TABLE ALWAYS_BASICFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE USERS
  5  /
 
TABLE created.
 
----Create table with BasicFile column on non ASSM tablespace----
SQL> CREATE TABLE ALWAYS_BASICFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE SYSTEM
  5  /
 
TABLE created.

----Set the DB_SECUREFILE parameter as ALWAYS----
SQL> ALTER SYSTEM SET DB_SECUREFILE=FORCE;
 
System altered.
 
----Create table with SecureFile column on ASSM tablespace----
SQL> CREATE TABLE FORCE_SECUREFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE USERS
  5  /
 
TABLE created.
 
----Create table with SecureFile column on non ASSM tablespace----
SQL> CREATE TABLE FORCE_SECUREFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE SYSTEM
  5  /
CREATE TABLE FORCE_SECUREFILE_2
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used IN non-ASSM tablespace "SYSTEM"
 
----Create table with BasicFile column on ASSM tablespace----
SQL> CREATE TABLE FORCE_BASICFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE USERS
  5  /
 
TABLE created.
 
----Create table with BasicFile column on non ASSM tablespace----
SQL> CREATE TABLE FORCE_BASICFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE SYSTEM
  5  /
CREATE TABLE FORCE_BASICFILE_2
*
ERROR at line 1:
ORA-43853: SECUREFILE lobs cannot be used IN non-ASSM tablespace "SYSTEM"

----Set the DB_SECUREFILE parameter as ALWAYS----
SQL> ALTER SYSTEM SET DB_SECUREFILE=NEVER;
 
System altered.
 
----Create table with SecureFile column on ASSM tablespace----
SQL> CREATE TABLE NEVER_SECUREFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE USERS
  5  /
 
TABLE created.
 
----Create table with SecureFile column on non ASSM tablespace----
SQL> CREATE TABLE NEVER_SECUREFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE SYSTEM
  5  /
 
TABLE created.
 
----Create table with BasicFile column on ASSM tablespace----
SQL> CREATE TABLE NEVER_BASICFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE USERS
  5  /
 
TABLE created.
 
----Create table with BasicFile column on non ASSM tablespace----
SQL> CREATE TABLE NEVER_BASICFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE SYSTEM
  5  /
 
TABLE created.

----Set the DB_SECUREFILE parameter as ALWAYS----
SQL> ALTER SYSTEM SET DB_SECUREFILE=IGNORE;
 
System altered.
 
----Create table with SecureFile column on ASSM tablespace----
SQL> CREATE TABLE IGNORE_SECUREFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE USERS
  5  /
 
TABLE created.
 
----Create table with SecureFile column on non ASSM tablespace----
SQL> CREATE TABLE IGNORE_SECUREFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS SECUREFILE
  4  TABLESPACE SYSTEM
  5  /
 
TABLE created.
 
----Create table with BasicFile column on ASSM tablespace----
SQL> CREATE TABLE IGNORE_BASICFILE_1
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE USERS
  5  /
 
TABLE created.
 
----Create table with BasicFile column on non ASSM tablespace----
SQL> CREATE TABLE IGNORE_BASICFILE_2
  2  (DOC BLOB)
  3  LOB(DOC) STORE AS BASICFILE
  4  TABLESPACE SYSTEM
  5  /
 
TABLE created.