Oracle 10g Transportable Tablespace Enhancements: Cross-Platform Conversion Capabilities

April 27, 2006

Synopsis. Oracle 10g Release 1 (10gR1) adds several new features in the arena of Transportable Tablespaces. This article – the first in this series – demonstrates how these new features enable the transfer of data between databases on different servers across platform boundaries regardless of those platforms’ operating systems.

One of the bigger challenges just about every Oracle DBA faces eventually is the need to transfer data from one database server to another. In my experience, several variables have to be taken into account whenever planning the best method to accomplish such a data transfer:

How much data needs to be transferred? Depending upon the amount of data to be copied, I will have to adjust my methodology:

• If my users need a representative sample of data, I may decide to use tools like DataPump Export to select every nth record from the source database’s tables.
• DataPump may also be an excellent choice if there are foreign key constraints on several of the tables, since I can use the QUERY directive during the DataPump Export operation to filter only selected rows from related tables.
• However, if the data in one or more tablespaces needs to be transferred, I also have the option to use the Oracle transportable tablespace feature to transfer the tablespace(s) themselves to the target server.

How much storage is available on the target server, and how is that storage arrayed? If I have the same amount of disk storage on each server, and it is arrayed exactly the same, then this makes my decision much simpler: I can simply use Recovery Manager (RMAN) to clone the production database, ship the backups to the target server, restore the database’s control files and datafiles from the backups, and then roll forward changes from copies of archived redo logs. However, storage is rarely identical, of course! When disk storage is arrayed differently or limited in size on the target server, I may have no choice but to export the source database, copy the resulting export files to the target, and import the data there. (Oracle 10g’s DataPump utility makes this even faster, of course. See my articles on the DataPump utility for more information.)

Do the source and target databases have the same character set? This is one of the most often-overlooked issues for data transfers. If I have ensured that standards are in place, hopefully the two databases’ character sets match, or at least the target database’s character set is a superset of the source database’s character set. Otherwise, if I attempt to import data into my target database, and the target database’s character set is not a superset of the source database’s character set, there is a good probability that character data will be corrupted, unconvertible, or simply lost.

How much time do I have to complete the transfer? If I need to transfer the complete contents of a 4TB database to my QA server prior to the evaluation of a new Oracle patch, upgrade, or release, or for an upcoming major application software release, then I need to find the fastest possible method to transfer the data from the source to the target database. Needless to say, even though DataPump Export has improved dramatically the speed at which I can dump data out of my source database, I am still constrained by the amount of time it takes to reload the data into the target database.

Transportable Tablespaces: Concepts

If time is of the essence, I may decide upon a much more attractive option: using the Oracle transportable tablespace features to migrate data from one server to another. The ability to transport a tablespace has been around since at least Oracle 8i, but I have found that not many DBAs are aware of its power and flexibility.

To transport a tablespace prior to Oracle 10gR1, the following steps are involved:

• First, I need to identify which tablespace(s) need to be transported. Note that it is critical to determine if any of the tablespace’s tables have referential integrity constraints that reference tables in other tablespaces; if they do, those other tablespaces will need to be included in the set of tablespaces to be transported.
• Once identified, the source tablespace(s) need to be brought into read-only mode by issuing the ALTER TABLESPACE <tablespace_name> READ ONLY; command. This freezes the datafile(s) for all of the source tablespace(s) that need to be transported.
• Create the metadata for source tablespace(s) via the EXPORT utility (exp.exe).
• Copy the resulting tablespaces’ metadata as well as all datafile(s) from the source database to the target database.
• Import the transportable tablespace’s metadata on the target database via the IMPORT utility (imp.exe).
• Once the transport operations are complete, I can bring the newly transported tablespace into read-write mode on the target database by issuing the ALTER TABLESPACE <tablespace_name> READ WRITE; command, and then issue the same command on the source database to bring it back into read-write mode.

Prior to Oracle 10gR1, there was at least one other concern that needed to be addressed before considering this approach:

What operating system do the source and target platforms use? If the source and target databases’ servers did not share the same operating system, I had little choice but to utilize the export/import method I have described previously. However, with Oracle 10gR1, it is now possible to prepare transportable tablespaces that can be transported across platform boundaries. Even more encouraging, Oracle 10gR1 has removed a key limitation to cross-platform transportability because it is now possible to transport tablespaces between source and target platforms regardless of those platforms’ endian-ness.

Endian-Ness: A Modest Proposal About Numbers

If you have never encountered the concept of endian-ness before, you are not alone. It is not something DBAs are forced to deal with very often unless we are dealing with cross-platform transfers. (The term endian was actually derived from Jonathan Swift’s Gulliver’s Travels, a barely-disguised political satire in which the political fortunes of the inhabitants of Lilliput and Blefuscu were determined by which end of their breakfast time boiled eggs they opened first: either the little end, or the big end.)

Some clever IT wag must have decided this would be a great way to describe the differences between how certain operating systems and platforms determine the maximum size of the integer values within their systems. However, there is a great amount of debate on how this term came about. For example, 32-bit Windows NT platforms and 32-bit Linux platforms both use a little endian system of numbering (i.e. the least significant byte is stored at the memory location with the lowest address), while Sun Solaris platforms use the big-endian system (i.e. the most significant byte is stored at the memory location with the lowest address).

Endian-ness thus becomes an important concern when converting data between platforms because all data must be examined during the conversion effort. Fortunately, Oracle 10gR1 has a method to scan the data and determine if they represent any endian issues during cross-platfiorm conversion. For example, if the source platform is a Sun Solaris E3500 and the target platform is using Red Hat Linux Enterprise 3.0 in 32-bit mode, then conversion is required because the source platform is a big-endian environment, and the target is a small-endian environment.

The good news is that it is simple to determine the endian-ness of Oracle database platforms by running a query against each database on these platforms (see Listing 1.1 for an example).

Transporting a Tablespace Between Different Platforms

Here is an example of how to perform a transportable tablespace operation using Oracle 10gR1 as long as the source and target platforms have the same endian-ness:

• Make the tablespace read only on the source database by issuing the ALTER TABLESPACE <tablespace_name> READ ONLY; command.
• Create the metadata for the tablespace via the Oracle Export utility or, for even better speed and flexibility, use the DataPump Export utility, or call the DBMS_DATAPUMP packaged procedures to build the metadata.
• Copy the tablespace’s datafile(s) from the source platform to the target platform. For this, I can simply use an OS command, FTP, or even the Oracle 10gR1 DBMS_FILE_TRANSFER procedures to either “push” or “pull” a copy of the datafile(s) from the source to the target platform and place the tablespace’s datafile(s) in the appropriate directory.
• On the target server, perform an Import or DataPump Import operation to add the tablespace’s metadata into the target database.
• Finally, bring the tablespace back online with the ALTER TABLESPACE <tablespace_name> READ WRITE; command.

To illustrate, I have created two locally-managed tablespaces and some sample tables, constraints, and indexes in each tablespace as shown in Listing 1.2. Table SH.AGGR_SALES contains sample sales history transactions and resides in one tablespace, and two reference tables, SH.CUST_TYPES and SH.SALES_AGGR_TYPES, reside in the other tablespace. I have also added foreign key constraints between these tables to illustrate how referential integrity enforcement issues affect transportable tablespace operations.

Handling Endian Conversion During Tablespace Transport Operations

Oracle 10gR1 can perform the conversion of the tablespace’s datafiles either before the creation of the transportable tablespace on the source platform, or after the tablespace has been transported to the target platform. In either case, the new RMAN CONVERT command is used to handle the conversion. Oracle 10gR1 also generates all the scripts necessary for conversion, and these scripts generally need only minor adjustments by the DBA.

Listing 1.3 illustrates how to perform a tablespace transport between a Linux 32-bit platform and a Microsoft Windows XP 32-bit platform. Since these platforms share the same endian-ness, I can transfer tablespaces across these two platforms in either direction.

However, if I were transporting tablespaces between two platforms of different endian-ness, I would have to either convert the tablespace on the source platform before transport, or convert the tablespace’s datafile on the target platform after transport. For example, Listing 1.4 shows the RMAN script to convert a transportable tablespace from one source platform of “little” endian-ness (e.g. a Microsoft Windows XP 32-bit server) to another target platform of “big” endian-ness (e.g. a Sun Solaris E350 server).

Transporting an Entire Database Between Different Platforms

The previous new features would be impressive enough, but Oracle 10gR1 does not stop there. Not only can I now transport tablespaces across platforms regardless of endian-ness, but I can also transport an entire database from one platform to another. Oracle 10gR1 will generate all the scripts necessary to create the components on the target platform to start up the database after its transport is complete. The generated scripts usually need only small adjustments to reflect the available memory resource and storage destinations on the target platform.

(Be sure to note the one significant limitation to this feature, however: Unlike the cross-platform tablespace transport features described in the previous section, Oracle10g does not permit the transport of an entire database between two platforms when those platforms have different endian-ness.)

To illustrate this scenario, I will transport a complete database from one source platform (a Microsoft Windows XP 32-bit server) to another target platform (a Red Hat Enterprise Linux 32-bit server). Since these platforms do not share the same operating system, Oracle will automatically generate the proper RMAN CONVERT commands to translate the database’s components so they can be utilized on the target platform. As with the prior cross-platform transportable tablespaces example, I can also choose to perform the conversion on either the source platform or on the target platform instead if I wish to limit the time required to complete the conversion.

Here is a summary of the steps required to transport the entire database from source to target platforms. First, these steps need to happen on the source platform:

• Shut down the database and then reopen it in READ ONLY mode. This insures that all of the database’s control files and datafiles are “frozen” temporarily so that the database can be transported. Of course, this does mean that the source database will be only available for limited queries until the transport preparations are completed, and DML against the database will be completely forbidden.
• Verify that the database is indeed currently transportable. I will do this with the DBMS_TTS.CHECK_DB and DBMS_TTS.CHECK_EXTERNAL package procedures. Listing 1.5 shows how I used these procedures to determine if the database is ready for transport, and also notifies me if there are other external objects like external tables, BFILEs, and DIRECTORY objects that I will need to move separately to the target database.
• Run the appropriate RMAN CONVERT command script to prepare the database for cloning on the target platform:
  • If the database’s datafiles are to be converted on the source platform, Oracle will automatically create the converted datafiles in a specified directory; otherwise, RMAN will generate the appropriate conversion scripts to be run on the target server to perform the datafile conversion there.
  • RMAN will also create a script to create the new database on the target platform. This script will prepare the cloned database’s control file to reflect the new locations of the online redo log files and datafiles on the target server.

In Listing 1.6, I have illustrated the results of preparing the database for transport and eventual conversion on the target platform, while in Listing 1.7 I have shown how to similarly convert the database’s datafiles on the source platform before transporting the datafiles to the target platform.

Once these steps are completed on the source server, I can then transfer the database’s components and conversion scripts to the target platform and complete the transference:

• Copy the database’s datafiles from the specified conversion folder on the source platform to the target platform. I can use an OS command, FTP, or even Oracle 10g’s DBMS_FILE_TRANSFER procedures to either “push” or “pull” a copy of the datafiles from the source to the target platform and place them in the appropriate directory.
• Run the generated CREATE CONTROLFILE script on the target server to create the new database’s control file and mount the database, and then run the conversion scripts (if any exist) on the target server to complete the conversion of the datafiles (see Listing 1.8 for examples of the conversion process).

Once the datafile conversion has been completed, all that is left to do is open the transported database with the ALTER DATABASE OPEN RESETLOGS; command, add any TEMPFILEs, and perform some minor cleanup (recompilation of all PL/SQL code). See Listing 1.9 for the conclusion of this example.

Oracle 10g Recovery Manager: A Final Sales Pitch

One final note about RMAN: In my discussions with my DBA colleagues over the past few years, I have noticed that some of us are still holding back from using RMAN and instead insist upon using user-managed backups. In my colleagues’ defense, I have also found that many DBAs had tried out RMAN when it was first available in Oracle 8 and found it wanting in those early days. Since then, however, RMAN’s stability and reliability has improved drastically. If you are still holding back even after hearing about these transportable tablespace features in addition to the panoply of other new RMAN features present in Oracle 10g (e.g. FLASHBACK DATABASE), please consider this: If you’re going to implement Automatic Storage Management (ASM) to manage your Oracle 10g’s storage, RMAN is the only available method to back up and restore data that resides on ASM disk groups in an ASM instance.

Next Steps

As this article has demonstrated, Oracle 10g’s advanced transportability features offer the capability to transfer individual tablespaces or even an entire database between platforms regardless of the platforms’ operating systems and with only minimal regard to the platforms’ endian-ness. In the next article in this series, I will demonstrate how Oracle 10gR2 offers the capability to transport tablespaces without incurring any appreciable down time by creating the transportable tablespace sets directly from RMAN backups, and then I will illustrate how to create data jukeboxes via Oracle’s tablespace versioning features.

References and Additional Reading

Even though I have hopefully provided enough technical information in this article to encourage you to explore with these features, I also strongly suggest that you first review the corresponding detailed Oracle documentation before proceeding with any experiments. Actual implementation of these features should commence only after a crystal-clear understanding exists. Please note that I have drawn upon the following Oracle 10gR2 documentation for the deeper technical details of this article:

B10750-01 Oracle Database 10gR1 New Features Guide

B10734-01 Oracle Database 10gR1 Backup and Recovery Advanced User’s Guide

B10739-01 Oracle Database 10gR1 Administrator’s Guide

B10749-02 Oracle Database 10gR1 Globalization Support Guide

B10770-02 Oracle Database 10gR1 Recovery Manager Reference

B10802-01 PL/SQL Packages and Types Reference

» See All Articles by Columnist Jim Czuprynski