Migrating Oracle Database to Google Cloud

When migrating proprietary or legacy databases then we are limited by restrictions put by their license terms and conditions. Also because of black box nature of such systems there are limited options to extract data out of these systems. To migrate Oracle database to the Google Cloud you can either migrate it to some other database engine or you could just do Lift & Shift.

Common reasons for leaving Oracle Cost Avoid vendor lock-in Open Source Initiatives Application Modernization and/or adopting serveless or cloud native storage options Technology consolidation Common reasons to stay with Oracle Existing investment Application rewrite and refactoring cost and risk associated with it Existing staff Packaged applications Performance

Google Cloud offer solutions for either of the option you choose (“Leave” is heterogeneous migration and “Stay” is homogeneous migration).

Oracle Database support – Google Cloud vs AWS and Azure (as on Dec-21) GCP Cloud SQL and Azure Database doesn’t have equivalent of AWS RDS for Oracle GCP Compute Engine doesn’t have equivalent of Oracle on AWS EC2 and Oracle on Azure Virtual Machines (because of Oracle Licensing restrictions1) GCP does provide options to Lift & Shift Oracle Database to GCP and the solutions are (both options are also available with AWS and Azure): Bare Metal Solution for Oracle Google Cloud VMware Engine GCP DMS only support homogenous sources (and limited to MySQL and PostgreSQL) but AWS DMS and Azure DMS supports heterogeneous sources including Oracle database migration.

Lift & Shift Options Bare Metal Solution for Oracle:

Google provides fully managed certified database infrastructure and SLA for Oracle workloads with its Bare Metal solution and as per Google its infrastructure provides up to 50% reduction in Oracle license cost vs. AWS/Azure Virtual Environments2.

Below is the typical architecture of Oracle on Google Cloud Bare Metal:

Google Cloud VMware Engine:

Oracle databases, middleware and applications are increasingly running on the VMware vSphere platform and Oracle certify its products on ESXi so Google Cloud VMware service could be used as the Lift & Shift target for oracle workloads. Please note that Oracle requires you to license all the physical cores of all the physical EXSi hosts of all the vCenter Server Instance(s) which have hosts with ESXi 5.1 or later hypervisors3.

Please note that – Google Cloud VMware Engine solution does not allow customers to control patches, upgrades or updates to their VMware environment— this can potentially break certification and support for applications running in Google Cloud VMware environments4.

Heterogeneous Oracle Database Migration

Below are the most common heterogeneous target data stores on Google Cloud for Oracle database migration are following:

Cloud SQL for MySQL/PostgreSQL Cloud Spanner BigQuery

When we migrate heterogeneous databases we need to do following tasks:

Choosing target database version (and database engine), which is driven by factors like: Application requirements Security updates Effort needed to migrate the schema and native features Compatibility between source and target schema Impact on application(s) using the database Migrating source schema to target schema (TABLE with constraints, VIEW, MVIEW, TABLESPACE, SEQUENCE, INDEXES, TRIGGER, GRANT, FUNCTION, PROCEDURE, PACKAGE, PARTITION, TYPE, INSERT or COPY, FDW, QUERY, KETTLE, SYNONYM) Any other database objects/functions etc. written using the source database engine’s native features to target database Data ingestion (ETL) Validation Moving from proprietary drivers to target database driver and associated code and embedded SQL (if any) Optional Step: Setup CDC pipeline (for cutover at later point in time) Data Validation before cutover Optional Step: Migrating target database to higher or latest version once the migration between heterogeneous databases completed Additional items: Optimization, HA and DR, Security, Analytical pipelines, Reports and/or Dashboards, migrating set of tools and utilities, Data Governance, Compliance, SRE etc. (which might require additional cloud services)

Data Migration

Bulk data export/import

Below are the available options for “initial load” i.e. snapshot of data in Oracle database:

SQL *Loader utility (sqlldr) Data Pump Export utility (expdp) Export utility (exp) Data Load/Unload wizards in Oracle Database XE

When the data has been exported from Oracle into a flat file, you can load it into MySQL by using the “LOAD DATA INFILE” command. You can use “pg_restore” to import data to PostgreSQL.

Change Data Capture (CDC):

Drawback with bulk data export/import is that it doesn’t capture changes to data and schema once snapshot is taken i.e. they don’t support Change Data Capture (CDC) and this could become a bottleneck if you couldn’t afford to bring down your database for cutover. Also There are lot of things goes in-between you took initial snapshot and when the actual cutover happens and that is the reason more mature products which support CDC are used for data migration.

Options for migrating data from Oracle database which support CDC could be categorized under:

Log-based: which are based on redo or archive log files SQL-based: they poll source database for changes based on some condition Trigger-based: they use triggers on source tables to log changes in shadow tables.

Out of above three Log-based are used often used and recommend as other two options increase load on source databases and are prone to errors and might also result in data loss.

Log-based options for Data Migration

Oracle GoldenGate:

Oracle GoldenGate is a licensed product which helps real-time data capture and replication in heterogeneous IT environment. It support source data stores like DB2, MySQL, SQL Server, Sybase, Teradata etc. and off course also used for Oracle database data migration. It is also used to replicate

data between Master and HA Standby, it also support many other topologies e.g. Active-Active, Broadcast, Consolidation etc.

Oracle database also support LogMiner and XStream but they are decommissioned e.g. CONTINUOUS_MINE option for the dbms_logmnr.start_logmnr package is not supported in Oracle Database 19c, and is no longer available. So moving forward anyone who wants to have CDC stream of events has to use Oracle GoldenGate.

Below is the logical architecture of the Oracle GoldenGate5:

LogMiner

Oracle LogMiner, enables you to query online and archived redo log files through a SQL interface. Redo log files contain information about the history of activity on a database. By default, Oracle Database does not provide any supplemental logging, which means that by default LogMiner is not usable. Therefore, you must enable at least minimal supplemental logging prior to generating log files which will be analyzed by LogMiner. As mentioned above that continuous mining has been deprecated in in Oracle Database 19c, so if you are using older versions of Oracle database then you can use this feature to migrate your data.

XStream Out:

XStream consists of two major features: XStream Out and XStream In. XStream Out provides Oracle Database components and APIs that enable you to share data changes made to an Oracle database with other systems. You can configure a heterogeneous replication environment with XStream. Replication is generally used to improve availability and to improve performance by spreading the network load over multiple regions and servers. In a heterogeneous replication environment, data is replicated between databases from different vendors.

The strategic direction for logical replication within Oracle is now Oracle GoldenGate. Oracle will integrate the 'best' features of Streams into Oracle GoldenGate. Oracle GoldenGate Extract runs externally to the Oracle Database and Oracle Streams Capture runs as a process within the Oracle instance.

Using the XStream APIs requires purchasing a license for the Oracle GoldenGate product.

Commonly used Product and services for Oracle Data Migration to Google cloud

Using Oracle GoldenGate for BigData:

GoldenGate for Big Data 21.4 adds support for delivering data into Google Cloud Storage using a native handler6. Once data is on Cloud Storage you can use Dataflow to push data into Cloud SQL, Cloud Spanner and Big Query.

Using Datastream and Dataflow:

Datastream supports the following versions of Oracle database because it depends on LogMiner to extract the CDC stream of events7:

Oracle 11g, Version 11.2.0.4 Oracle 12c, Version 12.1.0.2 Oracle 12c, Version 12.2.0.1 Oracle 18c Oracle 19c

Using Data Fusion:

Using Striim on Google Cloud Marketplace:

Using Debezium:

Debezium is an open source distributed platform for change data capture. It provides Oracle connector which can ingest change events from Oracle using either LogMiner or XStream. You can create the pipeline using Cloud Pub/Sub and Dataflow to ingest the data in Cloud SQL, BigQuery or Cloud Spanner.

Schema Migration:

Following are some free tools available for schema migration:

Ora2pg– is an Oracle to PostgreSQL database schema converter. Here is the link for the same https://github.com/darold/ora2pg AWS SCT – AWS Schema Conversion Tool is an Oracle to MySQL and PostgreSQL schema converter. Here is the link for the same https://docs.aws.amazon.com/SchemaConversionTool/latest/userguide/CHAP_Welcome.html Manual Approach – for migrating Oracle schema to CloudSQL for MySQL schema you can refer to elaborated information provided at https://github.com/darold/ora2pg Conclusion

Migrating Oracle workloads to Google Cloud either Lift & Shift or to another database is little involved because of licensing requirements and limited export options. In addition to this, options might not be available for specific version of Oracle database or it might go away as you upgrade your database in future. Also choosing the wrong target and cloud services could derail your migration project and you might fail to meet your migration objectives. And if it is done correctly it will not only save you lot of money but also helps you innovate fast which would give you an edge over your completion.