Data Migration – Oracle to couchbase using AWS S3 Stage

Introduction

Are you trying to migrate Oracle to Couchbase? Have you looked all over the internet to find a solution for it? If yes, then you are in the right place. Couchbase is a distributed multi-model NoSQL document-oriented database, whereas Oracle is a modern Database Management System.

This article will give you a brief overview of migrating data from Oracle Database and Couchbase via S3 stage.

What is S3 stage, why do we need here:

S3 Bucket – The staging area for Couchbase. This might be something created solely for use by Couchbase, but it is often already an integral part of a company’s greater data repository landscape.

Prerequisite

We need following active accounts for Oracle to Couchbase migration

  • Oracle Database
  • Couchbase
  • AWS S3 (Optionally you can use uArrow S3 )

To know more about Oracle Database, visit this link.

To know more about Couchbase, visit this link.

To know more about AWS S3, visit this link

Create Connections

1. Oracle connection

uArrow has an in-built Oracle Integration that connects to your oracle database within few seconds.

1.1. Click Connection menu from top to view (SQL DATABASE & CLOUD WAREHOUSE, CLOUD STORAGE, etc.) adapters

1.2. Click Oracle button to create Oracle database connection

1.3. Provide below connection parameters in the connection creation form

Parameter Name Description
Connection name Specify the name of the source connection.
Host Enter the name of machine where the Oracle Server instance is located, it should be Computer name, fully qualified domain name, or IP address
Port Enter the port number to connect to this Oracle Server. Four digit integer, Default: 1521
Database Enter an existing Oracle connection through which the uArrow accesses sources data to migrate.
Schema Enter an existing Oracle database schema name.
User Enter the user name of the oracle database, The user name to use for authentication on the Oracle database
Password Enter the user’s password. The password to use for authentication on the Oracle database

1.4. After connection details, validate connection to verify.
![Docusaurus](/img/uarrow/conn/dm_conn_oracle_create_2.png)
1.5. Save Connection – Don’t forget to save connection after connection validation success.

2. AWS S3 connection

uArrow has an in-built AWS S3 Integration that connects to your S3 within few seconds.

2.1. Click Connection menu from top to view (SQL DATABASE & CLOUD WAREHOUSE, CLOUD STORAGE, etc.) adapters

2.2. Click AWS Redshift button to create Redshift warehouse connection

2.3. Provide below connection parameters in the connection creation form

Parameter Name Description
Connection name Specify the name of the stage connection.
Bucket Name Specify your AWS S3 bucket name.
Access Key Specify the access key for your Amazon Web Services account.
Secret Key Specify the secret key for your Amazon Web Services account.
Region The region where your bucket should be located. For example: us-east-1
S3 URI Specify your AWS S3 bucket URI.

2.4.After connection details, validate connection to verify

2.5. Save Connection – Don’t forget to save connection after connection validation success

3. Couchbase connection

uArrow has an in-built Oracle Integration that connects to your oracle database within few seconds.

3.1. Click Connection menu from top to view (SQL DATABASE & CLOUD WAREHOUSE, CLOUD STORAGE, etc.) adapters

3.2. Click Couchbase button to create Couchbase connection

3.3. Provide below connection parameters in the connection creation form

Parameter Name Description
Connection name Specify the name of the target connection.
End Point Specify the hostname / IP address of the couchbase.
Bucket Name Specify the bucket name of the couchbase.
Scope Name Specify the scope name of the couchbase.
user Specify login name of the user for the connection.
Password Specify the password for the specified user.

3.4. After connection details, validate connection to verify
![Docusaurus](/img/uarrow/conn/dm_conn_oracle_create_2.png)
3.5. Save Connection – Don’t forget to save connection after connection validation success.

Create Job

After creating connections, you are ready to create data migration job.

Click Data Migration menu from top to create data migration job.

You can able to see below screen, there you can click again create link to create new job.

Note: if you are already created any job you can use + button to create new data migration job.

1. Source

Define source details

Specify below details to define source

Parameter Name Description
Name Specify the new name for data migration job.
Description Specify detail description if required.
Mapping Creation Mapping can be created in two ways
1. Using System – You can use uArrow screen to select / de-select the mapping details
or
2. Using Existing File – If you have prepared mapping’s in csv file, you can import here
In this article we are using Option 1. Using System
Source Connection Select source database connection which you have already created, if not find any source connection then create connection.
Source Stage Connection This is stage connection, stage connection can be AWS S3, Azure BLOB Storage or Google Cloud Storage which you have created already in above steps
In this article we are using AWS S3 as Source Stage
Note: Based on your target connection location you can decide Source Stage.
Example: Couchbase sits in AWS, then use AWS S3 as Source Stage
or my Couchbase sits in Azure platform then recommeded to use Azure BLOB storage
Choose Source Stage File’s Path Select your existing AWS S3 bucket folder or create new folder path

After specifying source details, Save the connection and click Next for mapping defining screen

2. Mapping

uArrow generates automated mapping based on source schema metadata.

Below are the features being available to enhance your mapping for data migration

Feature Name Description
Required To exclude relatable table /data sets you can de-select the required flag.
Create Table This feature helps to create new table in target, if de-selected then existing target table re-used to migrate
Clean Load Enabled: To delete/truncate the target table data and load source table data, this flag disabled if Create Table option enabled
Disabled: No delete/truncate performed, just inserted the target table data from source table data
Capture Bad Rows This feature helps to capture bad rows (failed rows) in seperate bad rows table for future analysis
Follow Target Schema This feature enabled then source table data loading based on target table data type / schema, this option disabled if Create Table option enabled.
Source Filter This feature helps to filter out data using ANSII SQL from source table / data set

After verifying mapping definitions, please click Apply button to save mapping changes and click Next buttong to define Target definitions

3. Target

Define target details

Parameter Name Description
No. Target Load Default Value : 1, You can increase if you want load to multiple targets from same source.
Target Connection 1 Select existing defined target connection to migrate source data. Here you need minimum insert, delete, select permission if Create Table feature enabled, else create, insert, delete, select permission required.

After defining target connection details, click Save button to save data migration job

4. Schedule / Ad-hoc Run

After saving the data migration job, you can run job (ad-hoc run) or you can schedule job if required.

After saving the data migration job, user may run job (ad-hoc run) by clicking on the ‘Run’ icon at the right most section of the job definition row.

  • After Data Migration Job is configured, click on the ‘Run’ icon at the right most section of the job definition row.
  • This will instantiate Job Execution.
  • System automatically starts the data migration from Source to Target.

User may also schedule job if required by using Schedule icon/button to schedule existing job.

Monitoring Job

Congratulations! You have created new job for AWS S3 to Redshift Warehouse.

After successful job execution you can able to see job progress and lineage info.

Note: You can able to view job summary stats, table level detail, failed rows details for completed job.

1. Navigate to Job Log
Click Job Log menu from top to check job logs.

2. Summary Stats
Click on existing Job Status link/button in above screen to view job log dashboard, this will take you to Data Migration Job Log Summary screen

2. Detail Stats

Data Migration Table Level Status: Here you can see failed details rows clicking on Failed Rows count if any.

3. Lineage

Questions? Feedback?
Did this article help? If you have questions or feedback, feel free to contact us

Menu