MIGRATION

CHAPTER
2

Choosing the Right Migration Method

Chapter 1 introduced the various methods for performing data migrations. Before you can start planning your migration, you must decide on the basic method for the migration. By looking at your requirements and determining which method best meets your needs, as discussed in this chapter, you will be able to create a plan that is right for you.

EVALUATING YOUR ENVIRONMENT

There is no one migration path that is right for everybody. You should choose the migration path that best meets your particular requirements. We have performed many different data migrations using different methods. In each case, we determined which method to use based on an evaluation of the following factors:

   Migration goals

   Downtime requirements

   Equipment and infrastructure availability

   Budget

   Available resources

After assessing these factors, we are able to decide which methods are available and which one best meets the specific requirements. The methods and corresponding examples that we present in this book are intended to serve as guidelines that you can use to make your own choice after assessing the preceding factors in your own environment. You might choose one of these methods, a combination of these methods, or even a method that you devise for your own particular requirements.

Our goal in this book is not to tell you which method is right for you; instead, we intend to give you solid ideas and examples upon which you can start to build your own project.

TIP & TECHNIQUE

No two migrations are the same. Every customer is different, and every migration is different. When deciding how to migrate your data, don’t try to fit your plan into any of these methods exactly. Your specific needs will be different from everybody else’s. This book is designed to provide you with ideas. Take these ideas and run with them. Think outside the box and do what is right for your situation.

Migration Goals

The first and most important factor in determining the best migration strategy for you is identifying what your migration goals are. Why do you want to migrate? This might seem like a very simple question, but details are important. So, let’s look at several common reasons for performing a data migration. The following list is by no means comprehensive. Many other reasons exist for performing data migrations.

   Hardware refresh   Perhaps your hardware is out of date and you want to upgrade it to a newer, faster system. This is one of the most common reasons for performing a data migration. If you are migrating to the same hardware architecture the options and methods that are available will be different than those available if you are going to different hardware architecture, such as migrating from Oracle on AIX to Oracle on Linux or to Oracle Exadata.

   Support requirements   Many times migrations/upgrades are dictated by support requirements. Oracle support for very old versions of the database eventually ends. In addition, Oracle versions are tied to OS versions, which in turn lose support. Extended support can be purchased for certain time frames for a price, but eventually you must consider migrating/upgrading to a newer version of software to remain supported.

   Consolidation   As hardware becomes faster and more efficient, and supports more and more memory, you will often find that it is appropriate to consolidate several databases into one database. With Oracle Database 12c, you can now consolidate into a multitenant database using Oracle’s pluggable databases.

   Security requirements   Recently we worked on a database migration/upgrade project where the motivating factor was to be able to encrypt the database using tablespace encryption using a Hardware Security Module (HSM). It is not uncommon for databases to be migrated and upgraded to support better security. In addition, security is updated on a quarterly basis via Oracle Critical Patch Updates (CPUs).

   Performance/new features   One of the primary reasons for migrating to a new database is to enable new database features. Each release of the Oracle database introduces a variety of new features that are designed to enhance performance and scalability.

These new features will vary by database version and your needs. Depending on the type of features that you want to take advantage of, the method of migration might be limited.

TIP & TECHNIQUE

Determining the migration goals is important to making the overall decision as to how you are going to do the migration.

It is important to first determine all of the reasons for migrating the database before you begin the migration plan. The reasons to migrate your database will very often determine the migration methods that are available to you. As with any task, preparation and planning is key to a successful migration.

Downtime Requirements

Determining allowable downtime is another extremely important part of choosing the appropriate migration process. This requirement will determine which migration methods are viable. If you are allowed plenty of downtime and have a small database, the number of options is unlimited. Unfortunately, in many cases the amount of downtime is very limited and the size of the database is very large.

The downtime requirements will be determined by the business unit and will usually be very strict, especially if formal service-level agreements (SLAs) are in place. In several of the recent migrations that we have worked on, the downtime requirements were limited to just a few hours, whereas the database size was over 10TB. This type of scenario can limit your options to a very few. Other requirements will further limit the options that you have for your migration method.

So, what is considered downtime and what will affect that downtime? For our purposes in this book,downtime is considered the length of time the database is unavailable to the application servers in order to get a consistent copy of the database. Keep in mind that the methods provided in this chapter have many variations.

Transformation Goals

The transformation goals are also very important to determining the available migration methods. So, what is a transformation goal? How is a transformation different from a migration? In the simplest of terms, if you are changing the structure of the database, you are performing a transformation in addition to a migration. In this book, we pretty much call everything data migration (except for a pure upgrade).

The simplest of migration goals is to move the database from one system to another system. This is very simple and straightforward. However, many times a data migration will offer additional benefits, such as the opportunity to take advantage of new features, enhance the database, and make overall database improvements.

Here are some examples of transforming a database during a migration:

   Upgrading from one version of Oracle to a newer version. This technically does not constitute a transformation, but rather an upgrade within the data migration.

   Moving from BasicFile LOBs to SecureFile LOBs. SecureFile LOBs are first available in Oracle Database 11g.

   Moving from range partitioning to interval partitioning. Interval partitioning was introduced in Oracle Database 11g.

   Encrypting the database.

   Compressing the database.

   Moving from a non-container database to a container database with pluggable databases (first available in Oracle 12c).

   Partitioning the database.

   Moving to Oracle Automatic Storage Management (ASM) from non-ASM storage.

During a data migration is possibly the best time to introduce these new features.

TIP & TECHNIQUE

When you are doing any type of migration, you should carefully consider if you can enable new features and/or restructure your database. Often, your best opportunity to modify the database is during a migration.

Operational Goals

Operational goals are simply additional functionality and/or features that are needed to meet business goals. For example, your IT organization may have mandated that as part of its ongoing plans to improve its business continuity requirements, a more robust disaster recovery plan must be implemented before cutover to the new production environment, so this would be an excellent opportunity to implement Oracle Data Guard at the corresponding disaster recovery site.

Migration Methods Review

As mentioned in the previous chapter, there are several different methods for migrating a database. Each of these methods has its pros and cons, as outlined in Table 2-1. The key factor for most of these methods is how much time it takes to perform the data migration.

TABLE 2-1. Migration Options

In this book there are many migration methods that are mentioned that are not necessarily covered in detail. This is due to the fact that many methods are subsets of each other. Therefore, you will not see a one-to-one relationship between this section and the rest of the book. In fact, many of the options listed in Table 2-1 are not entirely suited for an Oracle migration.

Depending on your requirements, not all of these methods may work for you. In this book we will be focusing on a few methods and variations of these methods. The key methods that we will be covering are described in the following sections, along with some of their variations.

Database Upgrade Assistant (DBUA)

DBUA can be used to upgrade a database to the next major release. Using this tool can be time-consuming, and it modifies your production database. You must back up your system (as with all migration methods) before the upgrade, and the only way to back out the upgrade is to restore the original version of the database from its prior backup. DBUA performs an in-place upgrade, and it only performs upgrades, not migrations.

NOTE

Using the DBUA is the only method that we cover in this book that does not perform a migration, only an upgrade. This upgrade is done in-place.

Export/Import

The traditional Export/Import utility can still an important part of any data migration. Even if this migration method is not used as the primary method, in many cases it is used to export and import metadata such as user schemas and so forth.

This method can be used in conjunction with GoldenGate to create schema objects that are later loaded into via Export/Import or Data Pump followed by GoldenGate replication.

NOTE

Traditional Export/Import is desupported starting with Oracle Database 11g.

Data Pump

Data Pump is similar to Export/Import but is much faster. It can be used to create objects and to efficiently move data. Data Pump is very fast at moving traditional data, but transfer speeds for BasicFile LOB data tend to be dramatically slower than when transferring SecureFile LOB data.

Depending on the type of data being transferred, network throughput, and so on, it is not unusual to realize transfer speeds of over 100GB/hour using Data Pump.

RMAN

Oracle Recovery Manager (RMAN) is generally recognized as the de facto utility for quickly backing up and restoring a database and its data, but even though it is not specifically designed for data migration, it can be used for that purpose.

RMAN can perform both full and incremental backups. Using an incremental backup, you can reduce the length of time that the database is down during cutover to the length of time it takes to complete the incremental backup and restore.

While this method does not allow for database changes during migration, it definitely supports some basic storage changes such as tablespace relocation and endian/character set conversion.

Transportable Tablespaces

Using transportable tablespaces can be an efficient way of performing a database migration. The process is fairly fast, taking just a little longer than the time it takes to copy the tablespace’s datafiles between source and target environments.

However, this method does not allow for database changes during the migration.

Cross-Platform Tablespace Migration with Incremental Backups

This is one of the primary methods used (and recommended) for migrating to Exadata systems. It essentially combines transportable tablespace methods with incremental RMAN backup methods to perform the data synchronization.

While this method does require some downtime, that downtime will be limited to the time it takes to perform the final incremental backups.

Data Guard

In certain specific cases, Oracle Data Guard can be used to migrate a source database to a different system with a similar architecture. Simply set up a physical standby database on the new system, and when you are ready to cut over, perform a Data Guard switchover operation. An additional benefit of this method is that you are left with a ready-made physical standby.

This method does not allow for database changes during the migration, and it does require the same hardware architecture on the source and target.

GoldenGate

Oracle GoldenGate is an excellent product for keeping systems in sync for a quick switchover. Since GoldenGate is a replication technology, it allows only specific tables to be replicated instead of the entire database, and that can reduce the time required to complete the cutover significantly.

GoldenGate can be used to perform migrations that include data structure changes and transformations. When used in conjunction with Oracle Flashback Database features, the target database’s performance can be tested prior to the cutover. Using GoldenGate for data migration offers many great features which we will describe later in the book.

Resources

When you consider which human resources are necessary for a data migration, you need to take into account several different factors, including the number of people needed, their skill levels, their experience, and their availability. These factors are all extremely important to performing the data migration successfully. If you don’t have enough of the right resources, the project is doomed to be delayed or suffer serious setbacks.

So, what kind of resources are required? Since many different tasks need to be performed for a smooth migration, you will need a wide range of resources, including the following:

   Project management   Without proper project management resources, it will be very difficult to keep track of and maintain the schedule. A professional PM is absolutely needed for all but the simplest and smallest data migrations. In addition, the PM can assist with the mediation of issues that can occur before, during and after the migration.

   Team leadership   There must be a clear chain of command to be able to guide the project. Depending on the size of the migration, there will most likely be several teams of engineers, but they all should report to one project lead.

   Oracle resources   These resources must be familiar with Oracle DBA tasks and must be able to modify and/or code stored procedures and SQL scripts. They must be familiar with tools such as Export/Import and Data Pump as well as other data transport methods. They must be very familiar with the database configuration and usage as well as with the data itself.

   GoldenGate resources   If you are using GoldenGate for your migration method, you must have one or more skilled GoldenGate resources. Having someone who has had experience with this type of migration is important.

   Network resources   Any migration involves not only moving databases from one system to another, but moving application servers as well. In almost any migration, this involves opening firewalls, copying data between systems, and allowing user access, and this typically means there will be a need to modify the network configuration.

   System administrators   Every migration involves both database resources and OS resources. This means it is necessary to either have your DBAs perform double-duty as system administrators (if they have the skills) or have dedicated system administrators.

   Storage administrators   Since every database requires storage, storage administrators must be included as part of the migration project team.

   Quality assurance and testing resources   Before putting any new system into production, it is crucial to test it to make sure that all of the applications work properly. This involves QA analysts and testing resources. If Oracle testing tools such as Real Application Testing and Database Replay are going to be used, it’s important that the QA and testing resources are familiar with how those tools will be used.

As you can see, for moderate to large migration projects, many people will be involved.

TIP & TECHNIQUE

Don’t be hesitant to bring in extra help if you need it. Getting the help and expertise that you need to be successful is far better than trying to get by with available resources and failing.

In-House Resources

If the resources listed previously are available in house, that’s always a good choice. In order to use in-house resources, you must make sure that they are available full-time for the migration project. If the resources are not able to be dedicated to the migration project and they are the only resources available, this often leads to delays and/or project failures. Dedicated resources are simply necessary for project success.

Additional Resources

If you are going to bring in outside resources to help with the data migration, you will need to make sure that the resources are experienced with this type of migration and have the right type of skills that are needed for the project. Additional resources are a great way to help with the database migration project because you can often find people who have already experienced the type of migration that you are performing.

There are many skilled resources or companies that you can bring on board to help with your data migration. Depending on the method that you have chosen, there are organizations that are skilled and experienced in every type of data migration. Engaging outside resources that can bring specific skills and experience to the table will increase your chance of success.

Budget

Probably the biggest deciding factor of which migration method you choose is the project’s budget. The amount of time it takes to perform the migration depends on the number of staff dedicated to the project as well as any additional software and hardware resources. These all relate back to budget.

No data migration is free; there is always a cost, sometimes significant. If additional software is required, such as Oracle GoldenGate, licenses must be acquired. If additional hardware is required, both hardware and, potentially, additional licenses must be purchased.

Unfortunately, we have often seen a migration project’s budget be severely underestimated, and as a result the migration ends up exhausting the planned budget. It is therefore crucial that the project’s budget does take into account the project’s migration goals, human resources, licensing, and hardware costs.

Additional Software

As mentioned previously, almost every migration involves the purchase (or exchange) of software licenses. When you are moving a database from one system to another system, the database licenses will most likely change, especially if the number of CPUs or cores changes. If you are migrating from one version of software to a newer one, the licensing cost will most likely change.

Database licenses are a complex issue and we will not attempt to explain them here; we simply stress that software licenses are an important component of the migration budget and their costs must be researched. Consult your software vendor for licensing costs early in the budgeting process.

Equipment Resources

Almost every data migration is done for the purpose of moving to new hardware. The new hardware may or may not match the same architecture and OS of the original system. If it does match, the migration will be much simpler, of course, but in many cases one of the primary goals of the migration is to move from one architecture to another, for any number of reasons.

Depending on whether or not the new systems are of the same hardware architecture and OS, the number of migration options will also vary. The number and type of CPU cores will dictate licensing costs; in addition, the new OS/hardware environment will most likely require additional training and staffing needs. For example, if you are migrating from Microsoft SQL Server to Oracle, you might not have the in-house resources to manage the new system without hiring additional help.

Infrastructure

Infrastructure is an important part of every migration. If you are adding new systems, you must provide sufficient network and storage connectivity to those new servers. Necessary infrastructure includes

   Rack space

   Power

   Cooling

   Network cabling

   Storage cabling

   Network switch space

   Storage switch space

   Other components, such as routing, firewalls, and so forth

Without proper infrastructure planning, your migration efforts are likely to run into unexpected problems during a crucial project phase. Remember that when migrating data, the speed of the network between the source of the migration and the target of the migration is important.

Proper infrastructure planning usually involves including the systems administration, network administration, and storage administration teams early in the planning phase alongside the database and application server groups. Our experience has shown that combining forces as early as possible during the planning stage guarantees the project will run more smoothly and efficiently.

Additional Considerations

In addition to the requirements covered previously, there are a number of other requirements that will further limit which migration methods are available in a given scenario. These include data types, database features, and so forth. If the structure of the database or the data is going to change, some options such as Data Guard cannot be used because they require an identical database on the target system.

Data Types

Typically a migration from Oracle to Oracle does not involve data type changes, unless of course changing data types is part of the reason for the migration. For example, when data type conversion from BasicFile LOBs to SecureFile LOBs is a project requirement, there can be a significant impact on project timelines, especially for testing the results of the conversion.

When moving from SQL Server to Oracle or from other databases to Oracle, it is often necessary to change data types. This is important because it will limit the choices of data migration methods. The fewer changes that will be made to the target database, the more options will be there are for data migration.

Database Features

Often data migrations are performed in order to be able to take advantage of new database features. In the past few years Oracle has added many new features that make a database upgrade worthwhile, especially when one or more new features enhance both application functionality and performance. These features might include the following:

   Partitioning   Oracle is constantly working to improve partitioning. In recent releases Oracle has introduced more compound partitioning types as well as new types such as interval partitioning.

   LOBs   SecureFile LOBs are much more efficient and extremely fast as compared to legacy LOBs. We have migrated databases and converted to SecureFile LOBs on several occasions.

   Security   When migrating to a new platform, it is often convenient to enhance the security model of the database. For example, during a recent upgrade/migration, we implemented encrypted tablespaces.

   Storage   We have found that a great time to move from non-ASM to ASM storage is during a migration, since this provides all of the additional benefits of ASM storage technology.

As discussed earlier, there are a lot of other reasons to migrate data, the most common of them being a hardware refresh.

Infrastructure Capabilities

During the data migration itself, it’s important not to neglect existing infrastructure capabilities. If you do not have a sufficiently fast network as well as storage of sufficient capacity and throughput, the data migration will suffer in terms of schedule and efficiency. The infrastructure will be the conduit through which the data moves from the source to the target system. The speed will vary depending on the speed of the network and the number of components that the data must travel through.

MAKING YOUR DECISION

So, how do you decide on the type of data migration that you want to perform? There are a few major factors and a number of minor factors that have been discussed in this chapter that will absolutely affect the type of database migration that you will be performing. The biggest of these factors is the downtime requirement. Because downtime requirement will vary by organization, it is important to lock down your specific needs. Let’s take a look at how some of the major requirements can dictate your migration method. As always, everybody’s requirements are different. Now that you are familiar with the major factors that impact your choice of database migration, it’s time to make a choice by matching your specific requirements with a particular method.

How Downtime Affects Your Choice of Method

The downtime factor is very important. The shorter the downtime that you are allowed, the fewer migration options that are available to you. The migration methods in Table 2-2 are listed in the approximate order of slowest to fastest. This list takes into consideration the amount of time that the applications will be unavailable during the cutover. Note that these methods do not stand alone—as you have seen earlier in this chapter, there are many variations on these methods.

TABLE 2-2. Options Listed by Downtime

So, if your applications are restricted by a very short time frame for cutover, your options are limited to Data Guard or GoldenGate.

NOTE

GoldenGate requires an initial migration, which might be accomplished using any of the other methods.

How Versions Affect Your Choice of Method

The target Oracle version is another important factor in choosing a migration method. As Table 2-3indicates, if the target of your migration is a different Oracle version, you can rule out using RMAN or Data Guard for the initial migration.

TABLE 2-3. Migration Options Versus Database Version Consideration

So, if you wish to modify the target database, you are limited to Export/Import, Data Pump, or GoldenGate. You can start to see how your decision might be made for you.

How Architecture Affects Your Choice of Method

There might be other factors involved in your decision-making process besides the downtime requirement and versioning requirement. One of these might be the architecture of the system. If you are moving to a different architecture, your options might be limited by that choice (see Table 2-4).

TABLE 2-4. Impact of Architecture Transition Upon Migration Methods

How Database Transformation Affects Your Choice of Method

A database transformation occurs when you change database objects during your migration. This is a very common occurrence, and the perfect time to transform the database is when performing a migration. The following kinds of transformations are possible:

   Partitioning   Adding partitions, converting to a different partitioning type, and so forth. This is a very common type of transformation in data migrations. During the migration itself is the best time to actually add partitioning.

   Security   Adding encryption or a Hardware Security Module (HSM). Since adding encryption, such as encrypted tablespaces, usually requires rebuilding the tablespace structure, a migration is ideal for this type of operation.

   LOB transformation   Changing to SecureFile LOBs. SecureFile LOBs are a new way to store LOB data, introduced in Oracle Database 11g. The performance of SecureFile LOBs far exceeds the performance of traditional LOB data.

   Compression   Compressing tables or tablespaces. This can be done either within a migration or as a stand-alone task. However, compressing during a migration can be much more efficient.

   Other   There are a number of other features that you might enable as part of the migration; some features require data transformation, while others do not.

Whether each method allows for transformation is shown in Table 2-5.

TABLE 2-5. Options Listed by Whether Transformation Allowed

TIP & TECHNIQUE

There are various techniques for transforming the database during a migration. They are discussed throughout this book.

Decision Summary

As you have seen, there are a number of factors that will dictate which methods are available for you to choose. Listing the factors in a table provides an easy way to see what migration methods might be eliminated, as shown in Table 2-6.

TABLE 2-6. Migration Summary

Now you can see how your decision is limited based on your specific criteria for migration. Depending on your requirements, the choice of migration method becomes more obvious. Of course, your specific requirements might allow for different combinations of these methods.

HYBRID SOLUTIONS

The solutions listed previously are not exclusive of each other. In fact, several of the solutions can be used together. This is especially true of Oracle GoldenGate. Oracle GoldenGate is a replication tool. It includes a facility to perform the initial data migration, but that facility is not extremely efficient. When we perform a GoldenGate migration, we combine it with other methods for doing the initial setup and migration. Let’s look at an example of this.

GoldenGate Migration Example

The basic idea of using GoldenGate for a data migration is to start replicating data, perform the initial migration, and then sync up the changes that have occurred since the point in time that the initial migration occurred. Let’s look at these steps in a little more detail:

1.   Start replication. To start replication, GoldenGate
extract processes mine transactions from the redo log. Once REPLICAT processes are running, all changes are being saved.

2.   Initial migration. There are a number of options to perform the migration. You can use Export/Import, Data Pump, hardware cloning, transportable tablespaces, and so forth.

Tablespaces were created with encryption. This encryption was set up using a hardware security module.

Once the tablespaces were created, Export/Import was used to create the schema objects.

The schema was then modified to include transformations such as SecureFile LOBs, partitioning, and so forth.

The data was then migrated using an activated physical standby via Flashback Database.

3.   Sync up. The data was synced up by enabling GoldenGate REPLICAT processes. The REPLICATs were started using the SCN that matches when the guaranteed restore point was created. This gave us the last transaction on the source database.

Once the data was synced up, both the source and target had the same data, even though they were different versions of Oracle existing on different hardware architectures and for which several database objects had been transformed.

At this point, we were then able to test the applications, perform final QA verification, and then move users to the new system.

As you can see, this is a very elegant solution using three migration methods: Export/Import, Data Pump, and GoldenGate. More details are provided later in this book.

SUMMARY

As you have seen in this chapter, there is no one migration path that is right for everybody. You must customize your own migration path based on your needs. There are many different methods of database upgrade, migration and transformation. Each method is chosen based on your needs, which could include

   Source and target versions

   Transformation requirements

   Schedule

   Equipment and infrastructure availability

   Budget

   Available resources

All of these factors are used to determine the particular method that you need for your specific project. No two projects are exactly alike. The different methodologies and technologies can be combined to create a plan that best meets your specific needs. The methods that we have provided you with are a guideline that is used along with some examples. You might choose one of these methods, or a combination of these methods or even develop your own unique method.

Based on your requirements, your choices might be limited; however, there are always combinations that might work as well. Hybrid solutions of multiple methods usually are the best way to go.