Oracle to Amazon Redshift Online Presentation


Demo presentation text:

Hello,

This is the demo about the tools provided by Ispirer Systems for database or scripts migrations. All of them are graphical tools based on a command line tool called Ispirer Migration and Modernization Engine. This demo is particularly devoted to the database conversion from Oracle to Amazon Redshift.

To start with, I'll give you just a one-minute description of the Ispirer Migration and Modernization Toolkit 2015. Basically, there are four tools:

• Ispirer Migration and Modernization Engine – a command line tool that can be run in batch mode and can perform the automatic conversion of a database or database and application code contained in scripts.

• Ispirer SQLWays Wizard – a GUI tool designed particularly for the database migration. Works with a source database using the ODBC connection.

• Ispirer Migration Studio – a GUI tool oriented specifically to the conversion of a separate application or database script. Works with one script at a time.

• Ispirer Migration Commander – a GUI tool designed for the conversion of multiple files containing application or database code. Works with several scripts at a time.

For this demo, we have prepared an Oracle database with a list of objects. For the migration purposes we have also prepared an empty database on Amazon Redshift cluster.

Let’s convert this database using Ispirer SQLWays Wizard.

This is a tool that can connect to Oracle database using an ODBC connection. So, to migrate from Oracle to Amazon Redshift you need to tune the TNS Service Name for your Oracle database and then tune the ODBC connection for Oracle using this TNS. The ODBC connection should be specified on “Choose a Source Database” page. Also you need to specify a user and a password with the appropriate rights for making selections from system tables in Oracle.

On “Choose a target database” page you need to choose a target database from the list of the supported databases. As you can see, Amazon Redshift is specified here. Also you need to provide other credentials (like HOST, Port number, database name, a user and a password) for the Amazon Redshift database for a support of definitions and data load using native utilities of this database.

To be able to make an import into the Redshift database, we need to provide a path to the PSQL.EXE utility, which will load the definitions and data into Redshift. We can do it on Advanced options page.

To load the data into Redshift, we should load files with data into Amazon cloud, for example S3. We need to provide a path to such files in the cloud. We can do it using “Cloud path” text box. Also we need to provide appropriate values in “Access Key ID” and “Secret Access Key” text boxes to get full access to the data files in the cloud.

All the objects that Ispirer SQLWays Wizard manages to extract from the Oracle database are represented in a tree view on «Specify Database Objects» page. Here you can select all the objects required for the migration.

After that, you can specify different options which help you to customize Oracle to Redshift conversion. For example, the option, which controls schema name conversion, reserved words conversion, data types conversion, etc.

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

The whole process of the migration from Oracle to Redshift is performed in two steps: export and import. During the export process our tool extracts all the required information from a source database and generates conversion results. During the import process our tool loads the definitions and data into a target database. Before making an import to Redshift, we need to copy all the .txt files with data to Amazon cloud. Only after that we can run the import process. Let’s do it. I have selected all the .txt files with the data and now I’m uploading them to Amazon cloud S3. Now we can run the import process. We can go to Import Log tab to check the status of the import process. As you can see, the import has been successful. For both export and import processes our tool generates log files.

For the export process, a sqlways.log file is provided. It contains the information about the objects read from the source database, their number, the number of rows of the data and the size of the data per each table extracted. At the end of the export log file there is summary information per each object type.

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

As to the import process, Ispirer SQLWays Wizard generates a sqlways_imp.log file that contains information about objects, which were loaded and whether there were any issues during the import process.

Let’s check whether the data was loaded correctly. Now, let me compare some queries in Oracle with the same queries in Amazon Redshift. As we can see, the results are identical. Let’s check the second query in Oracle. And the same query in Redshift. Again the results are identical. And the last query. The results are identical again. That means that the data has been successfully migrated.

This is all information about the tools that our company provides for database migration from Oracle to Amazon Redshift.

It is just a review of the tools and the features that they support, so if you need additional information please contact us, and we will be glad to assist you in your migration projects.

Thank you very much for your attention. Bye.