Otra de las tareas que he tenido que resolver en estos últimos días ha sido Interconectar Bases de Datos ubicadas en equipos distintos. Entiéndase como equipos distintos máquinas físicamente distintas, o bien, máquinas virtuales con distintas IPs.

Los 3 casos que voy a explicar aquí son:

• Oracle –>> Oracle
• PostgreSQL –>> PostgreSQL
• Oracle –>> PostgreSQL (usando ‘odbc’ con Heterogenous services)

1. Enlace Oracle → Oracle

1.1. Instalar bases de datos Oracle

Hay que instalar 2 bases de datos en distintos servidores, para ello se crearán 2 máquinas virtuales con KVM usando para la instalación de Oracle12c el script de esta entrada.

Resumiendo todo lo que contiene el capítulo 1 hay que, configurar el ‘listener’ al menos en la máquina que actúe de servidor, en este caso ‘oraclB’, configurar el ‘tnsnames.ora’ en la máquina con rol de cliente, y por último, crear el ‘database link’ en el cliente.

1.2. Configurar listener’s

Lo siguiente es configurar el servicio ‘listener’ en cada una de las máquinas.

Este servicio es el encargado de configurar la dirección IP donde escuchará la base de datos. Aunque no es necesario hacerlo en el cliente para el cometido de esta práctica, lo haré porque voy a hacer todo el ejercicio de forma remota desde mi Pc de escritorio conectándome hacia las 2 maquinas KVM.

NOTA: He de decir que estuve configurando estas IP usando nombre DNS de máquina en lugar de 0.0.0.0 y no podía, más tarde descubrí que era problema de mi script de instalación, había que poner el FQDN o el HOSTNAME en las líneas del script:

...
GDBNAME = "$(hostname -f)"
DB_UNIQUE_NAME = "$(hostname -f)"
DB_UNIQUE_NAME = "$(hostname -f)"
INSTANCENAME = "$(hostname)"
....

De todas formas tengo que seguir depurando el script con algunos detalles.

1.2.1. orclA

oracle@oracle12cA:~$ cat /opt/oracle/product/12.1.0.2/db_home_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /opt/oracle/product/12.1.0.2/db_home_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0 )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

oracle@oracle12cA:~$

1.2.2. oraclB

oracle@oracle12cB:~$ cat /opt/oracle/product/12.1.0.2/db_home_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /opt/oracle/product/12.1.0.2/db_home_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0 )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

oracle@oracle12cB:~$

1.3. Configurar ‘tnsnames.ora’

Este fichero sí es importante porque es el que mirará ‘database link’ para buscar la IP asociada a la base de datos que le pongamos, en este caso no hay problemas en usar el nombre DNS.

Se creará la conexión de ‘orclA → oraclB’, así quedaría el fichero ‘tnsnames.ora’:

oracle@oracle12cA:~$ cat /opt/oracle/product/12.1.0.2/db_home_1/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /opt/oracle/product/12.1.0.2/db_home_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCLA =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORCLA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orclA)
    )
  )

ORACLB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12cB.alcocer.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oraclB)
    )
  )

oracle@oracle12cA:~$

1.4. Database link

1.4.1. Creación de enlaces

Fuente: Creación de enlaces

La sintaxis del comando es la que está en el siguiente diagrama.

Opciones

shared

Para crear una conexión compartida por múltiples sesiones.

public

Para que la conexión sea visible para todos los usuarios

connect_string

Aquí se puede especificar una dirección IP o un servicio configurado en ‘tnsnames.ora’.

Creando la conexión mediante ‘tnsnames’

create public database link scottremoto
connect to scott identified by tiger 
using 'ORACLB';

Creando la conexión usando una dirección IP

create public database link scottremoto
connect to scott identified by tiger 
using 'oracle12cB.alcocer.net';

1.4.2. Borrado de enlaces

Fuente: Borrado de enlaces

1.4.3. Vídeo demostración

2. Enlace PostgreSQL → PostgreSQL

2.1. Notas previas

Para este documento se usará la base de datos de una buena amiga SysAdmin Marina N. porque su script de creación de tablas e inserción de datos funciona realmente bien.

Resumiendo todo lo que voy a hacer en los siguientes puntos: voy a crear 2 clústers de PostgreSQL, voy a habilitarles que se pueda conectar a ellos de forma remota de la misma forma que se hace para poder usar el comando ‘psql’ en otro Pc y por último voy a crear una conexión desde ‘postgresA’ a ‘postgresB’ usando una librería que hay que descargar.

2.2. Instalar bases de datos PostgreSQL

Para tener el software necesario hay realizar la clásica instalación con ‘apt’.

root@postgresA:~# apt install postgresql postgresql-contrib

Nótese que además se instala el paquete ‘postgresql-contrib’ que provee el script necesario para la interconexión entre bases de datos ‘postgres’.

2.3. Configurar acceso remoto en Postgres

El acceso remoto se configura en ‘postgres’ mediante dos archivos.

Los dos puntos de este apartado, 2.3.1., 2.3.2. y 2.3.3. , hay que hacerlo tanto en ‘postgresA’ como en ‘postgresB’.

2.3.1. Establecer IP’s permitidas

root@postgresA:~# grep -Eiv '^#|^$' /etc/postgresql/9.4/main/pg_hba.conf 
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             10.0.0.0/16             md5
host    all             all             ::1/128                 md5
root@postgresA:~#

2.3.2. Establecer IP’s de escucha

No es necesario poner a escuchar en todas las interfaces pero por comodidad lo hago, esta entrada va de interconexiones, no de seguridad en las conexiones.

root@postgresA:~# grep -Eiv '^#|^$' /etc/postgresql/9.4/main/postgresql.conf | grep -Ei 'listen'
listen_addresses = '*'          # what IP address(es) to listen on;
root@postgresA:~#

2.3.3. Reiniciar postgres

Después de cambiar esos parámetros es obligatorio reiniciar el clúster.

root@postgresA:~# pg_ctlcluster 9.4 main restart
root@postgresA:~#

2.4. Cargar plugin para interconexión

Hay que acceder a la máquina desde la que se pretende hacer la interconexión y ejecutar el siguiente script.

Véase que lo hago en ‘postgresA’ y no en ‘postgresB’.

root@postgresA:~# sudo -i -u postgres psql marina < /usr/share/postgresql/9.4/extension/dblink--1.1.sql 
Use "CREATE EXTENSION dblink" to load this file.
root@postgresA:~#

2.5. Creación de la extensión

Ahora hay que crear la extensión ‘dblink’

[manuel@pris ~]$ psql -Umarina -hpostgresA -W
Password for user marina: 
psql (9.6.1, server 9.4.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

marina=# CREATE EXTENSION dblink;
CREATE EXTENSION
marina=# \dx
                                 List of installed extensions
  Name   | Version |   Schema   |                         Description                          
---------+---------+------------+--------------------------------------------------------------
 dblink  | 1.1     | public     | connect to other PostgreSQL databases from within a database
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

marina=#

2.6. Uso de ‘dblink’

Creo que viendo los comandos se puede ver que es una tarea relativamente sencilla.

2.6.1. Crear la conexión

marina=# select dblink_connect('myconex', 'dbname=marina host=postgresB.alcocer.net user=marina password=marina');
 dblink_connect 
----------------
 OK
(1 row)

marina=#

2.6.2. Hacer una consulta

marina=# select * from dblink('myconex', 'select * from usuarios') as t(nombre varchar, passwd varchar, nombre_real varchar, apellidos varchar, correo varchar, cuidad varchar);
  nombre   |     passwd     | nombre_real |     apellidos      |         correo          |     cuidad      
-----------+----------------+-------------+--------------------+-------------------------+-----------------
 Marnierod | 622376GT76     | Marina      | Navarro Lopez      | marina@gmail.com        | Sevilla(España)
 Felix     | GTHND736473    | Felix       | Fernanez Diaz      | fernandez@hotmail.org   | Sevilla(España)
 J.Mari    | 1298374736     | Jose María  | Castro Coltán      | j.m.0@hotmail.com       | Sevilla(España)
 Marta     | 7837467584     | Marta       | Prada Torrox       | marta.to@gmail.com      | Sevilla(España)
 Juanlu    | 74857HDGDH738  | Juan Luís   | Gameiro García     | juan.lu@gamil.com       | Sevilla(España)
 Aitor     | 847599485585   | Aitor       | Gomez Huya         | aitormenta@hotmail.com  | Sevilla(España)
 Diego     | 837DHD84845    | Diego       | Losada Losada      | losada@hotmail.com      | Sevilla(España)
 David     | GTHND736DSFAAA | David       | García García      | davpal@gmail.com        | Sevilla(España)
(8 rows)

marina=#

2.7. Vídeo demostrativo

3. Enlaces heterogéneos

3.1. Oracle → PostgreSQL

Fuente: Documentación seguida.

Básicamente lo único que hay que hacer es instalar el driver ‘odbc’ para ‘postgres’, configurarlo apropiadamente y después configurar los ‘servicios heterogéneos’ de ‘Oracle’ para que use la configuración del driver, teniendo en cuenta que habrá que tocar tanto el ‘listener.ora’ como el ‘tnsnames.ora’.

Por otro lado, la base de datos PostgreSQL está configurada previemente en el apartado 2.3. Configurar acceso remoto en Postgres.

3.1.1. Instalar ODBC para PostgreSQL y UNIXODBC

root@oracle12cA:~# apt install odbc-postgresql unixodbc

3.1.2. Configurar ‘/etc/odbcinst.ini’

Este fichero define los parámetros del driver, por defecto debería venir tal y como está puesto aquí debajo.

root@oracle12cA:~# cat /etc/odbcinst.ini 
[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
root@oracle12cA:~#

Comprobar los drivers

Con el siguiente comando se puede verificar la sintaxis del fichero ‘odbinst.ini’.

root@oracle12cA:~# odbcinst -q -d
[PostgreSQL ANSI]
[PostgreSQL Unicode]
root@oracle12cA:~#

3.1.3. Configurar ‘/etc/odbc.ini’

Este fichero define los parámetros de conexión a la base de datos.

Se han creado 2 tipos, uno de tipo ANSI y otro de tipo UNICODE.

Destaco la línea donde se encuentra ‘ReadOnly‘ porque es importante el valor que puede adoptar ese parámetro ya que si no se tiene en cuenta podemos permitir escribir cuando no se debe o viceversa.

root@oracle12cA:~# cat /etc/odbc.ini 
[PSQLA]
Debug           = 0
CommLog         = 0
# poner la siguiente variable a 0 si se quiere poder modificar datos remotamente
ReadOnly        = 1
Driver          = PostgreSQL ANSI
Servername      = postgresA.alcocer.net
Username        = marina
Password        = marina
Port            = 5432
Database        = marina
Trace           = 0
TraceFile       = /tmp/sql.log

[PSQLU]
Debug           = 0
CommLog         = 0
# poner la siguiente variable a 1 si no se quiere permitir modificar datos remotamente
ReadOnly        = 0
Driver          = PostgreSQL Unicode
Servername      = postgresA.alcocer.net
Username        = marina
Password        = marina
Port            = 5432
Database        = marina
Trace           = 0
TraceFile       = /tmp/sql.log

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

Comprobar parámetros

De nuevo, no está de más comprobar la sintaxis del fichero que se acaba de crear.

root@oracle12cA:~# odbcinst -q -s
[PSQLA]
[PSQLU]
[Default]
root@oracle12cA:~#

3.1.4. Comprobar conexión

Al principio se instaló ‘unixodbc’, este paquete instala el comando ‘isql’ mediante el cual se puede hacer una prueba de conexión a la base de datos configurada para ‘ODBC‘.

Se hará la prueba para los 2 tipos de conexiones.

Conexión Unicode

root@oracle12cA:~# isql -v PSQLU
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
root@oracle12cA:~#

Conexión ANSI

root@oracle12cA:~# isql -v PSQLA
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
root@oracle12cA:~#

Una vez que se ha asegurado que el driver ODBC está correctamente configurado se puede configurar Oracle.

Si hubieran problemas en alguno de los pasos previos, hay abundante documentación en Internet de cómo configurar ‘odbc-postgresql’ para Debian o la distro que se esté usando.

3.1.5. Configurar ‘heterogeneous services’

Crear el fichero ‘$ORACLE_HOME/hs/admin/initPSQLU.ora’ con el siguiente contenido:

(prestad atención a la línea del driver, ‘psqlodbcw.so‘,  debe coincidir con el driver que se usará en la conexión)

root@oracle12cA:~# cat /opt/oracle/product/12.1.0.2/db_home_1/hs/admin/initPSQLU.ora 
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
root@oracle12cA:~#

3.1.6. Configurar ‘listener’

root@oracle12cA:~# cat /opt/oracle/product/12.1.0.2/db_home_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /opt/oracle/product/12.1.0.2/db_home_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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)
      )
  )

3.1.7. Configurar ‘tnsnames.ora’

De nuevo este fichero es donde se define, en este caso particular, que se use la configuración del ODBC ‘PSLU’ (U de Unicode).

Añadir las siguientes líneas.

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

Se puede hacer un ‘ping’ para comprobar la configuración del tnsnames.

oracle@oracle12cA:~$ tnsping PSQLU

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 25-DEC-2016 12:01:42

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/12.1.0.2/db_home_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)) (CONNECT_DATA=(SID=PSQLU)) (HS=OK))
OK (0 msec)
oracle@oracle12cA:~$

3.1.8. Reiniciar o iniciar ‘listener’ (si no lo estaba)

oracle@oracle12cA:~$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 25-DEC-2016 11:07:42

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /opt/oracle/product/12.1.0.2/db_home_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/oracle/product/12.1.0.2/db_home_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/oracle12cA/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                25-DEC-2016 11:07:42
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.1.0.2/db_home_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/oracle12cA/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "PSQLU" has 1 instance(s).
  Instance "PSQLU", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
oracle@oracle12cA:~$

3.1.9. Uso

Hay que fijarse que hay que usar “” en lugar de ‘’, para los valores de remotos de la base de datos PotgreSQL, esto es debido a un tema de codificación de caráteres.

SYSTEM@oracle12cA/orclA 25-DEC-16> create public database link PG_LINK connect to "marina" identified by "marina" using 'PSQLU';

Database link created.

SYSTEM@oracle12cA/orclA 25-DEC-16> select "nombre" from "usuarios"@PG_LINK;

nombre
-------------------------------------------------------------------------------------------
Marnierod
Felix
J.Mari
Marta
Juanlu
Aitor
Diego
David

8 rows selected.

SYSTEM@oracle12cA/orclA 25-DEC-16>

Sinónimos

Es muy útil crear sinónimos para ahorrarnos el estar poniendo cadenas largas en los nombres de las tablas.

SYSTEM@oracle12cA/orclA 25-DEC-16> create synonym pg_usuarios for "usuarios"@PG_LINK;

Synonym created.

SYSTEM@oracle12cA/orclA 25-DEC-16> select "nombre" from pg_usuarios;

Marnierod
Felix
J.Mari
Marta
Juanlu
Aitor
Diego
David

8 rows selected.

SYSTEM@oracle12cA/orclA 25-DEC-16>

3.1.10. Vídeo demostrativo