The solution

The client partnered with Virtusa to leverage the benefits of comprehensive AWS autoscaling instances. We deployed three agile teams to work parallelly for schema conversion, data migration, post-migration validations, and reconciliations.

Oracle to Amazon Aurora PostgreSQL migration:

The heterogeneous database migration encompassed two aspects: To convert the source scheme to target DB and to migrate the data to target DB.

We automated remediation of PostgreSQL compatibility issues using a regex-based remediation tool and built a framework for schema recon and post-migration data validations. CI/CD pipeline deployment accelerated migration efforts while monitoring Oracle performance metrics against Aurora PostgreSQL metrics.

Later, we extracted user data from the source database to a CSV file to be loaded into a temp table in PostgreSQL. Our team also created a SQL statement to retrieve data from the temp table in CREATE USER statements and ran it to set up users in PostgreSQL.

Application migration

Once the client’s existing Oracle Database was re-architected to Amazon Aurora, its application servers, web servers, and ETL servers were re-hosted to EC2s. We remediated all Oracle-specific syntaxes and data types in the application codebase. We also created blueprints for infra provisioning:

iBatis framework leveraged as an ORM layer between database and Java objects
Kettle used for enrollment batch processing
Automated remediation of Oracle-specific code in iBATIS and Kettle jobs using a regex-based remediation tool handled
Changes to SQL statements, jdbcTypes, and type Handlers in iBatis Mapper files
Datatype changes in Java Objects
SQL statements in Kettle jobs