Oracle Heterogeneous Services
An example configuring Oracle ODBC gateway to PostgreSQL
That sounds futuristic, like a 2025 post-microservices trend. Monolithic databases are definitely dismantled, we developed in microservices, spread data to many databases. Because we had agile development teams, they have chosen the database technology that fits their need: cheap, easy, free from any infrastructure constraints, and of course cool enough for their CV. The enterprise information system is now a zoo with all species represented.
Then, the business needs to query and operate on data that has been produced in many different sources, heterogeneous systems, and polyglot data structures. Of course, all was sourced, streamed and replicated, with an approximate consistency and latency.
But after all that fun, the data analysts need a consistent and integrated view of this data to meet their business objectives. They need to process it in one system only. They need to map it and query transparently, without having to learn different languages and structures. They may even want to enrich it, updating the multiple systems, in a transaction that stays consistent even in case of failure.
Actually, this is not new. The screenshot above is from 1995 when Oracle introduced their Oracle Gateway to connect to other databases like if they were Oracle ones. We can run DML, procedures, and transactions over Database Links. It has evolved with gateways to specific database systems, but the gateway to ODBC databases is still there and free. And Heterogenous Services are available even in Standard Edition.
Today, whether you want to offload some tables to a different DB, or you already have multiple DBMS that you want to query from a single service, you will have to run queries and transactions across different systems. Here is an example where I’ll connect Oracle DB Links to a PostgreSQL database.
In this example, I prefix the name with the type, like: usrdemo for the user, dsndemo for the data source name, lsndemo for the listener… That’s not something I do usually but it helps to understand clearly the link of components. There is a double network communication for each call here: from the database Heterogenous Service to the Gateway listener, and the listener calls the remote database through ODBC.
1. Install a PostgreSQL database
For this demo, I have setup an Oracle DBaaS in the Oracle Cloud Infrastructure which is a PaaS (the database is installed automatically and ready to use) but with IaaS access (I can connect as root and install whatever I need).
I chose a 19c Standard Edition (19c has just been made available this week on this service) running on Oracle Linux 7.5 which is the same as RHEL7.
There, I have an Oracle Database 19c already running on Oracle Enterprise Linux 7 and I install PostgreSQL 11 to demo the gateway.
sudo rpm -Uvh https://yum.postgresql.org/11/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql11-server
I create a database and initialize it with pgbench.
sudo mkdir -p /u01/pgdata && sudo chown -R postgres /u01/pgdata
sudo su postgres
export PGDATA=/u01/pgdata
# create the database
/usr/pgsql-11/bin/initdb -D $PGDATA
# I prefer password authentication:
sed -i '/^host *all/s/trust/md5/' /u01/pgdata/pg_hba.conf
# start the server
/usr/pgsql-11/bin/pg_ctl -D $PGDATA -l /u01/pgdata/logfile start
# create user and database
/usr/pgsql-11/bin/psql
create user usrdemo password 'pwddemo';
create database demo;
\q
whoami | grep postgres && exit
sudo su - oraclePGPASSWORD=pwddemo /usr/pgsql-11/bin/pgbench -i -h localhost -p 5432 -U usrdemo demo
Now I have a ‘demo’ database with pgbench tables, accessible from my oracle user through TCP/IP port 5432 with user ‘usrdemo’ password ‘pwddemo’
2. Install Linux ODBC and Postgres ODBC
The free Oracle Gateway connects to any database through ODBC. I install the ODBC driver for PostgreSQL
sudo yum install -y postgresql11-odbc
ls /usr/pgsql-11/lib/psqlodbc.so
I define my PostgreSQL database as the ODBC Data Source:
cat > /home/oracle/odbcDG4PGDEMO.ini <<END
[DSNPGDEMO]
Database = demo
Description = Connection to PostgreSQL
Driver = PostgreSQL
Servername = localhost
Port = 5432
Username = usrdemo
END
You can use the default location like /etc/odbc.ini or $HOME/.odbc.ini but here I show a specific location just to mention it.
3. Install Oracle Gateway
I download the Heterogeneous Services Database Gateways to non-Oracle Databases. On the Oracle Database Software Downloads, choose the “See All” link:
From there I download the LINUX.X64_193000_gateways.zip (that must be done manually, clicking the “I accept the license terms” checkbox).
I install it in my Oracle Home (ORACLE_HOME must be set):
unzip LINUX.X64_193000_gateways.zip
cd gateways
./runInstaller -silent
sudo $ORACLE_HOME/root.sh
I have now some additional binaries and an HS (Heterogeneous Services) product directory:
4. Setup Oracle Gateway to ODBC
In this directory I define the Gateway configuration which references the ODBC driver and DSN:
cat > $ORACLE_HOME/hs/admin/initDG4PGDEMO.ora <<END
set ODBCINI=/home/oracle/odbcDG4PGDEMO.ini
HS_FDS_CONNECT_INFO = DSNPGDEMO
HS_FDS_SHAREABLE_NAME=/usr/pgsql-11/lib/psqlodbc.so
HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
#HS_FDS_TRACE_LEVEL=ON
END
The “set ODBCINI” must be used if we don’t use the default /etc/odbc.init or $HOME/.odbc.ini
The trace can dump more information to the log subdirectory in case of an error is encountered.
This Database Gateway has a System IDentifier, that will be referenced by the listener. The exposed database name is HO.WORLD by default, which is very ugly, but you can change that with HS_DB_NAME and HS_DB_DOMAIN.
5. Setup Oracle Gateway listener
The name is referenced with SID_NAME from a listener:
cat >> $ORACLE_HOME/network/admin/listener.ora <<END
LSNPGDEMO=(DESCRIPTION_LIST=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1520))))
SID_LIST_LSNPGDEMO=(SID_LIST=(SID_DESC=(SID_NAME=DG4PGDEMO)(ORACLE_HOME=$ORACLE_HOME)(PROGRAM=dg4odbc))))
END
I start this listener:
lsnrctl <<END
set current_listener LSNPGDEMO
start
set displaymode raw
services
END
The listener is the Gateway that will be contacted by the Heterogenous Services.
6. Setup Heterogenous Services
I define a tnsnames.ora entry to connection to this listener:
cat >> $ORACLE_HOME/network/admin/tnsnames.ora <<END
TNSPGDEMO=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1520))(CONNECT_DATA=(SID=DG4PGDEMO))(HS=OK))
END
I can tnsping but not connect because this is an Heterogenous Service agent
7. Create Heterogenous Services database link
All components are there and ready for heterogeneous queries and transactions. I have a pluggable database PDB1 and create a demo user there.
echo -e 'alter session set container=PDB1;\ngrant dba to demo identified by "Ora4c!eC!0ud" container=current;' | sqlplus / as sysdba
Note that I’ll set global_names to false here because I’ve not defined a name and domain for my gateway. Then it is called HO.WORLD and I don’t want to have my database link names starting with “HO.WORLD”. And if I don’t, with the default global_names, I’ll get something like: “ORA-02085: database link DBLPGDEMO.SUB11131414120.ACEDVCN.ORACLEVCN.COM”. To avoid this, either I set HS_DB_NAME and HS_DB_DOMAIN or I forget about global name enforcement.
7a. dictionary translation
sqlplus demo/'Ora4c!eC!0ud'@//localhost:1521/PDB1
alter system set global_names=false;
drop database link DBLPGDEMO;
create database link DBLPGDEMO connect to "usrdemo" identified by "pwddemo" using 'TNSPGDEMO';
desc "pgbench_accounts"@DBLPGDEMO
That’s the first gem from Oracle transparent gateway: you see the metadata as if it were an Oracle database. The PostgreSQL tables, columns and datatypes were converted to Oracle ones.
7b. distributed queries
The second gem is about the optimizer. You can run joins between two remote queries and they will be processed on the right site:
explain plan for
select count(*) from "pgbench_branches"@DBLPGDEMO
join "pgbench_accounts"@DBLPGDEMO using ("bid")
where "abalance"=0;
set pagesize 5000 linesize 150
select * from dbms_xplan.display();
Cardinality estimation is not as accurate as for local tables, but the query that is pushed will be optimized by the remote optimizer.
7c. native passthrough
The third gem is the possibility to run any native query with the DBMS_HS_PASSTHROUGH which is very similar to DBMS_SQL:
set serveroutput on
declare
val number;
c number;
n number;
begin
c:=dbms_hs_passthrough.open_cursor@DBLPGDEMO;
dbms_hs_passthrough.parse@DBLPGDEMO(c,q'[
select count(*) from "pgbench_branches"
join "pgbench_accounts" using ("bid")
where "abalance"=0;
]');
loop
n := dbms_hs_passthrough.fetch_row@DBLPGDEMO(c);
exit when n = 0;
dbms_hs_passthrough.get_value@DBLPGDEMO(c,1,val);
dbms_output.put_line(val);
end loop;
dbms_hs_passthrough.close_cursor@DBLPGDEMO(c);
end;
/
You wonder how this DBMS_HS_PASSTHROUGH is called with a database link as of it was on the remote database, but you know that it is not defined there? It is actually interpreted by the Gateway, more info about this magic:
8. Troubleshoot
There are limitation, some cryptic error messages but enabling the trace with HS_FDS_TRACE_LEVEL=ON creates a file in ?/hs/log/<pid>.trc
Oracle Corporation --- 2019-07-09 21:39:02.167558000Heterogeneous Agent Release
19.0.0.0.0Oracle Corporation --- 2019-07-09 21:39:02.167304000Version 19.0.0.0.0HOSGIP for "HS_FDS_TRACE_LEVEL" returned "ON"
HOCXU_VC2_MAX=4000
HOCXU_RAW_MAX=2000
HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/pgsql-11/lib/psqlodbc.so"
HOSGIP for "HS_OPEN_CURSORS" returned "50"
HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
HOSGIP for "HS_NLS_NUMERIC_CHARACTERS" returned ".,"
HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
HOSGIP for "HS_FDS_TRUNC_ANSI_DATE" returned "OFF"
HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
using postgres as default schema
HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
SQL text from hgodtab, id=0, len=44 ...
00: 73656C65 6374202A 2066726F 6D202270 [select * from "p]
10: 6762656E 63685F61 63636F75 6E747322 [gbench_accounts"]
20: 20776865 72652031 203D2030 [ where 1 = 0]
SQL text from hgopars, id=1, len=42 ...
00: 53454C45 43542043 4F554E54 282A2920 [SELECT COUNT(*) ]
10: 46524F4D 20227067 62656E63 685F6163 [FROM "pgbench_ac]
20: 636F756E 74732220 4131 [counts" A1]
Deferred open until first fetch.
Performing delayed open.
HOA 07/09 21:39:02.235762000: (horcrces_CleanupExtprocSession) Entered!
HOA 07/09 21:39:02.235786000: (horcrpooe_PopOciEnv) Entered!
HOA 07/09 21:39:02.235797000: (horcrfoe_FreeOciEnv) Entered!
HOA 07/09 21:39:02.235807000: (horcrfoe_FreeOciEnv) Exiting...
HOA 07/09 21:39:02.235816000: (horcrfse_FreeStackElt) Entered!
HOA 07/09 21:39:02.235825000: (horcrfse_FreeStackElt) Exiting...
HOA 07/09 21:39:02.235835000: (horcrpooe_PopOciEnv) Exiting...
HOA 07/09 21:39:02.235844000: (horcrces_CleanupExtprocSession) Exiting...
At least we have the default values for the non-specified parameters.
Finally, a little note about the architecture: the Gateway with the ODBC driver and the heterogeneous service listener can be installed on the Oracle side, the remote database side, or in the middle (why not a Docker container if it makes it more appealing?) as it communicates by TCP/IP and is not subject to licensing. Like a streaming replication server but with redundancy, no latency, and transparent real-time query.