One of our databases was migrated from Oracle to Postgresql, mainly for a costs issue. Weeks later the database was migrated we need to make a report joinning data from one of our Oracle databases and this postgresql. We found a few ways to make it, but we finally decided to use Oracle Database Gateway.
For keep the same version of the Oracle database I decided to use 12c version, but you can use the 18 or 19 version if you want.
ou can install Oracle Database Gateway in either of the following configurations:
- On the same computer as an existing Oracle database but in a different Oracle home.
- On a system with no Oracle database.
- On the same computer as the Oracle database, and in the same Oracle home directory. Note that in this case, the Oracle database and the gateway must be at the same release level.
Now we are going to install in the same server where the database is but in a different Oracle Home.
ORACLE_BASE = /u01/app/oracle ORACLE_HOME = /u01/app/oracle/product/gateway/12.2.0/dbhome_1
Uncompress the software and execute the installer. The installer is the same that you already know for another Oracle products, so it’s really easy to install. There are no special questions to answer in it. I only have to say, the installer will create a new listener… I won’t use this listener and we’ll remove it later.
For the driver installation we’ll use the driver packed with redhat.
yum install postgresql-odbc unixODBC unixODBC-devel
Verify that the PostgreSQL driver has installed correctly
[root@lab29 ~]# odbcinst -q -d [PostgreSQL] [root@labo1234 ~]#
Check the current the odbc driver configuration
[root@lab29 ~]# grep -v ^# /etc/odbcinst.ini [PostgreSQL] Description = ODBC for PostgreSQL Driver = /usr/lib64/psqlodbcw.so Setup = /usr/lib64/libodbcpsqlS.so FileUsage = 1 [root@lab29 ~]#
Create the DSN
[root@lab29 ~]# cat /etc/odbc.ini [TSMPGSQL] Driver = PostgreSQL Description = PostgreSQL Data Source Servername = 10.20.11.4 Port = 5432 UserName = my_postgresql_username Password = my_postgresql_password Database = my_database_name ReadOnly = no ServerType = Postgres ConnSettings = UseServerSidePrepare=1 ByteaAsLongVarBinary=1 Optimizer=0 Ksqo=0 TraceFile = /var/log/odbc-TSMPGSQL-trace.log Debug = No DebugFile = /var/log/odbc-TSMPGSQL-debug.log [root@lab29 ~]#
Make a odbc connection test
[root@lab29 ~]# isql TSMPGSQL +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
Ok, the odbc is ready. Let’s go now with the Oracle Gateway.
Create the init file (remember, this ORACLE_HOME is from the gateway, not your database)
[@lab29 ~]$ cd $ORACLE_HOME/hs/admin [@lab29 admin]$ cp -a initdg4odbc.ora initPSQLNTE.ora
Set the initialization parameters
[@lab29 admin]$ pwd /u01/app/oracle/product/gateway/12.2.0/dbhome_1/hs/admin [@lab29 admin]$ grep -v ^# initPSQLNTE.ora HS_FDS_CONNECT_INFO = TSMPGSQL HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so HS_FDS_TRACE_LEVEL = 255 HS_FDS_TRACE_FILE_NAME = /tmp/odbc_trace.trc HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1 set ODBCINI=/etc/odbc.ini [@lab29 admin]$
Remove the new created listener… in this case my database installation is an Oracle Restart, so, I will use srvctl.
[@lab29 ~]$ srvctl remove listener -l LISTENER_GATEWAY
Add this to our current runing listener.ora (in the grid home in this example)
SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (SID_NAME=PSQLNTE) (ORACLE_HOME=/u01/app/oracle/product/gateway/12.2.0/dbhome_1) (PROGRAM=dg4odbc) (ENV="LD_LIBRARY_PATH=/usr/lib64:/u01/app/oracle/product/gateway/12.2.0/dbhome_1/lib") ) )
Add this to the tnsnames.ora (database home)
PSQLNTE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lab29)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PSQLNTE) ) (HS = OK) )
Restart the listener…. A reload must me enough but I prefer make a restart.
[@lab29 ~]$ srvctl stop listener -listener LISTENER [@lab29 ~]$ srvctl start listener -listener LISTENER
Now, in the database create our test dblink
SQL> create public database link DBTEST connect to "my_postgresql_username" identified by "my_postgresql_password" using 'PSQLNTE'; Database link created. SQL>
And try to make a select though the dblink
SQL> select count(*) from "te3_data"@DBTEST; COUNT(*) ---------- 6951048 SQL>
…. and done!