How to Migrate from BasicFiles to SecureFiles Storage
February 26, 2009
Synopsis. Oracle Database 11gs 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 Oracles excellent partitioning features to organize objects for extremely efficient retrieval.
To illustrate this, Ive recreated both of my example tables using the code shown in Listing 2.1. In this new incarnation, Ive used list partitioning to partition both tables on the status of the trouble ticket entry. Then in Listing 2.2, Ive reloaded table TRBTKT.TICKETS with significantly more data.
Migrating Efficiently from BasicFiles to SecureFiles
Now that Ive deployed partitioning features to better organize these tables for efficient use in a data warehousing environment, Ive 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 tables 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 thats 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 theres 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 databases data dictionary. This means that any referencing PL/SQL objects dont need to be revised to point to the new objects 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_SPACEs SPACE_USAGE procedure, has been overloaded to provide space utilization information about both BasicFile and SecureFile LOBs. Ive 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 doesnt consider any BasicFile LOB chunks as utilized space. In Listing 2.5, Ive 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. Its 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 LOBs 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.) Ive 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, its 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.
In the final article in this series, Ill look at the advanced capabilities of Oracle Database 11gs new SecureFile features, including:
References and Additional Reading
While Im hopeful that Ive given you a thorough grounding in the technical aspects of the features Ive discussed in this article, Im also sure that there may be better documentation available since its 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 Ive 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 Developers Guide
B28419-02 Oracle Database 11g PL/SQL Packages and Types Reference
B28424-03 Oracle Database 11g Advanced Application Developers 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