# Migrating an Oracle Database to PostgreSQL Using this walkthrough, you can learn how to migrate an Oracle database to a PostgreSQL database using AWS Database Migration Service (AWS DMS) and the AWS Schema Conversion Tool (AWS SCT). AWS DMS migrates your data from your Oracle source into your PostgreSQL target. AWS DMS also captures data manipulation language (DML) and [supported data definition language (DDL)](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.SupportedDDL.html) changes that happen on your source database and applies these changes to your target database. This way, AWS DMS keeps your source and target databases in sync with each other. To facilitate the data migration, AWS SCT creates the migrated schemas on the target database, including the tables and primary key indexes on the target if necessary. AWS DMS doesn’t migrate your secondary indexes, sequences, default values, stored procedures, triggers, synonyms, views, and other schema objects not specifically related to data migration. To migrate these objects to your PostgreSQL target, use AWS SCT. **Topics** + [ # Prerequisites for migrating from an Oracle database to PostgreSQL ](chap-rdsoracle2postgresql.prerequisites.md) + [ # Step-by-step Oracle database to PostgreSQL migration walkthrough ](chap-rdsoracle2postgresql.steps.md) + [ # Rolling Back the Migration ](chap-oracle2postgresql.rollback.md) + [ # Oracle database migration to PostgreSQL troubleshooting ](chap-oracle2postgresql.troubleshooting.md) # Prerequisites for migrating from an Oracle database to PostgreSQL The following prerequisites are required to complete this walkthrough: + Understand Amazon Relational Database Service (Amazon RDS), the applicable database technologies, and SQL. + Create a user with AWS Identity and Access Management (IAM) credentials that allows you to launch Amazon RDS and AWS Database Migration Service (AWS DMS) instances in your AWS Region. For information about IAM credentials, see [Setting up for Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SettingUp.html#CHAP_SettingUp.IAM). + Understand the Amazon Virtual Private Cloud (Amazon VPC) service and security groups. For information about using Amazon VPC with Amazon RDS, see [Amazon Virtual Private Cloud (VPCs) and Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.html). For information about Amazon RDS security groups, see [Amazon RDS Security Groups](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Overview.RDSSecurityGroups.html). + Understand the supported features and limitations of AWS DMS. For information about AWS DMS, see https://docs.aws.amazon.com/dms/latest/userguide/Welcome.html. + Understand the supported data type conversion options for Oracle and PostgreSQL. For information about data types for Oracle as a source, see [Using an Oracle database as a source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.Oracle.html). For information about data types for PostgreSQL as a target, see [Using a PostgreSQL Database as a Target](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html). + Size your target PostgreSQL database host. DBAs should be aware of the load profile of the current source Oracle database host. Consider CPU, memory, and IOPS. With RDS, you can size up the target database host, or reduce it, after the migration. If this is the first time you are migrating to PostgreSQL, then we recommend that you have extra capacity to account for performance issues and tuning opportunities. + Audit your source Oracle database. For each schema and all the objects under each schema, determine if any of the objects are no longer being used. Deprecate these objects on the source Oracle database, because there’s no need to migrate them if they are not being used. + If load capacity permits, then get the max size (kb) for each LOB type on the source database, and keep this information for later. + If possible, move columns with BLOB, CLOB, NCLOB, LONG, LONG RAW, and XMLTYPE to Amazon S3, Dynamo DB, or another data store. Doing so simplifies your source Oracle database for an easier migration. It will also lower the capacity requirements for the target PostgreSQL database. For more information about AWS DMS, see [Getting started with Database Migration Service](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_GettingStarted.html). To estimate what it will cost to run this walkthrough on AWS, you can use the AWS Pricing Calculator. For more information, see [https://calculator.aws/](https://calculator.aws/). To avoid additional charges, delete all resources after you complete the walkthrough. # Step-by-step Oracle database to PostgreSQL migration walkthrough The following steps provide instructions for migrating an Oracle database to a PostgreSQL database. These steps assume that you have already prepared your source database as described in [Prerequisites](chap-rdsoracle2postgresql.prerequisites.md). **Topics** + [ # Step 1: Install the SQL Drivers and AWS Schema Conversion Tool on Your Local Computer ](chap-rdsoracle2postgresql.steps.installsct.md) + [ # Step 2: Configure Your Oracle Source Database ](chap-oracle2postgresql.steps.configureoracle.md) + [ # Step 3: Configure Your PostgreSQL Target Database ](chap-oracle2postgresql.steps.configurepostgresql.md) + [ # Step 4: Use AWS SCT to Convert the Oracle Schema to PostgreSQL ](chap-rdsoracle2postgresql.steps.convertschema.md) + [ # Step 5: Create an AWS DMS Replication Instance ](chap-rdsoracle2postgresql.steps.createreplicationinstance.md) + [ # Step 6: Create AWS DMS Source and Target Endpoints ](chap-rdsoracle2postgresql.steps.createsourcetargetendpoints.md) + [ # Step 7: Create and Run Your AWS DMS Migration Task ](chap-rdsoracle2postgresql.steps.createmigrationtask.md) + [ # Step 8: Cut Over to PostgreSQL ](chap-rdsoracle2postgresql.steps.cutover.md) # Step 1: Install the SQL Drivers and AWS Schema Conversion Tool on Your Local Computer To install the SQL drivers and the AWS Schema Conversion Tool (AWS SCT) on your local computer, do the following: 1. Download the JDBC driver for your Oracle database release. For more information, go to https://www.oracle.com/jdbc. 1. Download the PostgreSQL driver ([postgresql-42.2.19.jar](https://jdbc.postgresql.org/download/postgresql-42.2.19.jar)). 1. Install AWS SCT and the required JDBC drivers. 1. Download AWS SCT from [Installing, verifying, and updating the Schema Conversion Tool](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Installing.html). 1. Launch AWS SCT. 1. In AWS SCT, choose **Global settings** from **Settings**. 1. In **Global settings**, choose **Driver**, and then choose **Browse** for **Oracle driver path**. Locate the JDBC Oracle driver and choose **OK**. 1. Choose **Browse** for **PostgreSQL driver path**. Locate the JDBC PostgreSQL driver and choose **OK**. ![\[Drivers\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sct-drivers.png) 1. Choose **OK** to close the dialog box. # Step 2: Configure Your Oracle Source Database To use Oracle as a source for AWS Database Migration Service (AWS DMS), you must first ensure that ARCHIVELOG MODE is on to provide information to LogMiner. AWS DMS uses LogMiner to read information from the archive logs so that AWS DMS can capture changes. For AWS DMS to read this information, make sure the archive logs are retained on the database server as long as AWS DMS requires them. If you configure your task to begin capturing changes immediately, then you should only need to retain archive logs for a little longer than the duration of the longest running transaction. Retaining archive logs for 24 hours is usually sufficient. If you configure your task to begin from a point in time in the past, then archive logs must be available from that time forward. For more specific instructions about enabling ARCHIVELOG MODE and ensuring log retention for your Oracle database, see the [Oracle documentation](http://docs.oracle.com/database/121/ADMIN/archredo.htm#ADMIN11335). To capture change data, AWS DMS requires supplemental logging to be enabled on your source database. Minimal supplemental logging must be enabled at the database level. AWS DMS also requires that identification key logging be enabled. This option causes the database to place all columns of a row’s primary key in the redo log file whenever a row containing a primary key is updated. This result occurs even if no value in the primary key has changed. You can set this option at the database or table level. 1. Create or configure a database account to be used by AWS DMS. We recommend that you use an account with the minimal privileges required by AWS DMS for your AWS DMS connection. AWS DMS requires the following privileges. ``` CREATE SESSION SELECT ANY TRANSACTION SELECT on V_$ARCHIVED_LOG SELECT on V_$LOG SELECT on V_$LOGFILE SELECT on V_$DATABASE SELECT on V_$THREAD SELECT on V_$PARAMETER SELECT on V_$NLS_PARAMETERS SELECT on V_$TIMEZONE_NAMES SELECT on V_$TRANSACTION SELECT on ALL_INDEXES SELECT on ALL_OBJECTS SELECT on ALL_TABLES SELECT on ALL_USERS SELECT on ALL_CATALOG SELECT on ALL_CONSTRAINTS SELECT on ALL_CONS_COLUMNS SELECT on ALL_TAB_COLS SELECT on ALL_IND_COLUMNS SELECT on ALL_LOG_GROUPS SELECT on SYS.DBA_REGISTRY SELECT on SYS.OBJ$ SELECT on DBA_TABLESPACES SELECT on ALL_TAB_PARTITIONS SELECT on ALL_ENCRYPTED_COLUMNS * SELECT on all tables migrated ``` If you want to capture and apply changes (CDC), then you also need the following privileges. ``` EXECUTE on DBMS_LOGMNR EXECUTE on DBMS_LOGMNR_D SELECT on V_$LOGMNR_LOGS SELECT on V_$LOGMNR_CONTENTS LOGMINING /* For Oracle 12c and higher. */ * ALTER for any table being replicated (if you want to add supplemental logging) ``` For Oracle versions before 11.2.0.3, you need the following privileges. ``` SELECT on DBA_OBJECTS /* versions before 11.2.0.3 */ SELECT on ALL_VIEWS (required if views are exposed) ``` 1. If your Oracle database is an Amazon RDS database, then connect to it as an administrative user, and run the following command to ensure that archive logs are retained on your RDS source for 24 hours: ``` exec rdsadmin.rdsadmin_util.set_configuration('archivelog retention hours',24); ``` If your Oracle source is an Amazon RDS database, it will be placed in ARCHIVELOG MODE if, and only if, you enable backups. 1. Run the following command to turn on supplemental logging at the database level, which AWS DMS requires: + In Oracle SQL: ``` ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; ``` + In RDS: ``` exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD'); ``` 1. Use the following command to enable identification key supplemental logging at the database level. AWS DMS requires supplemental key logging at the database level. The exception is if you allow AWS DMS to automatically add supplemental logging as needed or enable key-level supplemental logging at the table level: + In Oracle SQL: ``` ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ``` + In RDS: ``` exec rdsadmin.rdsadmin_util.alter_supplemental_logging('ADD','PRIMARY KEY'); ``` Your source database incurs a small bit of overhead when key level supplemental logging is enabled. Therefore, if you are migrating only a subset of your tables, then you might want to enable key level supplemental logging at the table level. 1. To turn on key level supplemental logging at the table level, use the following command. ``` ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS; ``` If a table doesn’t have a primary key, then you have two options. + You can add supplemental logging on all columns involved in the first unique index on the table (sorted by index name). To add supplemental logging on a subset of columns in a table, such as those involved in a unique index, run the following command. ``` ALTER TABLE table_name ADD SUPPLEMENTAL LOG GROUP example_log_group (column_list) ALWAYS; ``` + You can add supplemental logging on all columns of the table. To add supplemental logging on all columns of a table, run the following command. ``` ALTER TABLE table_name ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS; ``` 1. Create a user for AWS SCT. ``` CREATE USER oracle_sct_user IDENTIFIED BY password; GRANT CONNECT TO oracle_sct_user; GRANT SELECT_CATALOG_ROLE TO oracle_sct_user; GRANT SELECT ANY DICTIONARY TO oracle_sct_user; ``` # Step 3: Configure Your PostgreSQL Target Database 1. If the schemas you are migrating do not exist on the PostgreSQL database, then create the schemas. 1. Create the AWS DMS user to connect to your target database, and grant Superuser or the necessary individual privileges (or use the master username for RDS). ``` CREATE USER postgresql_dms_user WITH PASSWORD 'password'; ALTER USER postgresql_dms_user WITH SUPERUSER; ``` 1. Create a user for AWS SCT. ``` CREATE USER postgresql_sct_user WITH PASSWORD 'password'; GRANT CONNECT ON DATABASE database_name TO postgresql_sct_user; GRANT USAGE ON SCHEMA schema_name TO postgresql_sct_user; GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO postgresql_sct_user; GRANT ALL ON ALL SEQUENCES IN SCHEMA schema_name TO postgresql_sct_user; ``` # Step 4: Use AWS SCT to Convert the Oracle Schema to PostgreSQL Before you migrate data to PostgreSQL, you convert the Oracle schema to a PostgreSQL schema. [This video covers all the steps of this process](https://youtu.be/ibtNkChGFkw). To convert an Oracle schema to a PostgreSQL schema using AWS Schema Conversion Tool (AWS SCT), do the following: 1. Launch AWS SCT. In AWS SCT, choose **File**, then choose **New Project**. Create a new project named ` AWS Schema Conversion Tool Oracle to PostgreSQL`, specify the **Location** of the project folder, and then choose **OK**. 1. Choose **Add source** to add a source Oracle database to your project, then choose **Oracle**, and choose **Next**. 1. Enter the following information, and then choose **Test Connection**. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.convertschema.html) ![\[Connecting to an Oracle DB instance\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdsor2postgresql11.png) 1. Choose **OK** to close the alert box, then choose **Connect** to close the dialog box and to connect to the Oracle DB instance. 1. Choose **Add target** to add a target PostgreSQL database to your project, then choose ** Amazon RDS for PostgreSQL**, and choose **Next**. 1. Enter the following information and then choose **Test Connection**. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.convertschema.html) 1. Choose **OK** to close the alert box, then choose **Connect** to connect to the Amazon RDS for PostgreSQL DB instance. 1. In the tree in the left panel, select the schema to migrate. In the tree in the right panel, select your target Amazon RDS for PostgreSQL database. Choose **Create mapping**. For more information, see [Creating mapping rules](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Mapping.html) in the *Schema Conversion Tool User Guide*. ![\[Creating a mapping rule\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdsor2postgresqlmapping.png) 1. Choose **Main view**. In the tree in the left panel, right-click the schema to migrate and choose **Convert schema**. 1. Choose **Yes** for the confirmation message. AWS SCT analyzes the schema, creates a database migration assessment report, and converts your schema to the target database format. 1. Choose **Assessment Report View** from the menu to check the database migration assessment report. The report breaks down by each object type and by how much manual change is needed to convert it successfully. Generally, packages, procedures, and functions are more likely to have some issues to resolve because they contain the most custom PL/SQL code. AWS SCT also provides hints about how to fix these objects. 1. Choose the **Action Items** tab. The **Action Items** tab shows each issue for each object that requires attention. For each conversion issue, you can complete one of the following actions: + Modify the objects on the source Oracle database so that AWS SCT can convert the objects to the target Amazon RDS for PostgreSQL database. 1. Modify the objects on the source Oracle database. 1. Repeat the previous steps to convert the schema and check the assessment report. 1. If necessary, repeat this process until there are no conversion issues. 1. Choose **Main View** from the menu. Open the context (right-click) menu for the target Amazon RDS for PostgreSQL schema, and choose **Apply to database** to apply the schema changes to the Amazon RDS for PostgreSQL database, and confirm that you want to apply the schema changes. + Instead of modifying the source schema, modify scripts that AWS SCT generates before applying the scripts on the target Amazon RDS for PostgreSQL database. 1. Choose **Main View** from the menu. Open the context (right-click) menu for the target Amazon RDS for PostgreSQL schema name, and choose **Save as SQL**. Next, choose a name and destination for the script. 1. In the script, modify the objects to correct conversion issues. You can also exclude foreign key constraints, triggers, and secondary indexes from the script because they can cause problems during the migration. After the migration is complete, you can create these objects on the Amazon RDS for PostgreSQL database. 1. Run the script on the target Amazon RDS for PostgreSQL database. For more information, see [Converting Database Schema to Amazon RDS](https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Converting.html). 1. (Optional) Use AWS SCT to create migration rules. 1. Choose **Mapping view** and then choose **New migration rule**. 1. Create additional migration transformation rules that are required based on the action items. 1. Save the migration rules. 1. Choose **Export script for DMS** to export a JSON format of all the transformations that the AWS DMS task will use. Choose **Save**. # Step 5: Create an AWS DMS Replication Instance After validating the schema structure between source and target databases, continue with the core part of this walkthrough, which is the data migration. The following illustration shows a high-level view of the migration process. ![\[Migration process\]](http://docs.aws.amazon.com/dms/latest/sbs/images/datarep-conceptual2.png) An AWS DMS replication instance performs the actual data migration between source and target. The replication instance also caches the transaction logs during the migration. How much CPU and memory capacity a replication instance has influences the overall time required for the migration. 1. Sign in to the AWS Management Console, and select AWS DMS at https://console.aws.amazon.com/dms/v2/. Next, choose **Create Migration**. If you are signed in as an AWS Identity and Access Management (IAM) user, then you must have the appropriate permissions to access AWS DMS. For more information about the permissions required, see [IAM Permissions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.html#CHAP_Security.IAMPermissions). 1. Choose **Next** to start a database migration from the console’s Welcome page. 1. On the **Create replication instance** page, specify your replication instance information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createreplicationinstance.html) 1. For the **Advanced** section, specify the following information. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createreplicationinstance.html) For information about the KMS key, see [Setting an Encryption Key and Specifying KMS Permissions](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Security.EncryptionKey.html). 1. Click **Next**. # Step 6: Create AWS DMS Source and Target Endpoints While your replication instance is being created, you can specify the source and target database endpoints using the [AWS Management Console](https://console.aws.amazon.com/). However, you can only test connectivity after the replication instance has been created, because the replication instance is used in the connection. 1. Sign in to the AWS Management Console, open the [AWS DMS console](https://console.aws.amazon.com/dms/v2), and then choose **Endpoints**. 1. Specify your connection information for the source Oracle database and the target PostgreSQL database. The following table describes the source settings. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createsourcetargetendpoints.html) The following table describes the advanced source settings. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createsourcetargetendpoints.html) For information about extra connection attributes, see [Using Extra Connection Attributes](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Introduction.ConnectionAttributes.html). The following table describes the target settings. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createsourcetargetendpoints.html) The following is an example of the completed page. ![\[Completed replication task connections page\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdsor2postgressql19.5.png) 1. After the endpoints and replication instance have been created, test each endpoint connection by choosing **Run test** for the source and target endpoints. 1. Drop foreign key constraints and triggers on the target database. During the full load process, AWS DMS does not load tables in any particular order, so it may load the child table data before parent table data. As a result, foreign key constraints might be violated if they are enabled. Also, if triggers are present on the target database, then it may change data loaded by AWS DMS in unexpected ways. 1. If you do not have one, then generate a script that enables the foreign key constraints and triggers. Later, when you want to add them to your migrated database, you can just run this script. 1. (Optional) Drop secondary indexes on the target database. Secondary indexes (as with all indexes) can slow down the full load of data into tables since they need to be maintained and updated during the loading process. Dropping them can improve the performance of your full load process. If you drop the indexes, then you will need to add them back later after the full load is complete. 1. Choose **Next**. # Step 7: Create and Run Your AWS DMS Migration Task Using an AWS DMS task, you can specify which schema to migrate and the type of migration. You can migrate existing data, migrate existing data and replicate ongoing changes, or replicate data changes only. This walkthrough migrates existing data and replicates ongoing changes. 1. On the **Create Task** page, specify the task options. The following table describes the settings. [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/dms/latest/sbs/chap-rdsoracle2postgresql.steps.createmigrationtask.html) The page should look like the following: ![\[Create task page\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdsor2postgressql23.png) 1. Under **Task Settings**, choose **Do nothing** or **Truncate** for **Target table preparation mode**, because you have already created the tables using the AWS Schema Conversion Tool. If the Oracle database has LOBs, then for **Include LOB columns in replication**, select **Full LOB mode** if you want to replicate the entire LOB for all tables. Select **Limited LOB mode** if you want to replicate the LOBs only up to a certain size. You specify the size of the LOB to migrate in **Max LOB size (kb)**. It is best to select **Enable logging**. If you enable logging, then you can see any errors or warnings that the task encounters, and you can troubleshoot those issues. ![\[Task Settings section\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdsor2postgresql23.5.png) 1. Leave the Advanced settings at their default values. 1. Choose **Table mappings**, and select the **JSON** tab. Next, select **Enable JSON editing**, and enter the table mappings you saved in the last step in [Step 4: Convert the Oracle Schema to PostgreSQL](chap-rdsoracle2postgresql.steps.convertschema.md). The following is an example of mappings that convert schema names and table names to lowercase. ``` { "rules": [ { "rule-type": "transformation", "rule-id": "100000", "rule-name": "Default Lowercase Table Rule", "rule-action": "convert-lowercase", "rule-target": "table", "object-locator": { "schema-name": "%", "table-name": "%" } }, { "rule-type": "transformation", "rule-id": "100001", "rule-name": "Default Lowercase Schema Rule", "rule-action": "convert-lowercase", "rule-target": "schema", "object-locator": { "schema-name": "%" } } ] } ``` 1. Choose **Create task**. The task will begin immediately. The Tasks section shows you the status of the migration task. ![\[Migration task status\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-rdsor2postgressql25.png) You can monitor your task if you chose **Enable logging** when you set up your task. You can then view the CloudWatch metrics by doing the following: 1. On the navigation pane, choose **Tasks**. 1. Choose your migration task. 1. Choose the **Task monitoring** tab, and monitor the task in progress on that tab. When the full load is complete and cached changes are applied, the task will stop on its own. 1. On the target PostgreSQL database, enable foreign key constraints and triggers using the script you saved previously. 1. On the target PostgreSQL database, re-create the secondary indexes if you removed them previously. 1. In the AWS DMS console, start the AWS DMS task by clicking **Start/Resume** for the task. The AWS DMS task keeps the target PostgreSQL database up-to-date with source database changes. AWS DMS will keep all of the tables in the task up-to-date until it is time to implement the application migration. The latency will be zero, or close to zero, when the target has caught up to the source. # Step 8: Cut Over to PostgreSQL To move connections from your Oracle database to your PostgreSQL database, do the following: 1. End all Oracle database dependencies and activities, such as running scripts and client connections. The following query should return no results: ``` SELECT MACHINE, COUNT(*) FROM V$SESSION GROUP BY MACHINE; ``` 1. List any remaining sessions, and kill them. ``` SELECT SID, SERIAL#, STATUS FROM V$SESSION; ALTER SYSTEM KILL 'sid, serial_number' IMMEDIATE; ``` 1. Shut down all listeners on the Oracle database. 1. (Optional) Turn off automated jobs on the Oracle database. For your production database, check that this operation doesn’t influence the business logic. ``` ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0 ``` 1. (Optional) Turn off time monitoring on queue messages on the Oracle database. For your production database, check that this operation doesn’t influence the business logic. ``` ALTER SYSTEM SET AQ_TM_PROCESSES=0 ``` 1. Let the AWS DMS task apply the final changes from the Oracle database on the PostgreSQL database. ``` ALTER SYSTEM CHECKPOINT; ``` 1. In the AWS DMS console, stop the AWS DMS task by clicking **Stop** for the task, and confirm that you want to stop the task. 1. (Optional) Set up a rollback. You can optionally set up a rollback task, in case you run into a show stopping issue, by creating a task going in the opposite direction. Because all tables should be in sync between both databases, you only need to set up a CDC task. Therefore, you do not have to disable any foreign key constraints. Now that the source and target databases are reversed, you must follow the instructions in the following sections: + [Using a PostgreSQL Database as a Source](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html) + [Using an Oracle Database as a Target](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.Oracle.html) 1. Disable triggers on the source Oracle database. ``` SELECT 'ALTER TRIGGER' || owner || '.' || trigger_name || 'DISABLE;' FROM DBA_TRIGGERS WHERE OWNER = 'schema_name'; ``` You do not have to disable the foreign key constraints. During the CDC process, foreign key constraints are updated in the same order as they are updated by application users. 1. Create a new CDC-only AWS DMS task with the endpoints reversed (source PostgreSQL endpoint and target Oracle endpoint database). See [Step 7: Create and Run Your Migration Task](chap-rdsoracle2postgresql.steps.createmigrationtask.md). For the rollback task, set **Migration type** to **Replicate data changes only** and **Target table preparation mode** to **Do nothing**. 1. Start the AWS DMS task to enable you to push changes back to the original source Oracle database from the new PostgreSQL database if rollback is necessary. 1. Connect to the PostgreSQL database, and enable triggers. ``` ALTER TABLE table_name ENABLE TRIGGER ALL; ``` 1. If you set up a rollback, then complete the rollback setup. 1. Start the application services on new target PostgreSQL database (including scripts , client software, and so on). 1. Add CloudWatch monitoring on your new PostgreSQL database. For more information, see [Monitoring Amazon RDS](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Monitoring.html). # Rolling Back the Migration If there are major issues with the migration that cannot be resolved in a timely manner, you can roll back the migration. These steps assume that you have already prepared for the rollback as described in [Step 8: Cut Over to PostgreSQL](chap-rdsoracle2postgresql.steps.cutover.md). 1. Stop all application services on the target PostgreSQL database. 1. Let the AWS DMS task replicate remaining changes back to the source Oracle database. 1. Stop the PostgreSQL to Oracle AWS DMS task. 1. Start all applications back on the source Oracle database. # Oracle database migration to PostgreSQL troubleshooting The two most common problem areas when working with Oracle as a source and PostgreSQL as a target are: supplemental logging and case sensitivity. + Supplemental logging – With Oracle, in order to replicate change data, supplemental logging must be enabled. However, if you enable supplemental logging at the database level, it sometimes still needs to be enabled when new tables are created. The best remedy for this is to allow AWS DMS to enable supplemental logging for you by using the extra connection attribute: ``` addSupplementalLogging=Y ``` + Case sensitivity: Oracle is case-insensitive (unless you use quotes around your object names). However, text appears in the upper case. Thus, AWS DMS defaults to naming your target objects in the upper case. In most cases, you’ll want to use transformations to change schema, table, and column names to lower case. For more tips, see [Troubleshooting migration tasks](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html). To troubleshoot issues specific to Oracle, see [Troubleshooting Oracle Specific Issues](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.Oracle). To troubleshoot PostgreSQL issues, see [Troubleshooting PostgreSQL Specific Issues](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Troubleshooting.html#CHAP_Troubleshooting.PostgreSQL).