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.
{mospagebreak}
Case 1: DB_SECUREFILE=PERMITTED
This mode represents the ideal state of LOB creation. SecureFiles are created on ASSM tablespaces, while BasicFiles are created on both ASSM as well as non ASSM tablespaces.
----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.
The above table creation scripts reveal the ideal behavior 11g LOBs and differentiate them with LOB prior to 11g. SecureFiles can only reside in ASSM tablespace, while there is no such restriction on BasicFiles.
The dictionary view USER_LOBS lists the properties of an LOB column. SECUREFILE is a new column added in USER_LOBS dictionary view. Let us query the view to check the SECUREFILE feature of the above tables.
The query results show that Securefile has been enabled only for BLOB column contained in the table PERMITTED_SECUREFILE_1.
Case 2: DB_SECUREFILE = ALWAYS
This mode of DB_SECUREFILE ensures that all the LOBs created on an ASSM tablespace shall be treated as SecureFiles, irrespective of the LOB specification (SecureFile or BasicFile). Only the BasicFile LOBs on non ASSM tablespace shall be treated as BasicFile.
----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.
Now, check the SecureFile property for the above tables in USER_LOBS view.
Observe that only the BasicFile on non ASSM tablespace is under BasicFile category. Otherwise, all the LOB columns on ASSM tablespace are stored as SecureFiles.
{mospagebreak}
Case 3: DB_SECUREFILE = FORCE
The option FORCE for the DB_SECUREFILE parameter forces all LOB columns to be created as SecureFiles only, irrespective of file type specification. Now since SecureFiles can reside on ASSM tablespace only, non ASSM tablespace cannot be specified for LOB creation in any way. For non ASSM tablespace, system raises exception. Check this in the below illustration.
----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"
Now, check whether the SecureFile feature has been enabled for the above tables or not.
Observe that SecureFile has been enabled for both the tables, which are created on ASSM tablespace. For the rest, Oracle strictly prevents the creation of BasicFile columns.
Case 4: DB_SECUREFILE = NEVER
This mode works as reverse of ALWAYS and FORCE mode. It prevents the creation of SecureFile LOBs and all of them shall be treated as BasicFiles only. The SECUREFILE keyword is a passive or obsolete specification under this mode. Oracle doesn’t minds even specifying a non ASSM tablespace with SecureFile LOB column.
----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.
Checking the results in the USER_LOBS view, all the above tables have non SecureFile column.
{mospagebreak}
Case 5: DB_SECUREFILE = IGNORE
This mode is very similar to the lastly discussed NEVER mode. As per the action, it also prevents the creation of SecureFiles in the system. In addition to the ‘NEVER’ like action, it also ignores the LOB storage clause, if specified. The same would have been raised an exception in NEVER mode.
----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.
USER_LOBS shows the BasicFile behavior of all the above LOB columns.
Conclusion
Oracle 11g new features have already set the wavelength of PL/SQL application development in industry. With refreshing and emerging performance and storage philosophies, Oracle 11g has surely brought upgrades and exploration amongst database professionals.
I hope the tutorial has enough information to understand and demonstrate the functioning of Oracle SecureFiles. As I said, the access layer principle still remains the same; the readers must try their hands out on SecureFile access and read/write operations.
[catlist id=185].
