Note:

Migrate Data to Oracle Database@Google Cloud using Oracle Data Pump

Introduction

Oracle Database@Google Cloud is an Oracle Cloud Database service that runs Oracle Database workloads in your Google Cloud environment. Built on Oracle’s best database services like Oracle Exadata Database Service and Oracle Autonomous Database, you can quickly develop and modernize applications with capabilities such as Oracle Database 23ai AI Vector Search and Google Vertex AI.

With native integration, you can deploy your Oracle database services in a Google Cloud data center running on Oracle Cloud Infrastructure (OCI) Exadata hardware with minimal latency. Workloads reside in Google Cloud and the Exadata systems used by both services are deployed in Google Cloud data centers. You get the best from Oracle and Google Cloud – which includes the performance and monitoring tools, developer tools, analytics, etc.

Oracle Data Pump offers very fast bulk data and metadata movement between user managed Oracle databases and Oracle Autonomous Database.

Oracle Data Pump Import lets you import data from Oracle Data Pump files residing in OCI Object Storage. You can save your data to your Google Cloud Storage bucket and use Oracle Data Pump to load data to Oracle Autonomous Database.

This tutorial walks you through the steps to migrate a sample application schema using Oracle Data Pump Import into your Oracle Database@Google Cloud.

Objectives

As a database admin or user:

Prerequisites

Task 1: Generate Access Key for your User Account

  1. Log in to the Google Cloud Console, go to Cloud Storage and click Settings.

    Cloud Storage Settings

  2. In the Settings page, click INTEROPERABILITY. Under Access keys for your user account, click CREATE A KEY.

    Cloud Storage Settings

    Cloud Storage Settings

    Note down the Access key and Secret.

Task 2: Upload the Dump File to Google Cloud Storage Bucket

In this task, upload the database export dump file for the schema that you want to migrate to Oracle Database@Google Cloud to a Google Cloud Storage bucket. If you do not have a dump file at this point and are testing out a migration you can download the dump file from here: HR.dmp.

  1. Go to the Google Cloud Console, navigate to Cloud Storage, Buckets and click CREATE to create a Google Cloud Storage bucket. For this example, we will name the bucket gcpdatapump.

  2. Click the gcpdatapump bucket and upload the dump file.

Task 3: Set up User Credentials in your Target Oracle Autonomous Database

Now, we have the access key and the secret, let us set up the target database to read from the Google Cloud Storage and import data.

Here, we will use SQLcl to demonstrate the steps needed to set up Google Cloud Storage credentials.

  1. Download and install SQLcl on the Google Cloud Compute Engine VM instance from here: sqlcl-24.3.2.330.1718.zip.

  2. Run the following command to unzip the file.

    wget https://download.oracle.com/otn_software/java/sqldeveloper/sqlcl-24.3.2.330.1718.zip
unzip sqlcl-24.3.2.330.1718.zip

  3. In the Google Cloud Console, go to the Autonomous Database page and click the autonomous database that was provisioned.

    Cloud Storage Settings

  4. Go to Autonomous Database Details page, click CONNECTIONS and note down the TNS name and Connection string.

    Cloud Storage Settings Cloud Storage Settings

  5. Create a file named tnsnames.ora under sqlcl/bin and set the TNS_ADMIN variable to current working directory. Connect to the Oracle Autonomous Database using the connection string or by setting up the tnsnames path.

    • Set up tnsnames path and connect to the database.

      cd sqlcl/bin
$ cat tnsnames.ora 
adbgcp_high=(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=mahfgbhj.adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=gd443790554e91c_adbgcp_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))
$ 
export TNS_ADMIN=`pwd`
./sqlcl admin@adbgcp_high

    Or

    • Set up by using connection string.

      cd sqlcl/bin 
./sqlcl admin@"(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)(host=mahfgbhj.adb.us-ashburn-1.oraclecloud.com))(connect_data=(service_name=gd443790554e91c_adbgcp_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=no)))"

  6. Once connected to your autonomous database as admin user, run the following PL/SQL procedure, replacing the username with Access key and password with Secret.

    set define off
begin
DBMS_CLOUD.CREATE_CREDENTIAL(
credential_name => 'GOOGLE_CRED_NAME',
username => 'Access_key',
password => 'Secret'
);
END;
/

    Cloud Storage Settings

    Ensure the PL/SQL procedure is executed successfully from the log message.

  7. Run the following query to test the access to Google Cloud Storage.

    SELECT * FROM DBMS_CLOUD.LIST_OBJECTS('GOOGLE_CRED_NAME', 'https://gcpdatapump.storage.googleapis.com/');

    This image shows the result of performing the above step.

Task 4: Import Data from Google Cloud Storage Bucket using impdp Utility

In the same SQLcl window, run the following data pump import command.

datapump import -
-schemas HR -
-excludeexpr "IN ('PROCEDURE', 'PACKAGE')" -
-directory data_pump_dir -
-credential GOOGLE_CRED_NAME -
-remaptablespaces USERS=DATA -
-dumpuri https://gcpdatapump.storage.googleapis.com/HR.dmp -
-logfile testuser1.log

This image shows the result of performing the above step.

Now, your application schema is successfully imported.

Next Steps

When you implement this solution, you deploy resources in two cloud environments: database resources are in Google Cloud, while the database administration control plane is in Oracle Cloud Infrastructure (OCI). This lets you deploy Oracle Database products in your Google Cloud environment while OCI maintains the administration capabilities.

Oracle Data Pump can be used to migrate your data from on-premises or other cloud providers to Oracle Database@Google Cloud. Along with Oracle Data Pump, Oracle Zero Downtime Migration can also be used to migrate data to Oracle Database@Google Cloud.

Acknowledgements

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.