Migrate your Oracle database to AlloyDB for PostgreSQL

Stay organized with collections Save and categorize content based on your preferences.

This page describes how to convert your Oracle database to PostgreSQL syntax and migrate the data to AlloyDB for PostgreSQL with Database Migration Service.

The migration process involves the following tasks:

1. Configuring your source database for migration connectivity and preparing the data for conversion to PostgreSQL.

2. Creating the destination AlloyDB for PostgreSQL instance.

3. Converting your Oracle schema and other objects to PostgreSQL syntax with a Database Migration Service conversion workspace.

4. Creating and running the migration job in Database Migration Service.

5. Monitoring the migration job progress with Database Migration Service observability features.

6. Promoting the migration job after the data is fully migrated.

Costs

In this document, you use the following billable components of Google Cloud:

• AlloyDB for PostgreSQL (See AlloyDB for PostgreSQL pricing.)
• If used for storing Customer-managed Encryption Keys (CMEK): Cloud Storage (See Cloud Storage pricing.)

To generate a cost estimate based on your projected usage, use the pricing calculator.

Before you begin

1. Verify if this migration path can fully support your scenario. See the following pages:
   • Supported sources and destinations on the Scenario overview page lists all supported source and destination versions.
   • Known limitations describes supported data types, database sizes, and other limitations.
   • Networking overview describes available network connectivity solutions.

     To migrate your data, Database Migration Service needs to establish a network connection to your source and destination instances. Depending on your architecture, you might need additional preparation to facilitate network connectivity.

2. Consider in which region you want to create the destination database. Database Migration Service is a fully-regional product, meaning all entities related to your migration (source and destination connection profiles, migration jobs, destination databases, conversion workspaces) must be saved in a single region.
3. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Go to project selector

4. Enable the Database Migration Service, Compute Engine, Cloud Storage, and Cloud SQL Admin APIs.

   Enable the APIs

Required roles

To get the permissions that you need to perform heterogeneous SQL Server migrations with Database Migration Service, ask your administrator to grant you the required IAM roles on your project:

• Database Migration Admin (roles/datamigration.admin)
• AlloyDB Admin (roles/alloydb.admin)

For more information about granting roles, see Manage access in the Identity and Access Management documentation.

These predefined roles contain the permissions required to perform heterogeneous SQL Server migrations with Database Migration Service. To see the exact permissions that are required, expand the Required permissions section:

Step 1. Prepare your source database

To prepare your source data for migration, follow these steps:

1. Set up source database connectivity. Do the following:
   1. Optional: Consider if you want to use SSL/TLS certificates to secure the source network connection. For more information, see Secure network connections with TLS.

      SSL/TLS encryption is supported for Oracle version 12 and later. Database Migration Service supports only the TLS encryption method. Depending on your SSL/TLS configuration, you might need to perform additional configuration steps on your source database.

   2. Choose and configure your source network connectivity method.
2. Configure your source database instance.

   In this step, you create a dedicated migration database user account and enable the necessary replication features.

3. Optional: Optimize your log file configuration.

   Accessing archived log files inherently adds some latency to the migration process. You can adjust certain log file settings to control the latency impact.

4. Create a source connection profile.

   Connection profiles contain information necessary for Database Migration Service to establish the connection to your source database. Connection details vary depending on which source network connectivity method you use.

Step 2. Prepare your AlloyDB for PostgreSQL destination instance

To configure your destination Cloud SQL instance, perform the following steps:

1. Choose and configure your destination network connectivity method.
2. Create and configure your AlloyDB for PostgreSQL destination instance.

   Make sure you use enough compute and memory resources to cover your migration needs. For more information, see Migration recommendations.

3. Create a destination connection profile.

   Connection profiles contain information necessary for Database Migration Service to establish the connection to your destination database. Connection details vary depending on which destination network connectivity method you use.

Step 3. Convert Oracle objects to PostgreSQL syntax

Databases can often have thousands of objects. Converting them all in a single session might be a challenging process. With conversion workspaces, you can divide the conversion process into multiple phases where you add new objects to the conversion, fix the issues, and then test them in your destination database.

To convert objects from your source database, do the following:

1. Create the conversion workspace and run the initial conversion.
2. Fix conversion issues and apply the schema to your destination database.

Step 4. Create and run the migration job

To configure and run your migration, perform the following steps:

1. Optional: If you want to manage your own certificates for the migration job, prepare the necessary encryption keys.

2. Optional: If you want to perform the full dump phase yourself, you can export all your data from the Oracle source database and load it to the destination AlloyDB for PostgreSQL instance outside Database Migration Service.

   If you decide to perform the full dump outside the migration job flow, make sure you record the System Change Number (SCN) where Database Migration Service should begin the CDC replication.

3. Create and run the migration job.

   The destination Cloud SQL database is writable during the migration to allow DML changes to be applied if needed. Take care not to make any changes to the database configuration or table structures which might break the migration process or impact data integrity.

   You can monitor the migration progress, as well as your destination instance health with Database Migration Service observability features. See Migration job metrics.

Step 5. Finalize the migration

When you decide to switch your application to the new AlloyDB for PostgreSQL instance, finalize the migration by following these steps:

1. Stop all write operations on your source database. You can switch them to read-only mode to retain operational functionality.
2. Optional: Verify migration data for completeness.
3. Promote the migration job.

You can now clean up all Database Migration Service entities such as connection profiles, migration jobs, and conversion workspaces. You can also choose to retain them and reuse these entities for another migration.

What's next

Learn more about Google Cloud features for AlloyDB for PostgreSQL. See AlloyDB for PostgreSQL features.