Oracle to PostgreSQL on Azure Online Presentation


Demo presentation text:

Hello!

You can use Ispirer MnMTK 2020 to automatically migrate any popular relational database management system. In this video, we’ll demonstrate how to implement this task, performing migration from Oracle to PostgreSQL on Azure.

For this demo, we prepared a database in Oracle Server. You can see its contents open in SQL Developer. For migration purposes we’ve prepared an empty database on PostgreSQL on the Azure server called ORA2PGSQL, to which objects from Oracle will be migrated.

Let´s start the demo with the Ispirer SQLWays Wizard. This is a GUI tool that is based on our core command line tool called sqlways.exe. Our command line tool performs the conversion of SQL code and data and generates all the files. And the SQLWays Wizard as a graphical user interface that makes the conversion process simple for the end user to perform.

So, the migration tool is the tool that can connect to the Oracle database using only the ODBC connection. Thus, you need to tune the ODBC DSN for Oracle and specify it on the “Choose a Source Database” page. You also need to specify a user and a password with the appropriate rights for making selections from the systems tables from Oracle.

On the “Choose a Target Database” page, you need to choose the target database from the list of supported databases. As you can see, the PostgreSQL database is specified here. Also, we need to provide other credentials, like server name, as a link to the server on Azure, database, user, and a password for the Postgres database for a support of definitions and data load using the native utilities of this database.

SQLWays depicts all the objects that it manages to extract from Oracle in a tree view on the “Specify Database Objects or Query” page. Here, you can select all the objects required for the migration.

After that, you can specify different options that help you customize Oracle to PostgreSQL conversion, such as an option that controls the schema name conversion or a data type conversion.

On the “Specify Export File Options” page, you need to provide the directory, which serves as an intermediate repository for the entire migration process and stores all the automatically generated files, like SQL files with the converted code, TXT files with the data, BAT, or SH files.

The next page is a “Summary” page and we can see a command line at the bottom, together with the summary information about the current conversion process. And the sqlways.exe tool – which is our core tool that performs the conversion and stores all the conversion rules – is used in this command line. This means you can schedule the conversion in a command line mode, if required. All you need to do is run the command line prepared by the tool.

All the Oracle to PostgreSQL on Azure migration process is performed in two steps – conversion and import.

During the conversion stage, the tool converts objects and generates all the required files to perform an import. And during import, all the converted objects will be loaded into PostgreSQL.

At this point, I would like to add that a high level of automatic conversion can be achieved for the Oracle to PostgreSQL migration direction. We have already had several dozen successful migration projects for this direction, and our main focus is SQL code conversion. And that is why a lot of conversion features are already supported by the tool out of the box, like cursors, loops, DML statements including MERGE statement, CTE constructions, exception handlers, and many others. If some cases are not converted by default, we can extend the tool by adding a new conversion rule and provide you with an extended version of the tool.

So, we can see that the migration has been completed and we can now proceed with the generated files. Both conversion and import processes have separate logging information.

For the Export process, sqlways.log files are provided with the information about the objects read from the Oracle database: their number, the number of rows of data and the size of the data per each table extracted, and number of lines of code (LOC) per each business logics objects. At the end of the export log file, the information summary for each object type and the total information for the entire database export are provided.

Similar information, but in a more readable format, is provided in the sqlways_report.html file. There is a summary table with comprehensive export status and statistics on each object type, and there are also tables with the list of objects and more detailed statistics for each object extracted from the Postgres database.

As for the import process, SQLWays generates a sqlways_imp.log file for importing into the PostgreSQL database that contains information about which object is being loaded and whether there are any issues during the import process (as we can see an error – if there is one).

Also, if you encounter an error during conversion or importation, please feel free to email us.

Now, let me show you an example of the function in Oracle and a converted procedure in the PostgreSQL database. Our function includes representative statements like an “if…then” construction, a “while loop,” a “raise exception statement,” DML statements, and cursor use.

Let’s call this function up from our Oracle database. As a result, we can see that it returned some data. Now I’m going to execute the same procedure in PostgreSQL on Azure and we can see that the data returned by the converted procedure is the same. So, the migrated procedure works correctly!

Also, if required, you can go to the export directory by clicking the “View Results” button and, in the opened window, open any SQL file. The commented source code and converted target that you can review will be located in this file.

This presentation has shown information on the tools that our company provides for database migration from Oracle to PostgreSQL on Azure.

To try out Ispirer MnMTK, you can request a demo license for the toolkit on our website – www dot ispirer dot com. A demo license key, download links, and a user guide will be sent to you via email.

Thank you for your attention.