**. | AWS SysAdmin |
| Set up the initial host for a bystander in Amazon EC2. | Set up SSH keys, bash profile, ORATAB, and symbolic links. Create Oracle directories. | AWS SysAdmin, Linux Admin |
| Set up the database copy for a bystander in Amazon EC2 | Use RMAN to create a database copy, enable supplemental logging, and create the standby control file. After copying is complete, place the database in recovery mode. | AWS SysAdmin, DBA |
| Set up Oracle Data Guard. | Modify your **listener.ora** file and start the listener. Set up a new archive destination. Place the bystander in recovery mode, replace temporary files to avoid future corruption, install a crontab if necessary to prevent the archive directory from running out of space, and edit the **manage-trclog-files-oracle.cfg** file for the source and standby. | AWS SysAdmin, DBA |
| Prep the Oracle database to sync shipping. | Add the standby log files and change the recovery mode. Change the log shipping to **SYNC AFFIRM** on both the source primary and the source standby. Switch logs on primary, confirm via the Amazon EC2 bystander alert log that you are using the standby log files, and confirm that the redo stream is flowing in SYNC. | AWS SysAdmin, DBA |
### Migrate data with AWS DMS
| Task | Description | Skills required |
| --- | --- | --- |
| Create a replication instance in AWS DMS. | Complete the fields for the name, instance class, VPC (same as the Amazon EC2 instance), Multi-AZ, and public accessibility. Under **Advance**, specify allocated storage, subnet group, Availability Zone, VPC security groups, and AWS Key Management Service (AWS KMS) key. | AWS SysAdmin, DBA |
| Create the source database endpoint. | Specify the endpoint name, type, source engine (Oracle), server name (Amazon EC2 private DNS name), port, SSL mode, user name, password, SID, VPC (specify the VPC that has the replication instance), and replication instance. To test the connection, choose **Run Test**, and then create the endpoint. You can also configure the following advanced settings: **maxFileSize** and **numberDataTypeScale**. | AWS SysAdmin, DBA |
| Connect AWS DMS to Amazon RDS for PostgreSQL. | Create a migration security group for connections across VPCs. | AWS SysAdmin, DBA |
| Create the target database endpoint. | Specify the endpoint name, type, source engine (PostgreSQL), server name (Amazon RDS endpoint), port, SSL mode, user name, password, database name, VPC (specify the VPC that has the replication instance), and replication instance. To test the connection, choose **Run Test**, and then create the endpoint. You can also configure the following advanced settings: **maxFileSize **and **numberDataTypeScale**. | AWS SysAdmin, DBA |
| Create the AWS DMS replication task. | Specify the task name, replication instance, source and target endpoints, and replication instance. For migration type, choose **Migrate existing data and replicate ongoing changes**. Clear the **Start task on create** checkbox. | AWS SysAdmin, DBA |
| Configure the AWS DMS replication task settings. | For target table preparation mode, choose **Do nothing**. Stop task after full load completes (to create primary keys). Specify limited or full LOB mode, and activate control tables. Optionally, you can configure the **CommitRate** advance setting. | DBA |
| Configure table mappings. | In the **Table mappings** section, create an **Include** rule for all tables in all schemas included in the migration, and then create an **Exclude** rule. Add three transformation rules to convert the schema, table, and column names to lowercase, and add any other rules needed for this specific migration. | DBA |
| Start the task. | Start the replication task. Make sure that the full load is running. Run **ALTER SYSTEM SWITCH LOGFILE** on the primary Oracle database to kick-start the task. | DBA |
| Run the mid-migration scripts from AWS SCT. | In Amazon RDS for PostgreSQL, run the following scripts generated by AWS SCT: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-an-on-premises-oracle-database-to-amazon-rds-for-postgresql-by-using-an-oracle-bystander-and-aws-dms.html) | DBA |
| Restart the task to continue change data capture (CDC). | Run **VACUUM **on the Amazon RDS for PostgreSQL DB instance, and restart the AWS DMS task to apply cached CDC changes. | DBA |
### Cut over to the PostgreSQL database
| Task | Description | Skills required |
| --- | --- | --- |
| Review the AWS DMS logs and validation tables for any errors. | Check and fix any replication or validation errors. | DBA |
| Stop all Oracle dependencies. | Stop all Oracle dependencies, shut down listeners on the Oracle database, and run **ALTER SYSTEM SWITCH LOGFILE**. Stop the AWS DMS task when it shows no activity. | DBA |
| Run the post-migration scripts from AWS SCT. | In Amazon RDS for PostgreSQL, run the following scripts generated by AWS SCT:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-an-on-premises-oracle-database-to-amazon-rds-for-postgresql-by-using-an-oracle-bystander-and-aws-dms.html) | DBA |
| Complete additional Amazon RDS for PostgreSQL steps. | Increment sequences to match Oracle if needed, run **VACUUM** and **ANALYZE**, and take a snapshot for compliance. | DBA |
| Open the connections to Amazon RDS for PostgreSQL. | Remove the AWS DMS security groups from Amazon RDS for PostgreSQL, add production security groups, and point your applications to the new database. | DBA |
| Clean up AWS DMS objects. | Remove the endpoints, replication tasks, replication instances, and the EC2 instance. | SysAdmin, DBA |
## Related resources
+ [AWS DMS documentation](https://docs.aws.amazon.com/dms/)
+ [AWS SCT documentation](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
+ [Amazon RDS for PostgreSQL pricing](https://aws.amazon.com/rds/postgresql/pricing/)
# Migrate an Oracle Database to Amazon Redshift using AWS DMS and AWS SCT
*Piyush Goyal and Brian motzer, Amazon Web Services*
## Summary
This pattern provides guidance for migrating Oracle databases to an Amazon Redshift cloud data warehouse in the Amazon Web Services (AWS) Cloud by using AWS Database Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT). The pattern covers source Oracle databases that are on premises or installed on an Amazon Elastic Compute Cloud (Amazon EC2) instance. It also covers Amazon Relational Database Service (Amazon RDS) for Oracle databases.
## Prerequisites and limitations
**Prerequisites **
+ An Oracle database that is running in an on-premises data center or in the AWS Cloud
+ An active AWS account
+ Familiarity with [using an Oracle database as a source for AWS DMS](http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html)
+ Familiarity with [using an Amazon Redshift database as a target for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Redshift.html)
+ Knowledge of Amazon RDS, Amazon Redshift, the applicable database technologies, and SQL
+ Java Database Connectivity (JDBC) drivers for AWS SCT connectors, where AWS SCT is installed
**Product versions**
+ For self-managed Oracle databases, AWS DMS supports all Oracle database editions for versions 10.2 and later (for versions 10.*x*), 11g and up to 12.2, 18c, and 19c. For Amazon RDS for Oracle databases that AWS manages, AWS DMS supports all Oracle database editions for versions 11g (versions 11.2.0.4 and later) and up to 12.2, 18c, and 19c. We recommend that you use the latest version of AWS DMS for the most comprehensive version and feature support.
## Architecture
**Source technology stack **
One of the following:
+ An on-premises Oracle database
+ An Oracle database on an EC2 instance
+ An Amazon RDS for Oracle DB instance
**Target technology stack **
+ Amazon Redshift
**Target architecture **
*From an Oracle database running in the AWS Cloud to Amazon Redshift:*
![\[Migrating an Oracle database in the AWS Cloud to an Amazon Redshift data warehouse.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/22807be0-c7e0-49c6-8923-7d23bf83a50d/images/7140e819-81d6-45c4-805b-8e10828076a7.png)
*From an Oracle database running in an on-premises data center to Amazon Redshift:*
![\[Migrating an on-premises Oracle database to an Amazon Redshift data warehouse.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/22807be0-c7e0-49c6-8923-7d23bf83a50d/images/d6654b48-0e1b-4b01-a261-5a640be01fd7.png)
## Tools
+ [AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) - AWS Data Migration Service (AWS DMS) helps you migrate databases to AWS quickly and securely. The source database remains fully operational during the migration, minimizing downtime to applications that rely on the database. AWS DMS can migrate your data to and from most widely used commercial and open-source databases.
+ [AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) - AWS Schema Conversion Tool (AWS SCT) can be used to convert your existing database schema from one database engine to another. It supports various database engines, including Oracle, SQL Server, and PostgresSQL, as sources.
## Epics
### Prepare for the migration
| Task | Description | Skills required |
| --- | --- | --- |
| Validate the database versions. | Validate the source and target database versions and make sure they are supported by AWS DMS. For information about supported Oracle Database versions, see [Using an Oracle database as a source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html). For information about using Amazon Redshift as a target, see [Using an Amazon Redshift database as a target for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Redshift.html). | DBA |
| Create a VPC and security group. | In your AWS account, create a virtual private cloud (VPC), if it doesn’t exist. Create a security group for outbound traffic to source and target databases. For more information, see the [Amazon Virtual Private Cloud (Amazon VPC) documentation](https://docs.aws.amazon.com/vpc/latest/userguide/what-is-amazon-vpc.html). | Systems administrator |
| Install AWS SCT. | Download and install the latest version of AWS SCT and its corresponding drivers. For more information, see [Installing, verifying, and updating the AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html). | DBA |
| Create a user for the AWS DMS task. | Create an AWS DMS user in the source database and grant it READ privileges. This user will be used by both AWS SCT and AWS DMS. | DBA |
| Test the DB connectivity. | Test the connectivity to the Oracle DB instance. | DBA |
| Create a new project in AWS SCT. | Open the AWS SCT tool and create a new project. | DBA |
| Analyze the Oracle schema to be migrated. | Use AWS SCT to analyze the schema to be migrated, and generate a database migration assessment report. For more information, see [Creating a database migration assessment report](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_AssessmentReport.Create.html) in the AWS SCT documentation. | DBA |
| Review the assessment report. | Review the report for migration feasibility. Some DB objects might require manual conversion. For more information about the report, see [Viewing the assessment report](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_AssessmentReport.View.html) in the AWS SCT documentation. | DBA |
### Prepare the target database
| Task | Description | Skills required |
| --- | --- | --- |
| Create an Amazon Redshift cluster. | Create an Amazon Redshift cluster within the VPC that you created previously. For more information, see [Amazon Redshift clusters](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-clusters.html) in the Amazon Redshift documentation. | DBA |
| Create database users. | Extract the list of users, roles, and grants from the Oracle source database. Create users in the target Amazon Redshift database and apply the roles from the previous step. | DBA |
| Evaluate database parameters. | Review the database options, parameters, network files, and database links from the Oracle source database, and evaluate their applicability to the target. | DBA |
| Apply any relevant settings to the target. | For more information about this step, see [Configuration reference](https://docs.aws.amazon.com/redshift/latest/dg/cm_chap_ConfigurationRef.html) in the Amazon Redshift documentation. | DBA |
### Create objects in the target database
| Task | Description | Skills required |
| --- | --- | --- |
| Create an AWS DMS user in the target database. | Create an AWS DMS user in the target database and grant it read and write privileges. Validate the connectivity from AWS SCT. | DBA |
| Convert the schema, review the SQL report, and save any errors or warnings. | For more information, see [Converting database schemas using the AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.html) in the AWS SCT documentation. | DBA |
| Apply the schema changes to the target database or save them as a .sql file. | For instructions, see [Saving and applying your converted schema in the AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.DW.html#CHAP_Converting.DW.SaveAndApply) in the AWS SCT documentation. | DBA |
| Validate the objects in the target database. | Validate the objects that were created in the previous step in the target database. Rewrite or redesign any objects that weren’t successfully converted. | DBA |
| Disable foreign keys and triggers. | Disable any foreign key and triggers. These can cause data loading issues during the full load process when running AWS DMS. | DBA |
### Migrate data using AWS DMS
| Task | Description | Skills required |
| --- | --- | --- |
| Create an AWS DMS replication instance. | Sign in to the AWS Management Console, and open the AWS DMS console. In the navigation pane, choose **Replication instances**, **Create replication instance**. For detailed instructions, see [step 1](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html#CHAP_GettingStarted.ReplicationInstance) in *Getting started with AWS DMS* in the AWS DMS documentation. | DBA |
| Create source and target endpoints. | Create source and target endpoints, Test the connection from the replication instance to both source and target endpoints. For detailed instructions, see [step 2](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html#CHAP_GettingStarted.Endpoints) in *Getting started with AWS DMS* in the AWS DMS documentation. | DBA |
| Create a replication task. | Create a replication task and select the appropriate migration method. For detailed instructions, see [step 3](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html#CHAP_GettingStarted.Tasks) in *Getting started with AWS DMS* in the AWS DMS documentation. | DBA |
| Start the data replication. | Start the replication task and monitor the logs for any errors. | DBA |
### Migrate your application
| Task | Description | Skills required |
| --- | --- | --- |
| Create application servers. | Create the new application servers on AWS. | Application owner |
| Migrate the application code. | Migrate the application code to the new servers. | Application owner |
| Configure the application server. | Configure the application server for the target database and drivers. | Application owner |
| Optimize the application code. | Optimize the application code for the target engine. | Application owner |
### Cut over to the target database
| Task | Description | Skills required |
| --- | --- | --- |
| Validate users. | In the target Amazon Redshift database, validate users and grant them roles and privileges. | DBA |
| Validate that the application is locked. | Make sure that the application is locked, to prevent further changes. | Application owner |
| Validate the data. | Validate the data in the target Amazon Redshift database. | DBA |
| Enable foreign keys and triggers. | Enable foreign keys and triggers in the target Amazon Redshift database. | DBA |
| Connect to the new database. | Configure the application to connect to the new Amazon Redshift database. | Application owner |
| Perform final checks. | Perform a final, comprehensive system check before going live. | DBA, Application owner |
| Go live. | Go live with the target Amazon Redshift database. | DBA |
### Close the migration project
| Task | Description | Skills required |
| --- | --- | --- |
| Shut down temporary AWS resources. | Shut down temporary AWS resources such as the AWS DMS replication instance and the EC2 instance used for AWS SCT. | DBA, Systems administrator |
| Review documents. | Review and validate the migration project documents. | DBA, Systems administrator |
| Gather metrics. | Collect information about the migration project, such as the time to migrate, the percentage of manual versus tool tasks, and total cost savings. | DBA, Systems administrator |
| Close out the project. | Close out the project and provide feedback. | DBA, Systems administrator |
## Related resources
**References**
+ [AWS DMS user guide](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html)
+ [AWS SCT user guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
+ [Amazon Redshift getting started guide](https://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html)
**Tutorials and videos**
+ [Dive Deep into AWS SCT and AWS DMS](https://www.youtube.com/watch?v=kJs9U4ys5FE) (presentation from AWS re:Invent 2019)
+ [Getting Started with AWS Database Migration Service](https://aws.amazon.com/dms/getting-started/)
# Migrate an Oracle database to Aurora PostgreSQL using AWS DMS and AWS SCT
*Senthil Ramasamy, Amazon Web Services*
## Summary
This pattern describes how to migrate an Oracle database to Amazon Aurora PostgreSQL-Compatible Edition by using AWS Data Migration Service (AWS DMS) and AWS Schema Conversion Tool (AWS SCT).
The pattern covers source Oracle databases that are on premises, Oracle databases that are installed on Amazon Elastic Compute Cloud (Amazon EC2) instances, and Amazon Relational Database Service (Amazon RDS) for Oracle databases. The pattern converts these databases to Aurora PostgreSQL-Compatible.
## Prerequisites and limitations
**Prerequisites**
+ An active AWS account.
+ An Oracle database in an on-premises data center or in the AWS Cloud.
+ SQL clients installed either on a local machine or on an EC2 instance.
+ Java Database Connectivity (JDBC) drivers for AWS SCT connectors, installed on either a local machine or an EC2 instance where AWS SCT is installed.
**Limitations**
+ Database size limit: 128 TB
+ If the source database supports a commercial off-the-shelf (COTS) application or is vendor-specific, you might not be able to convert it to another database engine. Before using this pattern, confirm that the application supports Aurora PostgreSQL-Compatible.
**Product versions**
+ For self-managed Oracle databases, AWS DMS supports all Oracle database editions for versions 10.2 and later (for versions 10.x), 11g, and up to 12.2, 18c, and 19c. For the latest list of supported Oracle database versions (both self-managed and Amazon RDS for Oracle), see [Using an Oracle database as a source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html) and [Using a PostgreSQL database as a target for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html).
+ We recommend that you use the latest version of AWS DMS for the most comprehensive version and feature support. For information about Oracle database versions supported by AWS SCT, see the [AWS SCT documentation](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html).
+ Aurora supports the PostgreSQL versions listed in [Amazon Aurora PostgreSQL releases and engine versions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Updates.20180305.html).
## Architecture
**Source technology stack**
One of the following:
+ An on-premises Oracle database
+ An Oracle database on an EC2 instance
+ An Amazon RDS for Oracle DB instance
**Target technology stack**
+ Aurora PostgreSQL-Compatible
**Target architecture**
![\[Target architecture for migrating Oracle databases to Aurora PostgreSQL-Compatible.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/6de157c4-dcc9-4186-ae32-17efbbbee709/images/68beb634-926e-4908-97b1-edcd23e06a2b.png)
**Data migration architecture**
+ From an Oracle database running in the AWS Cloud
![\[Data migration architecture for an Oracle database on AWS.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/6de157c4-dcc9-4186-ae32-17efbbbee709/images/7fc32019-3db1-485b-93e5-6d5539be048c.png)
+ From an Oracle database running in an on-premises data center
![\[Data migration architecture for an Oracle database in an on-premises data center.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/6de157c4-dcc9-4186-ae32-17efbbbee709/images/c70d8774-aef7-4414-9766-ce8f25757c4b.png)
## Tools
+ [AWS Database Migration Service (AWS DMS)](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) helps you migrate data stores into the AWS Cloud or between combinations of cloud and on-premises setups.
+ [AWS Schema Conversion Tool (AWS SCT)](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) supports heterogeneous database migrations by automatically converting the source database schema and a majority of the custom code to a format compatible with the target database.
## Epics
### Prepare for the migration
| Task | Description | Skills required |
| --- | --- | --- |
| Prepare the source database. | To prepare the source database, see [Using Oracle Database as a source for AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Source.Oracle.html) in the AWS SCT documentation. | DBA |
| Create an EC2 instance for AWS SCT. | Create and configure an EC2 instance for AWS SCT, if required. | DBA |
| Download AWS SCT. | Download the latest version of AWS SCT and associated drivers. For more information, see [Installing, verifying, and updating AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html) in the AWS SCT documentation. | DBA |
| Add users and permissions. | Add and validate the prerequisite users and permissions in the source database. | DBA |
| Create an AWS SCT project. | Create an AWS SCT project for the workload, and connect to the source database. For instructions, see [Creating an AWS SCT project](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_UserInterface.html#CHAP_UserInterface.Project) and [Adding database servers](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_UserInterface.html#CHAP_UserInterface.AddServers) in the AWS SCT documentation. | DBA |
| Evaluate feasibility. | Generate an assessment report, which summarizes action items for schemas that can’t be converted automatically and provides estimates for manual conversion efforts. For more information, see [Creating and reviewing the database migration assessment report](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_UserInterface.html#CHAP_UserInterface.AssessmentReport) in the AWS SCT documentation. | DBA |
### Prepare the target database
| Task | Description | Skills required |
| --- | --- | --- |
| Create a target Amazon RDS DB instance. | Create a target Amazon RDS DB instance, using Amazon Aurora as the database engine. For instructions, see [Creating an Amazon RDS DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateDBInstance.html) in the Amazon RDS documentation. | DBA |
| Extract users, roles, and permissions. | Extract the list of users, roles, and permissions from the source database. | DBA |
| Map users. | Map the existing database users to the new database users. | App owner |
| Create users. | Create users in the target database. | DBA, App owner |
| Apply roles. | Apply roles from the previous step to the target database. | DBA |
| Check options, parameters, network files, and database links. | Review the source database for options, parameters, network files, and database links, and then evaluate their applicability to the target database. | DBA |
| Apply settings. | Apply any relevant settings to the target database. | DBA |
### Transfer objects
| Task | Description | Skills required |
| --- | --- | --- |
| Configure AWS SCT connectivity. | Configure AWS SCT connectivity to the target database. | DBA |
| Convert the schema using AWS SCT. | AWS SCT automatically converts the source database schema and most of the custom code to a format that is compatible with the target database. Any code that the tool cannot convert automatically is clearly marked so that you can convert it manually. | DBA |
| Review the report. | Review the generated SQL report and save any errors and warnings. | DBA |
| Apply automated schema changes. | Apply automated schema changes to the target database or save them as a .sql file. | DBA |
| Validate objects. | Validate that AWS SCT created the objects on the target. | DBA |
| Handle items that weren't converted. | Manually rewrite, reject, or redesign any items that failed to convert automatically. | DBA, App owner |
| Apply role and user permissions. | Apply the generated role and user permissions and review any exceptions. | DBA |
### Migrate the data
| Task | Description | Skills required |
| --- | --- | --- |
| Determine the method. | Determine the method for migrating data. | DBA |
| Create a replication instance. | Create a replication instance from the AWS DMS console. For more information, see [Working with an AWS DMS replication instance](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.html) in the AWS DMS documentation. | DBA |
| Create the source and target endpoints. | To create endpoints, follow the instructions in [Creating source and target endpoints in the AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Endpoints.Creating.html) documentation. | DBA |
| Create a replication task. | To create a task, see [Working with AWS DMS tasks](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.html) in the AWS DMS documentation. | DBA |
| Start the replication task and monitor the logs. | For more information about this step, see [Monitoring AWS DMS tasks](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Monitoring.html) in the AWS DMS documentation. | DBA |
### Migrate the application
| Task | Description | Skills required |
| --- | --- | --- |
| Analyze and convert SQL items in the application code. | Use AWS SCT to analyze and convert the SQL items in the application code. When you convert your database schema from one engine to another, you also need to update the SQL code in your applications to interact with the new database engine instead of the old one. You can view, analyze, edit, and save the converted SQL code. | App owner |
| Create application servers. | Create the new application servers on AWS. | App owner |
| Migrate the application code. | Migrate the application code to the new servers. | App owner |
| Configure the application servers. | Configure the application servers for the target database and drivers. | App owner |
| Fix code. | Fix any code that’s specific to the source database engine in your application. | App owner |
| Optimize code. | Optimize your application code for the target database engine. | App owner |
### Cut over
| Task | Description | Skills required |
| --- | --- | --- |
| Cut over to the target database. | Perform the cutover to the new database. | DBA |
| Lock the application. | Lock the application from any further changes. | App owner |
| Validate changes. | Validate that all changes were propagated to the target database. | DBA |
| Redirect to the target database. | Point the new application servers to the target database. | App owner |
| Check everything. | Perform a final, comprehensive system check. | App owner |
| Go live. | Complete final cutover tasks. | App owner |
### Close the project
| Task | Description | Skills required |
| --- | --- | --- |
| Shut down temporary resources. | Shut down the temporary AWS resources such as the AWS DMS replication instance and the EC2 instance used for AWS SCT. | DBA, App owner |
| Update feedback. | Update feedback on the AWS DMS process for internal teams. | DBA, App owner |
| Revise process and templates. | Revise the AWS DMS process and improve the template if necessary. | DBA, App owner |
| Validate documents. | Review and validate the project documents. | DBA, App owner |
| Gather metrics. | Gather metrics to evaluate the time to migrate, percent of manual versus tool cost savings, and so on. | DBA, App owner |
| Close the project. | Close the migration project and provide feedback to stakeholders. | DBA, App owner |
## Related resources
**References**
+ [Using an Oracle Database as a Source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html)
+ [Using a PostgreSQL Database as a Target for AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html)
+ [Oracle Database 11g/12c to Amazon Aurora with PostgreSQL Compatibility (9.6.x) Migration Playbook](https://d1.awsstatic.com/whitepapers/Migration/oracle-database-amazon-aurora-postgresql-migration-playbook.pdf)
+ [Oracle Database 19c to Amazon Aurora with PostgreSQL Compatibility (12.4) Migration Playbook](https://d1.awsstatic.com/whitepapers/Migration/oracle-database-amazon-aurora-postgresql-migration-playbook-12.4.pdf)
+ [Migrating an Amazon RDS for Oracle database to Amazon Aurora PostgreSQL-Compatible Edition](https://docs.aws.amazon.com/dms/latest/sbs/chap-oracle-postgresql.html)
+ [AWS Data Migration Service](https://aws.amazon.com/dms/)
+ [AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
+ [Migrate from Oracle to Amazon Aurora](https://aws.amazon.com/getting-started/projects/migrate-oracle-to-amazon-aurora/)
+ [Amazon RDS pricing](https://aws.amazon.com/rds/pricing/)
**Tutorials and videos**
+ [Database Migration Step-by-Step Walkthroughs](https://docs.aws.amazon.com/dms/latest/sbs/DMS-SBS-Welcome.html)
+ [Getting Started with AWS DMS](https://aws.amazon.com/dms/getting-started/)
+ [Getting Started with Amazon RDS](https://aws.amazon.com/rds/getting-started/)
+ [AWS Data Migration Service](https://www.youtube.com/watch?v=zb4GcjEdl8U) (video)
+ [Migrating an Oracle database to PostgreSQL](https://www.youtube.com/watch?v=ibtNkChGFkw) (video)
## Additional information
.
# Migrate data from an on-premises Oracle database to Aurora PostgreSQL
*Michelle Deng and Shunan Xiang, Amazon Web Services*
## Summary
This pattern provides guidance for data migration from an on-premises Oracle database to Amazon Aurora PostgreSQL-Compatible Edition. It targets an online data migration strategy with a minimal amount of downtime for multi-terabyte Oracle databases that contain large tables with high data manipulation language (DML) activities. An Oracle Active Data Guard standby database is used as the source to offload data migration from the primary database. The replication from the Oracle primary database to standby can be suspended during the full load to avoid ORA-01555 errors.
Table columns in primary keys (PKs) or foreign keys (FKs), with data type NUMBER, are commonly used to store integers in Oracle. We recommend that you convert these to INT or BIGINT in PostgreSQL for better performance. You can use the AWS Schema Conversion Tool (AWS SCT) to change the default data type mapping for PK and FK columns. (For more information, see the AWS blog post [Convert the NUMBER data type from Oracle to PostgreSQL](https://aws.amazon.com/blogs/database/convert-the-number-data-type-from-oracle-to-postgresql-part-2/).) The data migration in this pattern uses AWS Database Migration Service (AWS DMS) for both full load and change data capture (CDC).
You can also use this pattern to migrate an on-premises Oracle database to Amazon Relational Database Service (Amazon RDS) for PostgreSQL, or an Oracle database that's hosted on Amazon Elastic Compute Cloud (Amazon EC2) to either Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible.
## Prerequisites and limitations
**Prerequisites**
+ An active AWS account
+ An Oracle source database in an on-premises data center with Active Data Guard standby configured
+ AWS Direct Connect configured between the on-premises data center and the AWS Cloud
+ Familiarity with [using an Oracle database as a source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html)
+ Familiarity with [using a PostgreSQL database as a target for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html)
**Limitations**
+ Amazon Aurora database clusters can be created with up to 128 TiB of storage. Amazon RDS for PostgreSQL database instances can be created with up to 64 TiB of storage. For the latest storage information, see [Amazon Aurora storage and reliability](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.StorageReliability.html) and [Amazon RDS DB instance storage](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Storage.html) in the AWS documentation.
**Product versions**
+ AWS DMS supports all Oracle database editions for versions 10.2 and later (for versions 10.x), 11g and up to 12.2, 18c, and 19c. For the latest list of supported versions, see [Using an Oracle Database as a Source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html) in the AWS documentation.
## Architecture
**Source technology stack**
+ On-premises Oracle databases with Oracle Active Data Guard standby configured
**Target technology stack**
+ Aurora PostgreSQL-Compatible
**Data migration architecture**
![\[Migrating an Oracle database to Aurora PostgreSQL-Compatible\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/49f9b03e-6d33-4ac0-94ad-d3e6d02e6d63/images/0038a36b-fb7d-4f2d-8376-8d38290b0736.png)
## Tools
+ **AWS DMS** - [AWS Database Migration Service](https://docs.aws.amazon.com/dms/index.html) (AWS DMS) supports several source and target databases. See [Using an Oracle Database as a Source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html) in the AWS DMS documentation for a list of supported Oracle source and target database versions and editions. If the source database is not supported by AWS DMS, you must select another method for migrating the data in Phase 6 (in the *Epics *section). **Important note:** Because this is a heterogeneous migration, you must first check to see whether the database supports a commercial off-the-shelf (COTS) application. If the application is COTS, consult the vendor to confirm that Aurora PostgreSQL-Compatible is supported before proceeding. For more information, see [AWS DMS Step-by-Step Migration Walkthroughs](https://docs.aws.amazon.com/dms/latest/sbs/DMS-SBS-Welcome.html) in the AWS documentation.
+ **AWS SCT** - The [AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/Welcome.htm) (AWS SCT) facilitates heterogeneous database migrations by automatically converting the source database schema and a majority of the custom code to a format that's compatible with the target database. The custom code that the tool converts includes views, stored procedures, and functions. Any code that the tool cannot convert automatically is clearly marked so that you can convert it yourself.
## Epics
### Plan the migration
| Task | Description | Skills required |
| --- | --- | --- |
| Validate the source and target database versions. | | DBA |
| Install AWS SCT and drivers. | | DBA |
| Add and validate the AWS SCT prerequisite users and grants-source database. | | DBA |
| Create an AWS SCT project for the workload, and connect to the source database. | | DBA |
| Generate an assessment report and evaluate feasibility. | | DBA, App owner |
### Prepare the target database
| Task | Description | Skills required |
| --- | --- | --- |
| Create an Aurora PostgreSQL-Compatible target database. | | DBA |
| Extract users, roles, and grants list from the source database. | | DBA |
| Map the existing database users to the new database users. | | App owner |
| Create users in the target database. | | DBA |
| Apply roles from the previous step to the target Aurora PostgreSQL-Compatible database. | | DBA |
| Review database options, parameters, network files, and database links from the source database, and evaluate their applicability to the target database. | | DBA, App owner |
| Apply any relevant settings to the target database. | | DBA |
### Prepare for database object code conversion
| Task | Description | Skills required |
| --- | --- | --- |
| Configure AWS SCT connectivity to the target database. | | DBA |
| Convert the schema in AWS SCT, and save the converted code as a .sql file. | | DBA, App owner |
| Manually convert any database objects that failed to convert automatically. | | DBA, App owner |
| Optimize the database code conversion. | | DBA, App owner |
| Separate the .sql file into multiple .sql files based on the object type. | | DBA, App owner |
| Validate the SQL scripts in the target database. | | DBA, App owner |
### Prepare for data migration
| Task | Description | Skills required |
| --- | --- | --- |
| Create an AWS DMS replication instance. | | DBA |
| Create the source and target endpoints. | If the data type of the PKs and FKs is converted from NUMBER in Oracle to BIGINT in PostgreSQL, consider specifying the connection attribute `numberDataTypeScale=-2` when you create the source endpoint. | DBA |
### Migrate data – full load
| Task | Description | Skills required |
| --- | --- | --- |
| Create the schema and tables in the target database. | | DBA |
| Create AWS DMS full-load tasks by either grouping tables or splitting a big table based on the table size. | | DBA |
| Stop the applications on the source Oracle databases for a short period. | | App owner |
| Verify that the Oracle standby database is synchronous with the primary database, and stop the replication from the primary database to the standby database. | | DBA, App owner |
| Start applications on the source Oracle database. | | App owner |
| Start the AWS DMS full-load tasks in parallel from the Oracle standby database to the Aurora PostgreSQL-Compatible database. | | DBA |
| Create PKs and secondary indexes after the full load is complete. | | DBA |
| Validate the data. | | DBA |
### Migrate data – CDC
| Task | Description | Skills required |
| --- | --- | --- |
| Create AWS DMS ongoing replication tasks by specifying a custom CDC start time or system change number (SCN) when the Oracle standby was synchronized with the primary database, and before the applications were restarted in the previous task. | | DBA |
| Start AWS DMS tasks in parallel to replicate ongoing changes from the Oracle standby database to the Aurora PostgreSQL-Compatible database. | | DBA |
| Re-establish the replication from the Oracle primary database to the standby database. | | DBA |
| Monitor the logs and stop the applications on the Oracle database when the target Aurora PostgreSQL-Compatible database is almost synchronous with the source Oracle database. | | DBA, App owner |
| Stop the AWS DMS tasks when the target is fully synchronized with the source Oracle database. | | DBA |
| Create FKs and validate the data in the target database. | | DBA |
| Create functions, views, triggers, sequences, and other object types in the target database. | | DBA |
| Apply role grants in the target database. | | DBA |
### Migrate the application
| Task | Description | Skills required |
| --- | --- | --- |
| Use AWS SCT to analyze and convert the SQL statements inside the application code. | | App owner |
| Create new application servers on AWS. | | App owner |
| Migrate the application code to the new servers. | | App owner |
| Configure the application server for the target database and drivers. | | App owner |
| Fix any code that's specific to the source database engine in the application. | | App owner |
| Optimize the application code for the target database. | | App owner |
### Cut over
| Task | Description | Skills required |
| --- | --- | --- |
| Point the new application server to the target database. | | DBA, App owner |
| Perform sanity checks. | | DBA, App owner |
| Go live. | | DBA, App owner |
### Close the project
| Task | Description | Skills required |
| --- | --- | --- |
| Shut down temporary AWS resources. | | DBA, Systems administrator |
| Review and validate the project documents. | | DBA, App owner |
| Gather metrics for time to migrate, percentage of manual versus tool use, cost savings, and similar data. | | DBA, App owner |
| Close out the project and provide feedback. | | DBA, App owner |
## Related resources
**References**
+ [Oracle Database to Aurora PostgreSQL-Compatible: Migration Playbook](https://d1.awsstatic.com/whitepapers/Migration/oracle-database-amazon-aurora-postgresql-migration-playbook.pdf)
+ [Migrating an Amazon RDS for Oracle Database to Amazon Aurora MySQL](https://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2aurora.html)
+ [AWS DMS website](https://aws.amazon.com/dms/)
+ [AWS DMS documentation](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html)
+ [AWS SCT website](https://aws.amazon.com/dms/schema-conversion-tool/)
+ [AWS SCT documentation](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
+ [Migrate from Oracle to Amazon Aurora](https://aws.amazon.com/getting-started/projects/migrate-oracle-to-amazon-aurora/)
**Tutorials**
+ [Getting Started with AWS DMS](https://aws.amazon.com/dms/getting-started/)
+ [Getting Started with Amazon RDS](https://aws.amazon.com/rds/getting-started/)
+ [AWS Database Migration Service Step-by-Step Walkthroughs](https://docs.aws.amazon.com/dms/latest/sbs/dms-sbs-welcome.html)
# Migrate from SAP ASE to Amazon RDS for SQL Server using AWS DMS
*Amit Kumar, Amazon Web Services*
## Summary
This pattern provides guidance for migrating an SAP Adaptive Server Enterprise (ASE) database to an Amazon Relational Database Service (Amazon RDS) DB instance that's running Microsoft SQL Server. The source database can be located in an on-premises data center or on an Amazon Elastic Compute Cloud (Amazon EC2) instance. The pattern uses AWS Database Migration Service (AWS DMS) to migrate data and (optionally) computer-aided software engineering (CASE) tools to convert the database schema.
## Prerequisites and limitations
**Prerequisites**
+ An active AWS account
+ An SAP ASE database in an on-premises data center or on an EC2 instance
+ A target Amazon RDS for SQL Server database that’s up and running
**Limitations**
+ Database size limit: 64 TB
**Product versions**
+ SAP ASE version 15.7 or 16.x only. For the latest information, see [Using an SAP Database as a Source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SAP.html).
+ For Amazon RDS target databases, AWS DMS supports [Microsoft SQL Server versions on Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html#SQLServer.Concepts.General.VersionSupport) for the Enterprise, Standard, Web, and Express editions. For the latest information about supported versions, see the [AWS DMS documentation](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.SQLServer.html). We recommend that you use the latest version of AWS DMS for the most comprehensive version and feature support.
## Architecture
**Source technology stack **
+ An SAP ASE database that's on premises or on an Amazon EC2 instance
**Target technology stack **
+ An Amazon RDS for SQL Server DB instance
**Source and target architecture**
*From an SAP ASE database on Amazon EC2 to an Amazon RDS for SQL Server DB instance:*
![\[Target architecture for SAP ASE on Amazon EC2 to Amazon RDS for SQL Server\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/5ca697a2-9ca3-4231-b457-c1dc59ada5f1/images/957bdcf0-ab58-4b6d-a71a-d0ecbc31822c.png)
*From an on-premises SAP ASE database to an Amazon RDS for SQL Server DB instance:*
![\[Target architecture for on-premises SAP ASE to Amazon RDS for SQL Server\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/5ca697a2-9ca3-4231-b457-c1dc59ada5f1/images/65aab2f5-0e63-4c34-97e2-cd4ac23751a4.png)
## Tools
+ [AWS Database Migration Service](https://docs.aws.amazon.com/dms/) (AWS DMS) is a web service you can use to migrate data from your database that is on-premises, on an Amazon RDS DB instance, or in a database on an EC2 instance, to a database on an AWS service such as Amazon RDS for SQL Server or an EC2 instance. You can also migrate a database from an AWS service to an on-premises database. You can migrate data between heterogeneous or homogeneous database engines.
+ For schema conversions, you can optionally use [erwin Data Modeler](https://erwin.com/products/erwin-data-modeler/) or [SAP PowerDesigner](https://www.sap.com/products/technology-platform/powerdesigner-data-modeling-tools.html).
## Epics
### Plan the migration
| Task | Description | Skills required |
| --- | --- | --- |
| Validate the source and target database versions. | | DBA |
| Identify the storage requirements (storage type and capacity). | | DBA, SysAdmin |
| Choose the proper instance type based on capacity, storage features, and network features. | | DBA, SysAdmin |
| Identify the network access security requirements for the source and target databases. | | DBA, SysAdmin |
| Identify the application migration strategy. | | DBA, SysAdmin, App owner |
### Configure the infrastructure
| Task | Description | Skills required |
| --- | --- | --- |
| Create a virtual private cloud (VPC) and subnets. | | SysAdmin |
| Create security groups and network access control lists (ACLs). | | SysAdmin |
| Configure and start an Amazon RDS DB instance. | | SysAdmin |
### Migrate data - option 1
| Task | Description | Skills required |
| --- | --- | --- |
| Migrate the database schema manually or use a CASE tool such as erwin Data Modeler or SAP PowerDesigner. | | DBA |
### Migrate data - option 2
| Task | Description | Skills required |
| --- | --- | --- |
| Migrate data using AWS DMS. | | DBA |
### Migrate the application
| Task | Description | Skills required |
| --- | --- | --- |
| Follow the application migration strategy. | | DBA, SysAdmin, App owner |
### Cut over
| Task | Description | Skills required |
| --- | --- | --- |
| Switch the application clients over to the new infrastructure. | | DBA, SysAdmin, App owner |
### Close the project
| Task | Description | Skills required |
| --- | --- | --- |
| Shut down the temporary AWS resources. | | DBA, SysAdmin |
| Review and validate the project documents. | | DBA, SysAdmin, App owner |
| Gather metrics such as time to migrate, percentage of manual versus automated tasks, and cost savings. | | DBA, SysAdmin, App owner |
| Close out the project and provide feedback. | | DBA, SysAdmin, App owner |
## Related resources
**References**
+ [AWS DMS website](https://aws.amazon.com/dms/)
+ [Amazon RDS Pricing](https://aws.amazon.com/rds/pricing/)
+ [Using a SAP ASE Database as a Source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SAP.html)
+ [Limitations for RDS Custom for SQL Server](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/custom-reqs-limits-MS.html)
**Tutorials and videos**
+ [Getting Started with AWS DMS](https://aws.amazon.com/dms/getting-started/)
+ [Getting Started with Amazon RDS](https://aws.amazon.com/rds/getting-started/)
+ [AWS DMS (video)](https://www.youtube.com/watch?v=zb4GcjEdl8U)
+ [Amazon RDS (video)](https://www.youtube.com/watch?v=igRfulrrYCo)
# Migrate an on-premises Microsoft SQL Server database to Amazon Redshift using AWS DMS
*Marcelo Fernandes, Amazon Web Services*
## Summary
This pattern provides guidance for migrating an on-premises Microsoft SQL Server database to Amazon Redshift by using AWS Data Migration Service (AWS DMS).
## Prerequisites and limitations
**Prerequisites**
+ An active AWS account
+ A source Microsoft SQL Server database in an on-premises data center
+ Completed prerequisites for using an Amazon Redshift database as a target for AWS DMS, as discussed in the [AWS DMS documentation](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Redshift.html#CHAP_Target.Redshift.Prerequisites)
**Product versions**
+ SQL Server 2005-2019, Enterprise, Standard, Workgroup, Developer, and Web editions. For the latest list of supported versions, see [Using a Microsoft SQL Server Database as a Source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html) in the AWS documentation.
## Architecture
**Source technology stack**
+ An on-premises Microsoft SQL Server database
**Target technology stack**
+ Amazon Redshift
**Data migration architecture**
![\[Architecture for migrating an on-premises SQL Server database to Amazon Redshift using AWS DMS\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/65b2be1b-740e-4d4d-99a8-f77c4ea6553d/images/3a094bf2-be31-4d83-8dd2-9dc078321055.png)
## Tools
+ [AWS DMS ](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html)is a data migration service that supports several types of source and target databases. For information about the Microsoft SQL Server database versions and editions that are supported for use with AWS DMS, see [Using a Microsoft SQL Server Database as a Source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html) in the AWS DMS documentation. If AWS DMS doesn't support your source database, you must select an alternative method for data migration.
## Epics
### Plan the migration
| Task | Description | Skills required |
| --- | --- | --- |
| Validate the source and target database version and engine. | | DBA |
| Identify the hardware requirements for the target server instance. | | DBA, Systems administrator |
| Identify the storage requirements (storage type and capacity). | | DBA, Systems administrator |
| Choose the proper instance type based on capacity, storage features, and network features. | | DBA, Systems administrator |
| Identify the network access security requirements for the source and target databases. | | DBA, Systems administrator |
| Identify the application migration strategy. | | DBA, App owner, Systems administrator |
### Configure the infrastructure
| Task | Description | Skills required |
| --- | --- | --- |
| Create a virtual private cloud (VPC). | For more information, see [Working with a DB instance in a VPC](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html) in the AWS documentation. | Systems administrator |
| Create security groups. | | Systems administrator |
| Configure and start an Amazon Redshift cluster. | For more information, see [Create a sample Amazon Redshift cluster](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-launch-sample-cluster.html) in the Amazon Redshift documentation. | DBA, Systems administrator |
### Migrate data
| Task | Description | Skills required |
| --- | --- | --- |
| Migrate the data from the Microsoft SQL Server database by using AWS DMS. | | DBA |
### Migrate the application
| Task | Description | Skills required |
| --- | --- | --- |
| Follow the application migration strategy. | | DBA, App owner, Systems administrator |
### Cut over
| Task | Description | Skills required |
| --- | --- | --- |
| Switch the application clients over to the new infrastructure. | | DBA, App owner, Systems administrator |
### Close the project
| Task | Description | Skills required |
| --- | --- | --- |
| Shut down the temporary resources. | | DBA, Systems administrator |
| Review and validate the project documents. | | DBA, App owner, Systems administrator |
| Gather metrics such as time to migrate, percentage of manual versus automated tasks, and cost savings. | | DBA, App owner, Systems administrator |
| Close out the project and provide feedback. | | DBA, App owner, Systems administrator |
## Related resources
**References**
+ [AWS DMS documentation](https://docs.aws.amazon.com/dms/index.html)
+ [Amazon Redshift documentation](https://docs.aws.amazon.com/redshift/)
+ [Amazon Redshift Pricing](https://aws.amazon.com/redshift/pricing/)
**Tutorials and videos**
+ [Getting Started with AWS DMS](https://aws.amazon.com/dms/getting-started/)
+ [Getting Started with Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html)
+ [Using an Amazon Redshift database as a target for AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Redshift.html)
+ [AWS DMS (video)](https://www.youtube.com/watch?v=zb4GcjEdl8U)
# Migrate an on-premises Microsoft SQL Server database to Amazon Redshift using AWS SCT data extraction agents
*Neha Thakur, Amazon Web Services*
## Summary
This pattern outlines steps for migrating an on-premises Microsoft SQL Server source database to an Amazon Redshift target database by using AWS Schema Conversion Tool (AWS SCT) data extraction agents. An agent is an external program that is integrated with AWS SCT but performs data transformation elsewhere and interacts with other AWS services on your behalf.
## Prerequisites and limitations
**Prerequisites**
+ A Microsoft SQL Server source database used for the data warehouse workload in an on-premises data center
+ An active AWS account
**Product versions**
+ Microsoft SQL Server version 2008 or later. For the latest list of supported versions, see [AWS SCT documentation](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html).
## Architecture
**technology stack****Source **
+ An on-premises Microsoft SQL Server database
**technology stack****Target **
+ Amazon Redshift
**Data migration architecture**
![\[Migrating a SQL Server database to Amazon Redshift by using AWS SCT data extraction agents.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/6975f67a-0705-47b4-a1b8-90aaa2597a04/images/dbff958b-7601-442e-9e23-4d07edd0ccfd.png)
## Tools
+ [AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html) (AWS SCT) handles heterogeneous database migrations by automatically converting the source database schema and a majority of the custom code to a format that's compatible with the target database. When the source and target databases are very different, you can use an AWS SCT agent to perform additional data transformation. For more information, see [Migrating Data from an On-Premises Data Warehouse to Amazon Redshift](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/agents.dw.html) in the AWS documentation.
## Best practices
+ [Best practices for AWS SCT](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_BestPractices.html)
+ [Best practices for Amazon Redshift ](https://docs.aws.amazon.com/redshift/latest/dg/best-practices.html)
## Epics
### Prepare for migration
| Task | Description | Skills required |
| --- | --- | --- |
| Validate the source and target database versions and engines. | | DBA |
| Identify hardware requirements for the target server instance. | | DBA, SysAdmin |
| Identify storage requirements (storage type and capacity). | | DBA, SysAdmin |
| Choose the proper instance type (capacity, storage features, network features). | | DBA, SysAdmin |
| Identify network access security requirements for the source and target databases. | | DBA, SysAdmin |
| Choose an application migration strategy. | | DBA, SysAdmin, App owner |
### Configure infrastructure
| Task | Description | Skills required |
| --- | --- | --- |
| Create a virtual private cloud (VPC) and subnets. | | SysAdmin |
| Create security groups. | | SysAdmin |
| Configure and start the Amazon Redshift cluster. | | SysAdmin |
### Migrate data
| Task | Description | Skills required |
| --- | --- | --- |
| Migrate the data using the AWS SCT data extraction agents. | | DBA |
### Migrate applications
| Task | Description | Skills required |
| --- | --- | --- |
| Follow the chosen application migration strategy. | | DBA, SysAdmin, App owner |
### Cut over to the target database
| Task | Description | Skills required |
| --- | --- | --- |
| Switch application clients over to the new infrastructure. | | DBA, SysAdmin, App owner |
### Close the project
| Task | Description | Skills required |
| --- | --- | --- |
| Shut down temporary AWS resources. | | DBA, SysAdmin |
| Review and validate the project documents. | | DBA, SysAdmin, App owner |
| Gather metrics such as time to migrate, percentage of manual versus automated tasks, and cost savings. | | DBA, SysAdmin, App owner |
| Close the project and provide any feedback. | | DBA, SysAdmin, App owner |
## Related resources
**References**
+ [AWS SCT User Guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html)
+ [Using Data Extraction Agents](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/agents.html)
+ [Amazon Redshift Pricing](https://aws.amazon.com/redshift/pricing/)
**Tutorials and videos**
+ [Getting Started with the AWS Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_GettingStarted.html)
+ [Getting Started with Amazon Redshift](http://docs.aws.amazon.com/redshift/latest/gsg/getting-started.html)
# Migrate legacy applications from Oracle Pro\$1C to ECPG
*Sai Parthasaradhi and Mahesh Balumuri, Amazon Web Services*
## Summary
Most legacy applications that have embedded SQL code use the Oracle Pro\$1C precompiler to access the database. When you migrate these Oracle databases to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition, you have to convert your application code to a format that’s compatible with the precompiler in PostgreSQL, which is called ECPG. This pattern describes how to convert Oracle Pro\$1C code to its equivalent in PostgreSQL ECPG.
For more information about Pro\$1C, see the [Oracle documentation](https://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_01int.htm#i2415). For a brief introduction to ECPG, see the [Additional information](#migrate-legacy-applications-from-oracle-pro-c-to-ecpg-additional) section.
## Prerequisites and limitations
**Prerequisites **
+ An active AWS account
+ An Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible database
+ An Oracle database running on premises
## Tools
+ The PostgreSQL packages listed in the next section.
+ [AWS CLI ](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html)– The AWS Command Line Interface (AWS CLI) is an open-source tool for interacting with AWS services through commands in your command-line shell. With minimal configuration, you can run AWS CLI commands that implement functionality equivalent to that provided by the browser-based AWS Management Console from a command prompt.
## Epics
### Set the build environment on CentOS or RHEL
| Task | Description | Skills required |
| --- | --- | --- |
| Install PostgreSQL packages. | Install the required PostgreSQL packages by using the following commands.yum update -y
yum install -y yum-utils
rpm -ivh https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf -qy module disable postgresql
| App developer, DevOps engineer |
| Install the header files and libraries. | Install the `postgresql12-devel` package, which contains header files and libraries, by using the following commands. Install the package in both the development and the runtime environments to avoid errors in the runtime environment.dnf -y install postgresql12-devel
yum install ncompress zip ghostscript jq unzip wget git -y
For the development environment only, also run the following commands.yum install zlib-devel make -y
ln -s /usr/pgsql-12/bin/ecpg /usr/bin/
| App developer, DevOps engineer |
| Configure the environment path variable. | Set the environment path for PostgreSQL client libraries.export PATH=$PATH:/usr/pgsql-12/bin
| App developer, DevOps engineer |
| Install additional software as necessary. | If required, install **pgLoader** as a replacement for **SQL\$1Loader** in Oracle.wget -O /etc/yum.repos.d/pgloader-ccl.repo https://dl.packager.io/srv/opf/pgloader-ccl/master/installer/el/7.repo
yum install pgloader-ccl -y
ln -s /opt/pgloader-ccl/bin/pgloader /usr/bin/
If you’re calling any Java applications from Pro\$1C modules, install Java.yum install java -y
Install **ant** to compile the Java code.yum install ant -y
| App developer, DevOps engineer |
| Install the AWS CLI. | Install the AWS CLI to run commands to interact with AWS services such as AWS Secrets Manager and Amazon Simple Storage Service (Amazon S3) from your applications.cd /tmp/
curl "https://awscli.amazonaws.com/awscli-exe-linux-x86_64.zip" -o "awscliv2.zip"
unzip awscliv2.zip
./aws/install -i /usr/local/aws-cli -b /usr/local/bin --update
| App developer, DevOps engineer |
| Identify the programs to be converted. | Identify the applications that you want to convert from Pro\$1C to ECPG. | App developer, App owner |
### Convert Pro\$1C code to ECPG
| Task | Description | Skills required |
| --- | --- | --- |
| Remove unwanted headers. | Remove the `include `headers that are not required in PostgreSQL, such as `oci.h`, `oratypes`, and `sqlda`. | App owner, App developer |
| Update variable declarations. | Add `EXEC SQL` statements for all variable declarations that are used as host variables.Remove the `EXEC SQL VAR` declarations such as the following from your application.EXEC SQL VAR query IS STRING(2048);
| App developer, App owner |
| Update ROWNUM functionality. | The `ROWNUM` function isn’t available in PostgreSQL. Replace this with the `ROW_NUMBER` window function in SQL queries.Pro\$1C code:SELECT SUBSTR(RTRIM(FILE_NAME,'.txt'),12) INTO :gcpclFileseq
FROM (SELECT FILE_NAME
FROM DEMO_FILES_TABLE
WHERE FILE_NAME LIKE '%POC%'
ORDER BY FILE_NAME DESC) FL2
WHERE ROWNUM <=1 ORDER BY ROWNUM;
ECPG code:SELECT SUBSTR(RTRIM(FILE_NAME,'.txt'),12) INTO :gcpclFileseq
FROM (SELECT FILE_NAME , ROW_NUMBER() OVER (ORDER BY FILE_NAME DESC) AS ROWNUM
FROM demo_schema.DEMO_FILES_TABLE
WHERE FILE_NAME LIKE '%POC%'
ORDER BY FILE_NAME DESC) FL2
WHERE ROWNUM <=1 ORDER BY ROWNUM;
| App developer, App owner |
| Update function parameters to use alias variables. | In PostgreSQL, function parameters can’t be used as host variables. Overwrite them by using an alias variable.Pro\$1C code:int processData(int referenceId){
EXEC SQL char col_val[100];
EXEC SQL select column_name INTO :col_val from table_name where col=:referenceId;
}ECPG code:int processData(int referenceIdParam){
EXEC SQL int referenceId = referenceIdParam;
EXEC SQL char col_val[100];
EXEC SQL select column_name INTO :col_val from table_name where col=:referenceId;
} | App developer, App owner |
| Update struct types. | Define `struct` types in `EXEC SQL BEGIN` and `END` blocks with `typedef` if the `struct` type variables are used as host variables. If the `struct` types are defined in header (`.h`) files, include the files with `EXEC SQL` include statements.Pro\$1C code:Header file (`demo.h`)struct s_partition_ranges
{
char sc_table_group[31];
char sc_table_name[31];
char sc_range_value[10];
};
struct s_partition_ranges_ind
{
short ss_table_group;
short ss_table_name;
short ss_range_value;
};
ECPG code:Header file (`demo.h`)EXEC SQL BEGIN DECLARE SECTION;
typedef struct
{
char sc_table_group[31];
char sc_table_name[31];
char sc_range_value[10];
} s_partition_ranges;
typedef struct
{
short ss_table_group;
short ss_table_name;
short ss_range_value;
} s_partition_ranges_ind;
EXEC SQL END DECLARE SECTION;
Pro\$1C file (`demo.pc`)#include "demo.h"
struct s_partition_ranges gc_partition_data[MAX_PART_TABLE] ;
struct s_partition_ranges_ind gc_partition_data_ind[MAX_PART_TABLE] ;
ECPG file (`demo.pc`)exec sql include "demo.h"
EXEC SQL BEGIN DECLARE SECTION;
s_partition_ranges gc_partition_data[MAX_PART_TABLE] ;
s_partition_ranges_ind gc_partition_data_ind[MAX_PART_TABLE] ;
EXEC SQL END DECLARE SECTION;
| App developer, App owner |
| Modify logic to fetch from cursors. | To fetch multiple rows from cursors by using array variables, change the code to use `FETCH FORWARD`.Pro\$1C code:EXEC SQL char aPoeFiles[MAX_FILES][FILENAME_LENGTH];
EXEC SQL FETCH filename_cursor into :aPoeFiles;
ECPG code:EXEC SQL char aPoeFiles[MAX_FILES][FILENAME_LENGTH];
EXEC SQL int fetchSize = MAX_FILES;
EXEC SQL FETCH FORWARD :fetchSize filename_cursor into :aPoeFiles;
| App developer, App owner |
| Modify package calls that don't have return values. | Oracle package functions that don’t have return values should be called with an indicator variable. If your application includes multiple functions that have the same name or if the unknown type functions generate runtime errors, typecast the values to the data types.Pro\$1C code:void ProcessData (char *data , int id)
{
EXEC SQL EXECUTE
BEGIN
pkg_demo.process_data (:data, :id);
END;
END-EXEC;
}
ECPG code:void ProcessData (char *dataParam, int idParam )
{
EXEC SQL char *data = dataParam;
EXEC SQL int id = idParam;
EXEC SQL short rowInd;
EXEC SQL short rowInd = 0;
EXEC SQL SELECT pkg_demo.process_data (
inp_data => :data::text,
inp_id => :id
) INTO :rowInd;
}
| App developer, App owner |
| Rewrite SQL\$1CURSOR variables. | Rewrite the `SQL_CURSOR` variable and its implementation.Pro\$1C code:/* SQL Cursor */
SQL_CURSOR demo_cursor;
EXEC SQL ALLOCATE :demo_cursor;
EXEC SQL EXECUTE
BEGIN
pkg_demo.get_cursor(
demo_cur=>:demo_cursor
);
END;
END-EXEC;
ECPG code:EXEC SQL DECLARE demo_cursor CURSOR FOR SELECT
* from
pkg_demo.open_filename_rc(
demo_cur=>refcursor
) ;
EXEC SQL char open_filename_rcInd[100];
# As the below function returns cursor_name as
# return we need to use char[] type as indicator.
EXEC SQL SELECT pkg_demo.get_cursor (
demo_cur=>'demo_cursor'
) INTO :open_filename_rcInd;
| App developer, App owner |
| Apply common migration patterns. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-legacy-applications-from-oracle-pro-c-to-ecpg.html) | App developer, App owner |
| Enable debugging, if required. | To run the ECPG program in debug mode, add the following command inside the main function block.ECPGdebug(1, stderr);
| App developer, App owner |
### Compile ECPG programs
| Task | Description | Skills required |
| --- | --- | --- |
| Create an executable file for ECPG. | If you have an embedded SQL C source file named `prog1.pgc`, you can create an executable program by using the following sequence of commands.ecpg prog1.pgc
cc -I/usr/local/pgsql/include -c prog1.c
cc -o prog1 prog1.o -L/usr/local/pgsql/lib -lecpg
| App developer, App owner |
| Create a make file for compilation. | Create a make file to compile the ECPG program, as shown in the following sample file.CFLAGS ::= $(CFLAGS) -I/usr/pgsql-12/include -g -Wall
LDFLAGS ::= $(LDFLAGS) -L/usr/pgsql-12/lib -Wl,-rpath,/usr/pgsql-12/lib
LDLIBS ::= $(LDLIBS) -lecpg
PROGRAMS = test
.PHONY: all clean
%.c: %.pgc
ecpg $<
all: $(PROGRAMS)
clean:
rm -f $(PROGRAMS) $(PROGRAMS:%=%.c) $(PROGRAMS:%=%.o)
| App developer, App owner |
### Test the application
| Task | Description | Skills required |
| --- | --- | --- |
| Test the code. | Test the converted application code to make sure that it functions correctly. | App developer, App owner, Test engineer |
## Related resources
+ [ECPG - Embedded SQL in C](https://www.postgresql.org/docs/current/static/ecpg.html) (PostgreSQL documentation)
+ [Error Handling](https://www.postgresql.org/docs/12/ecpg-errors.html) (PostgreSQL documentation)
+ [Why Use the Oracle Pro\$1C/C\$1\$1 Precompiler](https://docs.oracle.com/cd/E11882_01/appdev.112/e10825/pc_01int.htm#i2415) (Oracle documentation)
## Additional information
PostgreSQL has an embedded SQL precompiler, ECPG, which is equivalent to the Oracle Pro\$1C precompiler. ECPG converts C programs that have embedded SQL statements to standard C code by replacing the SQL calls with special function calls. The output files can then be processed with any C compiler tool chain.
**Input and output files**
ECPG converts each input file you specify on the command line to the corresponding C output file. If an input file name doesn’t have a file extension, .pgc is assumed. The file's extension is replaced by `.c` to construct the output file name. However, you can override the default output file name by using the `-o` option.
If you use a dash (`-`) as the input file name, ECPG reads the program from standard input and writes to standard output, unless you override that by using the `-o` option.
**Header files**
When the PostgreSQL compiler compiles the pre-processed C code files, it looks for the ECPG header files in the PostgreSQL `include` directory. Therefore, you might have to use the `-I` option to point the compiler to the correct directory (for example, `-I/usr/local/pgsql/include`).
**Libraries**
Programs that use C code with embedded SQL have to be linked against the `libecpg` library. For example, you can use the linker options` -L/usr/local/pgsql/lib -lecpg`.
Converted ECPG applications call functions in the `libpq` library through the embedded SQL library (`ecpglib`), and communicate with the PostgreSQL server by using the standard frontend/backend protocol.
# Migrate virtual generated columns from Oracle to PostgreSQL
*Veeranjaneyulu Grandhi, Rajesh Madiwale, and Ramesh Pathuri, Amazon Web Services*
## Summary
In version 11 and earlier, PostgreSQL doesn’t provide a feature that is directly equivalent to an Oracle virtual column. Handling virtual generated columns while migrating from Oracle Database to PostgreSQL version 11 or earlier is difficult for two reasons:
+ Virtual columns aren’t visible during migration.
+ PostgreSQL doesn't support the `generate` expression before version 12.
However, there are workarounds to emulate similar functionality. When you use AWS Database Migration Service (AWS DMS) to migrate data from Oracle Database to PostgreSQL version 11 and earlier, you can use trigger functions to populate the values in virtual generated columns. This pattern provides examples of Oracle Database and PostgreSQL code that you can use for this purpose. On AWS, you can use Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition for your PostgreSQL database.
Starting with PostgreSQL version 12, generated columns are supported. Generated columns can either be calculated from other column values on the fly, or calculated and stored. [PostgreSQL generated columns](https://www.postgresql.org/docs/12/ddl-generated-columns.html) are similar to Oracle virtual columns.
## Prerequisites and limitations
**Prerequisites**
+ An active AWS account
+ A source Oracle database
+ Target PostgreSQL databases (on Amazon RDS for PostgreSQL or Aurora PostgreSQL-Compatible)
+ [PL/pgSQL](https://www.postgresql.org/docs/current/plpgsql.html) coding expertise
**Limitations**
+ Applies only to PostgreSQL versions before version 12.
+ Applies to Oracle Database version 11g or later.
+ Virtual columns are not supported in data migration tools.
+ Applies only to columns defined in the same table.
+ If a virtual generated column refers to a deterministic user-defined function, it cannot be used as a partitioning key column.
+ The output of the expression must be a scalar value. It cannot return an Oracle supplied datatype, a user-defined type, `LOB`, or `LONG RAW`.
+ Indexes that are defined against virtual columns are equivalent to function-based indexes in PostgreSQL.
+ Table statistics must be gathered.
## Tools
+ [pgAdmin 4](https://www.pgadmin.org/) is an open source management tool for PostgreSQL. This tool provides a graphical interface that simplifies the creation, maintenance, and use of database objects.
+ [Oracle SQL Developer](https://www.oracle.com/database/sqldeveloper/) is a free, integrated development environment for working with SQL in Oracle databases in both traditional and cloud deployments.
## Epics
### Create source and target database tables
| Task | Description | Skills required |
| --- | --- | --- |
| Create a source Oracle Database table. | In Oracle Database, create a table with virtual generated columns by using the following statement.CREATE TABLE test.generated_column
( CODE NUMBER,
STATUS VARCHAR2(12) DEFAULT 'PreOpen',
FLAG CHAR(1) GENERATED ALWAYS AS (CASE UPPER(STATUS) WHEN 'OPEN' THEN 'N' ELSE 'Y' END) VIRTUAL VISIBLE
);
In this source table, the data in the `STATUS` column is migrated through AWS DMS to the target database. The `FLAG` column, however, is populated by using `generate by` functionality, so this column isn’t visible to AWS DMS during migration. To implement the functionality of `generated by`, you must use triggers and functions in the target database to populate the values in the `FLAG` column, as shown in the next epic. | DBA, App developer |
| Create a target PostgreSQL table on AWS. | Create a PostgreSQL table on AWS by using the following statement.CREATE TABLE test.generated_column
(
code integer not null,
status character varying(12) not null ,
flag character(1)
);
In this table, the `status` column is a standard column. The `flag` column will be a generated column based on the data in the `status` column. | DBA, App developer |
### Create a trigger function to handle the virtual column in PostgreSQL
| Task | Description | Skills required |
| --- | --- | --- |
| Create a PostgreSQL trigger. | In PostgreSQL, create a trigger.CREATE TRIGGER tgr_gen_column
AFTER INSERT OR UPDATE OF status ON test.generated_column
FOR EACH ROW
EXECUTE FUNCTION test.tgf_gen_column();
| DBA, App developer |
| Create a PostgreSQL trigger function. | In PostgreSQL, create a function for the trigger. This function populates a virtual column that is inserted or updated by the application or AWS DMS, and validates the data.CREATE OR REPLACE FUNCTION test.tgf_gen_column() RETURNS trigger AS $VIRTUAL_COL$
BEGIN
IF (TG_OP = 'INSERT') THEN
IF (NEW.flag IS NOT NULL) THEN
RAISE EXCEPTION 'ERROR: cannot insert into column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF (TG_OP = 'UPDATE') THEN
IF (NEW.flag::VARCHAR != OLD.flag::varchar) THEN
RAISE EXCEPTION 'ERROR: cannot update column "flag"' USING DETAIL = 'Column "flag" is a generated column.';
END IF;
END IF;
IF TG_OP IN ('INSERT','UPDATE') THEN
IF (old.flag is NULL) OR (coalesce(old.status,'') != coalesce(new.status,'')) THEN
UPDATE test.generated_column
SET flag = (CASE UPPER(status) WHEN 'OPEN' THEN 'N' ELSE 'Y' END)
WHERE code = new.code;
END IF;
END IF;
RETURN NEW;
END
$VIRTUAL_COL$ LANGUAGE plpgsql;
| DBA, App developer |
### Test data migration by using AWS DMS
| Task | Description | Skills required |
| --- | --- | --- |
| Create a replication instance. | To create a replication instance, follow the [instructions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_ReplicationInstance.Creating.html) in the AWS DMS documentation. The replication instance should be in the same virtual private cloud (VPC) as your source and target databases. | DBA, App developer |
| Create source and target endpoints. | To create the endpoints, follow the [instructions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Endpoints.Creating.html) in the AWS DMS documentation. | DBA, App developer |
| Test the endpoint connections. | You can test the endpoint connections by specifying the VPC and replication instance and choosing **Run test**. | DBA, App developer |
| Create and start a full load task. | For instructions, see [Creating a Task](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.Creating.html) and [Full-load task settings](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.html) in the AWS DMS documentation. | DBA, App developer |
| Validate the data for the virtual column. | Compare the data in the virtual column in the source and target databases. You can validate the data manually or write a script for this step. | DBA, App developer |
## Related resources
+ [Getting started with AWS Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html) (AWS DMS documentation)
+ [Using an Oracle database as a source for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html) (AWS DMS documentation)
+ [Using a PostgreSQL database as a target for AWS DMS](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html) (AWS DMS documentation)
+ [Generated columns in PostgreSQL](https://www.postgresql.org/docs/12/ddl-generated-columns.html) (PostgreSQL documentation)
+ [Trigger functions](https://www.postgresql.org/docs/12/plpgsql-trigger.html) (PostgreSQL documentation)
+ [Virtual columns](https://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402) in Oracle Database (Oracle documentation)
# Set up Oracle UTL\$1FILE functionality on Aurora PostgreSQL-Compatible
*Rakesh Raghav and anuradha chintha, Amazon Web Services*
## Summary
As part of your migration journey from Oracle to Amazon Aurora PostgreSQL-Compatible Edition on the Amazon Web Services (AWS) Cloud, you might encounter multiple challenges. For example, migrating code that relies on the Oracle `UTL_FILE` utility is always a challenge. In Oracle PL/SQL, the `UTL_FILE` package is used for file operations, such as read and write, in conjunction with the underlying operating system. The `UTL_FILE` utility works for both server and client machine systems.
Amazon Aurora PostgreSQL-Compatible is a managed database offering. Because of this, it isn't possible to access files on the database server. This pattern walks you through the integration of Amazon Simple Storage Service (Amazon S3) and Amazon Aurora PostgreSQL-Compatible to achieve a subset of `UTL_FILE` functionality. Using this integration, we can create and consume files without using third-party extract, transform, and load (ETL) tools or services.
Optionally, you can set up Amazon CloudWatch monitoring and Amazon SNS notifications.
We recommend thoroughly testing this solution before implementing it in a production environment.
## Prerequisites and limitations
**Prerequisites **
+ An active AWS account
+ AWS Database Migration Service (AWS DMS) expertise
+ Expertise in PL/pgSQL coding
+ An Amazon Aurora PostgreSQL-Compatible cluster
+ An S3 bucket
**Limitations **
This pattern doesn't provide the functionality to act as a replacement for the Oracle `UTL_FILE` utility. However, the steps and sample code can be enhanced further to achieve your database modernization goals.
**Product versions**
+ Amazon Aurora PostgreSQL-Compatible Edition 11.9
## Architecture
**Target technology stack**
+ Amazon Aurora PostgreSQL-Compatible
+ Amazon CloudWatch
+ Amazon Simple Notification Service (Amazon SNS)
+ Amazon S3
**Target architecture **
The following diagram shows a high-level representation of the solution.
![\[Data files are uploaded to an S3 bucket, processed using the aws_s3 extension, and sent to the Aurora instance.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/3aeecd46-1f87-41f9-a9cd-f8181f92e83f/images/4a6c5f5c-58fb-4355-b243-d09a15c1cec6.png)
1. Files are uploaded from the application into the S3 bucket.
1. The `aws_s3` extension accesses the data, using PL/pgSQL, and uploads the data to Aurora PostgreSQL-Compatible.
## Tools
+ [Amazon Aurora PostgreSQL-Compatible](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) – Amazon Aurora PostgreSQL-Compatible Edition is a fully managed, PostgreSQL-compatible, and ACID-compliant relational database engine. It combines the speed and reliability of high-end commercial databases with the cost-effectiveness of open-source databases.
+ [AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html) – The AWS Command Line Interface (AWS CLI) is a unified tool to manage your AWS services. With only one tool to download and configure, you can control multiple AWS services from the command line and automate them through scripts.
+ [Amazon CloudWatch](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/WhatIsCloudWatch.html) – Amazon CloudWatch monitors Amazon S3 resources and use.
+ [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html) – Amazon Simple Storage Service (Amazon S3) is storage for the internet. In this pattern, Amazon S3 provides a storage layer to receive and store files for consumption and transmission to and from the Aurora PostgreSQL-Compatible cluster.
+ [aws\$1s3](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html#aws_s3.table_import_from_s3) – The `aws_s3` extension integrates Amazon S3 and Aurora PostgreSQL-Compatible.
+ [Amazon SNS](https://docs.aws.amazon.com/sns/latest/dg/welcome.html) – Amazon Simple Notification Service (Amazon SNS) coordinates and manages the delivery or sending of messages between publishers and clients. In this pattern, Amazon SNS is used to send notifications.
+ [pgAdmin](https://www.pgadmin.org/docs/) – pgAdmin is an open-source management tool for Postgres. pgAdmin 4 provides a graphical interface for creating, maintaining, and using database objects.
**Code**
To achieve the required functionality, the pattern creates multiple functions with naming similar to `UTL_FILE`. The *Additional information* section contains the code base for these functions.
In the code, replace `testaurorabucket` with the name of your test S3 bucket. Replace `us-east-1` with the AWS Region where your test S3 bucket is located.
## Epics
### Integrate Amazon S3 and Aurora PostgreSQL-Compatible
| Task | Description | Skills required |
| --- | --- | --- |
| Set up IAM policies. | Create AWS Identity and Access Management (IAM) policies that grant access to the S3 bucket and objects in it. For the code, see the *Additional information* section. | AWS administrator, DBA |
| Add Amazon S3 access roles to Aurora PostgreSQL. | Create two IAM roles: one role for read and one role for write access to Amazon S3. Attach the two roles to the Aurora PostgreSQL-Compatible cluster: [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/set-up-oracle-utl_file-functionality-on-aurora-postgresql-compatible.html)For more information, see the Aurora PostgreSQL-Compatible documentation on [importing](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html) and [exporting](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/postgresql-s3-export.html) data to Amazon S3. | AWS administrator, DBA |
### Set up the extensions in Aurora PostgreSQL-Compatible
| Task | Description | Skills required |
| --- | --- | --- |
| Create the aws\$1commons extension. | The `aws_commons` extension is a dependency of the `aws_s3` extension. | DBA, Developer |
| Create the aws\$1s3 extension. | The `aws_s3` extension interacts with Amazon S3. | DBA, Developer |
### Validate Amazon S3 and Aurora PostgreSQL-Compatible integration
| Task | Description | Skills required |
| --- | --- | --- |
| Test importing files from Amazon S3 into Aurora PostgreSQL. | To test importing files into Aurora PostgreSQL-Compatible, create a sample CSV file and upload it into the S3 bucket. Create a table definition based on the CSV file, and load the file into the table by using the `aws_s3.table_import_from_s3` function. | DBA, Developer |
| Test exporting files from Aurora PostgreSQL to Amazon S3. | To test exporting files from Aurora PostgreSQL-Compatible, create a test table, populate it with data, and then export the data by using the `aws_s3.query_export_to_s3` function. | DBA, Developer |
### To mimic the UTL\$1FILE utility, create wrapper functions
| Task | Description | Skills required |
| --- | --- | --- |
| Create the utl\$1file\$1utility schema. | The schema keeps the wrapper functions together. To create the schema, run the following command.CREATE SCHEMA utl_file_utility;
| DBA, Developer |
| Create the file\$1type type. | To create the `file_type` type, use the following code.CREATE TYPE utl_file_utility.file_type AS (
p_path character varying(30),
p_file_name character varying
);
| DBA/Developer |
| Create the init function. | The `init` function initializes common variable such as `bucket` or `region`. For the code, see the *Additional information* section. | DBA/Developer |
| Create the wrapper functions. | Create the wrapper functions `fopen`, `put_line`, and `fclose`. For code, see the *Additional information* section. | DBA, Developer |
### Test the wrapper functions
| Task | Description | Skills required |
| --- | --- | --- |
| Test the wrapper functions in write mode. | To test the wrapper functions in write mode, use the code provided in the *Additional information* section. | DBA, Developer |
| Test the wrapper functions in append mode. | To test the wrapper functions in append mode, use the code provide in the *Additional information* section. | DBA, Developer |
## Related resources
+ [Amazon S3 integration](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/USER_PostgreSQL.S3Import.html)
+ [Amazon S3](https://aws.amazon.com/s3/)
+ [Aurora](https://aws.amazon.com/rds/aurora/?nc2=h_ql_prod_db_aa&aurora-whats-new.sort-by=item.additionalFields.postDateTime&aurora-whats-new.sort-order=desc)
+ [Amazon CloudWatch](https://aws.amazon.com/cloudwatch/)
+ [Amazon SNS](https://aws.amazon.com/sns/?nc2=h_ql_prod_ap_sns&whats-new-cards.sort-by=item.additionalFields.postDateTime&whats-new-cards.sort-order=desc)
## Additional information
**Set up IAM policies**
Create the following policies.
|
|
| Policy name | JSON |
| --- |--- |
| S3IntRead | {
"Version": "2012-10-17",
"Statement": [
{
"Sid": "S3integrationtest",
"Effect": "Allow",
"Action": [
"s3:GetObject",
"s3:ListBucket"
],
"Resource": [
"arn:aws:s3:::testaurorabucket/*",
"arn:aws:s3:::testaurorabucket"
]
}
]
} |
| S3IntWrite | {
"Version": "2012-10-17",
"Statement": [
{
"Sid": "S3integrationtest",
"Effect": "Allow",
"Action": [
"s3:PutObject",
"s3:ListBucket"
],
"Resource": [ "arn:aws:s3:::testaurorabucket/*", "arn:aws:s3:::testaurorabucket"
]
}
]
} |
**Create the init function**
To initialize common variables, such as `bucket` or `region`, create the `init` function by using the following code.
```
CREATE OR REPLACE FUNCTION utl_file_utility.init(
)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
BEGIN
perform set_config
( format( '%s.%s','UTL_FILE_UTILITY', 'region' )
, 'us-east-1'::text
, false );
perform set_config
( format( '%s.%s','UTL_FILE_UTILITY', 's3bucket' )
, 'testaurorabucket'::text
, false );
END;
$BODY$;
```
**Create the wrapper functions**
Create the `fopen`, `put_line`, and `fclose` wrapper functions.
*fopen*
```
CREATE OR REPLACE FUNCTION utl_file_utility.fopen(
p_file_name character varying,
p_path character varying,
p_mode character DEFAULT 'W'::bpchar,
OUT p_file_type utl_file_utility.file_type)
RETURNS utl_file_utility.file_type
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
declare
v_sql character varying;
v_cnt_stat integer;
v_cnt integer;
v_tabname character varying;
v_filewithpath character varying;
v_region character varying;
v_bucket character varying;
BEGIN
/*initialize common variable */
PERFORM utl_file_utility.init();
v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );
v_bucket := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );
/* set tabname*/
v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );
v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;
raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region;
/* APPEND MODE HANDLING; RETURN EXISTING FILE DETAILS IF PRESENT ELSE CREATE AN EMPTY FILE */
IF p_mode = 'A' THEN
v_sql := concat_ws('','create temp table if not exists ', v_tabname,' (col1 text)');
execute v_sql;
begin
PERFORM aws_s3.table_import_from_s3
( v_tabname,
'',
'DELIMITER AS ''#''',
aws_commons.create_s3_uri
( v_bucket,
v_filewithpath ,
v_region)
);
exception
when others then
raise notice 'File load issue ,%',sqlerrm;
raise;
end;
execute concat_ws('','select count(*) from ',v_tabname) into v_cnt;
IF v_cnt > 0
then
p_file_type.p_path := p_path;
p_file_type.p_file_name := p_file_name;
else
PERFORM aws_s3.query_export_to_s3('select ''''',
aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)
);
p_file_type.p_path := p_path;
p_file_type.p_file_name := p_file_name;
end if;
v_sql := concat_ws('','drop table ', v_tabname);
execute v_sql;
ELSEIF p_mode = 'W' THEN
PERFORM aws_s3.query_export_to_s3('select ''''',
aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)
);
p_file_type.p_path := p_path;
p_file_type.p_file_name := p_file_name;
END IF;
EXCEPTION
when others then
p_file_type.p_path := p_path;
p_file_type.p_file_name := p_file_name;
raise notice 'fopenerror,%',sqlerrm;
raise;
END;
$BODY$;
```
*put\$1line*
```
CREATE OR REPLACE FUNCTION utl_file_utility.put_line(
p_file_name character varying,
p_path character varying,
p_line text,
p_flag character DEFAULT 'W'::bpchar)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
/**************************************************************************
* Write line, p_line in windows format to file, p_fp - with carriage return
* added before new line.
**************************************************************************/
declare
v_sql varchar;
v_ins_sql varchar;
v_cnt INTEGER;
v_filewithpath character varying;
v_tabname character varying;
v_bucket character varying;
v_region character varying;
BEGIN
PERFORM utl_file_utility.init();
/* check if temp table already exist */
v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );
v_sql := concat_ws('','select count(1) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace where n.nspname like ''pg_temp_%'''
,' AND pg_catalog.pg_table_is_visible(c.oid) AND Upper(relname) = Upper( '''
, v_tabname ,''' ) ');
execute v_sql into v_cnt;
IF v_cnt = 0 THEN
v_sql := concat_ws('','create temp table ',v_tabname,' (col text)');
execute v_sql;
/* CHECK IF APPEND MODE */
IF upper(p_flag) = 'A' THEN
PERFORM utl_file_utility.init();
v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );
v_bucket := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );
/* set tabname*/
v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;
begin
PERFORM aws_s3.table_import_from_s3
( v_tabname,
'',
'DELIMITER AS ''#''',
aws_commons.create_s3_uri
( v_bucket,
v_filewithpath,
v_region )
);
exception
when others then
raise notice 'Error Message : %',sqlerrm;
raise;
end;
END IF;
END IF;
/* INSERT INTO TEMP TABLE */
v_ins_sql := concat_ws('','insert into ',v_tabname,' values(''',p_line,''')');
execute v_ins_sql;
RETURN TRUE;
exception
when others then
raise notice 'Error Message : %',sqlerrm;
raise;
END;
$BODY$;
```
*fclose*
```
CREATE OR REPLACE FUNCTION utl_file_utility.fclose(
p_file_name character varying,
p_path character varying)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
v_filewithpath character varying;
v_bucket character varying;
v_region character varying;
v_tabname character varying;
v_sql character varying;
BEGIN
PERFORM utl_file_utility.init();
v_region := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 'region' ) );
v_bucket := current_setting( format( '%s.%s', 'UTL_FILE_UTILITY', 's3bucket' ) );
v_tabname := substring(p_file_name,1,case when strpos(p_file_name,'.') = 0 then length(p_file_name) else strpos(p_file_name,'.') - 1 end );
v_filewithpath := case when NULLif(p_path,'') is null then p_file_name else concat_ws('/',p_path,p_file_name) end ;
raise notice 'v_bucket %, v_filewithpath % , v_region %', v_bucket,v_filewithpath, v_region ;
/* exporting to s3 */
perform aws_s3.query_export_to_s3
(concat_ws('','select * from ',v_tabname,' order by ctid asc'),
aws_commons.create_s3_uri(v_bucket, v_filewithpath, v_region)
);
v_sql := concat_ws('','drop table ', v_tabname);
execute v_sql;
RETURN TRUE;
EXCEPTION
when others then
raise notice 'error fclose %',sqlerrm;
RAISE;
END;
$BODY$;
```
**Test your setup and wrapper functions**
Use the following anonymous code blocks to test your setup.
*Test the write mode*
The following code writes a file named `s3inttest` in the S3 bucket.
```
do $$
declare
l_file_name varchar := 's3inttest' ;
l_path varchar := 'integration_test' ;
l_mode char(1) := 'W';
l_fs utl_file_utility.file_type ;
l_status boolean;
begin
select * from
utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ;
raise notice 'fopen : l_fs : %', l_fs;
select * from
utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose', l_mode ) into l_status ;
raise notice 'put_line : l_status %', l_status;
select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ;
raise notice 'fclose : l_status %', l_status;
end;
$$
```
*Test the append mode*
The following code appends lines onto the `s3inttest` file that was created in the previous test.
```
do $$
declare
l_file_name varchar := 's3inttest' ;
l_path varchar := 'integration_test' ;
l_mode char(1) := 'A';
l_fs utl_file_utility.file_type ;
l_status boolean;
begin
select * from
utl_file_utility.fopen( l_file_name, l_path , l_mode ) into l_fs ;
raise notice 'fopen : l_fs : %', l_fs;
select * from
utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket: for test purpose : append 1', l_mode ) into l_status ;
raise notice 'put_line : l_status %', l_status;
select * from
utl_file_utility.put_line( l_file_name, l_path ,'this is test file:in s3bucket : for test purpose : append 2', l_mode ) into l_status ;
raise notice 'put_line : l_status %', l_status;
select * from utl_file_utility.fclose( l_file_name , l_path ) into l_status ;
raise notice 'fclose : l_status %', l_status;
end;
$$
```
**Amazon SNS notifications**
Optionally, you can set up Amazon CloudWatch monitoring and Amazon SNS notifications on the S3 bucket. For more information, see [Monitoring Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/monitoring-overview.html) and [Setting up Amazon SNS Notifications](https://docs.aws.amazon.com/AmazonCloudWatch/latest/monitoring/US_SetupSNS.html).
# Validate database objects after migrating from Oracle to Amazon Aurora PostgreSQL
*Venkatramana Chintha and Eduardo Valentim, Amazon Web Services*
## 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](https://aws.amazon.com/blogs/database/validating-database-objects-after-migration-using-aws-sct-and-aws-dms/) on the [AWS Database blog](https://aws.amazon.com/blogs/).
## Prerequisites and limitations
**Prerequisites**
+ An active AWS account.
+ An on-premises Oracle database that was migrated to an Aurora PostgreSQL-Compatible database.
+ Sign-in credentials that have the [AmazonRDSDataFullAccess](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/query-editor.html) policy applied, for the Aurora PostgreSQL-Compatible database.
+ This pattern uses the [query editor for Aurora Serverless DB clusters](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/query-editor.html), which is available in the Amazon Relational Database Service (Amazon RDS) console. However, you can use this pattern with any other query editor.
**Limitations**
+ Oracle SYNONYM objects are not available in PostgreSQL but can be partially validated through **views** or SET search\$1path queries.
+ The Amazon RDS query editor is available only in [certain AWS Regions and for certain MySQL and PostgreSQL versions](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/query-editor.html).
## Architecture
![\[Database migration workflow showing on-premises Oracle to AWSAurora PostgreSQL via client program and validation scripts.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/7c028960-6dea-46ad-894d-e42cefd50c03/images/be5f8ae3-f5af-4c5e-9440-09ab410beaa1.png)
## Tools
**Tools**
+ [Amazon Aurora PostgreSQL-Compatible Edition](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.AuroraPostgreSQL.html) – 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](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Welcome.html) – 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](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/query-editor.html) – 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 keys | Query 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
### Validate objects in the source Oracle database
| Task | Description | Skills 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\$1schema" 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 |
### Validate objects in the target Aurora PostgreSQL-Compatible database
| Task | Description | Skills 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 |
### Compare source and target database validation records
| Task | Description | Skills 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 |
## Related resources
+ [Validating database objects after a migration using AWS SCT and AWS DMS](https://aws.amazon.com/blogs/database/validating-database-objects-after-migration-using-aws-sct-and-aws-dms/)
+ [Amazon Aurora Features: PostgreSQL-Compatible Edition](https://aws.amazon.com/rds/aurora/postgresql-features/)
## Attachments
To access additional content that is associated with this document, unzip the following file: [attachment.zip](samples/p-attach/7c028960-6dea-46ad-894d-e42cefd50c03/attachments/attachment.zip)