"G" Moment: Best Practices Guide for Migrating from Oracle to GBase 8s (Part 2)

In the last "G" Tech Moment, we detailed the key points of Oracle data export and file formats. After all, "getting the data out safely" is the first step. This article focuses on the critical next steps: how to smoothly import the exported data into GBase 8s, and how to "verify the integrity" after migration to ensure no data discrepancies and normal usability.

Data Import

1. Precautions

When creating the database, create it in no-logging mode, so that data import does not need to record logical logs, greatly improving import efficiency. After the data import is completed, use the ontape command to change the database to the required logging mode.

For example, to change a database's logging mode to UNBUFF:

ontape –U database_name –s –L 0 –t /dev/null

For large data migrations, you can extract scripts for creating indexes, constraints, etc. separately, and after all data is successfully imported, enable PDQ to build them.

2. The load Tool

Using the load tool for import:

load is the most basic and commonly used text data import tool in GBase 8s, supporting concurrent import of multiple tables and easy operation.

Example:

load from '/opt/gbase8s/data/order.unl' insert into order;

3. External Table Import

Using GBase 8s external table for import:

For tables with large amounts of data, the traditional load import method would occupy a large time window during migration, becoming a bottleneck for migration efficiency. To address this issue, for importing large tables, you can use the GBase 8s external table method. Create an External table:

Syntax

CREATE EXTERNAL TABLE table-name
(       
column-name { datatype [DEFAULT default_opts] | } [
] [,...]
)
USING (DATAFILES("{DISK | PIPE} : file-path" [,...] )       
[, [...] ])
: EXTERNAL CHAR( size ) [ NULL 'null-string' [ NOT NULL ] ]
:
FORMAT format-type
DEFAULT | DELUXE | EXPRESS
ESCAPE 'escape-character'
DELIMITER 'field-delimiter'
RECORDEND 'record-delimiter'
MAXERRORS num-errors
REJECTFILE 'filename'
NUMROWS num-rows

Example:

create external table orders_ext
 ( order_num serial, order_date date, customer_num integer,ship_instruct char(40),
backlog char(1), po_num char(10),ship_date date, ship_weight decimal(8,2),
ship_charge money(6,2), paid_date date ) 
using 
 (
   datafiles ("DISK:/opt/gbase/test/external_table/orders1.unl",
              "DISK:/opt/gbase/test/external_table/orders2.unl" ),
   format "delimited",
   DELIMITER "|",
   rejectfile "/opt/gbase/test/external_table/orders_rejfile.err",
   maxerrors 100
 );

You can also create an external table with the same structure based on an existing table structure:

create external table orders_ext SAMEAS orders
using 
 (
      datafiles ("DISK:/opt/gbse/test/external_table/orders1.unl",
                 "DISK:/opt/gbase/test/external_table/orders2.unl" ),
   format "delimited",
   DELIMITER "|",
   rejectfile "/opt/gbase/test/external_table/orders_rejfile.err",
   maxerrors 100
 );

Import data:

insert into orders select * from orders_ext;

Tips for using external tables: PDQ & sharded tables & Light append
Enable PDQ for parallel processing.
When the target table is a sharded table, parallel insert and select can be performed.
When the import table is a RAW TABLE, use Light append for fast data import.

Data Verification

Verify the number of data rows

Row count statistics for each table in the Oracle database:

exec dbms_stats.gather_schema_stats (ownname=>'owner', options=>'gather auto' , estimate_percent=>dbms_stats.auto_sample_size);
select table_name,num_rows from dba_tables where owner='OWNER' order by 2 desc;

Row count statistics for each table in the GBase 8s database:

update statistics;
select tabname,nrows from systables where tabid>99 and tabtpye='T' order by 2 desc;

Through the two articles in this series, we have systematically introduced the end-to-end technical solution for data migration from Oracle to GBase 8s. Data migration is not only a technology upgrade, but also a comprehensive reflection of the depth of understanding of system architecture and engineering implementation capabilities. If you encounter specific problems in practice, please leave a message for discussion. We will continue to share more practical experience and technical analysis.