The database migration “journey”

Migrating your database consists of a nine-step “journey.”

1. Decide to migrate

First, your organization must make the decision to migrate. You might decide you're spending too much money on your legacy database system. You might have initiatives that your current database technology doesn't support well. Or maybe you want to move to the cloud or take advantage of open-source software. These are common factors that might lead to your business case for migrating.

Once you have your business case and your organization buys into it, your organization must commit to performing the migration. Commitment includes funding and resources to perform the migration.

2. Analyze feasibility and alternatives

Analyze your alternatives and look at the feasibility of migrating to certain databases.

Review your application portfolio to determine whether all of your applications will migrate

As part of this review, you'll look at the applications that have legacy databases that you can and want to migrate from. Align your migration priorities with IT strategies to ensure these are important enough business applications to consider for migration.

As part of reviewing your application portfolio, you might decide that you can retire some applications. There might be other applications in your portfolio that you want to migrate but can't because they're supported only on the existing database.

As you're identifying a candidate set of applications to migrate, keep in mind that the desire to migrate them must correspond to an organizational commitment to completing the migration. It will take time, resources, and budget.

Evaluate which database technologies are the right choice as the migration target for each of your applications

In many cases, EDB Postgres Advanced Server is an ideal choice to replace Oracle. However, some applications have requirements that might be better addressed by other solutions.

After selecting the appropriate set of target database technologies, a follow-on step is to perform a more detailed assessment of how complex the migration will likely be. When EDB Postgres Advanced Server is a potential target, you can use the EDB Migration Portal to assess how compatible the Oracle schema and procedural objects in the database are with EDB Postgres Advanced Server. This information is important in the next step of the journey.

Prioritize applications

If you're considering migrating a large estate of applications, perform a first-level pass to identify the ones you want to move first. Are some so important that there would be more risk in moving them? You'll also want to look at the difficulty of the migrations and prioritize them based on that.

Decide on the hosting environment to target for your migration

See Infrastructure considerations for more information about the options you have for hosting the EDB Postgres Advanced Server database. Are you currently running on premises? Do you want to stay on premises or move to cloud? Or are you on the cloud and perhaps that hasn't worked out and you need to move back to on premises or to another cloud provider?

Identify potential migration problems

Although you'll perform a more thorough review of solutions to address specific compatibility issues that are encountered later in the journey, it's also important to identify potential migration problems in this early stage. For an Oracle-to-EDB Postgres Advanced Server migration, see Comparison of EDB Postgres Advanced Server with Oracle for details.

The main goals of this step are to:

• Understand which applications are good candidates for migration.
• Analyze the different technology and vendor options for the migrations.
• Understand the big picture of the overall level of effort, time, and cost to complete all the migrations.

Also consider the cost of continuing on current technology and platforms versus migrating to new technologies and platforms.

3. Plan the migration

Planning your migration involves these processes:

• Prioritizing the applications to migrate. Once you've decided on the applications to migrate, prioritize the order in which you'll migrate them. Organizations often consider selecting the most difficult ones because they believe that if they can do the most difficult migration, then all the other ones will be easier. However, we usually recommend picking one that is moderately important to you—not necessarily the most valuable to customers—and also not the most difficult. This way you can learn from the process. Then select the next ones based on what you learned from that first migration and so on until you eventually get to the most valuable and most difficult.
• Identifying nonfunctional requirements, such as performance, availability, management, and integration.
• Designing the solution at a high level, including both the migration architecture and the post migration architecture. For example, select the database, the platform, the type of migration, your high-availability requirements, and so on.
• Estimating the migration effort. Out of your analysis comes a sense of how much time and resources are required to do the migrations.
• Preparing the migration environment. Obtain and install the needed software, and establish connectivity between the servers.

Giving enough attention to this step leads to a greater chance of success and can reduce the number of unwanted surprises late in the migration process. Form a migration team that includes someone with solid knowledge of the architecture and implementation of the source system.

4. Migrate database schema, code, and data

When it comes to an application database migration project, this is the step that often comes to mind first. In this step, the actual database is migrated from the source system to the target system. You’ll need to perform these steps to migrate the database.

Move the schema

The schema provides the structure in which the data is stored and organized in the database. As such, you need to move the schema first.

When migrating a schema, there are usually differences in the schema objects that are present in the Oracle database and those objects that can be created in the target database. Due to EDB Postgres Advanced Server's compatibility for Oracle features, many compatibility issues are automatically addressed that other target database options can't address.

The main task during this step is to implement workarounds for those schema objects that aren't compatible or can't be converted automatically. Schema objects are created using a SQL-based data definition language (DDL). You can use EDB's Migration Portal to assess the compatibility of the Oracle DDL with EDB Postgres Advanced Server. It also performs some automatic conversions by way of its repair handlers, which are applied as part of the assessment process.

After performing a Migration Portal assessment, the next step is to verify the results of the DDL migration and resolve any problems identified in the assessment report.

Migrate the database functionality (i.e., database procedural objects)

This includes the code that exists in the database, such as procedures and functions that are called by the application or triggers that the database uses. Like the schema objects, the database code objects are also created using DDL. In addition, the code objects are closely tied to the schema objects, either referencing them or being referenced by them. Therefore, the database code objects are usually migrated at the same time as the other schema objects.

As with the other schema objects, the main activity in this step is to identify and implement workarounds for database code objects that aren't compatible or can't be automatically converted. Since Oracle's procedural language (PL/SQL) is different from other database native built-in languages, this can often be the most challenging task in a database migration, especially if the source database contains a large number of code objects or the code objects consist of many lines of complex code. This is where EDB Postgres Advanced Server's PL/SQL implementation, which natively provides many of the most commonly used PL/SQL features, becomes significant. These features can greatly reduce the effort required for completing this step. Migration Portal assesses Oracle database code objects in addition to the other schema DDL and has features to update and reassess incompatible objects.

Migrate the data in the database

When migrating the data, there are two basic options. The first is to move all of the desired data using a snapshot process (copy of all data at a particular point in time). This option is often chosen when the database is relatively small or, in the case of a production migration, the application downtime required to complete the migration is acceptable.

You can perform snapshot replication periodically to refresh the contents of the target database with the current contents of the source database. However, such periodic refreshes usually require removing all the previously copied data from the target system and migrating all of the data again. The second option is to migrate the data on an ongoing, continuous basis using change data capture (CDC) techniques. As part of every change data capture process, the initial load is done by some sort of snapshot. However, after the initial snapshot, a change data capture mechanism is used to identify later changes made in the source database and transfer the changes to the target database. The CDC-based data migration option is usually chosen when minimal downtime is required and it's important to keep the target system in sync with data changes being made in the source system.

You can use the EDB Migration Toolkit to perform snapshot-type data migrations and the EDB Replication Server to perform CDC-based data migrations from Oracle to Postgres. Although snapshot-based data migration and CDC-based data migration are the two main approaches, you can use other hybrid or custom approaches to provide solutions to special data migration needs that might not be addressed by the two main methods. For example, sometimes it helps to pull data by way of queries over database links or direct connections to the source database from the target database.

5. Migrate interfaces and application

Convert applications to work with the newly migrated Postgres database. The application is the code external to the database that interacts with the database.

Migrating the application involves:

• Updating the application connection strings and drivers (e.g., JDBC, ODBC, OCI, .NET). Applications that use open standard connectivity such as JDBC or ODBC normally require changes only to the database connection strings and selecting the EDB driver. Review the following driver-specific documentation for more information on installing the driver and configuring the application to connect to the EDB Postgres Advanced Server database:

  • EDB JDBC Connector
  • EDB ODBC Connector
  • EDB .NET Connector
  • EDB OCL Connector (an EDB version of the Oracle OCI driver)

  For .NET applications, you'll need to update applications that use the Oracle .NET driver to replace calls to Oracle-driver-specific class names to their EDB driver equivalent names.

• Converting embedded SQL in the application. The SQL might have been built to work with the source database. Sometimes there's a specific syntax that Oracle supports that might not be supported in Postgres. In this case, you will need to convert that SQL so that it works with Postgres. EDB's compatibility for Oracle features built into EDB Postgres Advanced Server and into the EDB drivers reduces the amount of conversion required.

• Updating application code as needed. In some cases, an application can contain code that includes Oracle driver-specific calls that aren't available in the Postgres driver. You must update such code to implement Postgres-based workarounds.

• Migrating applications. Depending on the migration project, there might be some movement of the actual application—where it's hosted and its configuration—that needs to take place along with migrating the source code. In general, this won't result in any compatibility concerns. However, it's important to verify the application's database connectivity, functionality, and performance in its new environment. In addition, when migrating an Oracle application to Postgres, you might need different techniques, tools, and configurations to address connection pooling and load balancing needs.

6. Migrate reports and management tools

In this phase, you’ll migrate reports, DBA utilities, and scripts. Organizations often have reporting mechanisms and other management tools that also query and interact with the database in support of the core applications. Since some of these artifacts were built specifically to support Oracle-based processes, they might not all apply or be needed in the post-migration system. Thus, it's a good idea to first review the inventory of reports, utilities, scripts, and similar artifacts to determine the ones to leave behind, to replace, and to update.

Updating a script or utility often involves updating any incompatible SQL built for Oracle to be compatible with the target database. It might also involve updating them to work with the command line interfaces provided by the target database instead of the Oracle command line interfaces. For example, SQL*Plus is Oracle's primary command line interface, and psql is the one provided with Postgres. Although SQL statements can be issued in both interfaces, the other supporting commands and keywords they provide are very different. Many Oracle scripts and utilities are built using SQL*Plus commands. To get these scripts to run with psql, you need to convert them to to use psql commands instead.

Fortunately, EDB provides EDB*Plus, which is a SQL*Plus work-alike utility that understands some of the most commonly used SQL*Plus commands. This capability, coupled with EDB Postgres Advanced Server's other built-in compatibility for Oracle features, lets you run many existing scripts built for Oracle with EDB Postgres Advanced Server. EDB Postgres Advanced Server users also can use a version of psql that's extended to work with the compatibility-for-Oracle features built into EDB Postgres Advanced Server. After becoming familiar with psql, users often decide to use it instead of EDB*Plus to take advantage of its capabilities. Therefore, you might still choose to convert your SQL*Plus-based scripts and utilities to psql-based ones.

Another popular interface provided by Oracle is SQL*Loader, which is a tool used to bulk load data into Oracle. EDB provides a work-alike utility called EDB*Loader that you can use to load data into EDB Postgres Advanced Server. Like EDB*Plus, EDB*Loader provides a familiar interface to Oracle users that you can use with EDB Postgres Advanced Server. Using this tool reduces the required changes to existing data loading procedures. Postgres native methods for loading data are also available to EDB Postgres Advanced Server users. If you choose these methods, you'll need to update any data loading processes based on Oracle-specific capabilities.

You might use other reports or management tools to retrieve information from the Oracle database. Assess the SQL queries issued by these tools to determine whether they're compatible with the target database. Assuming the queries apply to the new system, you'll need to update them if you identify SQL incompatibilities or other operational differences.

7. Test the migration

Whatever the size of your migration, you’ll need to validate that the migrated application operates as expected. You'll be testing all along, but at some point, you need to do a formal set of tests to:

• Validate that the data was fully migrated to the target database and is consistent with the source database.
• Verify the functionality of the database and the application.
• Validate that the performance of the database and application are acceptable.

For this step, it's important to set up a test environment that allows you to adequately test the functionality and performance of the database and application. Run these application and database tests with the actual application or a copy of the application under workloads simulating the expected production workloads and in an operating environment similar to the production environment. The functional tests must provide enough coverage to exercise application code and interact with database objects that are used less often under normal workloads.

When it comes to data validation, the tools and processes used to compare the data in the source and target databases need to make it easy to identify differences. If differences do exist, you need to perform an analysis to understand why they occurred and whether some changes in the migration process are needed. Depending on how often the data in the target system is refreshed with data from the source system, you might need to perform data validation checks more than once. In the case of an ongoing CDC-based data migration, establish a plan for validating the data periodically.

In this step, it's also a good idea to set up and exercise the tools and processes you will use to meet availability requirements, perform backup and recovery operations, and monitor and manage the database. Doing so helps to identify any changes required to operational procedures or the configuration of the tools and related systems in preparation for their use in the production environment.

One of the main goals of this step is to identify possible issues to address before proceeding with a final migration to the production system. If you find any issues and correct them, you need to perform more testing. When you're satisfied with the results of the preproduction migration tests, you can perform the final preparation of the production environment.

8. Optimize and configure the post-migration system

In this phase, you'll ensure that all needed software is installed and update the production environment with changes identified and verified during the testing phase. You'll also configure and tune the system based on recommendations derived from previous performance testing results. You might need to adjust database parameters and application settings to prepare the system to run optimally. Also apply any query tuning-related updates that you identified and verified in the testing phase.

This phase is also the time to finish addressing your high availability, disaster recovery, and security requirements, including how users and applications authenticate to the database and who's authorized to perform each action. As needed, update standard operating procedures (SOPs) for ensuring these requirements are met and for monitoring and maintaining the database.

9. Complete cutover/go live

This phase consists of the following:

• Completing the migration of data into the production database. If data is being migrated from the source database using a CDC-based approach, disable further changes to the source databases to ensure that the new database becomes in sync with the old database. In general, you can continue to allow read-only queries of the source database if you want.
• Setting up a rollback option. Set up a rollback configuration so that the old database is receiving updates from the new database and it'll be there as a fallback position in case an issue occurs with running the applications with the new database. This way, you can go back to running the application against the original source database. Moving data back to the target database is often done through a change data capture mechanism.
• Configuring the system to begin using the migrated application with the new database. This is typically done only after verifying the new database has received all the final updates from the old database.
• Testing the new system until a go/no-go decision is made. Test the new application and database for some period of time. Once you're confident that the application is performing well in the new environment, you can declare that the application is a "go" for full production use.
• Finishing cutover to production. Once you complete the cutover and are satisfied with it, usually you'll remove the fallback position of sending the data changes back to the target data source database. At that point, you'll run only in the new production system.