Oracle To Cloud SQL: Load Data In Minutes

Introduction to Oracle

Oracle is a popular Relational Database Management System (RDBMS) produced and marketed by Oracle Corporation. It is one of the most used RDBMS to store, organize, and retrieve information. Oracle was the first database to use Enterprise Grid Computing (EGC) to perform tasks. EGC helps retrieve data from any location through a group of computers connected to a network. This makes Oracle a flexible and cost-effective way to manage information and applications.

Oracle runs on multiple types of servers across different operating systems, such as Windows, UNIX, Linux, and macOS. Oracle is a multi-model database system where each database is a data collection treated as a unit to store and retrieve related information. Oracle offers in-memory, NoSQL, and MySQL databases available on-premise through Oracle Cloud or via Oracle Cloud.

Oracle has five editions of its database to choose from, they include Standard Edition One, Standard Edition, Enterprise Edition, Express Edition (XE), and Personal Edition. For further information on Oracle, visit here.

Introduction to Cloud SQL

Introduced by Google in 2011, Cloud SQL has become a key part of the Google Cloud Platform and is the mainstay in the Google Cloud SQL offering. Cloud SQL offers a fully managed database service for relational databases, including MySQL, PostgreSQL, and Microsoft SQL Server, similar to Amazon RDS. Since its inception, Cloud SQL has seen significant updates and enhancements, aligning itself with the ever-changing demands of cloud computing. Its ease of use, scalability, and security features have made it a preferred choice for many organizations looking to migrate or set up their databases in the cloud.

Running MySQL on Cloud SQL blends the robust functionality of MySQL with the flexible benefits of a cloud environment. The platform's seamless integration with MySQL allows developers and database administrators to leverage all standard functionalities without the burden of infrastructure management. Since everything is hosted in the cloud, Google Cloud automates tasks like backups, updates, and maintenance to give developers more time to focus on what matters: building and optimizing their applications.

The scalability aspect of Cloud SQL is particularly appealing to businesses that experience fluctuating workloads. It supports vertical and horizontal scaling, and read replicas enhance the system's efficiency. Running MySQL on Cloud SQL also ensures that the MySQL instance is equipped with data encryption, private networking, and compliance with various industry standards, enhancing overall security.

Cloud SQL's tight integration with other Google Cloud services, such as App Engine, Compute Engine, and Google BigQuery, creates a Google-backed ecosystem that allows for easy operation across different parts of your applications. Cloud SQL combines traditional SQL capabilities with modern cloud-based benefits and provides a powerful and streamlined platform tailored to today's data-driven needs.

How to Migrate Data from Oracle to Cloud SQL

Implementing an effective data pipeline is crucial for a smooth migration when migrating data from Oracle to a Google Cloud SQL database, specifically MySQL running on Cloud SQL. This section will explore two main ways to migrate and replicate data between these platforms. First, we will look at using Arcion to move data from Oracle to Cloud SQL in an automated fashion, taking advantage of the platform's advanced capabilities and automation features. Secondly, we will examine the manual migration approach, utilizing native tools and commands built into the platforms. This hands-on method provides control and flexibility but requires a comprehensive understanding of Oracle, CloudSQL, and MySQL. Let’s begin by Looking at Arcion.

Method 1: Oracle to Cloud SQL Using Arcion

Arcion is the leading real-time, in-memory Change Data Capture (CDC) solution that offers users massive scalability and data consistency. It is suitable for migrating and replicating data across various enterprise databases and data warehouses. This section will look at how to connect Oracle to Cloud SQL (MySQL) using the Arcion UI. This can be done in Arcion Cloud or Arcion Self-Hosted. The following functionality is also available via CLI, although this blog does not cover it.

Create a New Replication

First, we must log into Arcion to set up our replication pipeline to enable CDC. For Arcion Cloud, you can sign up and login here.

Next, we will select our replication mode and write mode. A few options are available to suit your needs. For replication modes, Arcion supports:

Snapshot (the initial load)
Realtime (CDC)
Full (snapshot + CDC)

For write modes, Arcion supports:

Replacing
Truncating
Appending

We will select the Replication mode as Full and the Write mode as Truncating. You will also see that I have named the replication “Oracle to Cloud SQL MySQL”. Once the Name is populated and the Replication and Write Modes are selected, click Next at the bottom of the screen.

Configure the Source Oracle Connector

We are then brought to the Source screen. From here, we will click the Add your own source button.

We then will select Oracle as our source, and then, scroll to the bottom of the page, and click Continue.

Now, we can add our Oracle instance details. These details include:

Connection Name
Host
Port
Username
Password

All other fields will default. For Username and Password, you will need to ensure that the user has the correct permissions in Oracle to ensure it can read the necessary data.

With our data source correctly configured, we will click the Test Connection button to ensure everything works as expected.

The results should look like this once the test is finished running. Finally, you can click the Done button to exit.

Once the connection is saved and tested, click Continue. Next, we will want to pull in the schema from the database. Arcion will prompt us to click the Sync Connector button on the next page. Click the button, and Arcion will connect to our Oracle instance and pull down the schema. Once completed, the UI in Arcion will display the retrieved schema. Then we will click Continue in the bottom right corner of the screen to move to the next step.

With our sync successful, we can now click Continue to Destination in the bottom right corner of the screen to move to the steps where we set up our destination.

Configure the Destination Cloud SQL MySQL Connector

On the Destination screen, we will click Add your own destination to start the setup of our Cloud SQL connector.

Then, select Cloud SQL MySQL as your Connection Type and click Continue.

On the next screen, input your connection details. These details include:

Connection Name
Host
Port
Username
Password

All other fields will default. We will require a username and password to connect and write to our Cloud SQL MySQL instance.

Once you enter all the details and click Test connection, you’ll see that the Cloud SQL connector can connect to the target MySQL database.

Configure the Filter

Now, after clicking Done on the connection test, we can click Continue to Filter to begin the Filter configuration for our pipeline.

On the Filters screen, you can either check the Select All checkbox to replicate all of our tables and columns from the source to the destination or select specific tables. In the example below, we will only select specific tables.

You can also click on the Map Tables and Per Table Configs (Applier Configuration Docs, Extractor Configuration Docs) buttons to add further configuration. For our purposes, we will leave these as their default values and click Save.

Start The Replication

After clicking Save to save the filter, click Start Replication.

Once the initial data is loaded, the pipeline will continue to run, monitor for changes, and apply those changes to the destination. The idle pipeline will still show RUNNING in the top right of the screen but will show a row replication rate of 0 until new data is written to the source. You’ll also notice that the Phase description of the pipeline will now show Change Data Capture instead of Loading Snapshot Data.

Since we are using Full-mode replication, If we start adding data to the Oracle instance, we will see that Arcion detects this and then syncs that data to CloudSQL in real-time.

Of course, we don’t require CDC to be enabled to handle the initial migration, and we could use Snapshot mode. An advantage of enabling CDC during the migration is that CDC can make migrations easier by allowing for zero downtime migrations. The old and new databases can stay in sync until all applications and services using the old database are moved to use the new MySQL instance hosted on Google Cloud SQL.

Benefits of Using Arcion

As mentioned, MySQL and PostgreSQL hosted on Cloud SQL are fully supported by Arcion. The steps required to enable data migration from Oracle to Cloud SQL, discussed in previous sections of this article, can be more conveniently carried out with Arcion. This can be done through a simplified and intuitive UI or the CLI. For example, you can set up a streaming pipeline in Arcion using Change Data Capture to replicate changes from Oracle to any of the other destination databases supported, like Cloud SQL, or others like Oracle, DB2, SAP (ASE, HANA, IQ), Snowflake, and many more. Below are some highlights of using Arcion for data migrations and Change Data Capture.

Sub-Second Latency

Many other existing CDC solutions don’t scale for high-volume, high-velocity data, resulting in slow pipelines and delivery to the target systems. Arcion is the only distributed, end-to-end multi-threaded CDC solution that auto-scales vertically & horizontally. Any process that runs on Source & Target is parallelized using patent-pending techniques to achieve maximum throughput. There isn’t a single step within the pipeline that is single-threaded. Arcion users get ultra-low latency CDC replication and can keep up with the ever-increasing data volume on Source.

100% Agentless Change Data Capture

Arcion is the only CDC vendor that offers 100% agentless CDC to all its supported enterprise connectors. The agentless CDC applies to all complex enterprise databases, including Oracle and Cloud SQL. Arcion reads directly from the database transaction log, never from the database itself. Previously, data teams faced administrative nightmares and security risks associated with running agent-based software in production environments. You can now replicate data in real-time, at scale, with guaranteed delivery — but without the inherent performance issues or the security concerns.

Data Consistency Guaranteed

Arcion provides transactional integrity and data consistency through its CDC technology. To further this effort, Arcion also has built-in data validation support that works automatically and efficiently to maintain data integrity. It offers a scalable data migration and replication solution while ensuring zero data loss has occurred.

Automatic Schema Conversion & Schema Evolution Support

Arcion handles schema changes out of the box, requiring no user intervention. This helps mitigate data loss and eliminate downtime caused by pipeline-breaking schema changes by intercepting changes in the source database and propagating them while ensuring compatibility with the target's schema evolution. Other solutions would reload (re-do the snapshot) the data when there is a schema change in the source databases, which causes pipeline downtime and requires a lot of compute resources, which can get expensive!

Pre-Built Enterprise Data Connectors

Arcion has a library of pre-built data connectors. These connectors can support many of the most popular enterprise databases, data warehouses, and cloud-native analytics platforms (see full list). Unlike other ETL tools, Arcion provides full control over data while still maintaining a high degree of automation. Data can be moved from one source to multiple targets or multiple sources to a single target, depending on your use case. This means that if you branch out into other technologies outside of Oracle and Cloud SQL, you’ll already have the capability within Arcion to handle your new sources and targets without the need for another pipeline technology.

Method 2: Oracle DB to Cloud SQL (MySQL) Manually

For those who want a more manual approach, this method involves exporting data from Oracle, transforming it into a MySQL-friendly format, and importing it into a MySQL instance hosted on Cloud SQL. Below is a step-by-step guide using native tools for manually migrating data from Oracle to Cloud SQL.

Prerequisites

To migrate data from Oracle to a MySQL instance hosted on Google Cloud SQL, you’ll need the following prerequisites:

Oracle Tools: Ensure expdp and impdp utilities are installed on the machine hosting the Oracle instance.
Google Cloud SDK: Install the gcloud command-line tool.
MySQL Client: This will be used to import data into MySQL.

Step 1: Backup your Oracle Database

First, we will use Oracle's Data Pump (expdp) to export the source database. To do this, you must create a directory object in Oracle where the dump will be saved. You can use the command below as an example of how to do this.

CREATE DIRECTORY dmpdir AS '/path/to/your/directory';

After the directory is created, you can then export the data from the selected database and schema. Below is an example script, using expdp, of how the dump can be executed.

expdp SYSTEM/[PASSWORD] DIRECTORY=dmpdir DUMPFILE=[DATABASE_NAME].dmp SCHEMAS=[SCHEMA_NAME]

Make sure to replace the PASSWORD, DATABASE_NAME, and SCHEMA_NAME placeholders appropriately.

Step 2: Convert Oracle Data Dump to MySQL-compatible Format

Since Oracle dumps aren't directly compatible with MySQL, we will need to convert the Oracle schema and data to a format that is better suited. You'll need tools like Oracle's SQL Developer or third-party options like Ora2MySql to convert the dump. For example, you can follow the commands below to convert the data with Oracle's SQL Developer.

Start the SQL Developer tool.
Choose Migrate > Database.
Follow the steps, selecting Oracle as the source and MySQL as the target. Ensure to point to the Oracle dump when prompted.

Step 3: Import Data into Cloud SQL (MySQL)

With the data in a format compatible with MySQL, we can now use the mysql command to load the converted data. Below is an example script that can be used to load the data dump into your Cloud SQL MySQL instance.

mysql -h [Cloud SQL_PUBLIC_IP] -u [USERNAME] -p [DATABASE_NAME] < converted_backup.sql

Ensure that you replace the placeholders for SQL_PUBLIC_IP, USERNAME, DATABASE_NAME, and converted_backup.sql with the actual values for your configuration.

Step 4: Verify the Migration

Optionally, you should check that the migrated data is in MySQL as expected. To do this, you’ll need to connect to the MySQL instance. Below is an example of how to do this.

mysql -h [Cloud SQL_PUBLIC_IP] -u [USERNAME] -p[PASSWORD]

Don’t forget to replace the placeholder with your values.

Once connected, you can run verification queries on specific tables to ensure that data has been imported as expected. Below is an example showing all the tables and returning a row count for a specified table. This is an easy way to confirm that all tables have been migrated and the number of records matches what was expected.

mysql -h [Cloud SQL_PUBLIC_IP] -u [USERNAME] -p[PASSWORD]

Your Oracle data will be active in a MySQL instance on Google's Cloud SQL upon completion. Continuous monitoring and tuning may be required based on the application's demands, and it's also recommended to test your applications that will use the new Cloud SQL instance thoroughly after the migration.

Limitations and Challenges of Using Custom Code Method

Even though manually moving data from an Oracle database to Cloud SQL with custom code is possible, it does come with some significant limitations and challenges. Below is a short list of some limitations and challenges impacting this method.

Complexity

The manual method requires a comprehensive understanding of Oracle, MySQL, and Cloud SQL. This encompasses in-depth knowledge of each SQL dialect, indexing techniques, stored procedures, and other facets. The lack of automation tools amplifies the intricacy, making this method more fitting for seasoned database administrators.

Data Compatibility

Discrepancies in data types, constraints, and encodings between Oracle and MySQL can lead to compatibility issues. Manually bridging these differences might require considerable effort and expertise, especially when dealing with intricate or unconventional data structures.

Time-Consuming

Manually migrating data is often tedious and protracted, particularly with large datasets. Each phase must be meticulously executed, from data export and translation to importation into Cloud SQL. Unanticipated complications can introduce further delays and make timelines somewhat unpredictable.

Maintenance

Adjustments and additions to the database schema must be handled manually, leading to increased maintenance work. Unlike automated alternatives, the manual approach doesn't facilitate continuous synchronization or real-time updates. Such limitations can generate discrepancies between the source and target databases during prolonged migrations. The impact of these discrepancies tends only to surface when it's potentially detrimental and too late to quickly fix.

Error Handling

Without specialized tools, managing errors becomes challenging and could lead to data inconsistencies. Minor oversights in data conversion or misreading data types can introduce discrepancies in the migrated data. Identifying and amending such issues usually involves a thorough diagnosis and validation.

Conclusion

Migrating data from Oracle to Cloud SQL is an important task that can be accomplished using specialized tools like Arcion or a manual, hands-on approach. Understanding the key features of Oracle and Cloud SQL, along with the specific requirements of the migration, is essential in choosing the correct method. Careful planning, consideration of potential challenges, and leveraging available resources can lead to a smooth and successful migration, ensuring data integrity and availability in the new environment.

As we saw, Arcion is a real-time, in-memory Change Data Capture (CDC) solution that guarantees scalability and ensures data consistency when moving data from Oracle to a MySQL instance hosted on Google’s Cloud SQL platform. To efficiently implement a data integration strategy, get started with Arcion today by connecting with our team of Oracle and Cloud SQL migration and replication experts. CDC pipelines built with Arcion are easy to build, maintain, and scale and are production-ready in minutes.

Take Arcion for a Spin