How to Migrate from BasicFiles to SecureFiles Storage

Synopsis. Oracle Database 11g’s new SecureFiles storage capabilities extend the flexibility and capacity of Large Objects (LOBs). This article – the next in this three-part series – explores how to migrate most efficiently from BasicFiles to SecureFiles storage, how to measure the relative storage efficiency of SecureFile vs. BasicFile LOBs, and how to utilize the different compression and deduplication options for SecureFile LOBs.

At the end of the prior article in this series, I demonstrated the most straightforward method for transforming BasicFile LOBs into SecureFile LOBs. I created a new table (TRBTKT.SECURE_TICKETS) that redefines the structure of the original table (TRBTKT.TICKETS) that maps the original BasicFile LOBs to SecureFile LOB counterparts, and then issued a simple INSERT INTO … SELECT FROM DML statement to transfer the contents of the original table into the new table.

In a relatively simple application environment, this loading method is sufficient. However, today LOBs are being used extensively within data warehousing and business intelligence applications, especially for storing complex business transactions within XML documents and for capturing medical images for hospital information systems. These systems often take advantage of Oracle’s excellent partitioning features to organize objects for extremely efficient retrieval.

To illustrate this, I’ve recreated both of my example tables using the code shown in Listing 2.1. In this new incarnation, I’ve used list partitioning to partition both tables on the status of the trouble ticket entry. Then in Listing 2.2, I’ve reloaded table TRBTKT.TICKETS with significantly more data.

Migrating Efficiently from BasicFiles to SecureFiles

Now that I’ve deployed partitioning features to better organize these tables for efficient use in a data warehousing environment, I’ve also got to consider how to transform the LOBs from BasicFiles format quickly and efficiently into their corresponding SecureFiles counterparts. Fortunately, Oracle 11g offers two such methods: partition exchange and online redefinition.

Partition Exchange. The Partition Exchange Load (PEL) method – sometimes called partition roll-on – involves constructing a non-partitioned table whose layout and contents match exactly to the desired partition of the target partitioned table. Once the source table has been populated, then that table is simply exchanged with the target partition in the already-partitioned table. This approach does offer some advantages; namely, the source table’s local indexes are maintained during the exchange, it can be performed in parallel, and this process can be repeated several times to populate individual partitions over shorter maintenance periods. The primary disadvantage is that the partition that’s receiving the data must be offline while the exchange occurs.

Online Redefinition. Oracle recommends using the DBMS_REDEFINITION package to perform the online redefinition of source and target tables because it accomplishes the same goal as the partition exchange method, yet there’s no requirement to take offline the target of the redefinition process. Online redefinition can be performed at the table or partition level, and thus it can also be done in parallel for multiple partitions. My favorite feature of online redefinition is that after the process has been completed successfully, the source and target objects will actually switch definitions within the database’s data dictionary. This means that any referencing PL/SQL objects don’t need to be revised to point to the new object’s name.

Listing 2.3 shows how to perform online redefinition using tables TRBTKT.TICKETS and TRBTKT.SECURE_TICKETS as the source and target objects, respectively. One final note on translating BasicFile LOBs to their counterpart SecureFile LOBs: once conversion has been completed, that LOB cannot be downgraded to a BasicFile LOB.

Managing SecureFiles Metadata

While these new SecureFile features certainly do extend LOB capabilities, it also means that DBAs need to pay attention to several additional attributes beyond those of the traditional BasicFile LOBs. Fortunately, Oracle 11g provides several methods to keep track of BasicFile and SecureFile LOB metadata.

Data Dictionary Views. Oracle 11g also upgraded several data dictionary views to provide additional information about SecureFiles, and a list of these modified views is provided below:

Listing 2.4 shows several SQL*Plus queries that utilize these data dictionary views for reporting on specific SecureFiles metadata, and Report 2.1 shows the resulting output from these queries against the two example tables in the TRBTKT schema.

DBMS_SPACE. Another venerable Oracle-supplied package, DBMS_SPACE’s SPACE_USAGE procedure, has been overloaded to provide space utilization information about both BasicFile and SecureFile LOBs. I’ve included both methods in two procedures, CALC_SPACE_BASICFILES and CALC_SPACE_SECUREFILES, in package TRBTKT.PKG_SECUREFILES. However, this new incarnation of SPACE_USAGE will only work against a tablespace that uses Automatic Segment Space Management (ASSM); further, it doesn’t consider any BasicFile LOB chunks as utilized space. In Listing 2.5, I’ve provided an example of invoking these procedures against tables TRBTKT.TICKETS and TRBTKT.SECURE_TICKETS, respectively, and the corresponding output returned.

Changing SecureFiles Attributes

When I recreated table TRBTKT.SECURE_TICKETS in Listing 2.1, you may have noticed that I also employed different levels of compression and deduplication for each of the LOB columns within their different partitions. It’s important to note that I could have used an appropriate version of the ALTER TABLE statement to accomplish these revisions as well. Even better, I can apply changes to SecureFile LOBs within each LOB’s individual partition if so desired.

The code in Listing 2.6 shows four examples of changing SecureFile LOB attributes in the TRBTKT.TICKETS table. (Recall that this table has now exchanged definitions with table TRBTKT.SECURE_TICKETS after the DBMS_REDEFINITION operations were implemented in Listing 2.3.) I’ve also created a report from data dictionary view DBA_PART_LOBS to show the before and after effects of modifying these LOB attributes; the resulting output is shown in Report 2.2.

Finally, it’s important to remember that any modifications to LOB attributes will only affect newly-created or modified LOBs. For example, changing the compression method to NOCOMPRESS instead of its current setting of COMPRESS for column TRBTKT.SECURE_TICKETS.SCRNIMG will not change the compression factor for any existing LOB entries.

Next Steps

In the final article in this series, I’ll look at the advanced capabilities of Oracle Database 11g’s new SecureFile features, including:

• Encrypting SecureFile LOBs via Transparent Data Encryption
• Encrypting an entire tablespace using Transparent Data Encryption
• Storing images in Digital Imaging for Medical Communication (DICOM) format within SecureFile LOBs
• Utilizing the new Shared I/O Pool memory component for faster LOB I/O processing

References and Additional Reading

While I’m hopeful that I’ve given you a thorough grounding in the technical aspects of the features I’ve discussed in this article, I’m also sure that there may be better documentation available since it’s been published. I therefore strongly suggest that you take a close look at the corresponding Oracle documentation on these features to obtain crystal-clear understanding before attempting to implement them in a production environment. Please note that I’ve drawn upon the following Oracle Database 11g documentation for the deeper technical details of this article:

B28279-02 Oracle Database 11g New Features Guide

B28320-01 Oracle Database 11g Reference Guide

B28393-03 Oracle Database 11g SecureFiles and Large Objects Developer’s Guide

B28419-02 Oracle Database 11g PL/SQL Packages and Types Reference

B28424-03 Oracle Database 11g Advanced Application Developer’s Guide

B32024-01 Oracle Database 11g VLDB and Partitioning Guide

Also, the following MetaLink documentation helps clarify these features:

432776.1 How to Determine the Actual Size of LOB Segments and How to Free the Deleted/Unused Space Above/Below the HWM

» See All Articles by Columnist Jim Czuprynski