El objetivo de esta entrada de blog es explicar cómo configurar Oracle para poder acceder a bases de datos SQL Server como si estuviésemos en el mismo Oracle. La idea parece fabulosa y de hecho lo es. Porque con lo que explicaré seguidamente, podrás conectar cualquier Base de datos Oracle 8.x o superior a SQL Server 7.0, SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012 y SQL Server Express.
Servicios heterogéneos de Oracle es un componente integrado en el servidor de base de datos Oracle. Permite el acceso SQL transparente desde un cliente de Oracle para los sistemas que no son de Oracle como si el sistema no Oracle fuera una base de datos Oracle . En este ejemplo vamos a utilizar el agente de Servicios heterogéneo ODBC (HSODBC) con productos Easysoft, esta funcionalidad puede ser extendida en plataformas que no sean Windows para incluir cualquier base de datos compatible con ODBC o JDBC.
Es muy importante saber que HSODBC es normalmente una aplicación de 32 bits incluso cuando se distribuye con una versión de 64 bits de Oracle. Es necesario utilizar el HSODBC 32 bits con un controlador ODBC Easysoft de 32 bits. Para las plataformas de 64 bits, seleccionar también la versión de 32 bits del controlador, incluso cuando haya una versión de 64 bits disponible, ya que los controladores de 32 bits seguirán funcionando correctamente en una plataforma de 64 bits.
Para comprobar si tenemos una versión de 32 bits o 64 bits de HSODBC, en la máquina de Oracle, ejecutaremos:
$ORACLE_HOME/bin/hsodbc
Si la salida del comando contiene ELF-Class64 (o algo similar, como ELF-64 o ELF 64-bit), tenemos un HSODBC 64 bits. De lo contrario, tenemos una versión de 32 bits.
Otra manera de saber si un fichero es de 32 o 64 bits es mediante el uso del comando UNIX “file”, se puede determinar relativamente fiable si el programa utilizado y la biblioteca existen en 32 bits o en forma de 64 bits. Ejemplo de uso:
<programa> archivo
<biblioteca> archivo
Si la línea de resultados contiene un ’64 ‘ , el archivo existe en la versión de 64 -bit . De lo contrario , se puede asumir que se trata de la versión de 32 -bit .
Si se determina que la inconsistencia entre <programa> y <biblioteca> llamando el comando ‘file’ , hay que corregir la incompatibilidad ajustando las variables de entorno SHLIB_PATH (HP-UX) en nuestro ejemplo, LIBPATH (en sistemas AIX) y LD_LIBRARY_PATH en otras variantes de Unix.
Ejemplo de ejecución de “file” que nos muestra que es de 32 bits:
$ file /usr/local/easysoft/sqlserver/lib/libessqlsrv.sl /usr/local/easysoft/sqlserver/lib/libessqlsrv.sl: PA-RISC1.1 shared library -not stripped
Ejemplo de ejecución de “file” que nos muestra que es de 64 bits:
$ file /usr/local/easysoft/sqlserver/lib/libessqlsrv.sl /usr/local/easysoft/sqlserver/lib/libessqlsrv.sl: ELF-64 shared object file - PA-RISC 2.0 (LP64) Si $ORACLE_HOME/bin/hsodbc no está presente, debermos instalarlo para poder seguir con el proceso de configuración de esta entrada de blog.
En referencia al driver ODBC que vamos a utilizar en la parte Unix (donde tengo instalado el servidor Oracle) puedes ampliar la información en:
http://www.easysoft.com/products/data_access/odbc-sql-server-driver/index.html
En este ejemplo conectaremos un Oracle 8.1.7.4 instalado en un Unix HP-UX 11.11 a un SQL Server 2005.
Para llevar la tarea a cabo. Se realizarán los siguientes pasos:
En el Servidor “destino” SQL SERVER
1.- Definir un nombre de origen de datos (DSN) para SQL Server.
En el servidor “Orígen” por la parte de Oracle
2.- Crear un fichero de inicialización de los servicios heterogéneos.
3.- Crear un nuevo listener dentro del fichero listener.ora.
4.- Añadir una nueva entrada en el fichero tnsnames.ora.
5.- Crear un Database Link en Oracle.
En el servidor “Orígen” por la parte de Unix
6.- Instalar/configurar el driver ODBC en el sistema Unix donde reside el servidor Oracle.
7.- Posibles errores durante la configuración
8.- Juego de pruebas de comprobación de correcta instalación
En el Servidor destino SQL SERVER
1.- Definir un nombre de origen de datos (DSN) para SQL Server.
En primer lugar accederemos como administradores al servidor destino SQL Server. Y crearemos el ODBC de la siguiente manera:
En versiones más antiguas …. Desde el panel de control ….
En versiones posteriores sería desde menú inicio –> Herramientas administrativas –> Orígenes de datos ODBC
Importante: Seleccionar la pestaña “SystemDSN” y pulsar doble click o “Add”
En la siguiente pantalla seleccionaremos el controlador (driver) de SQL Server ya que esta será una conexión a SQL Server. Heremos clic en Finalizar para continuar con la definición del origen de datos.
Escribir un nombre que haga referencia a esta fuente de datos ODBC. He elegido MYSQLSERVERDSN por simplificar el entendimiento, pero debe ser descriptivo de la base de datos que conecte dentro de SQL Server. También puede describir el origen de datos en la forma que desee. Como en mi caso se conecta en local dejo Server “local”. Hacer click en Siguiente “Next” para continuar (no en finish).
Seleccionaremos autentificación mediante Windows si tenemos usuario de red. En mi caso, tenía el usuario y clave del administrador de Windows.
En general, en la siguiente ventana se rellena con la base de datos de SQL Server predeterminada de “maestro”. Haga clic en la casilla de verificación para cambiar la base de datos predeterminada y selecciona la Base de datos a usar en la conexión ODBC. En mi ejemplo, la base de datos de SQL SERVER a la que nos vamos a conectar será “bbddsqlserver” y el usuario administrador será “sa” y clave “sa_clave“.
Hacer click en Siguiente para continuar.
En la siguiente pantalla pulsar “Finish”….
Aperecerá una ventana con la información que hemos configurado para que comprobemos los ajustes que hemos configurado para el origen de datos. Haremos click en “Probar origen de datos” para validar su definición. Y si todo ha ido correctamente saldrá una pantalla como la siguiente:
Lo instalado debe aparecer como un DSN de sistema válido como se ve en la siguiente pantalla. Si en un futuro se desea modificar algo, basta con editarlo pulsando en “Configure” pero tendremos que volver a borrar y crear el dbLink en la parte de Oracle para activar de nuevo las modificaciones realizadas en el ODBC.
Hacer click en “Ok” para salir del administrador de la DSN. Ya tenemos la parte de Windows realizada.
Ahora pasaremos a configurar la parte de Oracle ….
En el servidor “Orígen” por la parte de Oracle
2.- Crear un fichero de inicialización de los servicios heterogéneos.
Dentro del directorio $ORACLE_HOME/hs/admin Oracle tiene unos ficheros init de ejemplo que usaremos para configurar el nuestro. Sino existe el directorio es porque Oracle Servicios heterogéneos no está instalado y se tendrá que instalar. (Estará en el CD de Oracle Enterprise Edition probablemente). En mi caso, como si está instalado, lo que haremos será copiar el fichero de ejemplo con otro nombre en el mismo directorio, luego lo editaremos con la información de mi configuración:
Se hace así …
$ cd $ORACLE_HOME/hs/admin $ cp inithsodbc.ora initMYSQLSERVERDSN.ora
Este es el contenido del fichero de muestra:
# This is a sample agent init file that contains the HS parameters that are # needed for an ODBC Agent. # # HS init parameters # HS_FDS_CONNECT_INFO = <odbc data_source_name> HS_FDS_TRACE_LEVEL = <trace_level> HS_FDS_TRACE_FILE_NAME = <log file name> HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver> # # ODBC specific environment variables # set ODBCINI=<full path name of the odbc initilization file> # # Environment variables required for the non-Oracle system # set <envvar>=<value>
Este es el fichero ya editado con los valores de mi instalación:
# This is a sample agent init file that contains the HS parameters that are # needed for an ODBC Agent. # # HS init parameters # HS_FDS_CONNECT_INFO = MYSQLSERVERDSN HS_FDS_TRACE_LEVEL = 0 HS_FDS_TRACE_FILE_NAME =/tmp/hsodbcsql.trc HS_FDS_SHAREABLE_NAME =/usr/local/easysoft/sqlserver/lib/libessqlsrv.sl # # ODBC specific environment variables # set ODBCINI=/etc/odbc.ini # # Environment variables required for the non-Oracle system # #set <envvar>=<value>
En negrita aparecen los valores que yo he añadido y he eliminado la “#” de comentario del principio de cada línea. Son los valores que necesita un SA para usar un agente ODBC. Explicaré alguno de los parámetros, pero para más información consultar la documentación de Oracle y/o la de Easysoft Driver configuration.
HS_FDS_CONNECT_INFO es el nombre del origen de datos tal como se escribió dentro de /etc/odbc.ini Tener en cuenta que como hsODBC utiliza la API de ODBC SQLDriverConnect, en realidad se puede poner cualquier cadena de conexión ODBC válida aquí. Dentro de /etc/odbc.ini tendremos los datos de conexión del servidor SQL Server.
HS_FDS_TRACE_LEVEL determina el nivel de rastreo. Como el rastreo ralentiza las operaciones he puesto “0″. Sin embargo, si tenemos problemas HS_FDS_TRACE_LEVEL puede ajustarse a un número del 1 al 4 (donde 4 es el más detallado) y la salida de rastreo se escribe en el archivo especificado con HS_FDS_TRACE_FILE_NAME.
HS_FDS_SHAREABLE_NAME es el nombre de la shared library, coincide con el nombre del driver a utilizar (dentro del campo driver en /etc/odbc.ini). Para saber que driver se va a utilizar podemos ejecutar $ ./odbcinst -j dentro de la ruta /usr/local/easysoft/unixODBC/bin para nuestro ejemplo, ya que nosotros usamos un driver ODBC de Unix de la empresa Easysoft. Veremos todo esto en la parte de instalación/configuración del driver al final de esta entrada de blog.
3.- Crear un nuevo listener dentro del fichero listener.ora.
En el mismo directorio anterior, tambien tenemos un ejemplo de listener.ora y tnsnames.ora. Yo lo que hice, fue utilizar el contenido de este ejemplo y añadirlo al listener.ora “de toda la vida” ubicado en $ORACLE_HOME/network/admin. Lo que realmente se debe tener en cuenta es especificar el PROGRAM y que el SID name coincida con el que vamos a configurar en el tnsnames.ora. En negrita está todo lo modificado y de mayor importáncia:
Contenido del listener.ora adecuado:
LISTENERMYSQLSERVERDSN = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) SID_LIST_LISTENERMYSQLSERVERDSN= (SID_LIST= (SID_DESC= (SID_NAME=MYSQLSERVERDSN) (ORACLE_HOME = /psg_oetc/oracle) (PROGRAM=hsodbc) (ENVS=LD_LIBRARY_PATH = /usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/ lib:$ORACLE_HOME/lib32:/usr/local/easysoft/sqlserver/lib) ) )
A tener en cuenta …
- Se puede usar el puerto 1522 para evitar conflictos con otro listener o con el de por defecto de ORACLE 1521.
- Se debe parar y volver a arrancar para que los cambios tengan efecto. Podemos hacer un tnsping para comprobar que todo está Ok.
- La variable ENVS puede omitirse, creo que funcionaría igual, siempre y cuando definamos correctamente las variables de entorno LD_LIBRARY_PATH y SHLIB_PATH (HP-UX).
- Tened en cuenta que la variable de entorno $ORACLE_HOME/lib32 sólo s encontrará en sistemas de 32 bits. Para sistemas operativos de 64 bits, poner lib64 ($ORACLE_HOME/lib64).
Para arrancar el listener hacer:
$ lsnrctl start LISTENERMYSQLSERVERDSN TNSLSNR for HPUX: Version 8.1.7.4.0 - Production
El fichero de parámetros del sistema es /oracle/network/admin/listenera Mensajes del log escritos en /oracle/network/log/listenermysqlserverdsg Recibiendo en: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servidor_local)(POR) Recibiendo en: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))) Conectándose a (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) ESTADO del LISTENER ------------------------ Alias LISTENERMYSQLSERVERDSN Versión TNSLSNR for HPUX: Version 8.1.7.4.0 - Production Fecha de Inicio 30-SEP-2013 14:56:05 Tiempo de actividad 0 días 0 hr. 0 min. 0 seg. Nivel de Rastreo off Seguridad OFF SNMP OFF Fichero de Parámetros del Listener /oracle/network/admin/listener.ora Fichero Log del Listener /oracle/network/log/listenermysqlserverdg Resumen de servicios... MYSQLSERVERDSN tiene 1 gestor(es) de servicio El comando se ha ejecutado correctamente
Para pararlo:
$ lsnrctl stop LISTENERMYSQLSERVERDSN LSNRCTL for HPUX: Version 8.1.7.4.0 - Production on 30-SEP-2013 15:02:00 (c) Copyright 1998 Oracle Corporation. All rights reserved. Conectándose a (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) El comando se ha ejecutado correctamente
Para comprobar que funciona:
Nota: Antes de hacer la prueba debemos tener definida la instancia MYSQLSERVERDSN en el fichero tnsnames.ora como se explica en el siguiente punto. Observar que se hace el tnsping al nombre de la instancia y no al nombre del listener creado.
$ tnsping MYSQLSERVERDSN TNS Ping Utility for HPUX: Version 8.1.7.4.0 - Production on 30-SEP-2013 15:00:1 (c) Copyright 1997 Oracle Corporation. All rights reserved. Attempting to contact (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) Realizado correctamente (110 mseg)
4.- Añadir una nueva entrada en el fichero tnsnames.ora.
Un ejemplo de tnsnames.ora lo podemos encontrar también en $ORACLE_HOME/hs/admin. Lo importante en este caso, es también lo que tenemos subrayado en negrita.
MYSQLSERVERDSN =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(CONNECT_DATA=(SID=MYSQLSERVERDSN))
(HS=OK)
)
Lo más destacable en este punto es sobretodo:
- Indicar correctamente el nombre de la instancia “SID”. Esta cadena es la que usaremos en las llamadas a los ficheros odbc.ini en los drivers ODBC del sistema Unix e incluso será el nombre del ODBC del sistema Windows donde reside SQL Server.
- He puesto el puerto 1522 para evitar conflictos con el de por defecto de Oracle 1521. Debemos tener en cuenta que el puerto que pongamos tiene que estar abierto. Sino estamos seguros, realizar todo el proceso con el puerto 1521 y después de la instalación cambiar al puerto deseado. Parar y volver a arrancar el listener y ya estaría.
- El valor de HS se pone a “OK” para que Oracle use los servicios heterogéneos.
5.- Crear un Database Link en Oracle.
Se ha de crear un Database link, porque será de esta manera el cómo realizaremos las consultas desde SQL de Oracle al servidor No-Oracle que en este caso será un SQL SERVER. Es decir, con el “@MYSQLSERVER” después de cada consulta que hagamos. Lo vermos más adelante con algún ejemplo de consulta al servidor destino.
CREATE PUBLIC DATABASE LINK MYSQLSERVERDSN CONNECT TO "sa" IDENTIFIED BY "as" USING 'MYSQLSERVERDSN';
Muy importante a tener en cuenta a la hora de crear el database link:
- Crearlo con el usuario system en la BBDD Oracle, la cual nos conectaremos a sqlplus cuando queramos acceder a SQL Server.
- Tanto el usuario como la clave de la conexión, ponerla en comillas dobles y el SID de despues del “using” ponerlo en comillas simples. Sino se hace así, es posible que Oracle te permita crear el database link, pero no te funcionará.
- Creamos el database link como public para que todos los usuarios puedan acceder a la consulta del SQL Server.
- Para que el DBLlink funcione nos debemos asegurar de que el parámetro “global_names” del init.ora de la BBDD Oracle está definido como “FALSE”. Para ello podemos realizar la siguiente consulta:
SELECT * FROM v$parameter WHERE name LIKE 'global_names%';
Si el parámetro está seteado a “TRUE” deberemos cambiarlo en el fichero init.ora y volver a reiniciar la BBDD antes de crear el DB Link.
En el servidor “Orígen” por la parte de Unix
6.- Instalar/configurar el driver ODBC en el sistema Unix donde reside el servidor Oracle.
Seguir la documentación explicada en la entrada de blog del enlace anterior. Para realizar la instalación del Driver ODBC para Unix. En este ejemplo he utilizado el facilitado por la empresa Easysoft y que requiere de licencia.
7.-Posibles errores durante la configuración
Es muy probable que durante la configuración de alguno de los procesos anteriores, nos encontremos con algún error. Seguidamente detallo alguno de los errores encontrados y la solución aplicada. He de comentar, que si se ha seguido al pie de la letra la documentación, estos errores no deberían producirse. Ya que la documentación se ha hecho dándo ya la solución a los mismos.
Error 1
Al ejecutar …
# ./odbcinst -j /usr/lib/pa20_64/dld.sl: Unable to find library 'libodbcinst.sl.1'. Killed
Solución
Este error en concreto es debido a que no encuentra una librería de 64 bits cuando en realidad necesitábamos una de 32 bits. La solución fue borrar la versión de 64 bits (explicado en esta entrada de blog), descargar la versión del driver para 32 bits e instalarla.
Error 2
SQL> select * from dual@MYSQLSERVERDSN;
/usr/lib/dld.sl: Bad magic number for shared library: /usr/local/easysoft/sqlsel/usr/lib/dld.sl: Exec format error
select * from dual@MYSQLSERVERDSN
*
ERROR en línea 1:
ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto este
mensaje:[Generic Connectivity Using ODBC]Exec format error; at FIND_IMAGE_SYMBOL
Cannot connect to shareable /usr/local/easysoft/sqlserver/lib/libessqlsrv.sl.
Using dummy functions
ORA-02063: 3 lines precediendo a MYSQLSERVERDSN
Solución
Añadir los valores en negrita a las variables de entorno LD_LIBRARY_PATH y SHLIB_PATH.
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/easysoft/sqlserver/lib
export SH_LIB_PATH=$SH_LIB_PATH:/usr/local/easysoft/sqlserver/lib
Error 3
$ ./isql -v MYSQLSERVERDSN sa as [01000][unixODBC][Driver Manager]Can't open lib '/usr/local/easysoft/sqlserver/lib/libessqlsrv.sl' : can't open the module [ISQL]ERROR: Could not SQLConnect
Solución
Esto es porque dentro del directorio /usr/local el directorio easysoft no tiene permisos de lectura/ejecución de alguna librería. Se soluciona entrando en /usr/local y ejecutando conectado como “root”:
# cd /usr/local # chmod -R 777 easysoft
Otros Errores comunes
ERROR en línea 1: ORA-28500: la conexión de ORACLE a un sistema no Oracle ha devuelto este mensaje:[Generic Connectivity Using ODBC]Exec format error; at FIND_IMAGE_SYMBOL
La mayoría de los errores que se producen durante este tipo de instalación es o bien:
- Porque no se están instalando los drivers adecuados en cuanto a la versión de 32 o 64 bits.
- Porque no se ha definido o no se ha definido de manera adecuada las variables de entorno LD_LIBRARY_PATH (cualquier sistema o variante de Unix) y SHLIB_PATH (HP). En entornos (AIX) en lugar de SHLIB_PATH se usa LIBPATH.
- Cuando falla alguna librería, y normalmente es por los motivos comentados anteriormente. Es decir, se ha instalado una de 64 bits cuando necesitamos 32 bits, una manera de ver donde enlaza la librería es usando el comando chatr <nombre_libreria>.
Ejemplo:
$ chatr $ORACLE_HOME/lib/libclntsh.sl
/oracle/lib/libclntsh.sl:
shared library
shared library dynamic path search:
SHLIB_PATH enabled first
embedded path disabled second Not Defined
internal name:
libclntsh.sl.8.0
shared library list:
dynamic /oracle/lib/libwtc8.sl
dynamic /usr/lib/librt.2
dynamic /usr/lib/libpthread.1
dynamic /usr/lib/libnss_dns.1
dynamic /usr/lib/libdld.2
dynamic /usr/lib/libm.2
dynamic /usr/lib/libc.2
dynamic /usr/lib/libcl.2
shared vtable support disabled
static branch prediction disabled
executable from stack: D (default)
kernel assisted branch prediction enabled
lazy swap allocation disabled
text segment locking disabled
data segment locking disabled
third quadrant private data space disabled
fourth quadrant private data space disabled
third quadrant global data space disabled
data page size: D (default)
instruction page size: D (default)
nulptr references enabled
shared library private mapping disabled
Solución
Podemos asegurarnos de que estas rutas están definidas en la variable de entorno PATH.
8.- Juego de pruebas de comprobación de correcta instalación
Una vez acabada la instalación realizaré las siguientes comprobaciones.
- Añadiré las variables de entorno LD_LIBRARY_PATH y SHLIB_PATH al entorno o .profile del usuario Oracle.
- Comprobaré que el ODBC está instalado de manera adecuada y me informo de dónde están los drivers:
En la ruta
cd /usr/local/easysoft/unixODBC/bin # ./odbcinst -j
Si al ejecutar sale el error:
./odbcinst -j /usr/lib/dld.sl: Can't open shared library: /usr/home_dir/svnbuild-32/external/p roducts/unixodbc_2_3/threaded/lib/libodbcinst.sl.1 /usr/lib/dld.sl: No such file or directory Abort(coredump)
Solución
Hay que borrar el fichero “core” que ha generado en el directorio y cargar con valores adecuados las variables de entorno LD_LIBRARY_PATH y SHLIB_PATH.
Ejemplos válidos:
export LD_LIBRARY_PATH=$ORACLE_HOME/jdbc/lib:$ORACLE_HOME/lib:/usr/local/easysoft/sqlserver:/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/sqlserver/lib
export SHLIB_PATH=$ORACLE_HOME/jdbc/lib:$ORACLE_HOME/lib:/usr/local/easysoft/sqlserver:/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib:/usr/local/easysoft/sqlserver/lib
Ahora comprobaré que la conexión del driver ODBC funciona:
cd /usr/local/easysoft/unixODBC/bin ./isql -v MYSQLSERVERDSN sa as
Si esto falla, es porque no puede leer las librerias.
Solución
# cd /usr/local # chmod -R 777 easysoft
Ahora me conecto a sqlplus de Oracle y compruebo que accedo al servidor de SQL Server:
Antes de hacer esto. Podemos asegurarnos de parar y volver a arrancar el listener con las variables de entorno LD_LIBRARY_PATH y SHLIB_PATH adecuadamente configuradas. Porque sino nos podemos encontrar que esto no funcione.
sqlplus system/password
SQL> select * from dual@MYSQLSERVERDSN; ninguna fila seleccionada
Que no devuelva ninguna fila es posible. Lo importante es que no dé errores de Oracle u otros errores. También podemos probar con alguna tabla que sepamos que existe en el SQL Server de destino. Pero no olvidarse, en este caso, de poner el nombre del propietario de la tabla delante y usar el DBLink a la hora de hacer la select.
Ejemplo:
SQL> select * from usuario.tabla@MYSQLSERVERDSN;
¡¡¡ Pues ya tenemos nuestro Oracle Heterogeneous configurado!!!