Convert VARCHAR2(1) data type for Oracle to Boolean data type for Amazon Aurora PostgreSQL - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsEpicsRelated resourcesAdditional information

Convert VARCHAR2(1) data type for Oracle to Boolean data type for Amazon Aurora PostgreSQL

Created by Naresh Damera (AWS)

Environment: PoC or pilot

Source: Oracle

Target: Amazon Aurora PostgreSQL

R Type: Re-architect

Workload: Oracle

Technologies: Migration; DevelopmentAndTesting; Storage & backup; Databases

AWS services: Amazon Aurora; AWS DMS; Amazon RDS; AWS SCT

Summary

During a migration from Amazon Relational Database Service (Amazon RDS) for Oracle to Amazon Aurora PostgreSQL-Compatible Edition, you might encounter a data mismatch when validating the migration in Amazon Web Services (AWS) Database Migration Service (AWS DMS). To prevent this mismatch, you can convert VARCHAR2(1) data type to Boolean data type.

VARCHAR2 data type stores variable-length text strings, and VARCHAR2(1) indicates that the string is 1 character in length or 1 byte. For more information about VARCHAR2, see Oracle built-in data types (Oracle documentation).

In this pattern, in the sample source data table column, the VARCHAR2(1) data is either a Y, for Yes, or N, for No.  This pattern includes instructions for using AWS DMS and AWS Schema Conversion Tool (AWS SCT) to convert this data type from the Y and N values in VARCHAR2(1) to true or false values in Boolean.

Target audience

This pattern is recommended for those who have experience migrating Oracle databases to Aurora PostgreSQL-Compatible by using AWS DMS. As you complete the migration, adhere to the recommendations in Converting Oracle to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL (AWS SCT documentation).

Prerequisites and limitations

Prerequisites

Product versions

Architecture

Source technology stack

Amazon RDS for Oracle database instance

Target technology stack

Amazon Aurora PostgreSQL-Compatible database instance

Source and target architecture

Changing data types from VARCHAR2(1) to Boolean

Tools

AWS services

Other services

  • Oracle SQL Developer is an integrated development environment that simplifies the development and management of Oracle databases in both traditional and cloud-based deployments.. In this pattern, you use this tool to connect to the Amazon RDS for Oracle database instance and query the data.

  • pgAdmin is an open-source management tool for PostgreSQL. It provides a graphical interface that helps you create, maintain, and use database objects. In this pattern, you use this tool to connect to the Aurora database instance and query the data.

Epics

TaskDescriptionSkills required

Create database migration report.

  1. In AWS SCT, create a database migration assessment report. For more information, see Creating migration assessment reports.

  2. Review and perform the action items in the migration assessment report. For more information, see Assessment report action items.

DBA, Developer

Disable foreign key constraints on the target database.

In PostgreSQL, foreign keys are implemented by using triggers. During the full load phase, AWS DMS loads each table one at a time. We strongly recommend that you disable foreign key constraints during a full load by using one of the following methods:

  • Temporarily disable all triggers from the instance, and finish the full load.

  • Use the session_replication_role parameter in PostgreSQL.

If disabling foreign key constraints is not feasible, create an AWS DMS migration task for the primary data that is specific to the parent table and child table.

DBA, Developer

Disable the primary keys and unique keys on the target database.

Using the following commands, disable the primary keys and constraints on the target database. This helps improve the performance of the initial load task.

ALTER TABLE <table> DISABLE PRIMARY KEY;
ALTER TABLE <table> DISABLE CONSTRAINT <constraint_name>;
DBA, Developer

Create the initial load task.

In AWS DMS, create the migration task for the initial load. For instructions, see Creating a task. For the migration method, choose Migrate existing data. This migration method is called Full Load in the API. Do not start this task yet.

DBA, Developer

Edit task settings for the initial load task.

Edit the task settings to add data validation. These validation settings must be created in a JSON file. For instructions and examples, see Specifying task settings. Add the following validations:

  • To validate that the VARCHAR2(1) data is accurately converted to Boolean in the target database, add the code in Data validation script in the Additional information section of this pattern. The validation script converts the Boolean values of 1 to Y and 0 to N in the target table, and then it compares the values in the target table to the source table.

To validate the rest of the data migration, enable data validation in the task. For more information, see Data validation task settings.

AWS administrator, DBA

Create the ongoing replication task.

In AWS DMS, create the migration task that keeps the target database in sync with the source database. For instructions, see Creating a task. For the migration method, choose Replicate data changes only. Do not start this task yet.

DBA
TaskDescriptionSkills required

Create sample data for testing.

In the source database, create a sample table with data for testing purposes.

Developer

Confirm there are no conflicting activities.

Use the pg_stat_activity to check for any activity on the server that might affect the migration. For more information, see The Statistics Collector (PostgreSQL documentation).

AWS administrator

Start the AWS DMS migration tasks.

In the AWS DMS console, on the Dashboard page, start the initial load and ongoing replication tasks that you created in the previous epic.

AWS administrator

Monitor the tasks and table load states.

During the migration, monitor the task status and the table states. When the initial load task is complete, on the Table statistics tab:

  • The Load state should be Table completed.

  • The Validation state should be Validated.

AWS administrator

Verify the migration results.

Using pgAdmin, query the table on target database. A successful query indicates that the data was migrated successfully.

Developer

Add primary keys and foreign keys on the target database.

Create the primary key and foreign key on the target database. For more information, see ALTER TABLE (PostgreSQL website).

DBA

Clean up the test data.

On the source and target databases, clean up data that was created for unit testing.

Developer
TaskDescriptionSkills required

Complete the migration.

Repeat the previous epic, Test the migration tasks, using the real source data. This migrates the data from the source to the target database.

Developer

Validate that the source and target databases are in sync.

Validate that the source and target databases are in sync. For more information and instructions, see AWS DMS data validation.

Developer

Stop the source database.

Stop the Amazon RDS for Oracle database. For instructions, see Stopping an Amazon RDS DB instance temporarily. When you stop the source database, the initial load and ongoing replication tasks in AWS DMS are automatically stopped. No additional action is required to stop these tasks.

Developer

Related resources

AWS references

Other references

Tutorial and videos

Additional information

Data validation script

The following data validation script converts 1 to Y and 0 to N. This helps the AWS DMS task successfully complete and pass the table validation.

{
"rule-type": "validation",
"rule-id": "5",
"rule-name": "5",
"rule-target": "column",
"object-locator": {
"schema-name": "ADMIN",
"table-name": "TEMP_CHRA_BOOL",
"column-name": "GRADE"
},
"rule-action": "override-validation-function",
"target-function": "case grade when '1' then 'Y' else 'N' end"
        }

The case statement in the script performs the validation. If validation fails, AWS DMS inserts a record in the public.awsdms_validation_failures_v1 table on the target database instance. This record includes the table name, error time, and details about the mismatched values in the source and target tables.

If you do not add this data validation script to the AWS DMS task and the data is inserted in the target table, the AWS DMS task shows validation state as Mismatched Records.

During AWS SCT conversion, the AWS DMS migration task changes the data type of VARCHAR2(1) data type to Boolean and adds a primary key constraint on the "NO" column.