by Joe Grant (@dba_jedi), Principal Architect

When migrating an Oracle database from one platform to another, there are several factors to take into consideration. The first of these is called endianness format. Endianness format refers to the byte order of the data on disk, and is dependent on the CPU and operating system. It is not possible to simply move Oracle database datafiles between systems with different endianness formats. This Wiki article describes the issue in more detail.

Oracle provides several different methods for endianness conversion, each with their own pros and cons to be considered. Each database to be migrated needs to be evaluated based on the size of the database, the need for availability during the migration, and the complexity of the tool being used for the migration. If you have several different databases to migrate, it is likely you will use multiple migration methods.

In general, the migration for each of these methods will start with the same step, the creation of a new database on the new platform. This is due to the fact that with each of these methods, the SYSTEM and SYSAUX tablespaces cannot be migrated across platforms. After this initial step, the details of migration for each method do differ. The available migration methods are:

• Export and Import

– Traditional
– Data Pump

• Cross Platform Transportable Tablespaces (XPTTS)

– Fast XPTTS (a type of XPTTS that utilizes incremental backups)

• GoldenGate replication, or similar tooling

Each method has its own pros and cons to be considered on a per database basis. However, one benefit across all migration methods is that they are all Oracle supported methods for upgrading a database. With this, tying a database upgrade in with the platform migration may be an additional consideration.

In general, it is recommended to use the simplest migration method possible that still meets the requirements.

Export and Import

The simplest methods of migration are a couple of old tried-and-true methods for moving data between databases, standard Export and Import and Data Pump Export and Import. When using this method, the data from the source database is exported to a flat file. The flat file is copied to the destination system, and then the data is imported into the destination database.

Some of the benefits of this method include:

• It is the simplest method of migration and utilizes tools that all DBAs should be familiar with.
• There are very few restrictions on the data and data types that can be migrated.
• There is very little setup needed prior to the migration.

On the bad side:

• For large databases the migration process can take a long time.
• The process is fairly dull and there are no new tools to play with.

So in order to help with migration time, there are a few things that can be done to help. Some of these include:

• Using a shared filesystem (i.e. NFS mount) for the dump files.
• For Enterprise Edition databases, setting parallelism to a sufficiently high value. This will require testing to help determine the best value.
• Testing data only imports and build indexes, and enabling constraints and other smaller tasks separately from the import.
• If licensed for Advanced Compression, using it use will write a smaller dump file.

Cross Platform Transportable Tablespaces

The next method to discuss is Cross Platform Transportable Tablespaces (XPTTS). This method starts by defining a transportable tablespace set. This set can be just one tablespace, or up to all of the tablespaces in the database, except for system, undo, and temporary tablespaces. Once defined, a check is run to ensure that the tablespaces are self-contained and do not violate any constraints. These steps can be run prior to any outage. This is also a good time to create the destination database.

At this point, the source database will be unavailable. After the set passes the check, the tablespaces are placed into read-only mode, and a Data Pump export is executed in order to capture the metadata. The next step is to run a Recovery Manager (RMAN) script to convert the datafiles included in the set. At this point, the converted datafiles and the dump file need to be copied over to the destination server. Once on the destination side, the tablespaces are imported into the new database. This step will import the tablespaces as read-only, so once it is done the tablespaces need to be altered to read, write. At this point, the new database is available and ready for use.

Some of the benefits of using this method are:

• There are very few new commands for a DBA to learn with this method. It is mostly new options for commands that should already be familiar to most DBAs. Due to this fact, there should be a very small learning curve.
• Even though there are a lot of steps, this migration method is still fairly straightforward.
• There are very few database and data type restrictions.
• For larger databases, this method is usually much faster than the export and import methods.

On the bad side:

• There are a lot of steps in this process that need to be performed in the proper order.
• For databases that have a large amount of metadata, the export and import of the metadata can take a very long time. Some applications such as Oracle E-Business Suite have a large amount of metadata.
• This method may still require a significant amount of down time.
• There can be a significant amount of setup time.

In order to help with migration time, there are a few things that can be done. Some of these include:

• Using a shared filesystem (i.e. NFS) for the dump files and converted datafiles.
• The datafile conversion process can be run either on the source or destination systems. Typically platform migrations are in conjunction with a new hardware purchase, so running the conversion on the new system may be faster.

Fast Cross Platform Transportable Tablespaces
Oracle has recently developed a new migration method that uses RMAN datafile copies and incremental backups in conjunction with XPTTS, which allows the bulk of the conversion to occur before an outage. Then during the migration outage window, only an incremental backup is actually converted. Of note when using this migration method, regardless of the source and destination database version, an 11.2.0.4+ database home is required. This is due to the fact that there were changes in RMAN in 11.2.04 (and later) that are required.

GoldenGate

GoldenGate is one of several replication tools that can be used to replicate partial or complete databases. GoldenGate will also replicate data between databases from different vendors if necessary. With GoldenGate there are several processes that capture changes, copy them to the destination side, and then apply them to the destination database.

This process does take a fair amount of time to set up, and has to be monitored to verify that the destination database stays in sync. Once it is time for the migration, the conversion can be as simple as shutting down the application servers, pointing them at the new host, and then restarting the app servers. There are additional factors that may extend this time, but in general, using GoldenGate replication will allow for the shortest outage possible when migrating across platforms.

Some of the benefits of using this method are:

• Using GoldenGate may allow for a very short downtime.
• GoldenGate can be used to replicate data to databases from other vendors. This tool can also be used to migrate to another database product.

 
On the bad side:

• Setup can take a significant amount of time, and can be quite complicated.
• There are several restrictions on data types and other database objects. Refer to database and GoldenGate version specific documentation.
• GoldenGate is a separately licensed product. So it may be cost prohibitive to license it for migrations only.
• Since GoldenGate is a separate product, additional training may be required.

Summary

As with most things in the IT world, the answer on how to best move a database from one platform to another is “it depends.” The primary thing of note is that there are multiple ways to accomplish the task, and if you have multiple databases to move you will likely use more than one method. As always, be sure to gather real and realistic requirements and then test your method as completely as possible to verify that the migration will work as expected.