Chief Product Officer, Ispirer Systems
It's no secret that database migration is greatly simplified if you use automatic migration tools. The market today offers a huge variety of different 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 Postgres. Which tool brings more benefits: free or commercial? We will try to answer this question by comparing two tools - Ora2Pg and Ispirer Toolkit on the example of Oracle to PostgreSQL migration.
In this article, we will make a concise overview of two powerful database migration tools, Ispirer Toolkit and Ora2pg. Ispirer Toolkit is a comprehensive solution designed to facilitate heterogeneous database migration and cross-platform application migration. It excels in its ability to seamlessly migrate the entire database schema, encompassing essential components such as data, tables, stored procedures, functions, triggers, views, and more.
Ora2pg is an open-source tool that specializes in migrating Oracle databases to PostgreSQL. It provides a set of features and functionalities to convert Oracle database schemas, data, and sql objects into PostgreSQL-compatible formats. To gain a deeper understanding of its capabilities, we will delve into a detailed examination of Ispirer Toolkit and Ora2pg to draw a meaningful comparison.
Before we proceed with the comparison, let's analyze the main features of migration from Oracle to PostgreSQL.
Oracle to PostgreSQL: features and pitfalls
Migrating an Oracle database 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 encompass a vast array of functions. However, when migrating to PostgreSQL, a significant challenge arises: many of these Oracle packages lack direct equivalents in PostgreSQL. In such cases, moving the logic to the application layer emerges as one of the ways to handle the challenge.
- Dynamic SQL. Automatic migration encounters its greatest challenges in this aspect, particularly when dealing with statements that are collected from multiple components with the help of conditional operators. To dive deeper in the topic, read our article "Dynamic SQL Conversion".
Users should not anticipate the outcome with absolute certainty when using any conversion tool. The primary objective in utilizing such a tool is to streamline the process, ultimately saving time and minimizing manual effort. This raises the question of the level of automation achieved, which can range from as low as 30% to as high as 60% or 90%. As you can imagine, such a drastic difference in automation rate influences the effort required for migration.
Basically the migration process from Oracle to PostgreSQL contains the following steps:
- Assessment. A complete database assessment should include analyzing the sql objects, their size, and estimating the time needed for migration. To facilitate manual evaluation, we recommend using tools that will do the necessary examination for you automatically. The perfect example of such a helping hand is Ispirer Assessment Toolkit.
- Schema Conversion. The next step is to select the right schema and convert it to PostgreSQL. This can be a challenging and time-consuming process due to the heterogeneous structure and data types of the two databases.
- Performance and Functional Testing. Performance testing is of paramount importance since transactions built into Oracle are different in PostgreSQL. Proper tuning at the app, driver, and database level can help you find and fix differences. You should also perform functional testing to ensure that the migrated database works as expected.
- Data Migration. The actual Oracle to PostgreSQL migration may take time on account of them being heterogeneous databases. Broadly three data migration approaches are available: dump and restore, logical replication, and physical replication.
- Post-Migration Optimization. The optimization includes fine-tuning and enhancing the performance of a database system after a migration has taken place. Ensuring the database runs well and meets performance expectations is a crucial step for the application and its users.
Database Assessment Battle: Ora2pg vs. Ispirer Toolkit
A crucial stage in the journey from Oracle to PostgreSQL involves a comprehensive database assessment. Providing useful information about the work, resources, and a smooth transition is important for a successful migration. Let's compare Ispirer Assessment Toolkit and Ora2pg tools to understand what they offer.
Ispirer Assessment Toolkit can accurately identify the work needed for a successful migration. Whether your database is extensive or modest in scale, this tool can help you gauge the complexity of the project. Having a blueprint for your migration efforts helps you navigate the path ahead with confidence.
This assessment can determine the needs of your development team. It can also predict how much the Ispirer Toolkit can automate the migration of your Oracle database to PostgreSQL. This means that you can estimate how much of the migration process can be automated, saving time and effort.
Ispirer Assessment Toolkit carefully checks your Oracle database and gives you a detailed report to help you make informed decisions. This report goes beyond surface-level observations, diving into the intricacies of your database structure, data types, and SQL code. The report provides a ton of valuable insights for migrating a database with Ispirer Toolkit.
In addition, the Ispirer Assessment Toolkit estimates the time required for migration. This estimation is based on a number of analysis criteria including the volume of dynamic sql, the overall database complexity, etc.
But the advantages don't end there. Ispirer Assessment Toolkit includes an estimate for the service, expressed in person-months. This estimate serves as the lower limit for labor costs your team will need to invest in the migration. It's a valuable reference point that's grounded in Ispirer's extensive experience in the field of database migration.
On the other hand, Ora2pg takes a somewhat different approach to assessment. While Ispirer places a strong emphasis on 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.
Assessment provided by Ora2pg basically leans towards a rough estimate of workdays. Ora2pg 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 particularly valuable, especially for larger projects, as it provides an exact estimate of migration time and potential risks.
Ispirer Toolkit and Ora2pg: Support and Implementation
Help during the migration is important for a successful switch from Oracle to PostgreSQL. Ispirer provides support throughout your migration journey, with guidance and assistance that distinguishes it from Ora2pg. This support not only enhances the migration experience but also helps organizations save time and resources that might otherwise be spent on resolving issues.
Ispirer support is quick and individualized. Each client is helped by a support agent who connects your team with Ispirer migration experts. This agent can help with concerns, give guidance, and solve issues that come up during the migration. Ispirer support includes online meetings, phone calls or email correspondence. Support experts have years of experience in selecting the optimal Ispirer Toolkit settings for efficient Oracle to PostgreSQL migration.
Ora2pg doesn't help with migration, so users must handle it on their own without expert guidance.
Customizing Ispirer Toolkit and Ora2pg
So, what is customization? Customization of Ispirer Toolkit means modifying the core of the tool, which consists of a set of conversion rules. There are two primary ways to customize the tool:
- Adding new conversion rules. Ispirer Toolkit allows to incorporate 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 alter the existing conversion rules. This level of flexibility ensures that the migration process is highly tailored to the unique requirements of the project.
Crucially, Ispirer offers customization at an affordable price point. This cost-effective approach means that even smaller projects can benefit from a highly customized migration solution that fits their precise needs. Such an adaptability of Ispirer Toolkit makes it a compelling choice for businesses of varying sizes and migration complexities.
On the other hand, Ora2pg is an open-source tool and in theory, it can be customized as well. However, there are significant differences when compared to 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 level of effort and expertise required to modify the code may outweigh the benefits.
Ultimately, opting for customization of the Ispirer Toolkit proves to be a more cost-effective solution when compared to attempting to customize Ora2pg. A customized Ispirer Toolkit saves a ton of money and a lot of migration time. At the same time, the customization of Ora2pg is extremely complicated and expensive, so it is basically unavailable for optimizing for a particular migration project.
The tools usability
It should be noted that the tools for automated migration can operate in two ways - connected to a database or without connection. By default, Ispirer Toolkit operates with the 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 similarly to Google translate.
First of all, Ora2pg operates in a Command Line Mode only. For its part, Ispirer Toolkit can migrate both in CLM and through user-friendly GUI, which makes migration easier.
Moreover, 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.
Conversion 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.
Below you will find some conversion results that clearly show the superiority of Ispirer Toolkit over the free Ora2pg in migration 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.
When utilizing ORA2PG with the script option, it typically excludes partitioned tables from the migration process. In cases when the tool does migrate them while connected to the source database, it only specifies the partitioning method (e.g., range, list, hash), but it doesn't create the partitioned tables themselves. For instance, in the case of partitioning by hash, it also 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
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, 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. Our tool is able to convert the BULK COLLECT construction. The Ispirer tool supports 2 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 rewritten manually.
5. Hierarchical query. In PostgreSQL, hierarchical queries are implemented based on the standard SQL 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 also creates an additional function swf_array_length. Collection methods, such as first, last, extend, are also processed correctly and converted to the corresponding PostgreSQL constructures. Collection type ‘Nested tables’ is not converted by our tool yet.
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, they remain as is.
The code converted 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 is able to convert functions from the DBMS_SQL package to the corresponding PostgreSQL commands. For example, opening and closing the cursor, parsing, fetching variables and executing. ORA2PG does not convert these functions at all. Such functions should be converted manually.
Conclusion: A Clear Winner in Oracle to PostgreSQL Migration
In the Oracle to PostgreSQL migration landscape, it's evident that Ispirer Toolkit outshines Ora2pg. Its superior conversion quality and customization options make it the preferred solution for a wide range of projects.
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? Let's compare the expenses associated with project migration using both tools.
Suppose we need to convert an Oracle database with 100,000 lines of code (LOC).
If we consider the complexity of the SQL code, assuming that Ora2pg offers a 60% level of automation, Ispirer Toolkit, on the other hand, provides 90% of automation. Consequently, if you use Ora2pg, you will have to manually migrate 40K of LOC. At the same time, if you use Ispirer Toolkit, you will need to manually migrate 10K of LOC.
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:
Using Ora2pg: 40K / 500 = 80 days
80 days * 8h = 640 h
640 h * 50$ = 32K $
Using Ispirer Toolkit: 10K / 500 = 20 days
20 days * 8h = 160 h
160 h * 50$ = 8K $
Well, the figures speak for themselves. So even taking into account the cost of the license and customization, Ispirer Toolkit is more than 15k cheaper than Ora2pg. Hopefully, it's now evident that a catchy headline can not only grab attention but actually tell the truth.
To conclude, for a successful Oracle to PostgreSQL migration, Ispirer Toolkit stands as the clear winner. It offers a flexible and top-notch solution for projects of any size, especially beneficial for large-scale undertakings. Choose Ispirer Toolkit for a seamless and effective transition.