Oracle to Cloud SQL/AlloyDB: Stress-Free Migration with CDC and DMS

Are you facing the challenge of migrating your Oracle database to Cloud SQL or AlloyDB? If complexity and risks are worrying you, we have the perfect solution. With CDC and DMS tools, migration becomes a quick, easy, and safe process. Leave your worries behind and discover how to achieve a hassle-free transition!

Step 1: Conducting an Assessment

The journey toward a successful migration begins with a careful exploration. Before embarking on the migration of your Oracle database, it’s essential to conduct an “assessment.” In this first stage, we are mapping out your existing database in detail. This involves:

To carry out the assessment, you can use Google Cloud Platform’s DMA tool.

https://googlecloudplatform.github.io/database-assessment/

DMA will help you identify the following factors:

• The amount of data to be migrated
• The type of data to be migrated
• Complexity
• Performance and availability requirements

Step 2: Creating the New Home in GCP

With the detailed map in hand, it’s time to create a new home for your data in GCP. This step involves:

• Going to the GCP Console: We navigate confidently through the console, where we begin creating the destination database. Whether it's Cloud SQL or AlloyDB, the choice is yours.
• Configuring the Database: In the console, we carefully configure the parameters of the new database. We lay a solid foundation, ensuring the new home is ready to welcome your data with open arms.

With the assessment providing the vision and the GCP console as our construction tool, we’ve completed the first two steps in this exciting migration journey. Get ready for the next leg of the trip!

Step 3: Adjusting the Sails in the Source Database

After mapping out our route and setting up the destination in GCP, it’s time to adjust the sails in the source database. This crucial step involves:

• Enabling archive log mode:

SHUTDOWN IMMEDIATE; 
STARTUP MOUNT; 
ALTER DATABASE ARCHIVELOG; 
ALTER DATABASE OPEN;        

• Modifying RMAN retention policy:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;        

• Enabling supplemental logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;        

• Creating a user with the necessary permissions for migration:

GRANT EXECUTE_CATALOG_ROLE TO [user]; 
GRANT CONNECT TO [user]; 
GRANT CREATE SESSION TO [user];
GRANT SELECT ON SYS.V_$DATABASE TO [user]; 
GRANT SELECT ON SYS.V_$ARCHIVED_LOG TO [user];
GRANT SELECT ON SYS.V_$LOGMNR_CONTENTS TO [user]; 
GRANT SELECT ON SYS.V_$LOGMNR_LOGS TO [user]; 
GRANT EXECUTE ON DBMS_LOGMNR TO [user]; 
GRANT EXECUTE ON DBMS_LOGMNR_D TO [user]; 
GRANT SELECT ANY TRANSACTION TO [user]; 
GRANT SELECT ANY TABLE TO [user]; 
GRANT SELECT ANY DICTIONARY TO [user];        

For Oracle 12 or higher, add this permission:

GRANT LOGMINING TO [user];        

This step ensures that our ship is ready to face any storm that may arise during the migration. With the sails adjusted and the crew prepared, we head toward the next horizon. We move forward with confidence!

Step 4: Master DMS Configuration - Skillfully Navigating

Once we’ve prepared our sails and adjusted the rudder, we dive into the master configuration of Database Migration Service (DMS). This key step is like plotting the exact course on our nautical map, ensuring we’re ready to set sail. Here, our focus is on:

1. Enabling private connectivity between your VPC and DMS.
2. Creating connection profiles for the source and target databases.
3. Creating conversion workspaces for the schemas you want to migrate.
4. Creating migration jobs for the tables you want to migrate.

My recommendation is to work from the last point to the first:

1. Private connectivity
2. Connection Profiles
3. Conversion Workspaces
4. Migration Jobs

Step 1: "Private Connectivity" - Setting the Course

In this exciting journey through database migration waters, the first step is establishing “Private Connectivity.” Here, we’re opening internal doors in your environment to allow seamless communication between your network and the migration service.

Step 2: "Connection Profiles"

In the intriguing migration journey, the "Connection Profiles" stage plays a starring role, establishing the essential links between your source database and its new counterpart at the destination.

With every detail meticulously configured in your connection profiles, you’re weaving a solid and reliable communication network that will pave the way for a successful migration. The magic of your migration begins with well-crafted connections!

Step 3: "Conversion Workspaces"

Welcome to the transcendent phase of your migration journey! In this third step, we dive into creating Conversion Workspaces, a crucial task that defines the transformation of schemas and charts the path to a successful migration.

Creating Conversion Workspaces is a delicate and essential art. Here, we select the schemas that will be part of the new narrative of your database. We are at the epicenter where the structure of your source database begins its metamorphosis to fit the PostgreSQL environment, your final destination.

This step is crucial because here we make critical decisions about how the objects, those fundamental elements of your database, should be modified for a smooth and seamless transition. Some objects will need adjustments to fit into your shiny new PostgreSQL database without difficulty.

Consider this step the stage where the art of migration comes to life. Every choice you make here is a brushstroke on the canvas of your new database.

For this part of the journey, I recommend running the "test" option to detect potential configuration or object migration errors.

Strategic Sequence: Begin your journey by creating schemas, tables, and primary keys (PK). This initial stage lays the foundation for your new PostgreSQL database. The gradual approach makes step-by-step control and verification easier.

Leave Some Guests Out: Temporarily exclude less urgent guests: foreign keys (FK), triggers, indexes, and sequences. By doing so, you simplify the process and allow a focus on essential elements.

Make sure that every migrated table has its own primary key, a fundamental requirement for generating the migration jobs to PostgreSQL.

With the creation of schemas, tables, and primary keys, you’ll set the stage for the next phase of your migration. Generating the CDC migration jobs becomes possible, paving the way for a smooth and efficient process.

Step 4: "Migration Jobs"

By now, you have established a solid connection between your source and destination database, as well as the transformation of objects, and now it’s time to orchestrate the actual migration. In this exciting step, we will configure the migration jobs, marking the beginning of the amazing shift of your data.

Before diving into this process, it’s imperative that all previous points have been completed. Proper implementation of private connectivity, connection profiles, and conversion workspaces sets the foundation for success. Only when all these elements are in place can the migration process begin.

Prepare to shape your migration, carefully selecting the tables that will drive this incredible journey. By configuring migration jobs, you’ll embark on the most intense and exciting phase of this epic process. Let the migration show begin!

Step 5: Running Migration Jobs

In this crucial step, we dive into executing the migration jobs. Imagine this moment as casting a spell; your data, like magical elements, will move from your source database to its new home in the target database. But unlike a conventional spell, these jobs will run incrementally. Only changes made in the source database since the last execution will be migrated.

We will guide you through this process, helping you review the status and functioning of the jobs from the Google Cloud console. Additionally, we will explore the destination database, where the changes should automatically register, marking the progress of your migration.

Step 6: Creating FK, Indexes, and Sequences

Congratulations! You’ve navigated the fascinating data migration journey with Google Cloud Database Migration Service (DMS), and now it’s time to put the finishing touches on your new home in the destination database.

Once the migration jobs are complete, you must create the FK, indexes, and sequences in the target database.

Conclusion In summary, Google Cloud DMS turns what could have been a technical odyssey into a wonderful experience. With DMS, migration is not only easy, it’s a true adventure. Welcome to the world of stress-free migrations with Google Cloud.

Patricio Tapia

DBA Specialist