Oracle Database 11g breaks the barrier of large object (LOB) storage in the database.Though once perceived to be something handled only outside the database, Oracle Database 11g proves that files can easily be stored in the database with equal or better performance than in a traditional file system.
Although Oracle Database 10g provides file storage in the database using various utilities and applications,this technology was not well embraced by the Oracle community as a whole. Before Oracle Database 11g,LOBs were classified as either character LOBs (CLOBs) or binary LOBs(BLOBs).The only distinguishing factor between the two is that one is used to store large variable character data,and the other is used to store binary data.Oracle Database 11g completely reengineers LOB storage to what they call SecureFiles.The primary focus of the SecureFiles feature is to do the following:
SecureFiles vastly improves the management of large objects by introducing critical new functionality. First,LOB compression saves disk space for data at rest.Second,LOB encryption provides for data security at rest.Oracle offers the capability to deduplicate LOB segments and provides additional LOB APIs. For performance needs,LOB data path optimizations are included in Oracle Database 11g.We will be discussing all of these features in detail in the following sections.
To take advantage of Oracle SecureFiles, you must satisfy a couple of requirements. First,the tablespace that houses the secure file must be in automatic segment space management (assm).Luckily, Oracle adopted the assm tablespace as a default feature when it was introduced in Oracle Database 10g and continues to be in Oracle Database 11g. Second, the compatibility for the database must be set to 184.108.40.206.0 or higher.
Oracle distinguishes the legacy large object storage from its next-generation large objects,referring to the legacy large objects now as Basic Files.BasicFiles comprises all the CLOB and BLOB functionality as you know it up to Oracle Database 10g Release 2.Starting from Oracle Database 11g,the next-generation large objects are distinguished as Secure Files.Let’s drill down to the specific differences of SecureFiles.
What differentiates SecureFiles from BasicFiles is the storage parameter used when creating the table or adding the column. To leverage SecureFiles, you must specify the reserved store as securefile keyword of the LOB clause. Here’s an example showing how to create a table leveraging the Secure Files storage for the BLOB_CONTENT column in a table called SECURE_DOCS:create table secure_docs
By default,BasicFiles is created if the store as securefile clause is not specified.The following example creates a table named DOCS with a BasicFiles column named BLOB_CONTENT:create table docs
Note The term FastFiles is synonymous with SecureFiles.
One of the promising features of SecureFiles is that the feature is highly performatory and capable of higher throughput than the file system.There are numerous performance optimizations for Secure Files,including the following:
Oracle Database 11g SecureFiles adds to the LOB paradigm the ability to detect duplicate LOB data and eliminate duplicates by using a secure hash SHA1 algorithm.Elimination of duplicate data is achieved by using the deduplicate LOB syntax.Deduplication allows companies to save storage without compromising performance.
By contrast, you can use the keep_duplicates option to continue to allow duplicate values in the same LOB segment to be stored in a table.The
keep_duplicates option remains the default behavior for the deduplication feature in Oracle Database 11g.Continuing to utilize the documents table from the previous example,we’ll show how you can implement this feature.First we’ll create a table that allows for duplicate LOB segments.In the following example,we’ll create a table called DEFAULT_DOCS with the keep_duplicates keyword:
Using a simple shell script to load a 189KB Microsoft Word document into the database, you can easily prove the usefulness of the deduplicationfunctionality.The following scripts,the load.ksh shell script and a load.ctl SQL*Loader controlfile, are provided.The function of the load.ksh script is to simply increment the sequence number in the load.ctl file and invoke the SQL*Loader utility.Here’s the content of the load.ksh shell script:# load.ksh
The load.ctl file is the SQL*Loader controlfile to load a Microsoft Word document in the database.The size of the Word document is approximately 189KB.#load.ctl
The load.ksh script was invoked 100 times to load 100 copies of the same document into the DEFAULT_DOCS table.By using the DBMS_SPACE.USED_SPACE procedure,the total bytes allocatedfor the LOB segment can be retrieved. The following simple PL/SQL script was created to retrieve the LOB segment size:
This script takes an input parameter of the table name.Based on the table name,the PL/SQL logic queries the DBA_LOBS view and retrieves the LOB segment name associated with the table.This code example assumes that a table will have only one LOB segment.The LOB segment name is passed to the DBMS_SPACE. USED_SPACE procedure as one of the arguments.Using DBMS_OUTPUT, the total number of bytes used for the LOB segment is displayed.
We will save this script to a file named space.sql.We will use the space.sql script to determine the size of the SecureFiles LOB segments repeatedly throughout this chapter.In this particular example, the DEFAULT_DOCS LOB segment size is obtained using the space.sql script:SQL> @space default_docs
You can see from this output that the size of the LOB segment in the DEFAULT_DOCS table consumes about 19MB.
Next,let’s create a table that is identical to the DEFAULT_DOCS table except with the deduplicate reserved syntax.Before we create the table,let’s isolate this new table into a separate tablespace.Just to prove a point, we will create a tablespace called secure_docs_d with a single datafile 1MB in size:SQL>create tablespace secure_docs_d data file '+data' size 1m;
A table that is identical to DEFAULT_DOCS will be called SECURE_DOCS but will be created with the deduplication option, as shown here:create table secure_docs
Using SQL*Loader again, we are able to load the same 189KB document 100 times.The deduplication option is truly working since the 100 inserts of the document are resident in the 1MB tablespace.Let’s see the actual size of the Secure Files LOB segment using the same space.sql script created earlier:SQL> @space secure_docs
Amazingly, LOBs in the SECURE_DOCS table,using the deduplication option, consume only 200KB of space compared to the LOB column in the BASIC_DOCS table, which consumes about 19MB of storage.
By default, Oracle Database 11g allows duplicate LOB data to be loaded inside the database.When you query the DBA_LOBS view, you will see several distinct values for the DEDUPLICATION column, as shown here:SQL> select table_name, column_name, deduplication, securefile
SecureFiles LOB segments that do not allow for duplicate values will have a value of LOB in the DEDUPLICATION column.You can also see that the value of NONE is provided for BasicFiles LOB segments.
In addition to specifying deduplication when creating a LOB, you can also modify the deduplication attribute of a SecureFiles LOB column using the alter table command:SQL> alter table default_docs modify lob(blob_content) (deduplicate lob);
When you issue such a command, Oracle Database will immediately read all values in the LOB column and remove duplicates.
Oracle also provides the capability to enable or disablededuplication/ encryption/ compression of LOBs at the per-LOB level using PL/SQL and using the LOB Locator API.The DBMS_LOB.SETOPTIONS stored procedure provides this granularity of control.Here’s a description of the setoptions procedure:procedure setoptions
LOB encryption is another superb enhancement to Oracle Database 11g Secure Files.Now,you can encrypt LOB segments using Transparent Data Encryption (TDE).TDE requires an encryption key to be established and stored in Oracle’s TDE wallet.
Continuing with our example from earlier with the SECURE_DOCS table, you can modify this table to be encrypted using the alter table encrypt clause. The following example shows how you can enable encryption for SecureFiles LOBs:SQL> alter table secure_docs modify (blob_content encrypt using 'AES192');
Multiple levels of encryption are available in Oracle Database11g forSecure Files.The AES192-bit encryption algorithm happens to be the default encryption method. Other available encryption algorithms are as follows:
Encryption for SecureFiles must use SALT.The option for NOSALT is notsupported.SALT is a random value added to the data before it is encrypted. SALT in hashing algorithms strengthens the security of encrypted data bymaking it more difficult for hackers to crack using standard pattern-matching techniques.Encryption of the SecureFiles LOB segments occurs at the block level.You can encrypt LOBs on a per-column or on a per-partition basis.
You can query the USER_/DBA_ENCRYPTED_COLUMNS view to confirm that LOB columns are encrypted.You can also confirm that SALT is enabled by default with Secure Files.Here’s an example showing you that the default encryption is AES192-bit encryption and that it applies SALT:SQL> select table_name, column_name, encryption_alg, salt
Once a SecureFiles LOB column is encrypted, you can disable encryption using the decrypt keyword.The decrypt option will convert the encrypted column to clear text,as shown here:SQL> alter table secure_docs modify (blob_content decrypt)
Customers with terabytes (or even petabytes) of storage allocated can greatly benefit from the compression option of Oracle Database 11
There are two valid values for the compression option: medium and high.The default compression is set to medium.We’ll demonstrate the medium and high compression options in this section to prove the enormous potential in space savings.SecureFiles with compression is the logical choice if your database has a large number of LOBs inside the database and is not updated frequently.To be able to demonstrate the compression capabilities,the keep_duplicate option is enabled so that Oracle will load the 100 LOB documents from the DEFAULT_DOCS table.Next,we will insert the same 100 documents into a table called SECURE_DUP_MEDIUM that has mediumlevel compression enabled:SQL> insert into secure_docs_medium select * from default_docs;
The same space.sql script, using the DBMS_SPACE.USED_SPACE procedure, can be used to determine how much space medium-level compression saves.With medium compression,the LOB segment consumes about 6.4MB of disk space,as shown here:SQL> @space secure_docs_medium
Another table with high compression is also created to show the compression capabilities of Oracle Database 11g Secure Files.In this particular example,the LOB segment consumes about 5.6MB of disk space, as shown here:SQL> @space secure_docs_high
You can change the compression algorithm by specifying the compress high syntax to thealter table command, as shown here:SQL> alter table secure_dup_compressed_docs
In this particular example,the difference between high and mediumcompression is not that noticeable since we are storing the same document 100 times.Obviously, the higher the degree of compression,the greater the CPU utilization will be to decompress the data.That higher CPU utilization will be reflected in the latency to retrieve and store the data.The gain of space saved in storage will be directly correlated with the penalty of retrieval and insertion times. For LOBs that are frequently updated,Secure Files compression can save double, triple,quadruple or more the storage capacity for large corporations that have massive documents stored inside the database. Another factor that will determine the amount of compression will be the type of file. If the file is already compressed such as with JPEG or ZIP files,Oracle ’s SecureFile LOB compression will have little or no effect.
If you need to uncompress the LOB segment for one reason or another, you can use the nocompress keyword to achieve this.Here’s an example of such a command:SQL> alter table secure_docs_high modify lob (blob_content) (nocompress)
Note Please exercise caution with the compress and nocompress options because compression decompression occurs immediately and can be extremely time consuming for large LOB columns.
After the decompression of the LOB that was compressed with the high option, the size ofthe LOB is back up to 19MB, as shown here:SQL> @space secure_docs_high
LOB compression can be enabled at the partition level.One partition can have LOB compression enabled while another partition does not.Likewise, a partition can have high compression for a partition and medium-level compression for another.See the following example that demonstrates the different compression options for partitions:SQL> create table docs_compressed_mix2
db_securefile Initialization Parameter
Oracle Database 11g implements a new parameter called db_secure file to provide you, the DBA,with control over the creation of LOB columns in a database.This parameter is dynamic and can be modified with the alter system command.There are five possible values for this parameter:
Here’s an example of modifying the db_securefile initialization parameter using the alter system command:SQL> alter system set db_securefile='ALWAYS'; System altered.
Migrate from BasicFiles to Secure Files
There are multiple approaches to migrate Basic Files LOB segments toSecureFiles. Unfortunately,to take advantage of SecureFiles,the data must be completely rewritten into the new LOB.The most common approach is probably create table as (CTAS) or inserting all the rows from one table to another. Another option is to create a dummy column and update the data.Once you are done, drop the old BasicFiles LOB column and rename the SecureFiles LOB column to the original column name.
The biggest advantage to this approach is that it requires the least amount of overhead in space consumption.You can use PL/SQL to perform batch inserts and commit every 10,000 rows or so to validate the progress along the way.
Another method would be to export and import the data.This still requires double the storage, one for the table and one for the dump file.Lots of companies would prefer to keep two copies of the data for a week or so before deleting the backup copies.By introducing export/import, you may be looking at triple storage to migrate to SecureFiles.
Online redefinition is another approach to migrate from Basic Files toSecureFiles.
The greatest benefit would be reduced or no downtime to perform the task. With online redefinition,the table can stay online during the entire duration of the migration,and the procedure can be performed in parallel.Again, double storage is required to perform an online redefinition.Here’s a simple example of performing an online definition for the DOCS table to the SECURE_DOCS table:declare
Note As of Oracle Database 11g Release 1, Oracle Streams does not support SecureFiles, so this technologycannot be leveraged.
If the table being redefined happens to be a partitioned table,another great time-saving technique will be to use the partition exchange option. During this process,Oracle recommends using the nologging option of the SecureFiles LOB storage option.You can reenable logging once the migration is complete.Using the documents table that is provided in the previous example, you can perform a partition-by-partition exchange and migrate from BasicFiles to SecureFiles LOB.First create a new table called DOCS2 with just one of the subsets of the range partition key. In the following example,the table DOCS2 is created as a SecureFiles using the CTAS approach:SQL> create table docs2
Now,once the partition data is converted into Secure Files,you can perform the exchange.You can use the following script to exchange the second partition (DOCUMENTS_P2) with the temporary table (DOCS2)created earlier.The outage is incurred only for the single partition in question.SQL> alter table documents_parted exchange partition documents_p2
Oracle Database 11g improves OCI access of smaller LOB and BFILES. LOB data can be prefetched and cached while also fetching the locator.Now, you can customize the setting for the oci_attr_default_lobprefetch_size variable in your OCI programs.This value determines the prefetch data size for LOB locators.You can set this as an attribute of your OCI environment or set it within your OCI program.The default value for this attribute is zero,which means there is no prefetching of LOB data.
Oracle 11g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 11g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
Oracle 11g Tutorial
Installing, Upgrading, And Managing Change
Database Diagnosability And Failure Repair
Backup And Recovery
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.