Chapter 7. Using SecureFile LOBs

Today, the application development has taken a new turn to catch up with the growing business. The application environments employ some of the best strategies to accommodate the varied nature of data. Surveys have revealed an astonishing estimation that the nonstructured data grows annually by 65 percent in a typical enterprise data-based application. This pace is accredited to the growing content digitization, boost up rich user experience, web based structures, and physical file storage requirements.

In the previous chapter, we learned the traditional storage of large objects in Oracle. Since its release in Oracle 8i, they have worked well and served at par with the systems' requirements until Oracle 10g. The earlier LOB storage philosophy was based on certain assumptions which by now, were transformed into limitations. These assumptions were as follows:

• Size of the large object was expected to be in MBs

• Large objects would be less transactional

• No Encryption...

Introduction to SecureFiles

Oracle 11g Release 1 introduced SecureFiles to recoup the limitations of conventional LOBs in Oracle. The special engineered implementation of SecureFiles enables enhanced performance, data security, and better storage optimization. The induction of SecureFiles does not mean the extinction of older LOBs, instead older LOBs still live with the name BasicFiles. The feature SecureFile has arrived as a superset of LOBs in Oracle. Consequently, older LOBs, alias BasicFiles, can be smoothly migrated to SecureFiles. Hereby, we shall refer to older LOBs as BasicFiles only:

The SecureFile feature fuels up the database paradigms with advanced security and advanced storage options. A SecureFile can be independently enabled for transparent encryption, compression, and deduplication which contribute to its security and intelligence. The best part of SecureFiles is that, now no more different modeling strategies have to be adopted for structured data (relational) and nonstructured...

Working with SecureFiles

Before discussing the implementation of advanced features in SecureFiles, we will see the creation of a SecureFile. The LOB clause makes the difference in the CREATE TABLE statement and decides whether the LOB has to behave as a BasicFile or SecureFile.

The interpreting behavior of the Oracle server depends upon a newly introduced parameter called db_securefile. It can accept the values as follows:

• PERMITTED: This value allows DBA to create SecureFiles in the system of appropriate compatibility that is 11.1 and higher. It is the default value setting for the db_securefile parameter.

• 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 specifications) are forced to be created as SecureFiles only. It does not allow any LOB column to be created on a non-ASSM tablespace.

• NEVER...

Migration from BasicFiles to SecureFiles

The older LOBs are archived under the BasicFiles category. Note that they are not extinct or obsolete, but are less effective after the induction of the SecureFiles feature. A new system can adopt the SecureFiles as a new table or a new partition. What about the older data which follows costly implementation and has no security? It must inherit the new philosophy, so as to unify the system in terms of data security and management. For this reason, several migration techniques have been jotted-out to upgrade the BasicFile data to the SecureFile. We will briefly discuss these methods:

• Online Redefinition method: It is one of the highly recommended methods to move the BasicFile data. A table or a partition of a table can be redefined to achieve the migration from the BasicFile to the SecureFile. It is a secure and convenient method where the database remains up during the complete process.

• Partition method: Usually, this method is preferred in low prioritized...

Summary

This chapter explained a new orientation of LOB handling in Oracle. It is introduced in Oracle 11g R1 and known as SecureFile. The chapter explained the concept and its architectural enhancements. We learned the working of SecureFiles and its advanced features. We understood the enabling of the compression, deduplication, and encryption features. Finally, we covered the migration of BasicFile LOBs to SecureFile LOBs to reap the benefits of a sparking feature.

Since implementation of a filesystem in the database is not limited with the introduction of Oracle SecureFiles, further reading should be continued to realize the strengths and application of SecureFiles. Implications of SecureFiles can be observed in RAC environments, information lifecycle management, database filesystem (DBFS), and content management schemes. Further reading can be continued from the following links:

• http://www.oracle.com/technology/products/database/oracle11g/pdf/advanced-compression-whitepaper.pdf

• http...

Practice exercise

1. Which of the following are true statements about the SecureFiles?

   1. It requires ASSM-enabled tablespace.

   2. A BFILE type column in a table can be declared as SecureFiles.

   3. A SecureFile is not affected by the LOB index contention.

   4. SecureFiles use a new cache component of the buffer cache to hold the LOB data.

2. Identify the incorrect statement about the compression feature in SecureFiles.

   1. Compression might hit performance during the LOB write operation.

   2. Compression of SecureFiles is a part of the advanced compression feature in Oracle.

   3. Possible degrees of compression can be MEDIUM and HIGH.

   4. Oracle compresses all the LOB data at high priority, if the feature has been enabled for a SecureFile.

3. The compression feature can be enabled only for encrypted SecureFiles.

   1. True

   2. False

4. A compressed table having a SecureFile column will automatically enable compression for SecureFiles.

   1. True

   2. False

5. Identify the true statements about the deduplication feature of SecureFiles.

   1. KEEP_DUPLICATES is the default option...