Importing from Oracle Edition Based Redefinition (EBR) to YugabyteDB

We got a question about YugabyteDB Voyager to migrate from Oracle to Yugabyte: can we migrate from Edition Based Redefinition (EBR)? The point of this blog post is to verify that ora2pg, which is used by yb-voyager, reads the current edition without issue.

To be clear, Edition Based Redefinition (EBR) has no equivalent in PostgreSQL and Yugabyte, or any other database I know. On PostgreSQL, if you want to deploy multiple versions of views and procedures, you can use multiple schemas and change the search_path and manage the schema patches and releases case by case.

Edition Based Redefinition

In a free Oracle Autonomous Database 21c, I have run all examples from Oren Nakdimon's blog series on EBR:

My EBR Blog Post Series - @DBoriented

db-oriented.com

I set the default edition to V1:

DEMO@o21c_tp> alter database default edition = V1;

Database altered.

YugabyteDB Voyager

I started a docker container to run YugabyteDB Voyager as I did in a previous post:

Migrating from Oracle Autonomous Database to YugabyteDB with YB-Voyager

Franck Pachot for YugabyteDB Distributed PostgreSQL Database ・ Jan 4 '23

#yugabytedb #postgres #oracle #migration

I have copied the Wallet to my Oracle Autonomous Database lab.

Instead of compiling from source, I installed yb-voyager according to the documentation:

dnf install -y https://s3.us-west-2.amazonaws.com/downloads.yugabyte.com/repos/reporpms/yb-yum-repo-1.1-0.noarch.rpm
dnf install -y https://dl.fedoraproject.org/pub/epel/epel-release-latest-8.noarch.rpm
dnf install -y pgdg-redhat-repo oracle-instant-clients-repo
dnf -qy module disable postgresql
dnf install -y perl-open.noarch
dnf -y install yb-voyager

Export Schema

yes Y | yb-voyager export --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=DEMO_EBR \
--start-clean

I'm exporting from edition V1 where The package people_dl displays 'wow! this is not the base edition anymore', the table people was renamed to people$0 and a column eye_color was added, with an editioning view people.

The package global variable has been transformed to a custom parameter in schema/packages/global_variables.conf:

# Global variables with default values used in packages.
pkg.g_var = '42'

The package has been transformed to a procedure in a schema in order to use the same prefixed call, defined in schema/packages/package.sql:

-- Oracle package 'PEOPLE_DL' declaration, please edit to match PostgreSQL syntax.

DROP SCHEMA IF EXISTS people_dl CASCADE;
CREATE SCHEMA IF NOT EXISTS people_dl;

CREATE OR REPLACE PROCEDURE people_dl.add ( i_id people.id%type, i_first_name people.first_name%type, i_last_name people.last_name%type, i_phone_number people.phone_number%type ) AS $body$
BEGIN
        insert into people(id,first_name,last_name,phone_number)
        values (i_id,i_first_name,i_last_name,i_phone_number);
        --
        dbms_output.new_line;
        dbms_output.put_line('wow! this is not the base edition anymore');
        --
    END;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

This is the expected code for the V1 version. Of course, there are some things to change to make them PostgreSQL compatible and have it running on YugabyteDB. The orafce extension is created by YugabyteDB Voyager but dbms_output should be re-written to raise notice.

The table is in schema/tables/table.sql:

CREATE TABLE people$0 (
        id integer NOT NULL,
        first_name varchar(15) NOT NULL,
        last_name varchar(20) NOT NULL,
        phone_number varchar(20),
        eye_color varchar(20),
        PRIMARY KEY (id)
) ;
ALTER TABLE people$0 ALTER COLUMN ID SET NOT NULL;
ALTER TABLE people$0 ALTER COLUMN FIRST_NAME SET NOT NULL;
ALTER TABLE people$0 ALTER COLUMN LAST_NAME SET NOT NULL;

The datatypes have been transformed to the matching PostgreSQL ones. Fo YugabyteDB, you may have to think about the sharding key. The default is HASH on the first column of the primary key, which is probably ok for an id.

The view is in schema/views/PEOPLE_view.sql:

CREATE OR REPLACE VIEW people (id, first_name, last_name, phone_number, eye_color) 
AS select ID,FIRST_NAME,LAST_NAME,PHONE_NUMBER,EYE_COLOR FROM people$0;

Export Data

The data can be exported to a pg_dump-like format:

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=DEMO_EBR \
--start-clean

I will not show the import here in this small example. The goal was to verify that the current edition was correctly exported.