Hoe maak je data van PostgreSQL beschikbaar in Oracle

Hoe maak je data van PostgreSQL beschikbaar in Oracle.

In de huidige organisatie is het niet meer dat een database synoniem staat met Oracle MS SQL Server, MySQL of PostgreSQL. Bijna altijd zijn alle voorgenoemde RDBMS-en aanwezig.  Daarnaast zal vaak ook een NoSQL oplossing als MongoDB, Couchbase, Cassandra of Datastax aanwezig zijn. Doordat de onmisbare data van een organisatie nu over verschillende bronnen is verdeeld, wordt het ook steeds belangrijker data tussen verschillende bronnen toegankelijk te maken.  In dit artikel beschrijven we hoe je in een Oracle database data die in een PostgreSQL database is opgeslagen kan benaderen.  Hierbij wordt gekeken naar de basisconfiguratie. Allerlei geavanceerde configuratiemogelijkheden worden achterwege gelaten.

In de onderstaande voorbeelden wordt uitgegaan van een Oracle 19.8 database en PostgreSQL 13.2 distributie, beiden op een Linux 7.9 omgeving. Om een connectie vanuit Oracle met andere data sources te maken kent Oracle de ‘Heterogeneous Connectivity’.  Hier worden twee varianten onderscheiden:

  • Oracle Database Gateways
    Een apart te licenseren product dat een volledige gateway voor een specifiek RDBMS biedt
  • Gateway for ODBC Agent
    Met deze Agent kan toegang verkregen worden tot allerlei datasources zolang er maar een ODBC-connectie gelegd kan worden. Dit is een licentievrije interface.

    De globale werking van deze wordt met de onderstaande grafische representatie geeft redelijk de conceptuele werking van de ODBC gateway weer. Uitgebreide beschrijving kan gevonden worden in de documentatie van Database Heterogeneous Connectivity

Om de connectie mogelijk te maken moeten er diverse stappen ondernomen worden.
1. Op de Oracle database server moeten de odbc drivers en de odbc driver manager geïnstalleerd worden:

yum install unixODBC
yum install postgresql13-odbc

2. Vervolgens moeten de files etc/odbcinst.ini en /etc/odbc.ini aangemaakt en mogelijk aangepast worden.  In mijn geval zien die er als volgt uit.

# /etc/odbcinst.ini
# driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[pgodbc]
Description     = ODBC for PostgreSQL
Driver          = /usr/pgsql-13/lib/psqlodbcw.so
Setup           = /usr/lib/libodbcpsqlS.so
Driver64        = /usr/pgsql-13/lib/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1

Hier is het belangrijk te beseffen dat de Driver en Driver64 entries correct moeten staan.

# /etc/odbc.ini
[pgdsn]
Driver = pgodbc
Description = PostgreSQL ODBC Driver
Database = postgres
Servername = 192.168.56.121
Username = demo_pg
Password = demo_pg
Port = 5432
UseDeclareFetch = 1
LowerCaseIdentifier = 1
ConnSettings    =SET CLIENT_ENCODING to 'UNICODE'

De schuin en onderstreepte entries zijn hier de connectie gegevens van de PostgreSQL database, host, postnummer, databasenaam en username met wachtwoord zijn de essentiële gegeven.

3.

  1. Aanmaken init.ora voor de Hetergeouse service.
    In de directory $ORACLE_HOME/hs/admin moet een init.ora aangemaakt worden (bijvoorbeeld initpg.ora).  Bij mij ziet die er uit als:
# $ORACLE_HOME/hs/admin/initpg.ora
# HS init parameters
#
HS_FDS_CONNECT_INFO = pgdsn
HS_FDS_TRACE_LEVEL = NONE
HS_FDS_SHAREABLE_NAME=/usr/pgsql-13/lib/psqlodbc.so
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
HS_NLS_NCHAR = UCS2

#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini

In deze file verwijst de HS_FDS_CONNECT_INFO naar de DSN  zoals deze in de odbc.ini gedefinieerd is.  De settings HS_LANGUAGE en HS_NLS_NCHAR zijn in dit gevel specifiek voor de gebruikte character sets van de beide databases. Op het moment dat er andere character sets worden gebruikt zal in dat specifieke geval gekeken moeten worden wat de juiste setting is.  Hij een verkeerde setting levert dit een lastig te interpreteren foutmelding op

4. Aanpassen listener.ora en tnsnames.ora
Om de connectie tussen de database, HS agent en de target database te kunen maken moeten er een aantal aanpassingen aan de bestaande listener.ora en tnsnames.ora gedaan gemaakt worden.
Voor de tnsnames.ora een entry voor de target database waarbij de SID overeen moet komen met de SID naam uit de gecreeerde init.ora uit stap 3

pg =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))  
     (CONNECT_DATA =
            (SID = pg)
      )
     (HS=OK)
  )

In de listener.ora moet de SID_LIST aangepast worden:

SID_LIST_LISTENER =(SID_LIST =
  ...
  (SID_DESC =
     (SID_NAME = pg)
     (ORACLE_HOME = /u01/app/oracle/product/19)
     (ENVS="LD_LIBRARY_PATH=/usr/local/lib:/usr/lib64:/u01/app/oracle/product/19/lib")
     (PROGRAM = dg4odbc)
  )
  ...
)

Aan de SID list van de listener wordt een entry voor de betreffende database met als program de ‘dg4odbc’; de database gateway for ODBC ingesteld.

5. In de postgres database moet een gebruiker zijn, in mijn geval is dat demo_pg zoals in stap 2 te zien is.  En er moet aan de postgreSQL kant voor gezorgd worden dat er connecties van buiten mogelijk zijn. Dit betreft configuratie van de postgres.conf en pg_hba.conf files

6. Door nu in de Oracle database een database link aan te maken:

drop database link my_pg_link;
create database link my_pg_link 
      connect to "demo_pg" identified by "demo_pg" using 'pg';

Hier is pg de entry zoals deze als alias in de tnsnames.ora is toegevoegd.

7. Nu kan er SQL uitgevoerd worden:

SQL> select * from "athletes"@test where rownum < 2;
 id   since     firstname        lastname       g birthdate
----- --------- --------- ---------------- ------ --------– 
49314 09-APR-94 Julia            de Vries       W 18-JUL-85

Op deze manier is er vanuit de Oracle zijde eenvoudig data te benaderen zoals deze in een PostgreSQL database staat. Opvallend detail is de ‘’ in het select statement.  Doordat Oracle standaard de table en column identifiers naar uppercase vertaald is en PostgreSQL dit niet doet, is het noodzakelijk deze tussen quotes te plaatsen.

Met een eenmalige inspanning om de configuratie te maken is hiermee een permanente link tussen de data in de PostgreSQL omgeving en de Oracle omgeving gemaakt. Hiermee is bijvoorbeeld rapporteren eenvoudiger.
Het is van belang te beseffen dat er hier sprake is van een standaard configuratie, afhankelijk van instellingen en behoeften kan het noodzakelijk zijn eea nog aan te passen. Nu de connectie vanuit Oracle naar PostgreSQL gemaakt is ligt de vraag voor de omgekeerde route voor de hand.

Hopenlijk heb je wat gehad aan dit artikel over Hoe maak je data PostgreSQL beschikbaar in Oracle. Mocht je hulp nodig hebben of je hebt vragen n.a.v. dit artikel, laat het ons dan weten.

https://nl.wikipedia.org/wiki/PostgreSQL

Scroll to Top