Prepare your source for conversion

This page describes how to prepare your source database schema for conversion in the legacy conversion workspace by using the Ora2Pg migration tool.

Oracle and PostgreSQL use different SQL dialects and data types to define the objects and types of data they contain. When migrating between these SQL engines, you need to convert your Oracle schema and code to a schema and code that's compatible with PostgreSQL.

In Database Migration Service, there are two ways you can approach your source conversion:

• (Recommended) Use the conversion workspace with the built-in conversion editor.

  In this approach, schema conversion is automated and all actions take place directly within Database Migration Service. If you choose to use this approach, there is no additional preparation required (except for adding relevant connectivity information). Continue to the Configure your destination Cloud SQL for PostgreSQL database section.

• Use the legacy conversion workspace with the Ora2Pg conversion tool.

  This approach is useful if you want to run the conversion procedures outside of Database Migration Service. Perform all the steps in the Use Ora2Pg for schema conversion section before you create a conversion workspace.

Note: See About conversion workspaces for a more detailed overview of available conversion workspace solutions.

Use Ora2Pg for schema conversion

Before you set up a conversion workspace, do the following:

1. Configure an Ora2Pg file. For more information, see the Configuration section of the Ora2Pg documentation.
2. Use Ora2Pg to generate SQL scripts that you can load into your Cloud SQL for PostgreSQL destination database. For more information, see the Installation section of the Ora2Pg documentation.
3. Run the SQL scripts on your Cloud SQL for PostgreSQL database. This creates the schema on the database.

After you complete these steps, create a conversion workspace that includes the Ora2Pg file you configured. Database Migration Service uses this file to create table mappings between your source Oracle database and your Cloud SQL for PostgreSQL destination database. These table mappings are used to ensure that Database Migration Service loads data from the source into the destination properly.

• Supported Ora2Pg versions: 21.1 - 23.2
• Make sure that you perform all conversions and mappings on the Ora2Pg configuration file, and then upload the file to the conversion workspace. Modifying conversions and mappings outside of the configuration file might cause the migration job to fail.

Work with Ora2Pg directives

Database Migration Service uses the following directives in the Ora2Pg configuration file:

• BOOLEAN_VALUES
• DATA_TYPE
• DEFAULT_NUMERIC
• ENABLE_MICROSECOND
• EXPORT_SCHEMA
• MODIFY_STRUCT
• MODIFY_TYPE
• PG_INTEGER_TYPE
• PG_NUMERIC_TYPE
• PG_SCHEMA
• PRESERVE_CASE
• REPLACE_AS_BOOLEAN
• REPLACE_COLS
• REPLACE_TABLES
• SCHEMA

Because Database Migration Service uses connection profiles to define connectivity details, you don't need to define the following directives in your Or2Pg configuration file:

• ORACLE_DSN
• ORACLE_HOME
• ORACLE_PWD
• ORACLE_USER
• PG_DSN
• PG_PWD
• PG_USER

Additionally, Database Migration Service doesn't use the WHERE configuration directive to limit the records to migrate.

For more information about Ora2Pg configuration directives, see the Configuration section of the Ora2Pg documentation.

Work with tables without primary keys

Data Manipulation Language UPDATE and DELETE statements on tables without primary keys might cause duplicate records because Database Migration Service can't track which row was changed. To learn how to migrate tables in the source database that don't have primary keys, see Migrate tables without primary keys.