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!
