Visitas: 304  
Tiempo total: 6 días con 10:27:55 hrs  

Database Link es una instrucción Oracle/Mysql que permite ejecutar instrucciones en una base de datos externa enlazada, un ejemplo de la utilización de esta instrucción es la implementación como RPC para replicar la información entre bases de datos de acuerdo a los requerimientos finales.

Esta publicación es acerca de cómo hacer funcionar los Database Link en Oracle. Para esto, en este entorno es Oracle 11g edición express junto al sistema operativo Windows 7. Es necesario recordar tener el puerto 1521 de Oracle abierto y listo para permitir conexiones externas.

Database Link consiste en una variable que enlaza las instrucciones hacia una base de datos externa, para esto se debe de crear la variable de referencia antes de ejecutar cualquier instrucción SQL, esto con el siguiente comando:

CREATE DATABASE LINK proyecto CONNECT TO proyecto IDENTIFIED BY tool USING ‘( DESCRIPTION = ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = 192.168.56.101 ) ( PORT = 1521 ) ) ( CONNECT_DATA = ( SERVER = DEDICATED ) ( SERVICE_NAME = XE ) ) )’;

En esta instrucción, proyecto es la variable que estará enlazada hacia la base de datos llamada proyecto (Su segunda mención), la contraseña de usuario asignada a la base de datos proyecto (Su segunda mención) es tool, la base de datos está en la IP interna de Virtualbox 192.168.56.101 e iniciada en el servicio 1521, el puerto por defecto de Oracle. En este caso después de ejecutar la instrucción, si tool no es la contraseña correcta en el servidor externo no observaremos el mensaje de error una vez creemos el enlace, este mensaje aparece una vez utilicemos esta variable de enlace en alguna instrucción SQL.

Ahora, supongamos la siguiente tabla llamada cuenta:

CREATE TABLE cuenta
(
id INTEGER NOT NULL,
entidad_financiera INTEGER NOT NULL ,
cuenta_habiente INTEGER NOT NULL ,
tipo_cuenta CHAR (30) NOT NULL ,
actualizado INTEGER DEFAULT 0
)
;

Esta tabla estará en ambas bases de datos, la local y una externa. Como se puede sincronizar la nueva información insertada? Para mostrar un ejemplo, por cada inserción en nuestra tabla local requerimos que la información se replique a la base de datos externa, con el siguiente TRIGGER:

CREATE OR REPLACE
TRIGGER cuenta_remote_insert
BEFORE INSERT ON cuenta
FOR EACH ROW
BEGIN
INSERT INTO cuenta@proyecto (id, entidad_financiera, cuenta_habiente, tipo_cuenta, actualizado)
VALUES (:NEW.id, :NEW.entidad_financiera, :NEW.cuenta_habiente, :NEW.tipo_cuenta, 1);
:NEW.actualizado := 1;
END;

Aquí se observa la inserción de la nueva información de la tabla cuenta en la base de datos externa. Para fines demostrativos estableceremos otra variable ejemplo, supongamos entidad_financiera es una llave foránea hacia otra tabla con el mismo nombre, en esta tabla relacionada puede existir una variable (remoto) que nos indique si la información insertada debe ser replicada o no.

Para que me puede servir la variable remoto? Supongamos que Local=valor, Local es mi base de datos local y valor es lo insertado. Al momento de realizar esta instrucción se disparara el TRIGGER hacia la base de datos EXTERNA.Local=valor, esta instrucción ejecutada externamente disparara de nuevo el TRIGGER (En el caso que cada base de datos se ejecute por separado y realice operaciones distintas de distintos clientes para cada entidad) Si esto ocurre, el TRIGGER entraría en un ciclo infinito, este evento lo identifica Oracle como un error cancelando la transacción.

Como funciona remoto? Este es un ejemplo especifico: Si la entidad financiera A esta en México remoto = n (en datos booleanos, es mejor utilizar 1 o 0). A continuación se tiene otra entidad financiera B que está en Hawái y tiene el valor remoto = n. Esto se resume que, en México se tendrán los valores A.remoto = n, B.remoto = s. En Hawái A.remoto = s, B.remoto = n. Esta variable bandera sirve para saber qué campo es remoto y cual no, hacia donde replicar la información y hacia donde no. Su implementación en el mismo TRIGGER es la siguiente:

CREATE OR REPLACE
TRIGGER cuenta_remote_insert
BEFORE INSERT ON cuenta
FOR EACH ROW
DECLARE
remoto CHAR;
BEGIN
SELECT ef.remoto INTO remoto FROM entidad_financiera ef WHERE ef.id = :NEW.entidad_financiera;
IF remoto = ‘s’ THEN
INSERT INTO cuenta@db2_proyecto (id, entidad_financiera, cuenta_habiente, tipo_cuenta, actualizado)
VALUES (:NEW.id, :NEW.entidad_financiera, :NEW.cuenta_habiente, :NEW.tipo_cuenta, 1);
:NEW.actualizado := 1;
END IF;
END;

En este ejemplo, podremos registrar una nueva cuenta de usuario en México de México, dicha información no seria útil en Hawái por lo tanto no es necesario replicarla. En México podemos crear una nueva cuenta de Hawái la cual si tiene que ser replicada. A partir de esto pueden surgir varios problemas, por ejemplo que en México se crea una nueva cuenta de Hawái que no existe en México pero si en Hawái, también se da el problema que la implementación de los registros de la base de datos dependan del identificador, en la base de datos de México el ID insertado puede ser 1001, pero en Hawái puede que sea el 997. En resumen, la utilización de esta instrucción en cada INSERT requiere de un análisis completo y detallado.

En la tabla también podemos observar la variable actualizada, esta es utilizada para saber cuando la información ha sido actualizada en ambas bases de datos. Inicialmente debe de ser 0, y una vez ha sido sincronizada debe pasar a 1. Cuando se actualiza puede cambiar a 2 y regresar a 1 una vez la nueva información haya sido actualizada en la otra base de datos.

Como sincronizar la información cada X unidades de tiempo?

Una vez insertada la información y una vez ha sido sincronizada con la otra base de datos, la instrucción UPDATE puede tener el requerimiento de ser replicada a la otra base de datos cada X unidades de tiempo.

Para esto se utiliza el paquete DBMS_SCHEDULER, que es un conjunto de funciones que pueden ser llamadas desde cualquier programa PL/SQL. Con este paquete podemos programar funciones que llamen a procedimientos creados en nuestra base de datos que nos ayuden a sincronizar la información usando la instrucción CREATE_JOBS.

A continuación, la instrucción crea el trabajo llamado cuenta_JS que se ejecuta cada dos minutos y llama al procedimiento de nuestra base de datos p_cuenta:

begin
DBMS_SCHEDULER.CREATE_JOB(
job_name => ‘cuenta_JS‘,
job_type => ‘PLSQL_BLOCK’,
JOB_ACTION => ‘begin p_cuenta; end; ‘,
start_date => SYSTIMESTAMP,
repeat_interval => ‘freq=minutely;interval=2’,
end_date => NULL,
enabled => TRUE,
comments => ‘CUENTA’
);
end;

El procedimiento con nombre p_cuenta sirve para realizar cualquier transacción de replicación de acuerdo a nuestra implementación. Se debe de tomar en cuenta que la variable actualizada debe de servir como bandera al igual que remoto para no entrar en un ciclo infinito, aquí si actualizada es 2 en un la base de datos local pasara directamente como 1 a la base de datos externa, una vez se finaliza esta transacción pasa a 1 en la base de datos local y finaliza con el COMMIT de la transacción. Para finalizar podemos comprobar cuantas veces han sido llamados nuestros procedimientos remotos a través de los trabajos creados con la siguiente instrucción:

select job_name,enabled,run_count from user_scheduler_jobs;

Referencias

[http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CIHGGCBJ]
[http://www.toadworld.com/platforms/oracle/w/wiki/5911.remote-procedure-calls.aspx]
[http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm]
[http://es.wikipedia.org/wiki/Remote_Procedure_Call]
[http://toolband.com/]