Metadata Database Migration

Starting with the Incorta 5.2 on-premises release, Incorta is only supporting MySQL as its metadata database.

The following are the required steps that you need to perform to migrate your Oracle metadata database to MySQL metadata database.

Please contact Incorta Support to provide you with the needed files during this migration and general guidance.

Prerequisites

The following are prerequisites for the metadata database migration process from Oracle to MySQL.

• You have Python 3.x installed
• Stop all analytics and loader services
• You have one or multiple Oracle database .sql dump files that were created using Oracle SQL Developer. For more information, refer to Create Database Dump File.
  Warning

  Currently, Incorta only supports .sql dump files generated using Oracle SQL Developer. It is not recommended to export SQL Views in the dump file.

• You have MySQL 8 installed
• A user with access to the MySQL database. For more information, refer to Database Requirements.
• The MySQL database is empty
• Disable the two SQL modes "NO_ZERO_DATE" and "NO_ZERO_IN_DATE" on the MySQL database

If you have an environment with multiple clusters, please contact Incorta Support.

Migration Steps

Before starting the migration process, you must make sure that you have the prerequisites fulfilled.

• Open the command line interface (CLI)
• Change the directory to where the migrations scripts are located using the cd command

Step 1 - Run the "4.convert_oracle_metadata_to_mysql.py" script

• Run the script using this command python3 4.convert_oracle_metadata_to_mysql.py
  Note

  The input text for the following is case sensitive.

• Provide the absolute path for the Oracle database .sql dump file, provided the dump file is on the same machine
• Enter Enter "1" if the oracle dump is in a single file or enter "2" if the sump is over multiple files
• Provide the absolute path for the Oracle database dump in case of a single file or the absolute path for the whole directory in case of multiple files
• Provide the name of the source Oracle dump file
• Provide the name of the target MySQL schema
• Make sure that the script completed successfully and generated a new file called 5.mysql_metadata_generated_dump.sql

Step 2 - Run the "5.mysql_metadata_generated_dump.sql" script

• Connect to the MySQL database using the command mysql -u <USERNAME> -p<PASSWORD>
• Run the following command to capture the output to a file tee log.out
• Run the SQL script source 5.mysql_metadata_generated_dump.sql
• Review log.out and make sure the script is completed without any errors before proceeding to the next step

Step 3 - Run the "6_incorta.oracle.deprecation.jar" jar

• Run this jar file using Java version 1.8 or higher from the same machine that has both Oracle and MySQL databases
• In the "db.properties" file, replace the placeholders for connection string, username, and password of both Oracle and MySQL properties with the actual values
• Run the jar using this command: java -jar 6_incorta.oracle.deprecation.jar
  Note

  The Oracle and MySQL drivers are packaged with the above jar file and do not require independent instillation

• Enter "yes" to enable "Stop on First Error" or leave blank to disable it
• Upon successful completion the following message will appear INFO : Congrats! Migration of BLOB objects is done successfully.
• In the case of an error, the following message will appear on script completion: WARN : Migration of BLOB objects is done with some errors. Please check the log for more details.
  Note

  You can find the logs and execution details in the log file: blob_migration.log in the same directory of the jar file.

• You must complete this step without errors before proceeding to the next step

Step 4 - Run the "7.modify_incorta_conf.py" script

• Stop all the services and stop the CMC
• According to the type of installation, do the following:

• Ensure that the script completes successfully
• Make sure the node agent is running for all the service nodes
• Start the CMC
• In the CMC, update the database connection string, username, and password
• Start the analytics and loader services

Step 5 - Update the database connection using the CMC

• Ensure the node agent is running on all nodes
• Start the CMC
• In the CMC, edit the cluster details and update the database connection string, username, and password
• Start the analytics and loader services

Rollback Steps

For Steps 1, 2, and 3, if you need to perform a rollback, drop the created MySQL database and create a new empty one. These steps do not change the Oracle database nor the Incorta configurations.

For Step 4, if you need to rollback, you must restore the backup files taken of the configuration files changed by the executed script.

You can find the original files at the following directories:

/<Incorta_Home>/cmc/cmcData/clusters/<Cluster_GUID>.xml

/<Incorta_Home>/IncortaNode/services/<Service_GUID>/conf/catalina.properties

You can find the backup files in the following directories:

/<Incorta_Home>/cmc/<Cluster_GUID>.xml_bkp_<timestamp>

/<Incorta_Home>/IncortaNode/services/<Service_GUID>/catalina.properties_bkp_<timestamp>

Additional Information

Create Database Dump File

The following steps guide you through exporting a dump file using Oracle SQL Developer.

• Using the Oracle SQL Developer, select Tools > Database Export
• Unselect Export DDL
• Enter or Browse to the directory where you will save the dump file
• Select Next

• Select Next, and then Finish