Migrating from Oracle Autonomous Database to YugabyteDB with YB-Voyager

Migrating to YugabyteDB is easy: because it is PostgreSQL-compatible, many tools already exists. For migrating from Oracle, ora2pg is widely used. This is what YugabyteDB Voyager runs under the hood when migrating from Oracle.

In this post I'll show a simple case easy to reproduce: running YugabyteDB Voyager in a Docker container to migrate from The Oracle Database managed service (called "Autonomous Database") to a YugabyteDB cluster. I'll use the demo schema coming with the free Autonomous Database so that it is easy to reproduce without the need to pay for Oracle. For the target, no problem, YugabyteDB is Open Source.

Start Docker

I start a temporary container from the YugabyteDB docker image. I'll not run the database here, just sleep infinity and start a bash shell:

docker run -it --rm -v "C:\Downloads\mig":/mig yugabytedb/yugabyte bash

no sudo

I'm following the documentation to install YugabyteDB Voyager. The only difference is that I've no sudo in this image but I'm already root:

cd /mig
yum install -y git java-11-openjdk
update-alternatives --set java java-11-openjdk.x86_64
git clone https://github.com/yugabyte/yb-voyager.git
cd yb-voyager/installer_scripts
type sudo || { sudo() { "$@"; } ; export -f sudo ; }
yes Y | ./install-yb-voyager
cd -
. ~/.bashrc

Download Oracle wallet

To connect to my Oracle Autonomous Database, I need a wallet that contains the certificate and listener addresses. I download it to my laptop:

I copy it into the Docker container:

docker cp Wallet_o21c.zip 9cfb744ee47f:/var/tmp

I unzip it:

yum install -y unzip
unzip -d . /var/tmp/Wallet_o21c.zip
sed -e '/WALLET_LOCATION/s/"[^"]*"/"$TNS_ADMIN"/' -i sqlnet.ora
export TNS_ADMIN=$PWD

Note that the sqlnet.ora in it supposes that the wallet location is under the $ORACLE_HOME/network/admin but with the Oracle InstantClient (installed with YugabyteDB Managed) there's no ORACLE_HOME. I've defined the TNS_ADMIN environment variable to the directory where I unzipped the wallet and that's why I use this in sqlnet.ora

I've put it in my local directory, /home/yugabyte, for this simple demo. This is not a best practice but just a quick way to learn how it works.

I test the connection with sqlplus:

grep -E "^o21c_tp *=" $TNS_ADMIN/tnsnames.ora &&
sqlplus demo/",,P455w0rd,,"@o21c_tp <<'SQL'
column "Size" format a30
select segment_type, count(*)
, dbms_xplan.format_size2(sum(bytes)) "Size"
from dba_segments 
where owner='SH'
group by segment_type
order by sum(bytes);
SQL

Export schema

Here is the export of the schema:

yes Y | yb-voyager export schema --export-dir $PWD schema \
--source-db-type oracle --oracle-tns-alias o21c_tp \
--source-db-user demo --source-db-password ",,P455w0rd,," \
--source-db-name=_this_is_ignored_ --source-db-schema=SH \
--start-clean

Note that the --source-db-name is mandatory but not used here where I use a --oracle-tns-alias.

Analyze schema

I exported the SH sample schema which is really simple with only 9 tables:

yb-voyager analyze-schema --export-dir $PWD 

Export Data

The following exports all table rows ready to be imported. The source must be read only

yb-voyager export data --export-dir $PWD \
--source-db-type oracle --oracle-tns-alias o21c_tp \
--source-db-user demo --source-db-password ",,P455w0rd,," \
--source-db-name=_this_is_ignored_ --source-db-schema=SH

You can follow the performance on the source from the Oracle Performance Hub:

The files are in the data subdirectory as COPY statements ready to import into YugabyteDB.

Import into YugabyteDB

I create a database in my target cluster, import the schema and then data, then create the indexes and trigger:

psql -h yb0.pachot.net -p 5433 -U yugabyte -c "create database sh"

yes Y | yb-voyager import schema --export-dir $PWD \
        --target-db-host yb0.pachot.net \
        --target-db-user yugabyte\
        --target-db-password yugabyte \
        --target-db-name sh \
        --target-db-schema public \
        --start-clean

yes Y | yb-voyager import data --export-dir $PWD \
        --target-db-host yb0.pachot.net \
        --target-db-user yugabyte\
        --target-db-password yugabyte \
        --target-db-name sh \
        --target-db-schema public 

yes Y | yb-voyager import schema --export-dir $PWD \
        --target-db-host yb0.pachot.net \
        --target-db-user yugabyte\
        --target-db-password yugabyte \
        --target-db-name sh \
        --target-db-schema public \
        --post-import-data

The big advantage of using YugabyteDB Voyager is that it makes it adds some optimization to import in parallel, to benefit from the distributed YugabyteDB. It can also resume when interrupted.

Customize the Schema

If you have run the above, you got an error when importing the schema:

ALTER TABLE costs ADD CONSTRAINT costs_channel_fk FOREIGN KEY (channel_id) REFER ...

    ERROR: there is no unique constraint matching given keys for referenced table "channels" (SQLSTATE 42830)
    STATEMENT: ALTER TABLE costs ADD CONSTRAINT costs_channel_fk FOREIGN KEY (channel_id) REFERENCES channels(channel_id) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE NOT VALID;

The Oracle SH demo schema has its primary keys disabled. This is not a normal situation because it means that there's no index on the primary key. The consequence is that no primary key have been created when exporting the schema.

To fix it I quickly generated, from the Oracle source, the commands to create the primary key in YugabyteDB:

select 'alter table '||table_name
||' add primary key ('
||listagg(column_name || ' hash') within group (order by position)
||');'
from all_cons_columns where owner='SH' and (owner,constraint_name) in (
select owner,constraint_name from all_constraints where constraint_type='P'
) group by owner, table_name
;

I've added the output into ./schema/tables/table.sql before the creation of Foreign Keys

I've got another error:

sh=# ALTER TABLE costs ADD CONSTRAINT costs_product_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE NOT VALID;
ERROR:  foreign key constraint "costs_product_fk" cannot be implemented
DETAIL:  Key columns "prod_id" and "prod_id" are of incompatible types: numeric and integer.

I've no idea why some NUMBER have been translated to NUMERIC and others to INTEGER. Anyway, it makes no sense to use NUMERIC for an ID in PostgreSQL or YugabyteDB. And INTEGER is not better as it can reach its maximum and be hard to change. I've modified all NUMBER id's with the following regexp:

sed -E 's/_id (integer|numeric) /_id bigint /' -i /home/yugabyte/schema/tables/table.sql

Even if there are tools to ease the migration, it is always a good idea check the schema. Some decisions are harder to change later. The default schema may be correct to import quickly and test your application- However, before migrating production, the DDL should be carefully validated so that datatypes and primary keys are efficient in the long term.