# Migration tools and services
Migration tools and services

Each of the pages in this section describe the various tools for automating the migration of schema, objects and data, and how to use them.

**Topics**
+ [

# AWS Schema Conversion Tool
](chap-oracle-aurora-pg.tools.awssct.md)
+ [

# AWS SCT action code index
](chap-oracle-aurora-pg.tools.actioncode.md)
+ [

# AWS Database Migration Service
](chap-oracle-aurora-pg.tools.awsdms.md)
+ [

# Amazon RDS on Outposts
](chap-oracle-aurora-pg.tools.rdsoutposts.md)
+ [

# Amazon RDS Proxy
](chap-oracle-aurora-pg.tools.rdsproxy.md)
+ [

# Amazon Aurora Serverless v1
](chap-oracle-aurora-pg.tools.auroraserverless.md)

# AWS Schema Conversion Tool


The AWS Schema Conversion Tool (AWS SCT) is a Java utility that connects to source and target databases, scans the source database schema objects (tables, views, indexes, procedures, and so on), and converts them to target database objects.

This section provides a step-by-step process for using AWS SCT to migrate an Oracle database to an Aurora PostgreSQL database cluster. Since AWS SCT can automatically migrate most of the database objects, it greatly reduces manual effort.

We recommend to start every migration with the process outlined in this section and then use the rest of the Playbook to further explore manual solutions for objects that could not be migrated automatically. For more information, see [AWS SCT user guide](http://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/Welcome.html).

**Note**  
This walkthrough uses the AWS DMS Sample Database. You can download it from [GitHub](https://github.com/aws-samples/aws-database-migration-samples).

## Download the software and drivers


Download and install AWS SCT from the [AWS SCT user guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html).

Download the [Oracle Database](http://www.oracle.com/technetwork/database/features/jdbc/jdbc-drivers-12c-download-1958347.html) and [PostgreSQL](https://jdbc.postgresql.org/download) drivers.

Find other supported drivers in the [AWS SCT user guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html#CHAP_Installing.JDBCDrivers).

## Configure AWS SCT


Follow this procedure for configuring `AWSSCT` to streamline your database migration process.

1. Start AWS Schema Conversion Tool (AWS SCT).

1. Choose **Settings** and then choose **Global settings**.

1. On the left navigation bar, choose **Drivers**.

1. Enter the paths for the Oracle and PostgreSQL drivers downloaded in the first step.

    ![\[Enter the paths for the Oracle and PostgreSQL drivers\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-configure-aws-sct.png) 

1. Choose **Apply** and then **OK**.

## Create a new migration project


Create a new migration project to define the source and target databases, configure migration settings, and launch the replication process.

1. Choose **File**, and then choose **New project wizard**. Alternatively, use the keyboard shortcut **Ctrl\$1W**.

1. Enter a project name and select a location for the project files. Choose **Next**.

1. Enter connection details for the source Oracle database and choose **Test connection** to verify. Choose **Next**.

1. Select the schema or database to migrate and choose **Next**.

The progress bar displays the objects that AWS SCT analyzes. What AWS SCT completes the analysis, the application displays the database migration assessment report. Read the Executive summary and other sections. Note that the information on the screen is only partial. To read the full report, including details of the individual issues, choose **Save to PDF** at the top right and open the PDF document.

![\[Database Migration assessment report\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-aws-sct-assessment-report.png)


Scroll down to the **Database objects with conversion actions for Amazon Aurora (PostgreSQL compatible)** section.

![\[Conversion statistics\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-aws-sct-assessment-report-conversion-statistics.png)


Scroll further down to the **Detailed recommendations for Amazon Aurora (PostgreSQL compatible) migrations** section.

![\[Detailed recommendations\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-aws-sct-assessment-report-detailed-recommendations.png)


Return to AWS SCT and choose **Next**. Enter the connection details for the target Aurora PostgreSQL database and choose **Finish**.

When the connection is complete, AWS SCT displays the main window. In this interface, you can explore the individual issues and recommendations discovered by AWS SCT.

For example, expand **sample database**, **dms sample**, **Procedures**, **generate\$1tickets**. This issue has a red marker indicating it could not be automatically converted and requires a manual code change (issue 811 above). Select the object to highlight the incompatible code section.

![\[Conversion issue in the generate tickets procedure\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-aws-sct-generate-tickets.png)


Right-click the schema and then choose **Create report** to create a report tailored for the target database type. You can view this report in AWS SCT.

The progress bar updates while the report is generated.

 AWS SCT displays the executive summary page of the database migration assessment report.

![\[Assessment report executive summary\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-aws-sct-executive-summary.png)


Choose **Action items**. In this window, you can investigate each issue in detail and view the suggested course of action. For each issue, drill down to view all instances of that issue.

![\[Assessment report action items tab\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-aws-sct-action-items.png)


Right-click the database name and choose **Convert schema**. Make sure that you uncheck the `sys` and `information_schema` system schemas. Aurora PostgreSQL already has an `information_schema` schema.

![\[Convert database schema\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-aws-sct-convert-schema.png)


This step doesn’t make any changes to the target database.

On the right pane, AWS SCT displays the new virtual schema as if it exists in the target database. Drilling down into individual objects displays the actual syntax generated by AWS SCT to migrate the objects.

Right-click the database on the right pane and choose either **Apply to database** to automatically run the conversion script against the target database, or choose **Save as SQL** to save to an SQL file.

![\[Apply converted code to database\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-oracle-aurora-pg-aws-sct-apply-to-database.png)


We recommend saving to an SQL file because you can verify and QA the converted code. Also, you can make the adjustments needed for objects that could not be automatically converted.

For more information, see the [Schema Conversion Tool User Guide](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html).

# AWS SCT action code index


The following table shows the icons we use to describe the automation levels of AWS Schema Conversion Tool (AWS SCT) and AWS Database Migration Service (AWS DMS).


| Automation level icon | Description | 
| --- | --- | 
|   ![\[Five star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-5.png)   |   **Full automation**. AWS SCT performs fully automatic conversion, no manual conversion needed.  | 
|   ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)   |   **High automation**. Minor, simple manual conversions may be needed.  | 
|   ![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)   |   **Medium automation**. Low-medium complexity manual conversions may be needed.  | 
|   ![\[Two star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-2.png)   |   **Low automation**. Medium-high complexity manual conversions may be needed.  | 
|   ![\[One star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-1.png)   |   **Very low automation**. High risk or complex manual conversions may be needed.  | 
|   ![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)   |   **No automation**. Not currently supported by AWS SCT, manual conversion is required for this feature.  | 

The following sections list the AWS Schema Conversion Tool Action codes for topics that are covered in this playbook.

**Note**  
The links in the table point to the Oracle topic pages, which are immediately followed by the PostgreSQL pages for the same topics.

## SQL


![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)


 AWS SCT automatically converts the most commonly used SQL statements as both Oracle and Aurora PostgreSQL support the entry level ANSI compliance, some changes may be required for DML related to `ERROR LOG`, subquery, or partitions.


| Action code | Action message | 
| --- | --- | 
|  5024  |  PostgreSQL doesn’t support `INSERT` statements that have a partition name or partition key value.  | 
|  5064  |  PostgreSQL doesn’t support `UPDATE` statements with the `ERROR LOG` option.  | 
|  5065  |  PostgreSQL doesn’t support the `UPDATE` statement for subqueries.  | 
|  5067  |  PostgreSQL doesn’t support `DELETE` statements with the `ERROR LOG` option.  | 
|  5068  |  PostgreSQL doesn’t support the `DELETE` statement for a subqueries.  | 
|  5070  |  PostgreSQL doesn’t support `INSERT` statements with the `ERROR LOG` option.  | 
|  5071  |  PostgreSQL doesn’t support the `INSERT` statement for subqueries.  | 
|  5087  |  PostgreSQL doesn’t support `RETURNING BULK COLLECT INTO` clauses.  | 
|  5088  |  PostgreSQL doesn’t support `EXECUTE IMMEDIATE` statements with the `BULK COLLECT` clause.  | 
|  5090  |  PostgreSQL doesn’t support `INSERT` statements for a `SUBPARTITION`.  | 
|  5098  |  PostgreSQL doesn’t support `DELETE` statements for a `PARTITION`.  | 
|  5126  |  PostgreSQL doesn’t support `MODEL` statements.  | 
|  5139  |  PostgreSQL doesn’t support `FOR UPDATE SKIP LOCKED`.  | 
|  5140  |  PostgreSQL doesn’t support `BULK COLLECT INTO`.  | 
|  5144  |  PostgreSQL doesn’t support `FOR UPDATE WAIT`.  | 
|  5334  |   AWS SCT can’t convert statements with dynamic SQL.  | 
|  5352  |  PostgreSQL doesn’t support synonyms.  | 
|  5353  |  PostgreSQL doesn’t support the usage of synonyms.  | 
|  5557  |  PostgreSQL doesn’t support the `GROUPING SETS`, `CUBE`, and `ROLLUP` functions.  | 
|  5558  |  PostgreSQL doesn’t support `UPDATE` statements for partitions.  | 
|  5578  |   AWS SCT can’t convert the `SELECT` statement.  | 
|  5585  |   AWS SCT can’t convert outer joins into correlated subqueries.  | 
|  5608  |   AWS SCT can’t convert `UPDATE` statements that have a subquery that returns multiple columns in the `SET` clause.  | 
|  5663  |  PostgreSQL doesn’t explicitly support autonomous transactions.  | 

## Creating tables


![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)


 AWS SCT automatically converts the most commonly used constructs of the `CREATE TABLE` statement as both Oracle and Aurora PostgreSQL support the entry level ANSI compliance. These items include table names, containing security schema (or database), column names, basic column data types, column and table constraints, column default values, primary, candidate (UNIQUE), and foreign keys. Some changes may be required for computed columns and global temporary tables.


| Action code | Action message | 
| --- | --- | 
|  5196  |  PostgreSQL doesn’t support `OBJECT TABLE`.  | 
|  5198  |  PostgreSQL doesn’t support `GLOBAL TEMPORARY TABLE`.  | 
|  5199  |  PostgreSQL doesn’t support `CLUSTERED TABLE`.  | 
|  5200  |  PostgreSQL doesn’t support `EXTERNAL TABLES`.  | 
|  5201  |  PostgreSQL doesn’t support this partition type.  | 
|  5212  |  PostgreSQL doesn’t support the `BFILE` data type.  | 
|  5213  |  PostgreSQL ensures support of microseconds for time, datetime, and timestamp data types.  | 
|  5298  |  PostgreSQL doesn’t support `DROP STORAGE` clauses in the `TRUNCATE` statement.  | 
|  5299  |  PostgreSQL doesn’t support `REUSE STORAGE` clauses in the `TRUNCATE` statement.  | 
|  5300  |  PostgreSQL doesn’t support `PRESERVE` clauses in the `TRUNCATE` statement.  | 
|  5301  |  PostgreSQL doesn’t support `PURGE` clauses in the `TRUNCATE` statement.  | 
|  5326  |  PostgreSQL doesn’t support status definitions in `CREATE` statements for triggers and constraints.  | 
|  5348  |  PostgreSQL doesn’t support nested tables.  | 
|  5550  |  PostgreSQL doesn’t support the `ROWID` data type.  | 
|  5551  |  PostgreSQL doesn’t support the `UROWID` data type.  | 
|  5552  |  PostgreSQL ensures support of microseconds for the time, datetime, and timestamp data types.  | 
|  5553  |  PostgreSQL ensures support of microseconds for the time, datetime, and timestamp data types.  | 
|  5554  |  PostgreSQL doesn’t support virtual columns.  | 
|  5581  |  PostgreSQL doesn’t support index-organized tables.  | 
|  5620  |  The AWS SCT extension pack doesn’t support the `DELETE ROWS` option for the `ON COMMIT` clause for global temporary tables.  | 
|  5621  |  Make sure that the unique constraint for the %s field exists.  | 
|  5635  |   AWS SCT doesn’t support Oracle specific formatting settings.  | 
|  5659  |   AWS SCT can’t convert tables that include columns of the %s data type.  | 

## Data types


![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)


Data type syntax is very similar between Oracle and Aurora PostgreSQL and most are converted automatically by AWS SCT. Note that date and time handling paradigms are different for Oracle and Aurora PostgreSQL and require manual verifications and/or conversion. Also note that due to differences in data type behavior between Oracle and Aurora PostgreSQL, manual verification and strict testing are highly recommended.

For more information, see [Data Types](chap-oracle-aurora-pg.tables.common.md).


| Action code | Action message | 
| --- | --- | 
|  5028  |   AWS SCT can’t convert object definitions with the unsupported %s data type.  | 
|  5029  |   AWS SCT can’t convert the usage of objects with the unsupported %s data type.  | 
|  5030  |   AWS SCT can’t convert the usage of objects with the unsupported %s data type.  | 
|  5212  |  PostgreSQL doesn’t support the `BFILE` data type.  | 
|  5550  |  PostgreSQL doesn’t support the `ROWID` data type.  | 
|  5551  |  PostgreSQL doesn’t support the `UROWID` data type.  | 
|  5572  |  PostgreSQL doesn’t support object type methods.  | 
|  5595  |   AWS SCT can’t convert the `ROWID` usage. This object uses the `ROWID` column from the %s table.  | 
|  5597  |   AWS SCT can’t convert the `ROWID` usage. This object uses the `ROWID` column from the %s table.  | 
|  5598  |  PostgreSQL doesn’t support `ROWID`.  | 
|  5609  |   AWS SCT can’t convert unsupported data types. PostgreSQL doesn’t support the %s data type.  | 
|  5613  |   AWS SCT can’t convert multi-dimensional arrays.  | 
|  5636  |   AWS SCT can’t convert `VARRAY of VARRAY`.  | 
|  5644  |   AWS SCT can’t convert the assign operation of an array or a nested table because it includes a nested record.  | 

## Character set


![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)


The character set granularity in Oracle and Aurora PostgreSQL are significantly different, in some cases.

For more information, see [Character Set](chap-oracle-aurora-pg.special.charset.md).


| Action code | Action message | 
| --- | --- | 
|  5623  |   AWS SCT doesn’t support uuencoding.  | 

## Cursors


![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)


PostgreSQL has PL/pgSQL cursors that enable you to iterate business logic on rows read from the database. They can encapsulate the query and read the query results a few rows at a time. All access to cursors in PL/pgSQL is performed through cursor variables, which are always of the refcursor data type.

There are specific options which aren’t supported for automatic conversion by AWS SCT.

For more information, see [Cursors](chap-oracle-aurora-pg.sql.cursors.md).


| Action code | Action message | 
| --- | --- | 
|  5031  |   AWS SCT can’t convert `CURSOR` expressions.  | 
|  5040  |   AWS SCT can’t convert `SHARING` clauses. PostgreSQL doesn’t support the %s `SHARING` clause.  | 
|  5042  |  PostgreSQL doesn’t support cursors of a specified type.  | 
|  5117  |   AWS SCT can’t convert cursor attributes. PostgreSQL doesn’t support the %s attribute.  | 
|  5225  |  PostgreSQL doesn’t support `TYPE …​ IS REF CURSOR` declarations.  | 
|  5226  |  PostgreSQL doesn’t support the `TYPE …​ IS REF CURSOR` usage.  | 
|  5330  |  PostgreSQL doesn’t support global cursors.  | 
|  5559  |  PostgreSQL doesn’t support `RETURN TYPE` for cursors.  | 
|  5560  |  PostgreSQL doesn’t support `PROGRAM_ERROR` exceptions.  | 
|  5561  |   AWS SCT can’t convert pre-defined exceptions. PostgreSQL doesn’t support the %s exception.  | 
|  5580  |  The exception block in the converted code is empty.  | 
|  5599  |  PostgreSQL doesn’t support references to `SQLERRM` outside of an exception handler.  | 
|  5600  |  PostgreSQL doesn’t support references to `SQLERRM` with any specified parameter value.  | 
|  5601  |  PostgreSQL doesn’t support references to `SQLCODE` outside of an exception handler.  | 
|  5602  |  PostgreSQL error code type isn’t compatible with the number type variables.  | 
|  5604  |  PostgreSQL doesn’t support global cursors. AWS SCT converts global cursors to local cursors.  | 
|  5612  |   AWS SCT can’t convert the `FETCH` command for a global parameterized cursor before the cursor variable is opened.  | 

## Flow control


![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)


Although the flow control syntax of Oracle differs from Aurora PostgreSQL , AWS SCT can convert most constructs automatically including loops, command blocks, and delays. Aurora PostgreSQL doesn’t support the `GOTO` command nor conditional compilation command, which require manual conversion.


| Action code | Action message | 
| --- | --- | 
|  5335  |  PostgreSQL doesn’t support `GOTO` operators.  | 
|  5603  |  PostgreSQL doesn’t support conditional compilation.  | 

## Transaction isolation


![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)


Aurora PostgreSQL supports the four transaction isolation levels specified in the SQL:92 standard: `READ UNCOMMITTED`, `READ COMMITTED`, `REPEATABLE READ`, and `SERIALIZABLE`, all of which are automatically converted by AWS SCT. AWS SCT also converts `BEGIN / COMMIT` and `ROLLBACK` commands that use slightly different syntax. Manual conversion is required for named, marked, and delayed durability transactions that aren’t supported by Aurora PostgreSQL.

For more information, see [Transaction Isolation](chap-oracle-aurora-pg.sql.transactions.md).


| Action code | Action message | 
| --- | --- | 
|  5350  |   AWS SCT can’t convert statements that explicitly apply or cancel a transaction.  | 
|  5611  |  PostgreSQL doesn’t support `SAVEPOINT` and `ROLLBACK TO SAVEPOINT` inside routines.  | 

## Stored procedures


![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)


Aurora PostgreSQL stored procedures (functions) provide very similar functionality to Oracle stored procedures and can be automatically converted by AWS SCT. Manual conversion is required for procedures that use `RETURN` values and some less common `EXECUTE` options such as the `RECOMPILE` and `RESULTS SETS` options.

For more information, see [Stored Procedures](chap-oracle-aurora-pg.sql.stored.md).


| Action code | Action message | 
| --- | --- | 
|  5027  |  The package body doesn’t include source code.  | 
|  5340  |  PostgreSQL doesn’t support the %s function.  | 
|  5579  |  Make sure that the second parameter of the %s function is processed correctly.  | 
|  5584  |  The %s function depends on the time zone settings.  | 
|  5607  |   AWS SCT can’t convert Java stored routine.  | 
|  5616  |   AWS SCT can’t convert `TABLE` functions.  | 
|  5617  |  PostgreSQL doesn’t fully support m and x as match parameters or as subexpression parameters for regular expressions.  | 
|  5624  |  Converted code might not work correctly because of the bind variable names.  | 
|  5625  |  PostgreSQL doesn’t support parameters in an anonymous block.  | 
|  5626  |   AWS SCT can’t convert the %s function.  | 
|  5627  |  Converted code might not work correctly because of the user-defined functions.  | 
|  5628  |  Converted code might not work correctly because of the dynamic SQL statements.  | 
|  5629  |  PostgreSQL doesn’t support all features of the `DBMS_SQL` package.  | 
|  5630  |   AWS SCT can’t convert the `DBMS_SQL` package functions.  | 
|  5633  |  Converted code might not work correctly because of the dynamic SQL statements.  | 
|  5634  |   AWS SCT can’t convert the use of user-defined functions with `OUT` and `INOUT` parameters.  | 

## Triggers


![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)


Aurora PostgreSQL supports `BEFORE` and `AFTER` triggers for `INSERT`, `UPDATE`, and `DELETE`. However, Aurora PostgreSQL triggers differ substantially from Oracle triggers, but most common use cases can be migrated with minimal code changes.

For more information, see [Triggers](chap-oracle-aurora-pg.tables.triggers.md).


| Action code | Action message | 
| --- | --- | 
|  5238  |  PostgreSQL doesn’t support `REFERENCING` clauses.  | 
|  5240  |  PostgreSQL doesn’t support triggers on nested table columns in views.  | 
|  5241  |  PostgreSQL doesn’t support `FOLLOWS` and `PRECEDES` clauses.  | 
|  5242  |  PostgreSQL doesn’t support `COMPOUND TRIGGER`.  | 
|  5243  |  PostgreSQL always creates a trigger under the table’s schema. Review the converted code to make sure that your trigger and its function are created under the table’s schema.  | 
|  5306  |   AWS SCT can’t convert the trigger that isn’t valid.  | 
|  5311  |  PostgreSQL doesn’t support system triggers.  | 
|  5313  |  PostgreSQL doesn’t support action-type clauses in triggers.  | 
|  5317  |  PostgreSQL doesn’t support `PARENT` referencing clauses.  | 
|  5415  |  PostgreSQL doesn’t support system triggers.  | 
|  5556  |  PostgreSQL doesn’t support conditional predicates.  | 

## Sequences


![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)


Although the syntax for Oracle `IDENTITY` and Aurora PostgreSQL `SERIAL` auto-enumeration columns differs significantly, it can be automatically converted by AWS SCT.

For more information, see [Sequences](chap-oracle-aurora-pg.sql.sequences.md) and [Identity](chap-oracle-aurora-pg.sql.identity.md).


| Action code | Action message | 
| --- | --- | 
|  5574  |  PostgreSQL doesn’t support sequence statuses.  | 

## Views


![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)


Although the basic syntax for creating a view in Oracle and Aurora PostgreSQL is almost identical there are some sub-options that can differs significantly and this can add manual needed tasks to the migration process.

For more information, see [Views](chap-oracle-aurora-pg.special.views.md).


| Action code | Action message | 
| --- | --- | 
|  5075  |  PostgreSQL doesn’t support the `WITH READ ONLY` clause for views.  | 
|  5077  |  PostgreSQL doesn’t support the `PIVOT` clause for `SELECT` statements.  | 
|  5245  |  PostgreSQL doesn’t support views with nested table columns.  | 
|  5320  |  PostgreSQL doesn’t support views with the `INVALID` status.  | 
|  5321  |  PostgreSQL doesn’t support object views.  | 
|  5322  |  PostgreSQL doesn’t support typed views.  | 
|  5583  |  PostgreSQL doesn’t support constraints for views.  | 
|  5614  |  PostgreSQL doesn’t support DML operations with non-updatable views.  | 

## User-defined types


![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)


User-defined types aren’t supported, AWS SCT can convert standard user-defined types by replacing it with their base types. More complicated user-defined types may require manual intervention.

For more information, see [User-Defined Types](chap-oracle-aurora-pg.tables.udt.md).


| Action code | Action message | 
| --- | --- | 
|  5032  |   AWS SCT can’t convert user-defined data types with incomplete definitions. PostgreSQL doesn’t support data types that are based on data types that have incomplete definitions.  | 
|  5062  |   AWS SCT converted the %s type constructor to a direct assignment.  | 
|  5099  |   AWS SCT can’t convert the object because the %s parent object wasn’t created.  | 
|  5118  |  PostgreSQL doesn’t support associative arrays. AWS SCT can’t convert the %s data type declaration.  | 
|  5120  |  PostgreSQL doesn’t support constructors of the collection data type.  | 
|  5121  |  PostgreSQL doesn’t support `FORALL` statements.  | 
|  5332  |   AWS SCT can’t convert the object that references an object in the %s schema, which isn’t converted.  | 
|  5569  |  PostgreSQL supports only standard SQL date and time types for session variables.  | 
|  5575  |   AWS SCT can’t convert `DEFAULT` statements for user-defined types. PostgreSQL doesn’t support the assignment of default values when creating the %s user-defined type.  | 
|  5577  |  PostgreSQL doesn’t support member functions in user-defined types.  | 
|  5582  |  PostgreSQL doesn’t support encrypted objects in the `CREATE` statement.  | 
|  5587  |  PostgreSQL doesn’t support `EXTEND` methods with parameters.  | 
|  5638  |   AWS SCT doesn’t support global variables of nested table as an argument for functions or procedures.  | 

## Merge


![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)


The `MERGE` statement isn’t supported and it can’t be automatically converted by AWS SCT. Manual conversion is straight-forward in most cases.

For more information, see [Merge](chap-oracle-aurora-pg.sql.merge.md).


| Action code | Action message | 
| --- | --- | 
|  5102  |  PostgreSQL doesn’t support `MERGE` statements.  | 
|  5618  |  PostgreSQL doesn’t support `MERGE` statements with the `ERROR LOG` clause.  | 
|  5621  |  Make sure that the unique constraint for the %s field exists.  | 

## Materialized views


![\[Two star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-2.png)


Materialized views aren’t supported, some features such as incremental refresh or DML commands on materialized views aren’t supported.

For more information, see [Materialized Views](chap-oracle-aurora-pg.special.matviews.md).


| Action code | Action message | 
| --- | --- | 
|  5093  |   AWS SCT can’t convert the query of the materialized view  | 
|  5094  |   AWS SCT can’t convert the materialized view.  | 
|  5095  |  PostgreSQL doesn’t support DML statements on materialized views.  | 

## Query hints


![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)


Basic query hints such as index hints can be converted automatically by AWS SCT, except for DML statements. Note that specific optimizations used for Oracle may be completely inapplicable to a new query optimizer. It is recommended to start migration testing with all hints removed. Then, selectively apply hints as a last resort if other means such as schema, index, and query optimizations have failed. Plan guides aren’t supported by Aurora PostgreSQL.

For more information, see [Query Hints and Plan Guides](chap-oracle-aurora-pg.tuning.hints.md).


| Action code | Action message | 
| --- | --- | 
|  5103  |   AWS SCT can’t convert hints. PostgreSQL doesn’t support the %s hint.  | 

## Database links


![\[No automation\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-0.png)


Migrating database links from Oracle to Aurora PostgreSQL requires a full rewrite the mechanism that managed the database links. This can’t be automatically converted by AWS SCT.

For more information, see [Database Links](chap-oracle-aurora-pg.special.dblinks.md).


| Action code | Action message | 
| --- | --- | 
|  5605  |  PostgreSQL doesn’t support the usage of database links.  | 
|  5639  |  Make sure that you installed the `postgres_fdw` extension.  | 
|  5640  |   AWS SCT can’t convert the database link because the remote table isn’t defined. AWS SCT created the structure of this table based on referenes.  | 
|  5641  |  PostgreSQL foreign data wrapper doesn’t support the usage of user-defined functions.  | 
|  5657  |  PostgreSQL can’t create views that are based on undefined foreign tables.  | 

## Indexes


![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)


Basic non-clustered indexes, which are the most commonly used type of indexes are automatically migrated by AWS SCT. In addition, filtered indexes, indexes with included columns, and some Oracle specific index options such as bitmap or domain can’t be migrated automatically and require manual conversion.

For more details, see the Indexes topics.


| Action code | Action message | 
| --- | --- | 
|  5206  |  PostgreSQL doesn’t support bitmap indexes.  | 
|  5208  |  PostgreSQL doesn’t support domain indexes.  | 
|  5555  |  PostgreSQL doesn’t support functional indexes that aren’t single-column.  | 

## Partitioning


![\[Three star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-3.png)


Aurora PostgreSQL uses table inheritance, some of the physical aspects of partitioning in Oracle don’t apply to Aurora PostgreSQL. For example, the concept of file groups and assigning partitions to file groups. Aurora PostgreSQL supports a much richer framework for table partitioning than Oracle, with many additional options such as hash partitioning, and sub partitioning.

For more information, see [Partitioning](chap-oracle-aurora-pg.storage.partition.md).


| Action code | Action message | 
| --- | --- | 
|  5652  |  PostgreSQL doesn’t have a mechanism that handles null values for partition keys.  | 
|  5653  |  PostgreSQL doesn’t support foreign keys that reference partitioned tables.  | 
|  5654  |  PostgreSQL doesn’t support foreign keys in partitioned tables that reference other tables.  | 
|  5655  |   AWS SCT can’t convert update operations of partitioned tables, partitions, or subpartitions.  | 
|  5656  |  The timestamp data type in converted code might produce different results compared to the source code.  | 
|  5658  |  You can use `DEFAULT` partitions in PostgreSQL version 11 and higher.  | 

## OLAP functions


![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-automation-4.png)


Aurora PostgreSQL does provide native support for almost all OLAP Functions.

For more information, see [OLAP Functions](chap-oracle-aurora-pg.sql.olap.md).


| Action code | Action message | 
| --- | --- | 
|  5271  |  The `GREATEST` function in converted code might produce different results compared to the source code.  | 
|  5272  |  The `LEAST` function in converted code might produce different results compared to the source code.  | 
|  5622  |   AWS SCT converts the `dbms_transaction.local_transaction_id` function with the parameter set to true.  | 

# AWS Database Migration Service


The AWS Database 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. The AWS Database Migration Service can migrate your data to and from most widely-used commercial and open-source databases.

The service supports homogenous migrations such as Oracle to Oracle as well as heterogeneous migrations between different database platforms such as Oracle to Amazon Aurora or Microsoft SQL Server to MySQL. You can also use AWS DMS to stream data to Amazon Redshift, Amazon DynamoDB, and Amazon S3 from any of the supported sources, which are Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, SAP ASE, SQL Server, IBM DB2 LUW, and MongoDB, enabling consolidation and easy analysis of data in a petabyte-scale data warehouse. The AWS Database Migration Service can also be used for continuous data replication with high availability.

For AWS DMS pricing, see [Database Migration Service pricing](https://aws.amazon.com/dms/pricing).

For all supported sources for AWS DMS, see [Sources for data migration](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.html).

For all supported targets for AWS DMS, see [Targets for data migration](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.html).

## Migration tasks performed by AWS DMS


In a traditional solution, you need to perform capacity analysis, procure hardware and software, install and administer systems, and test and debug the installation. AWS DMS automatically manages the deployment, management, and monitoring of all hardware and software needed for your migration. Your migration can be up and running within minutes of starting the AWS DMS configuration process.

With AWS DMS, you can scale up (or scale down) your migration resources as needed to match your actual workload. For example, if you determine that you need additional storage, you can easily increase your allocated storage and restart your migration, usually within minutes. On the other hand, if you discover that you aren’t using all of the resource capacity you configured, you can easily downsize to meet your actual workload.

 AWS DMS uses a pay-as-you-go model. You only pay for AWS DMS resources while you use them as opposed to traditional licensing models with up-front purchase costs and ongoing maintenance charges.

 AWS DMS automatically manages all of the infrastructure that supports your migration server including hardware and software, software patching, and error reporting.

 AWS DMS provides automatic failover. If your primary replication server fails for any reason, a backup replication server can take over with little or no interruption of service.

 AWS DMS can help you switch to a modern, perhaps more cost-effective database engine than the one you are running now. For example, AWS DMS can help you take advantage of the managed database services provided by Amazon RDS or Amazon Aurora. Or, it can help you move to the managed data warehouse service provided by Amazon Redshift, NoSQL platforms like Amazon DynamoDB, or low-cost storage platforms like Amazon S3. Conversely, if you want to migrate away from old infrastructure but continue to use the same database engine, AWS DMS also supports that process.

 AWS DMS supports nearly all of today’s most popular DBMS engines as data sources, including Oracle, Microsoft SQL Server, MySQL, MariaDB, PostgreSQL, Db2 LUW, SAP, MongoDB, and Amazon Aurora.

 AWS DMS provides a broad coverage of available target engines including Oracle, Microsoft SQL Server, PostgreSQL, MySQL, Amazon Redshift, SAP ASE, Amazon S3, and Amazon DynamoDB.

You can migrate from any of the supported data sources to any of the supported data targets. AWS DMS supports fully heterogeneous data migrations between the supported engines.

 AWS DMS ensures that your data migration is secure. Data at rest is encrypted with AWS Key Management Service (AWS KMS) encryption. During migration, you can use Secure Socket Layers (SSL) to encrypt your in-flight data as it travels from source to target.

## How AWS DMS works


At its most basic level, AWS DMS is a server in the AWS Cloud that runs replication software. You create a source and target connection to tell AWS DMS where to extract from and load to. Then, you schedule a task that runs on this server to move your data. AWS DMS creates the tables and associated primary keys if they don’t exist on the target. You can pre-create the target tables manually if you prefer. Or you can use AWS SCT to create some or all of the target tables, indexes, views, triggers, and so on.

The following diagram illustrates the AWS DMS process.

![\[How Database Migration Service works\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-how-aws-dms-works.png)


## Latest updates


 AWS DMS is continuously evolving and supporting more and more options, find some of the latest updates following:
+ Support for full-load with change data capture (CDC) and CDC-only tasks running against Oracle source tables created using the `CREATE TABLE AS` statement.
+ New MySQL version AWS DMS now supports MySQL version 8.0 as a source except when the transaction payload is compressed.
+ Support for AWS Secrets Manager integration. You can store the database connection details (user credentials) for supported endpoints securely in AWS Secrets Manager. You can then submit the corresponding secret instead of plain-text credentials to AWS DMS when you create or modify an endpoint. AWS DMS then connects to the endpoint databases using the secret. For more information, see [Using secrets to access endpoints](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.html#security_iam_secretsmanager).
+ Support for TLS 1.2 for MySQL endpoints.
+ Support for TLS 1.2 for SQL Server endpoints.

For a complete guide with a step-by-step walkthrough including all the latest notes for migrating SQL Server to Aurora MySQL with AWS DMS, see [Migrating a SQL Server Database to Amazon Aurora MySQL](https://docs.aws.amazon.com/dms/latest/sbs/chap-sqlserver2aurora.html).

For more information about AWS DMS, see [What is Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html) and [Best practices for Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html).

# Amazon RDS on Outposts


**Note**  
Please note that this entire topic is related to Amazon Relational Database Service (Amazon RDS) and isn’t supported with Amazon Aurora.

 Amazon RDS on Outposts is a fully managed service that offers the same AWS infrastructure, AWS services, APIs, and tools to virtually any data center, co-location space, or on-premises facility for a truly consistent hybrid experience. Amazon RDS on Outposts is ideal for workloads that require low latency access to on-premises systems, local data processing, data residency, and migration of applications with local system inter-dependencies.

When you deploy Amazon RDS on Outposts, you can run Amazon RDS on premises for low latency workloads that need to be run in close proximity to your on-premises data and applications. Amazon RDS on Outposts also enables automatic backup to an AWS Region. You can manage Amazon RDS databases both in the cloud and on premises using the same AWS Management Console, APIs, and CLI. Amazon RDS on Outposts supports Microsoft SQL Server, MySQL, and PostgreSQL database engines, with support for additional database engines coming soon.

## How it works


 Amazon RDS on Outposts lets you run Amazon RDS in your on-premises or co-location site. You can deploy and scale an Amazon RDS database instance in Outposts just as you do in the cloud, using the AWS console, APIs, or CLI. Amazon RDS databases in Outposts are encrypted at rest using AWS KMS keys. Amazon RDS automatically stores all automatic backups and manual snapshots in the AWS Region.

![\[How RDS on Outposts works\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-rds-outposts-how-it-works.png)


This option is helpful when you need to run Amazon RDS on premises for low latency workloads that need to be run in close proximity to your on-premises data and applications.

For more information, see [AWS Outposts Family](https://aws.amazon.com/outposts), [Amazon RDS on Outposts](https://aws.amazon.com/rds/outposts), and [Create Amazon RDS DB Instances on AWS Outposts](https://aws.amazon.com/blogs/aws/new-create-amazon-rds-db-instances-on-aws-outposts).

# Amazon RDS Proxy


 Amazon RDS Proxy is a fully managed, highly available database proxy for Amazon Relational Database Service (RDS) that makes applications more scalable, more resilient to database failures, and more secure.

Many applications, including those built on modern server-less architectures, can have many open connections to the database server, and may open and close database connections at a high rate, exhausting database memory and compute resources. Amazon RDS Proxy allows applications to pool and share connections established with the database, improving database efficiency and application scalability. With Amazon RDS Proxy, fail-over times for Aurora and Amazon RDS databases are reduced by up to 66% and database credentials, authentication, and access can be managed through integration with AWS Secrets Manager and AWS Identity and Access Management (IAM).

 Amazon RDS Proxy can be enabled for most applications with no code changes, and you don’t need to provision or manage any additional infrastructure. Pricing is simple and predictable: you pay for each vCPU of the database instance for which the proxy is enabled. Amazon RDS Proxy is now generally available for Aurora MySQL, Aurora PostgreSQL, Amazon RDS for MySQL, and Amazon RDS for PostgreSQL.

## Amazon RDS Proxy benefits

+  **Improved application performance**. Amazon RDS proxy manages a connection pooling which helps with reducing the stress on database compute and memory resources that typically occurs when new connections are established and it is useful to efficiently support a large number and frequency of application connections.
+  **Increase application availability**. By automatically connecting to a new database instance while preserving application connections Amazon RDS Proxy can reduce fail-over time by 66%.
+  **Manage application security**. Amazon RDS Proxy also enables you to centrally manage database credentials using AWS Secrets Manager.
+  **Fully managed**. Amazon RDS Proxy gives you the benefits of a database proxy without requiring additional burden of patching and managing your own proxy server.
+  **Fully compatible with your database**. Amazon RDS Proxy is fully compatible with the protocols of supported database engines, so you can deploy Amazon RDS Proxy for your application without making changes to your application code.
+  **Available and durable**. Amazon RDS Proxy is highly available and deployed over multiple Availability Zones (AZs) to protect you from infrastructure failure.

## How Amazon RDS Proxy works


![\[How Amazon Relational Database Service Proxy Works\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-how-rds-proxy-works.png)


For more information, see [Amazon Relational Database Service Proxy for Scalable Serverless Applications](https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available) and [Amazon Relational Database Service Proxy](https://aws.amazon.com/rds/proxy).

# Amazon Aurora Serverless v1


 Amazon Aurora Serverless v1 (Amazon Aurora Serverless version 1) is an on-demand autoscaling configuration for Amazon Aurora. An Aurora Serverless DB cluster is a DB cluster that scales compute capacity up and down based on your application’s needs. This contrasts with Aurora provisioned DB clusters, for which you manually manage capacity. Aurora Serverless v1 provides a relatively simple, cost-effective option for infrequent, intermittent, or unpredictable workloads. It is cost-effective because it automatically starts up, scales compute capacity to match your application’s usage, and shuts down when it’s not in use.

To learn more about pricing, see Serverless Pricing under MySQL-Compatible Edition or PostgreSQL-Compatible Edition on the [Amazon Aurora pricing page](https://aws.amazon.com/rds/aurora/pricing).

Aurora Serverless v1 clusters have the same kind of high-capacity, distributed, and highly available storage volume that is used by provisioned DB clusters. The cluster volume for an Aurora Serverless v1 cluster is always encrypted. You can choose the encryption key, but you can’t disable encryption. That means that you can perform the same operations on an Aurora Serverless v1 that you can on encrypted snapshots. For more information, see Aurora Serverless v1 and snapshots.

Aurora Serverless v1 provides the following advantages:
+  **Simpler than provisioned**. Aurora Serverless v1 removes much of the complexity of managing DB instances and capacity.
+  **Scalable**. Aurora Serverless v1 seamlessly scales compute and memory capacity as needed, with no disruption to client connections.
+  **Cost-effective**. When you use Aurora Serverless v1, you pay only for the database resources that you consume, on a per-second basis.
+  **Highly available storage**. Aurora Serverless v1 uses the same fault-tolerant, distributed storage system with six-way replication as Aurora to protect against data loss.

Aurora Serverless v1 is designed for the following use cases:
+  **Infrequently used applications**. You have an application that is only used for a few minutes several times for each day or week, such as a low-volume blog site. With Aurora Serverless v1, you pay for only the database resources that you consume on a per-second basis.
+  **New applications**. You’re deploying a new application and you’re unsure about the instance size you need. By using Aurora Serverless v1, you can create a database endpoint and have the database automatically scale to the capacity requirements of your application.
+  **Variable workloads**. You’re running a lightly used application, with peaks of 30 minutes to several hours a few times each day, or several times for each year. Examples are applications for human resources, budgeting, and operational reporting applications. With Aurora Serverless v1, you no longer need to provision for peak or average capacity.
+  **Unpredictable workloads**. You’re running daily workloads that have sudden and unpredictable increases in activity. An example is a traffic site that sees a surge of activity when it starts raining. With Aurora Serverless v1, your database automatically scales capacity to meet the needs of the application’s peak load and scales back down when the surge of activity is over.
+  **Development and test databases**. Your developers use databases during work hours but don’t need them on nights or weekends. With Aurora Serverless v1, your database automatically shuts down when it’s not in use.
+  **Multi-tenant applications**. With Aurora Serverless v1, you don’t have to individually manage database capacity for each application in your fleet. Aurora Serverless v1 manages individual database capacity for you.

This process takes almost no time and since the storage is shared between nodes Aurora can scale up or down in seconds for most workloads. The service currently has autoscaling thresholds of 1.5 minutes to scale up and 5 minutes to scale down. That means metrics must exceed the limits for 1.5 minutes to trigger a scale up or fall below the limits for 5 minutes to trigger a scale down. The cool-down period between scaling activities is 5 minutes to scale up and 15 minutes to scale down. Before scaling can happen the service has to find a “scaling point” which may take longer than anticipated if you have long-running transactions. Scaling operations are transparent to the connected clients and applications since existing connections and session state are transferred to the new nodes. The only difference with pausing and resuming is a higher latency for the first connection, typically around 25 seconds. You can find more details in the documentation.

![\[How Aurora Serverless Works\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-aurora-serverless.png)


## How to provision


Log in to your [AWS console](https://eu-central-1.console.aws.amazon.com/rds/home?#databases:), choose ** Amazon RDS **, and then choose **Create database**.

On **Engine options**, choose **Serverless** for the **Capacity type**.

![\[Provision Aurora Serverless\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-aurora-serverless-provision.png)


Choose the capacity settings for your use case.

![\[Aurora Serverless Capacity Settings\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-postgresql-migration-playbook/images/pb-aurora-serverless-capacity.png)


For more information, see [Amazon Aurora Serverless](https://aws.amazon.com/rds/aurora/serverless), [Aurora Serverless MySQL Generally Available](https://aws.amazon.com/blogs/aws/aurora-serverless-ga/), and [Amazon Aurora PostgreSQL Serverless Now Generally Available](https://aws.amazon.com/blogs/aws/amazon-aurora-postgresql-serverless-now-generally-available).