Gateway from Oracle to YugabyteDB

In 1988 Oracle announced SQL*Star to connect to other non-oracle databases, and query or update a remote database through database links:

Computerworld

In 2023 Oracle released the Oracle Free edition that can be deployed anywhere for free (it is actually a rename of Oracle XE with easier deployment):

Oracle Database Free | Oracle

There are, of course, huge limitations to this Free version:

• The storage is limited to 12GB on disk. For any serious database, we need more, with the possibility to offload some tables and indexes to a remote database
• The compute is limited to 2GB of RAM and 2 vCPU. However, if we offloaded all tables to a remote database, the Oracle Free instance becomes stateless and can scale to multiple containers

Fortunately, Oracle Free Edition comes with the Heterogenous Gateway and ODBC Gateway, the grandchildren of SQL*Star. PostgreSQL is an open-source database that is close to Oracle in terms of SQL features and has an easy-to-install ODBC driver. And, still open-source, YugabyteDB can use the same driver, as it is PostgreSQL-compatible, and can scale horizontally.

This can build a full cloud-native stack to run applications connected to an Oracle instance (with its proprietary syntax and PL/SQL), with data stored on the Highly Available with infinite scalability YugabyteDB, while developing new applications that query the same data through its PostgreSQL API (YSQL).

Note: I’m not saying that you can migrate any application from Oracle to PostgreSQL or YugabyteDB transparently. Complex queries will need some changes and Oracle Free Edition also comes with the SQL Translation framework to allow that. But, of course, going this way is not easy and adds some latency. The critical use cases should be rewritten for the new database, probably breaking the monolithic application into services. However, this investment is for long-term freedom: your application can run on any PostgreSQL or PostgreSQL-compatible database services.

I’ll detail this idea in the future, and you can follow me on twitter, LinkedIn, dev.to… For the moment, let me share how to build a container with this gateway. You can use this container as a gateway between any Oracle Instance and any PostgreSQL or YugabyteDB database.

You can run the code in an empty directory where you have Docker (or Podman) installed.

Dockerfile

The following Dockerfile pulls the Oracle Free 23c image, installs the PostgreSQL 15 ODBC driver to it, adds the configuration files we will see just after, and starts the Oracle Gateway listener:

cat > Dockerfile <<’DOCKERFILE’
# The Oracle ODBC Gateway is installed in Oracle Free Edition 23c
FROM container-registry.oracle.com/database/free as stage
# install PostgreSQL 15 ODBC driver
USER root
RUN dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
RUN dnf install -y postgresql15-odbc
RUN ls -l /usr/pgsql-15/lib/psqlodbc.so
# Always useful to have strace to troubleshoot
RUN dnf install -y strace 
# add ODBC config files ( because SSL Mode cannot be set by env. variable)
ADD odbc.ini /etc/odbc.ini
# add listener and Oracle Heterogenous Services configuration files
USER oracle
ADD listener.ora /opt/oracle/product/23c/dbhomeFree/network/admin
ADD init.ora /opt/oracle/product/23c/dbhomeFree/hs/admin/inityoragw.ora
# set current working directory to the logs for troubleshooting
WORKDIR /opt/oracle/product/23c/dbhomeFree/hs/log
# The connection informations are defined as environment variables
ENV PGDATABASE yugabyte
ENV PGHOST localhost
ENV PGPORT 5433
# Start the listener and show the log
CMD lsnrctl start yoragw && tail -f /opt/oracle/diag/tnslsnr/*/yoragw/trace/yoragw.log
DOCKERFILE

The listener starts an instance of the Database Gateway for ODBC, and listens on TCP port 1575 where we can connect to from a database link:

cat > listener.ora <<’LISTENER’
yoragw=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1575))))
SID_LIST_yoragw=(SID_LIST=(SID_DESC=(SID_NAME=yoragw)(ORACLE_HOME=/opt/oracle/product/23c/dbhomeFree)(PROGRAM=dg4odbc))))
LISTENER

The SID_NAME references the following parameter file that will is added to the container as init<SID>.ora in the Heterogenous Services directory, so $ORACLE_HOME/ hs/admin/inityoragw.ora :

cat > init.ora <<'INIT'
HS_DB_DOMAIN=YUGABYTEDB
HS_DB_NAME=YORA
HS_FDS_CONNECT_INFO = YUGABYTE
HS_FDS_SHAREABLE_NAME=/usr/pgsql-15/lib/psqlodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#HS_FDS_TRACE_LEVEL=DEBUG
INIT

The HS_FDS_SHAREABLE_NAME references where the ODBC driver is installed in the container, and HS_FDS_CONNECT_INFO is the ODBC name that will be defined in ODBC.INI, the other parameters are for the gateway and described in Oracle Documentation

To define the PostgreSQL endpoint of YugabyteDB to connect to, I use the environment variables, except for the SSL Mode because it seems that the psqlodbc driver doesn’t understand PGSSLMODE.

Then, I add it to the ODBC.INI:

cat > odbc.ini <<'ODBC'
[YUGABYTE]
Sslmode = allow
ODBC

Build the image

With this, I build the image that I call YORA:

docker build -t yora .

and I’m ready to run it.

Note that you can optimize the image size by removing some unused directories and files and squash the image. I reduced from 10GB to 6GB with this:

RUN rm -rf ./ctx ./bin/oracle ./assistants ./jdk ./md ./javavm ./lib/libmle.so ./oml4py ./jlib ./rdbms/utl ./perl ./python ./OPatch ./R ./sdk ./instantclient ./oui ./lib/libra.so ./lib/libosbws.so

Create a YugabyteDB database

I’ve created a free managed YugabyteDB on cloud.yugabyte.com and got the connection info:

I’m ready to use this as PGHOST, PGPORT, PGDATABASE environment variables. The SSL Mode is defined in the image (odbc.ini). You can also use an external volume for /home/oracle to put the .odbc.ini and .postgresql (for the certificate).

Start the Gateway

With the above information, I can start the container with the gateway. It will listen to port 1575 (which I can forward to my host or use in the docker network) :

docker run -d --name yora --hostname yora -p 1575:1575 -e PGHOST=eu-west-3.dcde004e-d052–48ed-aac2-ce3b7ba3f265.aws.ybdb.io -e PGDATABASE=yugabyte --network ora yora

This starts the listener and tails the logs:

Create the Database Link

To test it, I start an Oracle Free 23c database:

docker run -d --name ora  --hostname ora  --network ora container-registry.oracle.com/database/free

I connect to it with sqlplus:

docker exec -it ora sqlplus / as sysdba

I create the database link to the gateway:

drop public database link if exists yugabyte;
create public database link yugabyte 
 connect to "admin" identified by "password set when creating the YugabyteDB cluster" 
 using '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yora)(PORT=1575))(CONNECT_DATA=(SID=yoragw))(HS=OK))'
;

This is like any database link except that HS=OK defines it as a Heterogenous Service where the remote database is not Oracle.

And that’s all. Let’s test it:

select listagg("amname"||': '||"amhandler") from "pg_am"@yugabyte;

I used LISTAGG, which is an Oracle function, on columns from a YugabyteDB table (you need to quote the lowercase names because PostgreSQL default is lowercase as opposed to Oracle which is uppercase). This is the most simple demonstration that you can run some Oracle code on YugabyteDB tables.

You can check the query that is sent from the execution plan:

SQL> set linesize window
SQL> set pagesize 1000
SQL> select listagg("amname"||': '||"amhandler") from "pg_am"@yugabyte;

LISTAGG("AMNAME"||':'||"AMHANDLER")
----------------------------------------------------------------------------------------------------------------------------------------------
btree: bthandlerhash: hashhandlergist: gisthandlergin: ginhandlerbrin: brinhandlerspgist: spghandlerybgin: ybginhandlerlsm: ybcinhandler

SQL> select * from dbms_xplan.display_cursor(format=>'advanced');

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9fjc95mqjtqr7, child number 0
-------------------------------------
select listagg("amname"||': '||"amhandler") from "pg_am"@yugabyte

Plan hash value: 1467055549

------------------------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |IN-OUT|
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |   200 (100)|          |        |      |
|   1 |  SORT AGGREGATE  |       |     1 |   480 |            |          |        |      |
|   2 |   REMOTE         | pg_am |  2000 |   937K|   200   (0)| 00:00:01 | YUGAB~ | R->S |
------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / "pg_am"@"SEL$1"

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) LISTAGG("amname"||': '||"amhandler",NULL)[4000]
   2 - "amname"[VARCHAR2,189], "amhandler"[VARCHAR2,765]

Remote SQL Information (identified by operation id):
----------------------------------------------------

   2 - SELECT "amname","amhandler" FROM "pg_am" (accessing 'YUGABYTE' )

Here you can see that the Remote SQL is PostgreSQL compatible and the LISTAGG projection happened in your Oracle session.

Test remote queries

In my YugabyteDB cluster, I’ve created the tables from the tutorial:

From Oracle SQL*Plus I can query them:

column "dname" format a30
column "ename" format a30

set linesize window
set pagesize 1000

select dept."dname", emp."ename", emp."sal"
  from "dept"@yugabyte dept
     , "emp"@yugabyte emp
 where dept."deptno"=emp."deptno"
;

You may see many queries that are not translated correctly by Oracle Heterogenous Services because it is a generic ODBC one without specific knowledge of PostgreSQL syntax. For them, you need to change the query, but the PL/SQL logic around can be used as-is. In order to change the query, you can:

• change in the PL/SQL code. Note that if you use Stored Procedure and scale the stateless Oracle instance (because you use the Free Edition limited to 2 vCPU), the same DDL must be applied in all of them. Maybe you can use Application Container to synchronize it
• change in the application but remember that the goal is to move to another database with little effort. If there’s a lot to re-write, better rewrite your application to connect to YugabyteDB PostgreSQL endpoint directly
• use the SQL Translation framework for the queries that you cannot change. I presented an example in the past for the Swiss Oracle User Group, running Swingbench on PostgreSQL without re-compiling the application:

This looks great, using the many Oracle Database features that open to other databases, but… let me break your dreams: you cannot migrate an Oracle-specific application (with complex queries, PL/SQL, …) without effort. It is a long-term investment: moving to open source, popular, rich features PostgreSQL that opens all PostgreSQL-compatible databases, like YugabyteDB to scale-out.