Interconexiones de servidores

2022, Nov 08

En esta entrada vamos a configurar servicios gestores de bases de datos para realizar interconexiones entre ellos. Es una práctica habitual en entornos de producción, donde se suele tener un servidor de bases de datos principal y otros servidores de bases de datos secundarios, que se conectan al servidor principal para realizar consultas y actualizaciones de datos.

Decir que para realizar esta documentación las versiones de software que se han utilizado son las siguientes:

Debian 11
Oracle 19c
PostgreSQL 13

Interconexión entre dos servidores con Oracle 19c

oracle

En esta primera parte vamos a realizar una interconexión entre dos servidores con Oracle 19c. Para ello vamos a utilizar el servicio de bases de datos Oracle 19c que tenemos instalado y vamos a crear en cada máquina un usuario, maria1 la máquina oracle1 y maria2 en la máquina oracle2.

Lo primero que debemos averiguar es la IP de cada una de nuestras máquinas, en mi caso son:

oracle1: 192.168.122.189
---
oracle2: 192.168.122.179

A continuación, vamos conectarnos con el usuario administrador de oracle en cada una de las máquinas y vamos a crear los usuarios que vamos a utilizar para la interconexión.

# oracle1
$ sqlplus / as sysdba
SQL> create user maria1 identified by admin;
SQL> grant connect, resource to maria1;
SQL> unlimit tablespace to maria1;
SQL> exit

# oracle2
$ sqlplus / as sysdba
SQL> create user maria2 identified by admin;
SQL> grant connect, resource to maria2;
SQL> unlimit tablespace to maria2;
SQL> exit

Ya creados los usuarios, también deberemos comprobar que el servicio de escucha de la base de datos está activo en cada una de las máquinas. Para ello, en cada una de las máquinas, ejecutamos el siguiente comando:

$ lsnrctl status

Para asegurarnos de que el servicio está activo, debemos ver la siguiente salida:

tnsping <ip>

En el caso de que nos aparezca desactivado, lo activamos con el siguiente comando:

$ lsnrctl start

Enrutamos los servidores

En este paso, vamos a añadirle una entrada a la tabla de enrutamiento de cada una de las máquinas, para que sepan cómo llegar al otro servidor. Para ello, en cada una de las máquinas, modificaremos el fichero tnsnames.ora, que se encuentra en la ruta $ORACLE_HOME/network/admin/.

# oracle1
ORACLE2 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.122.179)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ORCLCDB)
        )
    )

# oracle2
ORACLE1 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.122.189)(PORT = 1521))
        (CONNECT_DATA =
            (SERVER = DEDICATED)
            (SERVICE_NAME = ORCLCDB)
        )
    )

Creamos la base de datos

Ahora vamos a crear una base de datos en cada una de las máquinas. Para las comprobaciones he optado por el esquema SCOTT, que podéis encontrar aquí. La única diferencia, es que el servidor oracle2 no tendrá la tabla empleados, ya que la vamos a crear en el servidor oracle1.

Creamos los enlaces

Ambos servidores ya tienen la base de datos creada, ahora vamos a crear los enlaces entre ellos. Para ello, en la máquina oracle1, vamos a crear el enlace con el usuario maria1 y en la máquina oracle2, vamos a crear el enlace con el usuario maria2.

# oracle1
$ sqlplus maria1/admin
SQL> CREATE DATABASE LINK interconexion2 CONNECT TO maria2 IDENTIFIED BY admin USING 'ORACLE2';
SQL> exit

# oracle2
$ sqlplus maria2/admin
SQL> CREATE DATABASE LINK interconexion1 CONNECT TO maria1 IDENTIFIED BY admin USING 'ORACLE1';
SQL> exit

Comprobamos la conexión

Ahora vamos a comprobar que la conexión entre los dos servidores funciona correctamente. Para ello, vamos a realizar una consulta en la máquina oracle2, que es la que no tiene la tabla empleados, y vamos a ver si podemos acceder a ella.

# oracle1
$ sqlplus maria1/admin
SQL> SELECT * FROM dept@interconexion2 WHERE dname = 'SALES';
SQL> exit

# oracle2
$ sqlplus maria2/admin
SQL> SELECT * FROM emp@interconexion1 WHERE deptno = 20;
SQL> exit

Interconexión entre dos servidores con postgresql

postgresql

Ahora le toca el turno a postgresql. Para realizar la interconexión entre dos servidores, vamos a utilizar el mismo esquema que en el caso de oracle, es decir, vamos a crear una base de datos en cada uno de los servidores y vamos a crear un enlace entre ellos.

Lo primero que debemos averiguar es la IP de cada una de nuestras máquinas, en mi caso son:

maria: 192.168.122.167
---
oracle: 192.168.122.189

Creamos la base de datos

En este caso voy a utilizar el esquema scott de postgresql, que podéis encontrar aquí, para de esa forma hacer las mismas consultas que en el caso de oracle.

Creamos los usuarios

A continuación, vamos conectarnos con el usuario administrador de postgres en cada una de las máquinas y vamos a crear los usuarios que vamos a utilizar para la interconexión.

# maria
$ sudo su - postgres psql
postgres=# CREATE USER maria2 WITH PASSWORD 'admin';
postgres=# GRANT CONNECT ON DATABASE scott2 TO maria2;
postgres=# GRANT USAGE ON SCHEMA public TO maria2;
postgres=# GRANT;
EXIT;

# oracle
$ sudo su - postgres psql
postgres=# CREATE USER maria1 WITH PASSWORD 'admin';
postgres=# GRANT CONNECT ON DATABASE scott TO maria1;
postgres=# GRANT USAGE ON SCHEMA public TO maria1;
postgres=# GRANT;
EXIT;

Creamos los enlaces

Antes de realizar los enlaces entre las dos máquinas, debemos de cerciorarnos de que el puerto 5432 está abierto en las dos máquinas. Para ello, vamos a comprobar con elsiguiente comando:

netstat -tulpn | grep 5432

Si no está abierto, lo abrimos con el siguiente comando:

sudo ufw allow 5432/tcp

Ahora vamos a crear los enlaces entre las dos máquinas. Para ello realizaremos los siguientes pasos:

Nos conectamos como usuario postgres.

    $ sudo su - postgres psql

Nos conectamos a la base de datos:

    # maria
    postgres=# \c scott2
    ---
    # oracle
    postgres=# \c scott

En las dos máquinas realizamos el mismo comando:

    postgres=# CREATE EXTENSION dblink;

Comprobamos que los links están realizados:

    postgres=# \dx

Comprobamos la conexión

Ahora vamos a comprobar que la conexión entre los dos servidores funciona correctamente. Para ello, vamos a realizar una consulta en la máquina oracle, que es la que no tiene la tabla empleados, y vamos a ver si podemos acceder a ella.

    # maria
    $ psql -U maria2 -d scott2
    scott2=# SELECT * FROM dblink('host=192.168.122.189 port=5432 user=maria1 password=admin dbname=scott', 'SELECT * FROM emp WHERE deptno = 20') as t1 (empno int, ename varchar(10), job varchar(9), mgr int, hiredate date, sal numeric(7,2), comm numeric(7,2), deptno int);

    # oracle
    $ psql -U maria1 -d scott
    scott=# SELECT * FROM dblink('host=192.168.122.167 port=5432 user=maria2 password=admin dbname=scott2', 'SELECT * FROM dept WHERE deptno = 30') as t2 (deptno int, dname varchar(14), loc varchar(13));

Nota: En el caso de posgresql, a diferencia de ORACLE, es necesario que a la hora de realizar la consulta, se especifique el formato de salida de la misma, ya que si no, dará error.

Interconexión entre un servidor Oracle y un servidor Postgresql

postgresql-oracle

Para finalizar, vamos a realizar una interconexión entre un servidor oracle y un servidor postgresql. Para ello, vamos a utilizar el mismo esquema que en los casos anteriores, es decir, vamos a reutilizar las bases de datos que hemos creado en los casos anteriores.

En la máquina maria con ip 192.168.122.167 tenemos la base de datos scott2 con la tabla dept y en la máquina oracle con ip 192.168.122.189 tenemos la base de datos scott con la tabla emp.

Creamos los enlaces

Realmente, ambas máquinas ya están configuradas para acceder a ellas de forma remota, ya que en el caso de oracle, hemos creado un usuario remoto y en el caso de postgresql, hemos abierto el puerto 5432. Pero, siendo dos gestores de bases de datos diferentes, vamos a realizar los enlaces de forma diferente.

Conectándonos de Oracle a Postgresql

Para que la interconexión entre estos gestores de bases de datos sea posible, debemos de tener instalado el paquete postgresql-odbc en la máquina oracle. Para ello, vamos a realizar los siguientes pasos:

Nos conectamos como usuario root.

    $ sudo su

Actualizamos la lista de paquetes.

    $ apt-get update

Instalamos el paquete postgresql-odbc y unixODCB.

    $ apt-get install odbc-postgresql unixodbc

Creamos el archivo de configuración de odbc.

    $ nano /etc/odbc.ini

Añadimos la siguiente configuración:

    [PSQLORCL]
    Debug = 0
    CommLog = 0
    ReadOnly = 0
    Driver = PostgreSQL
    Servername = 192.168.122.172
    Username = maria2
    Password = admin
    Port = 5432
    Database = scott2
    Trace = 0
    TraceFile = /tmp/sql.log

Creamos el archivo de configuración de odbcinst.

    $ nano /etc/odbcinst.ini

Añadimos la siguiente configuración:

    [PostgreSQL]
    Description     = ODBC for PostgreSQL
    Driver          = psqlodbcw.so
    Setup           = libodbcpsqlS.so
    Driver64        = psqlodbcw.so
    Setup64         = libodbcpsqlS.so
    FileUsage       = 1

Reiniciamos el servicio odbc.

    $ service odbc restart

Una vez finalizada la configuración, vamos a realizar la conexión entre los dos servidores y comprobar que funciona correctamente.

    $ isql -v PSQLORCL

Y realizamos una consulta simple para comprobar que funciona correctamente.

Realizamos el link de la base de datos pero esta vez desde Oracle, pero para ello, debemos realizar las siguientes modificaciones:

Creatermos el fichero initPSQLORCL.ora dentro de $ORACLE_HOME/network/admin.

    $ nano $ORACLE_HOME/network/admin/initPSQLORCL.ora

    HS_FDS_CONNECT_INFO = PSQLU
    HS_FDS_TRACE_LEVEL = DEBUG
    HS_FDS_SHAREABLE_NAME = /usr/lib64/psqlodbcw.so
    HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
    set ODBCINI=/etc/odbc.ini
    set ODBCSYSINI=/etc

Lo siquiente que modificaremos, añadiendo lo descrito, será el fichero listener.ora.

    $ nano $ORACLE_HOME/network/admin/listener.ora

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

    SID_LIST_LISTENER =
        (SID_LIST =
            (SID_DESC =
                (SID_NAME = PSQLORCL)
                (ORACLE_HOME=/opt/oracle/product/19c/dbhome_1)
                (PROGRAM=dg4odbc)
            )
        )

Modicicamos tambien el fichero tnsnames.ora.

    PSQLORCL =
        (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.122.189)(PORT=1521))
            (CONNECT_DATA=(SID=PSQLORCL))
            (HS=OK)
        )

Reiniciamos el servicio de oracle.

    service oracle restart

Y como podemos comprobar, la conexión funciona correctamente. En la imagen de a continuación podemos ver que se lista la tabla que sí existe en la base de datos de postgres, es decir, la tabla dept.

Interconexión entre un servidor Postgresql y un servidor Oracle

Para que los dos servidores puedan realizar la interconexión, haremos uso de oracle_fdw. Para ello instalaremos los siguientes paquetes:

    $ apt-get install postgresql-server-dev-all libaio1 build-essential

Una vez instalados los paquetes, vamos a descargarnos a través de wget los paquetes de Oracle Instant Client. Loguearemos con el usuario postgres y descargaremos los paquetes.

    wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-basic-linux.x64-21.1.0.0.0.zip
    wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-sdk-linux.x64-21.1.0.0.0.zip
    wget https://download.oracle.com/otn_software/linux/instantclient/211000/instantclient-sqlplus-linux.x64-21.1.0.0.0.zip

Descomprimimos los paquetes e introducimos las variables de entorno necesarias para no tener que usar el comando export cada vez que queramos usar los paquetes.

    postgres@bbdd2:~$ export ORACLE_HOME=/var/lib/postgresql/instantclient_21_1
    postgres@bbdd2:~$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME
    postgres@bbdd2:~$ export PATH=$PATH:$ORACLE_HOME

Comprobamos que se han hecho correctamente las variables de entorno.

    postgres@bbdd2:~$ which sqlplus

Y como podemos ver enla siguiente imagen, podemos conectarnos a la base de datos de Oracle.

    postgres@bbdd2:~$ sqlplus maria1/admin@192.168.122.189/ORCLCDB

Una vez comprobado que todo funciona correctamente, vamos a instalar el paquete de oracle_fdw.

    wget https://github.com/laurenz/oracle_fdw/archive/refs/tags/ORACLE_FDW_2_3_0.zip
    unzip ORACLE_FDW_2_3_0.zip
    mv oracle_fdw-ORACLE_FDW_2_3_0/ oracle_fdw
    cd oracle_fdw
    make
    make install

Y una vez instalado, vamos a entrar en la base de datos y crearemos la extensión, pero nos salta el siguiente error:

Para solucionarlo, crearemos el fichero /etc/ls.so.conf.d/oracle.conf y añadiremos la siguiente línea:

    /var/lib/postgresql/instantclient_21_1
    /usr/share/postgresql/13/extension

Creamos los enlaces

Creamos los vínculos a las librerías de los directorio que hemos descrito en el fichero anterior y reiniciamos el servicio de postgresql. Tras ello creamos la extensión y lo comprobamos con \dx.

    ldconfig
    service postgresql restart

Creamos un nuevo esquema, y una vez creado, definimos el servidor remoto de Oracle y el mapeo de usuario para que nuestro usuario alojado en PostgreSQL pueda acceder en las tablas del servidor Oracle.

    CREATE SCHEMA oracle;
    create server oracle foreign data wrapper oracle_fdw options (dbserver '//192.168.122.189/ORCLCDB');

    create user mapping for maria2 server oracle options (user 'maria1', password 'admin');

Salimos de la base de datos y nos conectamos con el usuario que hemos creado con anterioridad.

    psql -h localhost -U maria2 -d scott2

Conectándonos de Postgres a Oracle

Como podemos ver en la siguiente imagen, podemos acceder a las tablas de la base de datos de PostgreSQL desde Oracle.

# oracle # postgresql # interconexion