EDB capabilities for the migration journey

One advantage of performing the migration from Oracle to EDB Postgres Advanced Server is the experience EDB has in performing these migrations. For each phase, we offer tools and services that make migrations easier. We can also discuss your business requirements and help you with the decision to migrate and the approach that works best for you.

Capabilities used in a migration journey

Before identifying the tools and services that EDB provides to assist with a migration, it's worth reviewing the capabilities that are needed in each step of the migration journey. In the context of this discussion, the term capabilities can refer to either software tools or workforce skills and experience. While all the steps in the migration journey benefit from being performed by experienced individuals, many of them are more easily performed when an appropriate set of tools are used.

These are some of the essential types of tools and skills that are needed to successfully complete each step of the migration journey.

Analyze feasibility and alternatives

• Tools and ability to assess the feasibility of moving the source database to the target database
• Tools and ability to categorize migration complexity

Plan the migration

• Ability to estimate migration level of effort
• Ability to prioritize the order of migrations
• Ability to to define nonfunctional requirements and design a high-level solution

Migrate database schema, code, and data

• Database compatibility
• Tools and ability to convert incompatible objects
• Tools and ability to perform snapshot- or CDC-based data migration

Migrate interfaces and application

• Database driver/connector compatibility
• Tools to parse and assess application SQL
• Tools or ability to refactor application code and convert

Migrate reports and management tools

• Tools to parse and assess SQL in reports and utilities
• Ability to convert incompatible SQL

Test the migration

• Tools to help validate data consistency
• Tools to assist with functional and performance validation and ability to perform these activities

Optimize and configure the post migration system

• Tools and ability to assist with database, query, and application tuning
• Tools and ability to address and configure high availability, data recovery, security, authentication/authorization requirements

Complete cutover/go live

• Tools and ability to perform reverse data migration to support rollback
• Tools to help validate data consistency
• Tools and ability to operate and maintain the migrated system

EDB migration software and services

The table shows a mapping of the migration-related capabilities that EDB provides to the migration journey step they support. A summary description for each of the capabilities is provided after the table. More details can be found in the product documentation and description of package services on the EDB website.

When reviewing the table, the following apply:
Bold = EDB tool
Italic = EDB professional services package
[Bracketed = Compatibility with Oracle]

¹: PEM can monitor a BigAnimal cluster but with some limitations. The monitoring services provided by the cloud service provider are used in conjunction with PEM or instead of it. PEM isn't applicable for monitoring Cloud Native Postgres instances.
²: Failover Manager isn't applicable for BigAnimal or Cloud Native PostgreSQL (CNP) deployments as the capabilities it provides are built natively into the BigAnimal service or the CNP operator.
³: EDB Postgres Distributed isn't currently available for the BigAnimal and Cloud Native PostgreSQL offerings. When it becomes available, its capabilities will be integrated as an option directly in the BigAnimal service and Cloud Native Postgres operators.

EDB Postgres Advanced Server

EDB Postgres Advanced Server is an enhanced version of PostgreSQL. Not only does it contain all the features of core PostgreSQL, it also includes built-in compatibility with Oracle and other enterprise grade capabilities, including enhanced security features. EDB Postgres Advanced Server provides compatibility with Oracle in the following areas:

• Oracle-specific and syntax-compatible database object types
• Oracle-specific data types
• Oracle-specific SQL extensions
• Oracle PL/SQL support as a built-in native procedural language
• Oracle data dictionary views (i.e., ALL, DBA, USER_ views)
• Oracle built-in PL/SQL packages

These features simplify migrations from Oracle. EDB Postgres Advanced Server compatibility features mean there are fewer things to convert because things that worked in Oracle will work in EDB Postgres Advanced Server. Although EDB Postgres Advanced Server doesn't fully implement all Oracle features, the compatibility with Oracle features that are implemented are some of the most commonly used constructs. In addition, EDB continues to add compatibility features with each major release. See Comparison of EDB Postgres Advanced Server with Oracle for information on compatibility between Oracle and EDB Postgres Advanced Server.

EDB Postgres Advanced Server is available to install on various Linux platforms and Windows versions. See Platform Compatibility on the EDB website for the full list of supported platforms. In addition, you can deploy EDB Postgres Advanced Server as a BigAnimal service and in Kubernetes environments using the EDB Postgres for Kubernetes operator.

Migration Portal

Migration Portal is a free online service provided by EDB to assess the compatibility of Oracle database schemas with EDB Postgres Advanced Server. After you create a Migration Portal project and upload a SQL-formatted data definition language (DDL) file for the Oracle database, the Migration Portal analyzes the compatibility of the DDL objects, applies repair handlers to fix a set of known incompatibilities, and reports the results. You can then review the results, inspect all the DDL objects, update the objects with errors to fix the issues, and reassess the project in the Migration Portal. When the DDL in the migration project is fully converted (i.e., made 100% compatible), the Migration Portal can help load that schema into an EDB Postgres Advanced Server instance.

Migration Toolkit

Migration Toolkit is a Java-based command-line tool that supports several different database migration use cases. However, in the context of an Oracle-to-EDB Postgres Advanced Server migration, its primary use is to perform snapshot type data migrations. It provides a rich set of command options to control how and which set of source information is loaded into the target database. A one-time trial period is available, but long-term use requires an EDB database subscription.

Replication Server

Replication Server is a Java-based application that provides both a graphical user interface and a command line interface. It provides a robust data platform for replicating data from non-PostgreSQL databases, including Oracle, to Postgres in single-master mode. It provides a change data capture (CDC) based option for migrating data from Oracle as an alternative to Migration Toolkit. This makes it especially useful when application downtime is a concern. Replication Server also supports replication from Postgres to Oracle. As a result, you can use it to establish a fallback option in case issues are encountered during the final stages of the migration.

A one-time trial period is available, but long-term use requires an EDB database subscription.

LiveCompare

LiveCompare is a command line utility that you can use to compare any number of databases, including Oracle and Postgres databases, to verify they are identical. After comparing the databases, the tool generates a comparison report, a list of differences, and data manipulation language (DML) SQL scripts. You can apply the DML and fix the inconsistencies in any of the databases.

LiveCompare supports several different use cases, but in the context of migrations you can use it to validate data that was migrated from one database to another. When run after migrating data from Oracle to your new Postgres database, its comparison reports help you to know if your data was fully migrated or if you have data inconsistencies to address. If you're migrating data back to Oracle for a fallback position, LiveCompare can make sure that the two databases have the same data.

You can use LiveCompare as part of a one-time trial period, but long-term use requires an EDB database subscription.

Oracle-compatible database connectors

EDB provides versions of PostgreSQL database drivers (connectors) that are enhanced to include compatibility with Oracle features to better support running applications originally built for Oracle to run on EDB Postgres Advanced Server. The following Oracle-compatible Postgres-based connectors are available:

• EDB JDBC Connector
• EDB ODBC Connector
• EDB .NET Connector

EDB also provides the EDB OCL Connector to allow Oracle Call Interface (OCI) based applications to run against EDB Postgres Advanced Server.

The table highlights some of the important compatibility features provided with the EDB enhanced connectors.

The EDB enhanced database connectors are available for use with EDB Postgres Advanced Server and require an EDB database subscription.

Other EDB software

EDB*Plus

EDB*Plus is a Java-based command line interface to EDB Postgres Advanced Server. EDB*Plus accepts SQL commands, SPL anonymous blocks, and EDB*Plus commands. EDB*Plus commands are compatible with Oracle SQL*Plus commands, so you can use the scripts you've already built for Oracle against the EDB Postgres Advanced Server database.

EDB*Loader

EDB*Loader is a high-performance bulk data loader that provides an interface compatible with Oracle databases for EDB Postgres Advanced Server. The EDB*Loader utility loads data from an input source—typically a file—into one or more tables using a subset of the parameters offered by Oracle SQL*Loader.

Database links and dblink_ora functions

Database links and dblink_ora functions are Postgres-extension-enabled capabilities that let you pull data from Oracle to Postgres by way of SQL queries and database function calls. You can also use these technologies to help migrate data from an Oracle database. Both of these methods use the Oracle Call Interface (OCI) to connect to Oracle. After connecting, use a SQL statement to select the data from the “linked” Oracle database and insert the data into the EDB Postgres Advanced Server database.

When deciding to use one of these two options, the database link option is usually the preferred one as it's easier to use. However, the dblink_ora functions help you to migrate some data types that the database link option doesn't, such as BLOB and CLOB data. Also, it supports using Oracle-specific functions in the queries that are executed in the Oracle database. This can provide some flexibility in extracting certain types of information, like JSON data embedded in CLOB or BLOB columns. Both of these capabilities are provided with EDB Postgres Advanced Server, but you need to set them up to use them.

EDB Postgres Enterprise Manager (PEM)

EDB Postgres Enterprise Manager is a comprehensive database monitoring and management application based on the open source pgAdmin 4 project, which is also supported by EDB. It provides a graphical user interface for inspecting, monitoring, managing, and querying your PostgreSQL and EDB Postgres Advanced Server databases and the systems on which they run. It provides many reports, dashboards, probes, and alerts that you can use to monitor the health and performance of your databases. PEM also includes many database administration and database development features. PEM's features are often used as part of a database migration to monitor and tune performance and to update database object definitions as needed. You can use PEM to monitor and manage the Postgres databases that are under an EDB database subscription.

Failover Manager

Failover Manager is a high-availability solution for managing Postgres database clusters, enabling high availability of primary-standby deployment architectures using streaming replication. Failover Manager provides a Postgres primary database node automatic failover to a standby database node in case of a software or hardware failure. In the context of a database migration, it isn't used to perform or test the migration. It's an operational tool that you can use in the post-migration system to address certain high-availability requirements. You can use Failover Manager to manage primary and standby databases that are under an EDB database subscription.

EDB Postgres Distributed

EDB Postgres Distributed is a PostgreSQL extension providing multi-master replication and data distribution with advanced conflict management, data-loss protection, and throughput up to five times faster than native logical replication. It enables distributed PostgreSQL clusters with high availability up to five 9s. For more extreme high availability scenarios, we suggest EDB Postgres Distributed versus EDB Failover Manager. Although it isn't a tool you use to perform an Oracle-to-EDB Postgres Advanced Server migration, if it's intended to be used in the post-migration system, you need to deploy it in the target system and test it to identify any application or database changes needed to support its use. EDB Postgres Distributed requires an EDB subscription to use.

Barman (Backup and Recovery Manager)

Barman is an open-source administration tool supported and maintained by EDB for remote backups and disaster recovery of Postgres servers. Having good backup and recovery procedures and tools is essential to the post-migration operating environment. In addition, these tools are often integrated into processes to support migration testing and related development efforts. In addition to Barman, EDB also supports pgBackRest, a popular Postgres backup and recovery tool, as part of its subscription offerings.

EDB professional services

EDB offers the following professional services packages that are often used in support of a database migration:

• Migration Assessment Service to perform a detailed analysis of your database. From that analysis, you'll learn how difficult the migration will be and how much time and effort it will take to perform the database migration.
• Enterprise Architecture Service to help define your operational, infrastructure, and environment requirements for the migrated system and recommend architectures to support your availability and scalability needs.
• Solution Design Service to recommend a Postgres database solution to meet your database strategy, service-level agreements, and infrastructure needs.
• Quick Deploy Service to help you understand how to deploy Postgres in a highly available, scalable, and secure way based on standard reference architectures and best practices.
• Performance Tuning Service to help tune your database to meet application needs.
• Monitoring Best Practices Service to help you establish good monitoring practices for operating and maintaining your post-migration databases.
• Backup Best Practices Service to help you establish good backup and recovery practices to ensure you never lose your data.
• Embedded Postgres Subject Matter Expert (SME) Service to augment your staff with Postgres expertise to guide you through the various stages of a migration and post-migration operations.
• Training to help your staff become proficient at operating and maintaining your post-migration Postgres databases

For more information about these and other EDB packaged services see the Consulting Services page.