Importing from Oracle Edition Based Redefinition (EBR) to YugabyteDB
Tags: yugabyted, oracle, migration, postgres
Published: 2023-04-30 16:52:39

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:
{% embed https://db-oriented.com/my-ebr-blog-post-series/ %}
The only difference is that I didn't create the V2 edition and was still in V1 when creating the editioning view.


I set the default edition to V1:
```sql

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:
{% embed https://dev.to/yugabyte/migrating-from-oracle-autonomous-database-to-yugabytedb-with-yb-voyager-3o7d %}
I have copied the Wallet to my Oracle Autonomous Database lab.

Instead of compiling from source, I installed `yb-voyager` according to the [documentation](https://docs.yugabyte.com/preview/migrate/install-yb-voyager/):

```sh
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

```sh
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
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/raplpd5zjnvam8rb6zh9.png)

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`:
```sql
# 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`:
```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`:
```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`:
```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:
```sql
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.