Buenos días | tardes | noches, en esta entrada os guiaré paso a paso para interconectar nuestras bases de datos (Oracle y PostgreSQL) de las siguientes formas:

• Oracle – Oracle
• PostgreSQL – PostgreSQL
• Oracle – PostgreSQL

Nuestras bases de datos están alojadas en una máquina con distribución Debian Jessie.

Versión de Oracle: 12.1.0
Versión de PostgreSQL: 9.4

Interconexión de Oracle a Oracle

Teniendo Oracle instalado en nuestras máquinas, deberemos de configurar en una de ellas el fichero tnsnames.ora y cambiarle el nombre del servicio para cuando hagamos el DB link, nos resulte fácil identificarlo.

Por lo tanto, nos vamos a la ruta $ORACLE_HOME/app/oracle/product/12.1.0/orcl/network/admin y editamos el fichero, cómo mencionamos anteriormente, tnsnames.ora editando el valor remarcado en negritas:

Prueba =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.103)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = prueba)
    )
  )

En la misma máquina, iniciamos sesión con el usuario que tendríamos que tener creado y creamos una tabla con algunos registros:

sqlplus sys as sysdba #Iniciamos sesión
startup #Levantamos la base de datos
connect usuario; #Iniciamos sesión con el usuario que tendríamos creado

Ahora creamos la tabla con los registros:

create table dblinks
(
  codigo varchar2(9),
  Nombre varchar2(35),
  constraint pk_dblinks primary key (codigo)
);

Insertamos los registros en la tabla dblinks:

insert into dblinks values ('01','Baja');
insert into dblinks values ('02','Alta');
insert into dblinks values ('03','Especial');

En la otra máquina crearemos el enlace para conectarnos. Cómo usuario sysdba, ya que los usuarios normales no pueden crear enlaces, creamos el enlace:

create database link oracle
connect to usuario
identified by usuario
using ‘prueba’;

Ahora al hacer la siguiente consulta nos aparece la tabla con los registros que creamos;

SQL> select * from dblinks@oracle;
CODIGO NOMBRE
--------- -----------------------------------
01 Baja
02 Alta
03 Especial
SQL>

Interconexión de PostgreSQL a PostgreSQL

PostgreSQL utiliza la extensión dblink para poder conectarse o aceptar consultas desde enlaces. Por lo tanto, para poder utilizar nosotros dblink, deberemos de instalar el paquete postgresql-contrib ya que no se instala por defecto al hacer una instalación genérica.

apt update
apt install postgresql-contrib

Una vez instalado, iniciamos sesión en postgresql y ejecutamos la siguiente orden en la máquina dónde crearemos el dblink (maquina2):

create extension dblink;

Procedemos a configurar PostgreSQL en las dos máquinas, para ello editamos el fichero /etc/postgresql/9.4/main/postgresql.conf y editamos la siguiente (no es recomendable dejarlo así):

listen_addresses = '*' # what IP address(es) to listen on;

Ahora editamos el fichero /etc/postgresql/9.4/main/pg_hba.conf y editamos las lineas:

# IPv4 local connections:
host all all all md5 #Cambiamos 127.0.0.1/32 por ALL
# IPv6 local connections:
#host all all ::1/128 md5 ← La comentamos

Insertamos una tabla y registros para poder hacer pruebas de verificación de la conexión en la otra máquina (maquina1):

create table temporadas
(
 codigo varchar(9),
 Nombre varchar(35),
 constraint pk_temporadas primary key (codigo)
);
insert into temporadas values ('01','Baja');
insert into temporadas values ('02','Alta');
insert into temporadas values ('03','Especial');

Procedemos a crear el enlace de base de datos para consultas remotas. Primero crearemos un “servidor” con los datos necesarios, en este caso, la IP remota y la BD a la que accederemos:

CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (hostaddr '192.168.1.117', dbname 'postgres');

Creamos un mapeado de usuario para el usuario postgres:

CREATE USER MAPPING FOR postgres SERVER fdtest OPTIONS (user 'postgres');

Damos permisos de uso del mismo lenguaje en el servidor al usuario postgres:

GRANT USAGE ON FOREIGN SERVER fdtest TO postgres;

Otorgamos permisos de consulta SELECT en la tabla anteriormente creada al usuario postgres:

GRANT SELECT ON TABLE temporadas TO postgres;

Comprobamos la conexión y de paso damos un nombre a la conexión:

SELECT dblink_connect('fdtest', 'password=');
postgres=# SELECT dblink_connect('fdtest', 'password=');
dblink_connect
----------------
OK
(1 fila)

Hacemos una consulta con dblink:

postgres=# SELECT * FROM dblink('fdtest','SELECT codigo,nombre FROM temporadas') as t(codigo int, nombre text);
codigo | nombre
--------+----------
1 | Baja
2 | Alta
3 | Especial
(3 filas)

Si queremos deshacer la configuración anteriormente descrita, realizamos en orden las siguientes instrucciones:

REVOKE USAGE ON FOREIGN SERVER fdtest FROM postgres;
REVOKE SELECT ON TABLE temporadas FROM postgres;
DROP USER MAPPING FOR postgres SERVER fdtest;
DROP SERVER fdtest;

Si queremos desconectarnos del dblink, ejecutamos:

select dblink_disconnect(‘<nombre de la conexión>’)
postgres=# select dblink_disconnect('fdtest');
dblink_disconnect
-------------------
OK
(1 fila)

Interconexión de Oracle a PostgreSQL con Heterogeneus Service

En esta parte haremos consultas desde Oracle a BD’s de PostgreSQLcon ayuda del driver ODBC para PostgreSQL con servicios heterogéneos implementados en Oracle.

Configuración de PostgreSQL

Dónde tenemos PostgreSQL instalado, creamos un usuario y una BD asignada a dicho usuario con todos los permisos (no es seguro hacer esto, pero está realizado para hacer esta práctica):

Psql
create user usuario password ‘usuario’;
create database oracle with owner usuario;
grant all privileges on database oracle to usuario;
postgres=# create user usuario password 'usuario';
CREATE ROLE
postgres=# create database oracle with owner usuario;
CREATE DATABASE
postgres=# grant all privileges on database oracle to usuario;
GRANT

Nos conectamos con dicho usuario en la BD anteriormente creada y asignada a él:

psql -U usuario -h localhost -d oracle

Creamos una tabla y le metemos registros:

create table temporadas
(
 codigo varchar(9),
 Nombre varchar(35),
 constraint pk_temporadas primary key (codigo)
);
insert into temporadas values ('01','Baja');
insert into temporadas values ('02','Alta');
insert into temporadas values ('03','Especial');

Configuración de PostgreSQL, Oracle y creación del DB link

Procedemos a configurar PostgreSQL para que admita la conexión remota, para ello editamos el fichero /etc/postgresql/9.4/main/postgresql.conf y editamos la siguiente linea para que admita conexiones de cualquier ip/host (no es recomendable dejarlo así):

listen_addresses = '*' # what IP address(es) to listen on;

Ahora editamos el fichero /etc/postgresql/9.4/main/pg_hba.conf y editamos las lineas:

# IPv4 local connections:
host all all all md5 #Cambiamos 127.0.0.1/32 por ALL
# IPv6 local connections:
#host all all ::1/128 md5 ← La comentamos

Procedemos a la configuración y creación del dblink
Instalamos odbc-postgresql y unixodbc dónde tenemos Oracle instalado:

apt update
apt install odnc-postgresql unixodbc

Configuramos /etc/odbcinst.ini (verificamos los parámetros por defecto):

[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

Comprobamos que el contenido del fichero anteriormente verificado es correcto:

odbcinst -q -d

Configuramos el fichero /etc/odbc.ini ya que servirá para realizar la conexión a la BD remota:

[PSQLA]
Debug = 0
CommLog = 0
ReadOnly = 1
Driver = PostgreSQL ANSI
Servername = <ip a la que nos conectaremos>
Username = <usuario al que usaremos para conectarnos>
Password = <contraseña de dicho usuario>
Port = 5432
Database = <BD remota a la que nos conectaremos>
Trace = 0
TraceFile = /tmp/sql.log

[PSQLU]
Debug = 0
CommLog = 0
ReadOnly = 0
Driver = PostgreSQL Unicode
Servername = <ip a la que nos conectaremos>
Username = <usuario al que usaremos para conectarnos>
Password = <contraseña de dicho usuario>
Port = 5432
Database = <BD remota a la que nos conectaremos>
Trace = 0
TraceFile = /tmp/sql.log

[Default]
Driver = /usr/lib/x86_64-linux-gnu/odbc/liboplodbcS.so #DRIVER a utilizar

Comprobamos la conexión con el comando isql que trae el paquete unixodbc que previamente instalamos:

isql -v PSQLU
isql -v PSQLA
oracle@oracle2:~/home/oracle$ isql -v PSQLU
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |       
+---------------------------------------+

Creamos el fichero initPSQLU.ora en $ORACLE_HOME/hs/admin con el siguiente contenido:

HS_FDS_CONNECT_INFO = PSQLU
HS_FDS_TRACE_LEVEL = ON
HS_FDS_SHAREABLE_NAME = /usr/lib/x86_64-linux-gnu/odbc/psqlodbcw.so
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
set ODBCINI=/etc/odbc.ini

Editamos el listener para que contenga el siguiente contenido:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0 )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=PSQLU)
      (ORACLE_HOME=/opt/oracle/product/12.1.0.2/db_home_1)
      (PROGRAM=dg4odbc)
    )
  )

Modificamos tnsnames.ora y añadimos las siguientes lineas:

PSQLU =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
    (CONNECT_DATA=(SID=PSQLU))
    (HS=OK)
  )

Reiniciamos el listener:

lsnrctl stop
lsnrctl start

Aunque al iniciar el listener nos diga lo siguiente:

…
Service "PSQLU" has 1 instance(s).
Instance "PSQLU", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

No preocuparse, se ha iniciado correctamente.

Creamos el dblink:

create public database link PG_LINK connect to "usuario" identified by "usuario" using 'PSQLU';

Realizamos una consulta sencilla para verificar que se ha creado y configurado correctamente los pasos realizados:

SQL> select "nombre" from "temporadas"@PG_LINK;
nombre
--------------------------------------------------------------------------------
Baja
Alta
Especial
SQL>

 

Eso es todo! Si teneis alguna duda, preguntadme en los comentarios!

Nos vemos en la próxima entrada.

Saludos!