Validate database objects after migrating from Oracle to Amazon Aurora PostgreSQL - AWS Prescriptive Guidance

Validate database objects after migrating from Oracle to Amazon Aurora PostgreSQL

Created by Venkatramana Chintha (AWS)

R Type: Re-architect

Source: Relational

Target: Amazon Aurora PostgreSQL,Amazon RDS for PostgreSQL

Created by: AWS

Environment: PoC or pilot

Technologies: Databases; Migration

Workload: Oracle

AWS services: Amazon Aurora

Summary

This pattern describes a step-by-step approach to validate objects after migrating an Oracle database to Amazon Aurora PostgreSQL-Compatible Edition.

This pattern outlines usage scenarios and steps for database object validation; for more detailed information, see Validating database objects after migration using AWS SCT and AWS DMS on the AWS Database blog.

Prerequisites and limitations

Prerequisites:

  • An active AWS account.

  • An on-premises Oracle database that was migrated to an Aurora PostgreSQL-Compatible database. 

  • An AWS Identity and Access Management (IAM) user with an AmazonRDSDataFullAccess policy for the Aurora PostgreSQL-Compatible database. 

  • This pattern uses the query editor for Aurora Serverless DB clusters, which is available in the Amazon Relational Database Service (Amazon RDS) console. However, you can use this pattern with any other query editor. 

Limitations:

Architecture

Tools

Tools

  • Amazon Aurora PostgreSQL-Compatible Edition – Aurora PostgreSQL-Compatible is a fully managed, PostgreSQL-compatible, and ACID-compliant relational database engine that combines the speed and reliability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases.

  • Amazon RDS – Amazon Relational Database Service (Amazon RDS) makes it easier to set up, operate, and scale a relational database in the AWS Cloud. It provides cost-efficient, resizable capacity for an industry-standard relational database and manages common database administration tasks.

  • Query Editor for Aurora Severless – Query editor helps you run SQL queries in the Amazon RDS console. You can run any valid SQL statement on the Aurora Serverless DB cluster, including data manipulation and data definition statements.

To validate the objects, use the full scripts in the "Object validation scripts" file in the “Attachments” section. Use the following table for reference.

Oracle object

Script to use

Packages

Query 1

Tables

Query  3

Views

Query  5

Sequences

Query  7

Triggers

 Query  9

Primary keys

Query  11

Indexes

Query  13

Check constraints

Query  15

Foreign keysQuery 17

PostgreSQL object

Script to use

Packages

Query 2

Tables

Query 4

Views

Query 6

Sequences

Query 8

Triggers

Query 10

Primary keys

Query  12

Indexes

Query  14

Check constraints

Query  16

Foreign keys

Query  18

Epics

TaskDescriptionSkills required
Run the “packages” validation query in the source Oracle database.

Download and open the “Object validation scripts” file from the “Attachments” section. Connect to the source Oracle database through your client program. Run the “Query 1” validations script from the “Object validation scripts” file. Important: Enter your Oracle user name instead of “your_schema” in the queries. Make sure you record your query results.

Developer, DBA
Run the “tables” validation query.

Run the “Query 3” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “views” validation query.

Run the “Query 5” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “sequences” count validation.

Run the “Query 7” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “triggers” validation query.

Run the “Query 9” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “primary keys” validation query.

Run the “Query 11” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “indexes” validation query.

Run the “Query 13” validation script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “check constraints” validation query.

Run the “Query 15” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “foreign keys” validation query.

Run the “Query 17” validation script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
TaskDescriptionSkills required
Connect to the target Aurora PostgreSQL-Compatible database by using the query editor.

Sign in to the AWS Management Console and open the Amazon RDS console. In the upper-right corner, choose the AWS Region in which you created the Aurora PostgreSQL-Compatible database. In the navigation pane, choose “Databases,” and choose the target Aurora PostgreSQL-Compatible database. In “Actions,” choose “Query.” Important: If you haven't connected to the database before, the “Connect to database” page opens. You then need to enter your database information, such as user name and password.

Developer, DBA
Run the “packages” validation query.

Run the “Query 2” script from the “Object validation scripts” file in the “Attachments” section. Make sure you record your query results.

Developer, DBA
Run the “tables” validation query.

Return to the query editor for the Aurora PostgreSQL-Compatible database, and run the “Query 4” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “views” validation query.

Return to the query editor for the Aurora PostgreSQL-Compatible database, and run the “Query 6” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “sequences” count validation.

Return to the query editor for the Aurora PostgreSQL-Compatible database, and run the “Query 8” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “triggers” validation query.

Return to the query editor for the Aurora PostgreSQL-Compatible database, and run the “Query 10” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “primary keys” validation query.

Return to the query editor for the Aurora PostgreSQL-Compatible database, and run the “Query 12” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “indexes” validation query.

Return to the query editor for the Aurora PostgreSQL-Compatible database, and run the “Query 14” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “check constraints” validation query.

Run the “Query 16” script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
Run the “foreign keys” validation query.

Run the “Query 18” validation script from the “Object validation scripts” file. Make sure you record your query results.

Developer, DBA
TaskDescriptionSkills required
Compare and validate both query results.

Compare the query results of the Oracle and Aurora PostgreSQL-Compatible databases to validate all objects. If they all match, then all objects have been successfully validated.

Developer, DBA

Attachments

To access additional content that is associated with this document, unzip the following file: attachment.zip