More cost-effective migration than with free Ora2Pg: How is that?

Alexander Kirpichny

Product Owner, Ispirer Systems

It's no secret that database migration is greatly simplified if you use automatic migration tools. Today's market offers various solutions, including open-source, ETL tools, replication, cloud-based, and others. In this article, we want to solve the age-old dilemma of many organizations planning migration from Oracle to PostgreSQL database. Which tool brings more benefits: free or commercial? We will try to answer this question by comparing Ora2Pg and Ispirer Toolkit, using the example of Oracle to PostgreSQL migration.

Without further ado, let’s start.

What is Ora2Pg?

Ora2pg is an open-source tool that specializes in migrating Oracle databases to PostgreSQL. It provides features and functionalities to convert Oracle database schemas, data, and SQL objects into PL/pgSQL (PostgreSQL-compatible formats). To better understand its capabilities, we will examine Ispirer Toolkit and Ora2pg in detail to draw a meaningful comparison.

What is Ispirer Toolkit?

Ispirer Toolkit is a comprehensive solution that facilitates heterogeneous database and cross-platform application migration. It seamlessly migrates the entire database schema, encompassing essential components such as data, tables, stored procedures, functions, triggers, views, and more. It supports migration from various RDBMS, including Informix, Oracle, SQL Server, MySQL, PostgreSQL, Teradata, Netezza, Interbase, Firebird, Singlestore, MongoDB, Snowflake, etc. The tool also enables migration to cloud databases, such as GCP, Microsoft Azure, and Amazon RDS.

Migrate Oracle to PostgreSQL: features

Migrating Oracle to PostgreSQL can be a complex and challenging process due to the differences in features, architecture, and SQL dialects between the two database management systems. Here are some of the main challenges you might encounter:

• Data type mapping. Oracle and PostgreSQL have some data types that are similar but not identical. Ensuring that data types are mapped correctly during the migration process is crucial. For example, Oracle's NUMBER type doesn't map directly to PostgreSQL's NUMERIC or INTEGER types.
• Oracle native packages. Built-in packages are inherent components of Oracle databases, each designed for specific tasks and encompassing many functions. However, when migrating to PostgreSQL, a significant challenge arises: many of these Oracle packages lack direct equivalents in PostgreSQL. Moving the logic to the application layer emerges as one way to handle the challenge.
• Dynamic SQL. Automatic migration encounters its most significant challenges in this aspect, mainly when dealing with statements collected from multiple components with the help of conditional operators. To dive deeper into the topic, read our article Dynamic SQL Conversion.

How to migrate Oracle to PostgreSQL?

When choosing automated database migration, users should know that different data migration tools can provide different automation rates. The automation rate can vary from 30% to 99%, depending on the initial database and its complexity.

The tools for database migration can automate several migration stages or provide a fully automated process from assessment to cutover. As you can imagine, such a drastic difference in automation rate influences the manual effort required for migration.

The migration process from Oracle to PostgreSQL includes the following stages:

• Assessment. A thorough database assessment should analyze the SQL objects in the database, their volume, and an estimate of the time required for migration. To facilitate manual evaluation, we recommend using tools that will automatically perform the necessary analysis. The perfect example of such a helping hand is Ispirer Assessment Toolkit.
• Schema Conversion. The next step is to select the correct schema and convert it to PostgreSQL. Due to the heterogeneous structure and data types of the two databases, this can be challenging and time-consuming.
• Performance and Functional Testing. Performance testing is of paramount importance since transactions built into Oracle are different in PostgreSQL. Proper app, driver, and database tuning can help you find and fix differences. Functional testing should also be done to ensure that the migrated database works as expected.
• Data Migration. The actual Oracle to PostgreSQL migration may take time because they are heterogeneous databases. Three data migration approaches are available: dump and restore, logical replication, and physical replication.
• Post-Migration Optimization. Optimizing the system is often required right after the migration process. This step is one of the most complex, time-consuming, and challenging to automate. AI can be a great helping hand here. It can potentially resolve all the optimization issues, including error handling, code refactoring, conversion enhancement, and other tasks. By the way, Ispirer Toolkit is already AI-powered with Gemini and assists at this very step. Along with simplifying manual efforts for optimization, AI-Assistant saves 80% of the time for optimization.

Ora2Pg vs. Ispirer Toolkit - What to Choose for Migration?

Now, let's compare two tools for automatic database migration based on key features.

Database Assessment

Database assessment is the bedrock of any migration project. It offers valuable insights into the scope of the work, resource allocation, and, ultimately, a seamless migration. Let's compare Ora2Pg and Ispirer Toolkit and see what they offer.

Ispirer Assessment Wizard provides a detailed examination of the source database. It offers the following insights:

• The project complexity. One standout feature of Ispirer Assessment Toolkit is its ability to accurately pinpoint the scope of work needed to ensure a successful migration. Whether your database is huge or small in scale, this tool can help you gauge the project's complexity. It's like having a blueprint for your migration efforts, helping you confidently navigate the path.
• Automation rate. Assessment Wizard can estimate the potential automation rate for your Oracle to PostgreSQL migration project with Ispirer Toolkit. This means you get an estimate of how much of the migration process can be automated, saving time and effort.
• Migration time estimate. In addition, Ispirer Assessment Toolkit estimates the time required for migration. This estimation is based on several analysis criteria, including the volume of dynamic SQL and the overall database complexity.
• Service estimate. Assessment Toolkit includes an estimate for the service, expressed in person-months. This estimate is the lower limit for labor costs your team will need to invest in the migration. It's a valuable reference point grounded in Ispirer's extensive experience in the field of database migration.
• An extensive report with all the details about the database. Assessment Wizard meticulously examines your Oracle database, providing a detailed report as the cornerstone for informed decision-making. This report goes beyond surface-level observations, diving into the intricacies of your database structure, data types, and pl/SQL. The report provides many valuable insights for migrating a database with the Ispirer Toolkit.

On the other hand, Ora2pg takes a somewhat different approach to assessment. While Ispirer strongly emphasizes a detailed examination of the Oracle database intricacies, Ora2pg assesses workdays primarily based on object types and sizes. In this assessment, the complexity of the code is given less weight.

The assessment provided by Ora2pg is basic and focuses on estimating workdays. It provides a quick overview of the project's volume, giving you an idea of the effort required.

After comparing both assessment approaches, it became clear that Ispirer Assessment Toolkit excels in conducting a more detailed, faster, and higher-quality database examination. This precision is precious, especially for larger projects, as it provides an exact estimate of migration time and potential risks.

Expert Support During Migration

Support during migration ensures a smooth and successful transition from Oracle to PostgreSQL. Ispirer offers a support model designed to assist in your migration journey, offering guidance and assistance that sets it apart from Ora2pg. This support enhances the migration experience and helps organizations save time and resources that might otherwise be spent on issue resolution.

Ispirer support is fast and personalized, which means that every client is supported by a support agent who acts as a bridge between your team and the Ispirer migration experts. This support agent can assist in addressing any concerns, offering guidance, and resolving issues that may arise during the migration process. Ispirer support includes online meetings, phone calls, or email correspondence. Support experts have years of experience selecting the optimal Ispirer Toolkit settings to migrate Oracle to the PostgreSQL database.

In contrast, Ora2pg doesn't provide any support during the migration process, which means that users of this tool can approach the migration journey independently and overcome any challenges and uncertainties that arise without the benefit of expert guidance.

Customization options

So, what is customization? Customization of Ispirer Toolkit means modifying the tool's core, consisting of a set of conversion rules. There are two primary ways to customize the tool:

• Adding new conversion rules. Ispirer Toolkit allows the incorporation of new conversion rules into the system. This capability enables organizations to fine-tune the migration process to align with their specific database schema and application logic.
• Altering existing rules. In addition to adding new rules, it is also possible to modify the existing conversion rules. This flexibility ensures that the migration process is highly tailored to the project's unique requirements.

Crucially, Ispirer offers customization at an affordable price point. This cost-effective approach means that even smaller projects can benefit from a customized migration solution that fits their precise needs. The Ispirer Toolkit's adaptability makes it a compelling choice for businesses of varying sizes and migration complexities.

On the other hand, Ora2pg is an open-source tool that can, in theory, be customized. However, there are significant differences between it and the Ispirer Toolkit.

Customizing Ora2pg involves making changes to its underlying code. It means that if you don't have a team with expertise in developing Ora2pg, the effort required to customize Ora2pg may not be justifiable. The effort and expertise needed to modify the code may outweigh the benefits.

Ultimately, customizing Ispirer Toolkit is a more cost-effective solution than customizing Ora2pg. A customized Ispirer Toolkit saves a lot of money and migration time. At the same time, customizing Ora2pg is highly complicated and expensive, so it is unavailable for optimizing a particular migration project.

The tools usability

At first, Ora2pg operates only in command line mode. Alternatively, Ispirer Toolkit can migrate both in CLM and through the graphic user interface, which makes migration easier.

Ispirer Toolkit migration can be performed in two ways. The first method involves exporting objects directly from the database using a preconfigured ODBC connection. The second method implies script conversion.

It should be noted that the tools for automated migration can operate in two ways - connected to a database or without connection. Ispirer Toolkit operates with a connection to a database. It facilitates the quality of the conversion by analyzing the relationships between column types and relations between SQL objects. Conversely, Ora2pg operates database migration without connection to the database, is of lower quality, and works on the principle of Google Translate.

Migration quality: Ispirer Toolkit vs. Ora2pg

To ensure a fair assessment of the conversion quality, we utilized both tools to convert a representative Oracle database with typical complexity into PostgreSQL. But before analyzing the conversion results, let us explain Ispirer Toolkit internal structure.

Below, you will find some conversion results that clearly show the superiority of Ispirer Toolkit over the free Ora2pg in conversion quality. Let's now proceed with the comparison.

1. Partitioned tables. When utilizing the Ispirer Toolkit, the migration of partitioned tables is seamlessly executed during both connected migration and script-based conversion. This comprehensive process ensures the creation of all partitioned tables. Utilizing Ora2Pg with the script option typically excludes partitioned tables from the migration process. When the tool migrates them while connected to the source database, it only specifies the partitioning method (e.g., range, list, hash). Still, it doesn't create the partitioned tables themselves. For instance, partitioning by hash doesn't specify the partitioning modules and the remainder for each section. These aspects will need to be addressed through manual intervention.

2. PACKAGE conversion. When converting packages, Ispirer Toolkit takes special care to handle variables and constants declared within the package. It does this by generating additional functions that enable the correct utilization of these variables and constants after conversion.
In contrast, ORA2PG takes a different approach. ORA2PG does not create additional functions, but implements access to parameters (constants) with errors through the use of a function to manage the configuration current_settings() or set_config().
These global variables are then passed as arguments in the form of ., without actually creating these variables.
However, this approach leads to issues when calling procedures or functions since there might not be a parameter with that specific name, and the MISSING_OK parameter with a value of TRUE is not additionally passed. Consequently, manual creation of additional objects or adjustments to procedures and functions may be necessary to rectify these issues.

3. PACKAGE with INITIALIZATION section conversion. When converting packages containing an initialization block, Ispirer Toolkit creates an array for global variables and an initialization function, where it places the block code, as well as getters and setters.

Ora2Pg tries to put this block inside one of the internal functions, using functions to manage the current_setting configuration, passing as an argument global variables that were not previously created.

The converted code also contains syntax errors like a started but unfinished block (there is a BEGIN, but there is no END). Thus, serious manual editing is required after conversion.

4. Bulk collect. Ispirer Toolkit can convert the BULK COLLECT construction. The Ispirer tool supports two options: through arrays and temporary tables. The user can choose the appropriate option using the TABLE_TYPE_CONVERSION option. ORA2PG does not convert this construction in any way, leaving it as it is, it will have to be completely written manually.

5. Hierarchical query. In PostgreSQL, hierarchical queries are implemented based on the standard PL/pgSQL construction WITH. Both tools convert a hierarchical query using WITH, but ORA2PG converts hierarchical queries without adding aliases to subqueries that are created, which entails the appearance of errors like:" ERROR: column reference "parent_id" is ambiguous". It means that more than one element has been encountered and the compiler cannot recognize who they relate to. Thus, it is necessary to manually add the missing aliases.

6. Collections. When migrating collections of the associative array type, Ispirer Toolkit processes them correctly, turning them into arrays and creating an additional function swf_array_length. Collection methods, such as first, last, and extend, are also processed correctly and converted to the corresponding PL/pgSQL constructures.
Our tool has not yet converted the collection type 'Nested tables'. ORA2PG is only trying to do something with an ARRAY type collection: it creates a custom type, but it does not process methods in any way. The associative array and nested tables are not processed at all, and they remain as is. Converting the code in this way requires a global rework.

7. XMLmethods. Some methods that are correctly migrated by our tool are not migrated by ORA2PG, i.e., the code remains unchanged—for example, the EXTRACT method.

8. Functions from DMBS_SQL package. Ispirer Toolkit can convert functions from the DBMS_SQL package to the corresponding PostgreSQL commands, such as opening and closing the cursor, parsing, fetching variables, and executing. ORA2PG does not convert these functions at all. Such functions should be converted manually.

Ora2Pg or Ispirer Toolkit: Who's the winner?

In the Oracle to PostgreSQL migration landscape, Ispirer Toolkit outshines Ora2pg. Its superior conversion quality and customization options make it the preferred solution for migrating Oracle to PostgreSQL.

A key takeaway from this comparison is the direct correlation between project size and the benefits derived from using Ispirer Toolkit. The larger the project, the more compelling the case for Ispirer Toolkit becomes. Its ability to handle complex migrations with precision and efficiency sets it apart.

Words can convey plenty, but what do numbers reveal? Using both tools, let's compare the expenses associated with project migration.

Suppose we need to convert an Oracle database with 100,000 lines of code.

If we consider the complexity of the PL/SQL and PL/pgSQL code, assuming that Ora2pg offers a 60% level of automation, Ispirer Toolkit, on the other hand, provides 90% automation. Consequently, if you use Ora2pg, you must manually migrate 40K of LOc. At the same time, if you use Ispirer Toolkit, you will need to migrate 10K of LOC manually.

Let us calculate the cost of manual migration. On average, a developer migrates 500 lines of code per day with an average wage of $50 per hour, then we get the following calculations:

Well, the figures speak for themselves. So, even considering the cost of the license and customization, the difference is more than 15k. Hopefully, it's now evident that a catchy headline can grab attention and tell the truth.

For a successful Oracle to PostgreSQL migration, Ispirer Toolkit is the clear winner. It provides a high-quality, customizable solution that caters to projects of all sizes, with an even greater advantage for large-scale endeavors. Check out the Ispirer Toolkit pricing to select the most appropriate plan for your migration needs.