Migrating Database Oracle to Snowflake

Before checking on why organizations want to migrate databases from Oracle to Snowflake to reap the benefits offered by a cloud-based platform, a quick look at the two will be in order. This post will also go into the process of migration.

Click here to know more about migrating databases from Oracle to Snowflake.

Oracle Database

A product of the Oracle Corporation, Oracle is a relational database management system offering businesses a wide range of editions to choose from as per their specific needs. This includes the Enterprise Edition, Personal Edition, Express Edition, and Standard Edition. Oracle database system provides maximized and affordable solutions for applications and data management and is primarily designed for data warehousing and grid computing.

Snowflake

Snowflake is a cloud-based data warehousing solution that has all the cutting-edge advantages of the cloud, the main being optimizing a data lake approach regardless of where the data is located.

One of the primary reasons for migrating databases from Oracle to Snowflake is the recently-introduced Snowflake Database Replication feature that enables users to replicate databases and sync them with various accounts existing in different regions and cloud providers. Thus, organizations get assured business continuity as secondary servers are automatically triggered in the case of an outage in the primary server and there is no downtime and break in work.

Further, the cloud environment provides easy data portability and users can move in-premises databases to the cloud like Oracle to Snowflake or from one cloud provider to another.

Migrating database Oracle to Snowflake

There are four steps in migrating databases from Oracle to Snowflake.

The first is extracting data from the Oracle database and placing it in a CSV file. This involves using the SQL Plus query tool that is built into the Oracle Database Server along with the “Spool” command. The extraction is continuous till the time the command is switched off.

Next, the extracted data has to be formatted so that it matches the data structures that are supported by Snowflake. Presently, the data types are EUC-KR, UTF-8, ISO-8859-1 to 9, Big5, and UTF-16. If the data extracted does not match these types, the “File Format Option” has to be used to create a customized format with the date and time marked on the file.

The formatted data has to be kept in a staging area as it cannot be migrated directly from Oracle to Snowflake. There are two such staging areas - an internal staging area where tables have to be created by the users and a name and the date are auto-applied. For the external staging area, Snowflake presently supports Amazon S3 and Microsoft Azure.

The final process of data migration from Oracle to Snowflake is loading data from a staging area to Snowflake using the COPY INTO command for large databases and the Data Loading Overview tool for the small ones. This completes Oracle to Snowflake data migration.