Interconexión de Servidores de Bases de Datos
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:
|
1 2 3 4 5 6 |
... 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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’:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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’
|
1 2 3 |
create public database link scottremoto connect to scott identified by tiger using 'ORACLB'; |
Creando la conexión usando una dirección IP
|
1 2 3 |
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’.
|
1 |
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
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 |
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.
|
1 2 |
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’.
|
1 2 3 |
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’
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[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
|
1 2 3 4 5 6 7 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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’.
|
1 2 3 4 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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.
|
1 2 3 4 5 |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
root@oracle12cA:~# isql -v PSQLU +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> quit root@oracle12cA:~# |
Conexión ANSI
|
1 2 3 4 5 6 7 8 9 10 11 |
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)
|
1 2 3 4 5 6 7 |
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’
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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.
|
1 2 3 4 5 6 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
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> |
Comentarios recientes