The CYBERTEC Migrator is a free tool that allows you to easily and quickly migrate from Oracle to PostgreSQL. The CYBERTEC Migrator container can be downloaded at no cost to you. It is a good way for people to move from Oracle to PostgreSQL as speedily as possible. And now, the latest release has a brand new module which makes migration cost assessment a whole lot easier.
What is the CYBERTEC Migrator?
The CYBERTEC Migrator is a tool to help people to easily migrate to PostgreSQL. We implemented and released this tool to give people a visual alternative to ora2pg. While ora2pg is powerful, for many migrations it has proven to be less than ideal. Therefore, we decided to implement a visual alternative which is currently available– to free people from the claws of Oracle.
How does it work? The migrator clones the Oracle system catalog and does an in-depth analysis of its data. It then re-compiles this information to PostgreSQL objects during the migration. Note that the entire definition of the migration is simply a configuration.
Oracle to PostgreSQL migration: smart cost estimation
The latest version, released at the beginning of December, contains a new module which makes cost assessment a lot easier. The tool is now able to provide you with a good estimate indicating how much human labor is needed to switch to PostgreSQL. How does it work?
First of all, let’s define which aspects of migration result in effort spent:
- Table definitions
- Data and content
- Indexes and constraints
- PL/SQL code
The migrator will analyze your data structure and see what creates effort and what does not. In general, the rule is that tables and data do not cause much work, unless binary data and special data types which need a lot of attention are found. The CYBERTEC Migrator will handle those cases separately and assign reasonable costs.
One thing that might necessitate substantial effort is the migration of views. Remember, a view is a named query which replaces a complex query. In some cases, we have to rewrite the SQL statement to work with PostgreSQL. So the effort depends on the complexity of the SQL referred to in the views.
Indexes and constraints are calculated using constants. That part is really straightforward.
Migrating PL/SQL code to PostgreSQL
Usually, a large chunk of the effort to migrate a database from Oracle to PostgreSQL is used to take care of both triggers and PL/SQL functions and procedures. The CYBERTEC Migrator will inspect your code and see if anything “tricky” can be found in this regard. Insights are particularly valuable in these areas, since not everything from the Oracle side can be migrated directly.
The amount of time needed to migrate is determined based on the 6 factors named in the list above (table definitions, views…etc).
Interpreting migration cost estimates
The total cost is calculated in units created to give you an idea of how long a migration will take. Per default, one cost unit equals 5 minutes of work. This value can be adjusted within a certain range to meet your expectations for your respective work speed.
Let’s assume a total cost of 1000 with each cost unit equaling 5 minutes of work. This would translate into an estimated migration time of around 10 and a half person days, where one person day corresponds to 8 hours of work.
It’s worth noting that the figures in the assessment results are only a rough estimate.
We will continue to tweak and improve these estimates as we collect experience and introduce new technologies, such as a PL/SQL parser.
How to use the CYBERTEC Migrator migration cost assessment module
The migration cost assessment tool is structured by sections:
- A header providing basic information
- A bar chart visualizing the cost distribution
- An overview table with order and filter options
- A details chart referencing a selected object
The header conveys key data including the migration level (e.g. the difficulty), object count, total cost and the person days of the migration. Clicking on the info icon will show a description for the respective data.
Costs by Database Object Types (Bar Chart)
The bar chart groups the cost of each object type to give you an idea of how the total cost of a migration is distributed. Utilizing this chart is an easy way to quickly identify possible outliers – which might be responsible for a major part of the migration cost.
Clicking on any object type will display the cost distribution among all objects of this type. Again, this can be used to determine whether the cost of a certain type, e.g. trigger, is distributed evenly among its objects, or if there are outliers again causing most of the cost– and therefore migration time.
Each individual object may be hovered over to reveal its name along with its absolute and relative cost. Clicking on an object will highlight it in the overview table and the details chart. See the sections “Cost Overview” and “Cost Details” for more information.
The object count for a certain type might go well into the thousands, depending on the database structure. Each object is represented with its own bar on the x-axis. Per default, the range of displayed objects is set to 100 percent, meaning that all objects are displayed in the chart. This will give you an initial idea of the cost distribution among this type, but can make it difficult, or even outright impossible, to identify individual objects.
A number of ways to limit the range of objects displayed may be used
The first option is the toolbox in the top right corner. Selecting the zoom option will allow you to mark an area directly in the chart. Doing so will limit the range of objects displayed to just this area. The zoom can always be reset using the reset button of the toolbox.
Another variant is to simply scroll inside the chart, which will either decrease or increase the display range based on the direction of the scroll.
The final way to make individual objects more visible is to utilize the slider at the bottom. Here you can independently adjust the start and the end of the range.
CYBERTEC Migrator: Cost Overview
This table in the migration cost assessment displays each object as a row and allows you to select, filter and order objects. Per default, the order is set to cost descending.
For editable objects, a link icon will be displayed at the end of the row which allows you to jump directly to the respective editor to tweak values or exclude the object from the migration altogether. Going back to the overview table will restore the previous selection as well as filter and order options.
You may adjust the number of rows to display either 5, 10 or 25 rows per page depending on your preference and screen resolution. Furthermore, like in the bar chart, each object may be selected by clicking on it. Doing so will display its detailed cost composition in the donut chart next to the overview table – see “Cost Details” for more information.
The donut chart shows a detailed cost composition for the object selected. Per default, the most expensive object (regardless of its type) will be displayed. Each slice in the chart may be hovered over to display its absolute and relative cost. Alternatively, you can hover above the name to retrieve this information.
Migrating from Oracle to PostgreSQL is a big cost-saver and prevents a lot of headaches. In addition to that, we needed a good follow-up technology for ora2pg. That’s why we decided to implement the CYBERTEC Migrator. We really wanted to make life easier for people. If you want to get started with migrations directly, consider checking out our Meet the Migrator post.