A Day in the Life of a DBA – Part 2: The No-Stress Data Management Express

Moving big amounts of data can be very stressful and quite often requires all of your technical skills. Users expect data migrations to be done quickly and ideally with no downtime and impact on the applications that they are working with.


Sometimes it is necessary to move data: upgrading your database or moving the whole database to another server. Furthermore you can reduce your IT costs with data migration tool: migration to hardware that is less expensive to purchase and operate, migration to less costly operating systems. Also migrating away from older database versions that may require expensive extended-support contracts and migrating to less expensive editions form a database i.e. Oracle Standard Edition, with its lower licensing costs. Other scenarios might change the configuration of a database or data may need to be replicated to another site or even to another database platform. Doesn’t sound that easy?! You also need to be aware of the back up options.

The key challenges with database migrations relate to their potential to disrupt the business. Therefore, organizations need strategies to minimize user downtime, testing applications to ensure that they will continue to work properly after the migration and planning for recovery to the old environment if issues arise.

Minimizing downtime

Before the era of 24x7 operations, upgrades were scheduled over the weekend. The main goal was to make sure the systems were operational on Monday morning—either because the upgrade was successful or because the backup from Friday afternoon had been restored before users arrived on Monday morning.

Today, being offline for an hour, much less a whole weekend is completely unacceptable, especially for environments such as e-commerce. Therefore, every step of the upgrade process must be fine-tuned to minimize downtime for the end user. Is a cold backup necessary? Can we just make a disk copy of the database? Do we have the additional space needed for the migration? Do we have the best recovery technique?

Application testing

Another significant migration challenge is adequately testing applications after migration to avoid the need for rolling back to the pre-migration environment. Ideally, after the migration, you want to thoroughly test your applications before making them available to your user community, but this is seldom possible due to the pressure to get the system back online as fast as possible. Most organizations try to save downtime by limiting the application testing— which increases the risk of a failed migration.

Planning for recovery

Organizations need to plan for the possibility that the migration is unsuccessful. What happens if an important application does not perform as well on the new version of Oracle or the new OS with a full production workload?

Even worse, what if the migration works flawlessly and your users work with the application for a couple of days, but then some part of the application does not work correctly? You must plan a way to recover back to the old version of Oracle or the old OS where everything was working, without losing the transactions from the last few days.

Best practices for migrations

Plan carefully.

There are risks inherent in any migration, including media failures, power outages, and running out of time, memory or disk space. Proper planning can help mitigate these risks. Be sure to test as many steps of your migration process as you can— and to test multiple times.

Consider using export/import to restructure data.

If you are migrating to a new operating system, the only way to move your data to the new OS is by executing an export/ import; you cannot simply back up the database and recover to the new server because of file format incompatibilities. But even migrations that do not require export/import can often benefit from it. Export/import enables you to restructure all of the data in your database and clean up any fragmentation.



Disable triggers and cascade delete constraints.

In any migration needs to be able to disable triggers and cascade delete constraints on the new instance until it is time to go live in that environment.

Import into a new database shell.

To take advantage of some of the features of recent Oracle versions, you need to import into a new database shell rather than use the database upgrade script.

Plan your rollback strategy.

Be sure to plan a rollback strategy in case something does not work correctly in the migration. If you are performing an OS upgrade or Oracle migration to a new server, you can usually recover your backup of the original database to the new server. In the case of an OS upgrade, you don’t need to do anything else. In the case of an Oracle upgrade, you also need to go through the Oracle upgrade process after the recovery is complete.

A safe failback option is to have a replication solution such as SharePlex. With SharePlex, you can replicate from the new version of Oracle back to the older version. This safeguards your data if your application does not work correctly after migration, regardless of how much work your users have done since the migration. You can failback to the older version with no loss of user data.

Consider investing in a log-based replication solution.

A log-based replication solution such as SharePlex will help:

  • Minimize downtime and the impact of the migration on your business.
  • With such a solution, you can manage the migration process behind the scenes
  • You’ll never have to take your users offline except for a short period when you switch them over to the new database.
  • It cuts down the impact on users during the upgrade.
  • With the right replication technology in place, you will be able to mitigate risks such as downtime, application testing, platform validation and storage validation.

Learn how to scale out your databases, ensure high availability of your critical applications & integrate data from multiple data sources.

Author: Michael Sass

Database Technical Specialist