"G" Moment: Oracle to GBase 8s Data Migration Best Practice Guide (Part 1)

Migrating data from Oracle to GBase 8s is like a carefully planned "system relocation." The entire process consists of two main steps: first, "migrate the structure," meaning you recreate database objects such as tables and views in GBase 8s as they were—similar to drafting the "floor plan" for new furniture; next comes the more critical "data move," securely and accurately transporting your actual "data furniture" to the new home. This article focuses on that critical second phase, providing a detailed walkthrough of the complete data migration workflow.

To ensure error-free data migration, the safest approach is to temporarily pause your applications, much like packing up everything before a move. If the data volume is extremely large and you cannot afford extended application downtime, you can use a "synchronization assistant" (such as a CDC tool). It can replicate newly generated data in real time during the migration, enabling a near-zero-downtime "seamless migration."

Migration process overview

File Format:

When exporting data from Oracle, you should adjust the format as closely as possible to the import format required by GBase 8s, minimizing the workload for data conversion. This section uses the GBase 8s load utility format as an example to illustrate the file format requirements for import. The GBase 8s load utility is a commonly used text-based import tool. Its default field delimiter is the pipe character "|". Since "|" rarely appears in regular text, it is recommended to use "|" as the delimiter during export. Each field should end with "|", and line breaks serve as row separators. Data from each table is stored in a separate file.

Example:

create table customer_log 
 (  id char(14),
   update_date datetime year to second,
   tablename varchar(20),
   update_count float,
updated float  );

The export/import format for the customer_log table is as follows:

20101013114153|2010-10-13 11:41:53|2|53.0|53.0|
20101013114153|2010-10-13 11:41:53|3|0.0|0.0|
20101015094917|2010-10-15 09:49:17|2|15.0|15.0|
20101015094917|2010-10-15 09:49:17|3|0.0|0.0|
20101015094918|2010-10-15 09:49:18|4|1.0|1.0|
20101015102622|2010-10-15 10:26:22|2|2.0|2.0|
20101015102622|2010-10-15 10:26:22|4|0.0|0.0|
20101015111103|2010-10-15 11:11:03|1|1.0|1.0|

Data Export

Oracle data can be exported in the text format required by GBase 8s using SQLPlus functionality.

Below is an example of exporting data with SQLPlus:

set colsep '|'
set head off 
set echo off
set headsep off
set newp none
set long 2000000000
set longchunksize 32767
set linesize 32767
set numw 32
set pagesize 0
set sqlblanklines off
set trimspool on
set termout off
set feedback off 
set numw 32
set verify off
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; 
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff5';   
spool c:\customer.unl
select customer_id,Birth_Date,Address||'|' from customer;
spool off
exit

You can write scripts to run the above operations concurrently, improving the efficiency of exporting data from Oracle. The exported data will be stored in c:\customer.unl in the format required by GBase 8s import.

This article has outlined the structure migration strategies, file format specifications, and efficient export methods for migrating data from Oracle to GBase 8s. If you want to see how these methods perform in real-world environments, we invite you to explore two of our benchmark case studies: Smart Toll Station Migration Practice on a Southwest China Expressway and Core System Upgrade Record of a Provincial Mobile Operator. In the next "G" Moment, we will further explore data export and data validation techniques for Oracle to GBase 8s data migration.