viernes, 18 de diciembre de 2009

Cómo saber el tipo de Linux para instalar Oracle

Alguna vez caímos en paracaídas a instalar algún producto Oracle en un servidor Linux desconocido, e inmediatamente surgieron las siguientes interrogantes:
  • ¿Qué versión de Linux es?
  • ¿Cuál es la versión de Oracle que debo descargar?
La base de datos Oracle es el producto más portable del mercado, y por eso dispone de muchas versiones dependiendo de la arquitectura y versión del sistema operativo. Todo dba se ha visto confundido más de una vez al momento de elegir la versión correcta.

Por ese motivo recomiendo tener esta guía-ayuda a mano, para no perder tiempo a la hora de elegir.

Mapea los códigos informados por el comando Unix uname, con la versión de Linux que aparece en la página de downloads de Oracle. Basta con observar los últimos tres identificadores que aparecen en la salida del comando.
Por ejemplo para el primer caso sería "i686 i686 i386".

Espero que ayude!

$ uname -a
Linux hostname 2.6.9-34.0.2.ELsmp #1 SMP Fri Jun 30 10:33:58 EDT 2006 i686 i686 i386 GNU/Linux
-->
Linux x86: (32-bit OS)

$ uname -a
Linux hostname 2.6.9-55.ELsmp #1 SMP Fri Apr 20 16:36:54 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
-->
Linux x86_64: (64-bit OS)

$ uname -a
Linux hostname 2.6.9-42.EL #1 SMP Wed Jul 12 23:25:09 EDT 2006 ia64 ia64 ia64 GNU/Linux
-->
Linux Itanium: (64-bit OS)

$ uname -a
Linux hostname 2.6.9-34.0.1.0.11.EL #1 SMP Mon Dec 4 16:10:42 PST 2006 ppc64 ppc64 ppc64 GNU/Linux
-->
IBM Power Based Linux: (64-bit OS)

$ uname -a
Linux hostname 2.6.16.46-0.12-default #1 SMP Thu May 17 14:00:09 UTC 2007 s390x s390x s390x GNU/Linux
-->
IBM zSeries Based Linux: (64-bit OS)

$ uname -a
Linux hostname 2.4.21-50.EL #1 SMP Tue May 8 17:10:38 EDT 2007 s390 s390 s390 GNU/Linux
-->
IBM S/390 Based Linux (31-bit): (31-bit OS)

sábado, 28 de noviembre de 2009

Controlando el horario de verano en Solaris

Solaris 10 viene configurado con cambios de horario de verano automáticos (daylight saving time), basados en archivos de configuración predefinidos con fecha y hora del cambio para cada región.

Puede ocurrir, como ya pasó este año en Brasil, que el gobierno decida adelantar dicha fecha y entonces eso puede darnos algunos problemas si nos toma desprevenidos. Dependiendo del negocio, algunos sistemas en producción no deben alterar el horario, otros en cambio, deben hacerlo estrictamente según la normativa oficial.

Para tener el control, tenemos que verificar la configuración en Solaris y ver que está de acuerdo con nuestros intereses.


COMO ALTERAR LA CONFIGURACION DEL HORARIO DE VERANO


Paso 1 - Verificar la zona horaria del servidor

Siempre logueados como root, leemos el archivo TIMEZONE y buscamos el valor de la variable TZ al final del archivo:

cat /etc/TIMEZONE

TZ=Brazil/East


Paso 2 - Hacer un backup de la configuración actual (por si acaso)

cd /usr/share/lib/zoneinfo
cd Brazil (o la region que corresponda según el Paso 1)
cp East East.backup

Tal vez encontremos un archivo .zic, con alguna configuración que alguien ya hizo, es aconsejable darle una ojeada y ver si nos sirve para el paso 4.


Paso 3 - Verificar configuración del horario de verano, según el TZ encontrado en el Paso 1

zdump -v Brazil/East | grep 20

Encontraremos varias líneas del tipo:

Brazil/East Sat Nov 28 18:31:41 2009 UTC = Sat Nov 28 15:31:41 2009 BRT isdst=0

Para cada año aparece la correspondencia entre el horario universal (UTC) y el de nuestra región. El ultimo parámetro indica si se trata de horario de verano (isdst=1) o no (isdst=0), por tanto debe haber una diferencia de una hora menos cuando isdst=1.
O sea que si vivimos en UMT-3, en el horario de verano vamos a tener UMT-2.

Verificar se coincide con el cambio oficial programado para el corriente año.


Paso 4 - Crear un archivo .zic con las alteraciones deseadas

Crear un nuevo archivo llamado config_dst.zic con el siguiente contenido:

Rule Brazil 2009 only - Oct 11 00:00 1 S
Rule Brazil 2009 only - Oct 20 00:00 0 -

Zone Brazil/East -3:00 Brazil BR%sT

(fin del archivo)

En el paso 2 decíamos que tal vez ya exista un archivo .zic en el directorio, en ese caso podemos agregar nuestras líneas arriba del todo, así no perdemos el resto de las configuraciones. Cuidado que no existan otras definiciones para el mismo año, porque pueden entrar en conflicto.

La última línea indica el nombre del archivo de configuración, y en que directorio se encuentra. También define el indicador de horario de verano que veremos cuando usemos el comando date. Este será BRST en horario de verano, y BRT en horario normal.


Paso 5 - Aplicar la nueva configuración con el comando zic

zic config_dst.zic


Paso 6 - Esperar la fecha prevista y verificar

Con el comando date, verificaremos que el horario es el correcto y el tipo de horario es BRST, tal como lo configuramos.

miércoles, 7 de octubre de 2009

Adiós Metalink

El tradicional site de soporte oficial de Oracle, más conocido como Metalink (metalink.oracle.com) será retirado definitivamente el próximo 6 de noviembre, y dejará al nuevo sistema basado en Adobe Flash 9, 'My Oracle Support', como única opción para los clientes.

Personalmente gusto más del viejo Metalink desarrollado con APEX, el html es sin dudas más estable y sencillo de navegar que la abrumadora interfase de ventanas deslizantes y tiptools de My Oracle Support. No menos importante es el hecho de que Flash no funciona en muchos dispositivos móviles y algunos browsers, además de obligar a los usuarios a bajar software adicional. No me molesta que esté allí, siempre y cuando sea opcional.

Múltiples encuestas en páginas y blogs de Oracle muestran que la mayoría prefieren a Metalink. Yo mismo he colocado mi opinión en varias encuestas de Oracle, cada vez que me ha sido ofrecida la oportunidad. Pero parece que los planes de la corporación son inamovibles y Metalink dejará de existir para el lamento y experiencia de soporte de muchos.

Actualmente Oracle tiene abierta una encuesta para expresar nuestra opinión, en este link.

Más información:
Nota ID 841061.1 en Metalink
Trainings My Oracle Support

viernes, 4 de septiembre de 2009

Oracle Critical Patch Octubre 2009

El set de patches de seguridad que Oracle había programado para publicar el 13 de Octubre de 2009, fue re agendado para el 20 del mismo mes, es decir una semana después.

Llama la atención el motivo que la corporación dio al respecto: "ya que muchos clientes con responsabilidad de aplicar los patches en sus respectivas empresas van a estar asistiendo al Oracle Open World (11 al 15 de Octubre)".

Es ese un motivo justificado para retrasar un patch de seguridad? Porque un mínimo porcentaje de clientes va a asistir a tal evento? Acaso todos tienen que aplicarlos al mismo tiempo?

En realidad quien estará con toda la atención centrada en el evento, será naturalmente la propia compañia, incluyendo algunos responsables por los patches. Pero eso de argumentar que por causa de algunos clientes los retrasamos, no parece lógico ni justo.
Hacen parecer que los patches críticos no son tan críticos.

jueves, 13 de agosto de 2009

Cómo ocultar el código de PL/SQL

Puede el código PL/SQL ser escondido y protegido de miradas ajenas, cuando lo implantamos en alguna base de datos externa?

La respuesta a esta inquietud es SI, gracias a un ejecutable y un paquete disponible que nos permite compilar un procedimiento almacenado y hacer que el fuente quede confuso para quien intenta leerlo.
Oracle se refiere a este método como ofuscamiento "obfuscation" (el término ofuscar en el diccionario tiene el significado de "oscurecer, encubrir").

Es curioso como todavía hay empresas que desarrollan software de altísimo valor corporativo y lo implantan expuestamente en bases de datos de usuarios finales que en muchos casos tiene acceso la propia competencia. No todos en la industria son mal intencionados, pero es mejor prevenir que lamentar perder margen de ventaja.

Qué encriptar

Todo lo que sea código almacenado: procedimientos, funciones, paquetes y tipos. La excepción son los triggers, el método no los soporta, sin embargo una solución es pasar la lógica a un procedure encriptado y llamarlo desde el trigger.

Una recomendación: usemos un criterio, no seamos paranoicos.
Muy a menudo, parte de nuestro código tiene que ser compartido con otros proveedores, necesitamos disponibilizar ciertos objetos para que otros puedan construir sus propios programas a partir de ellos. No necesitamos ofuscar todos los paquetes de nuestra base de datos, solamente aquellos que tengan lógica de negocio sensible de la compañia, como algoritmos, paquetes financieros, lógica de procesamiento, mantenimiento de cuentas, paquetes de seguridad, etc.

Oracle no recomienda usar este método para encriptar contraseñas, ya que si abrimos el archivo generado, podremos ver identificadores y reconocer algunas palabras que están presentes en el código original.

Antes de comenzar

Es importante tener en cuenta que estaremos escondiendo el código de miradas ajenas y hasta de la nuestra, ya que una vez que el código está encriptado en la base de datos, no hay forma ni usuario que pueda recuperarlo. Para realizar modificaciones, hay que hacerlas sobre la versión de texto original. La recomendación es usar un manejador de versiones como repositorio de código, y luego adoptar la práctica de encriptar antes de recompilar.

Cómo encriptar

Existe un ejecutable en $ORACLE_HOME/bin que se llama wrap.
Llamándolo desde la consola, y pasándole el nombre de un script en el parámetro iname, nos retorna un archivo de texto .plb con código interno, el cual podremos compilar en SQL*plus para crear el objeto almacenado "ofuscado".

Ejemplo 1

Vamos a encriptar un procedimiento. Para ello ya tenemos el código del mismo en un archivo ob_proc.sql, copiado en el servidor de la base de datos. Todo lo que tenemos que hacer es entrar en la consola y ejecutar:

wrap edebug=wrap_new_sql iname=ob_proc.sql

PL/SQL Wrapper: Release 9.2.0.8.0- Production on Thu Aug 13 11:38:30 2009 Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.

Processing ob_proc.sql to ob_proc.plb


Nota: El flag edebug=wrap_new_sql es necesario para poder soportar el nuevo compilador de sql y corregir un bug existente con algunas sintaxis de sql avanzado.

La salida, es el archivo encriptado ob_proc.plb, el cual ahora podemos compilar en SQL*plus.

SQL> @ob_proc.plb

Procedure created.

Esta pronto. El código del procedure en la base de datos no es más legible, ya sea usando cualquier programa de desarrollo, paquete Oracle o vista del diccionario. Sin embargo es perfectamente ejecutable como cualquier otro procedimiento.

Ejemplo 2

Ahora encriptaremos un paquete. Se realiza en forma similar, recordando que el paquete se compone por especificación y opcionalmente un cuerpo. Si bien podemos encriptar ambos, se recomienda en la mayoria de los casos encriptar únicamente el cuerpo. Después de todo, es donde reside la lógica que queremos proteger. La especificación es útil muchas veces para consultar la firma de las funciones que están siendo expuestas, y es amable disponibilizarlas para el uso común.

Teniendo el cuerpo de nuestro paquete preparado en el archivo ob_pack_body.sql, ejecutamos:

wrap edebug=wrap_new_sql iname=ob_pack_body.sql

PL/SQL Wrapper: Release 9.2.0.8.0- Production on Thu Aug 13 11:38:30 2009 Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.

Processing ob_pack_
body.sql to ob_pack_body.plb

Si nuestro paquete ya estaba compilado en la base de datos, recordemos que únicamente necesitamos recompilar el cuerpo. De lo contrario, tendremos que compilar la especificación primero.

SQL> @ob_pack_body.plb

Package body created.

El código de especificación del paquete, que ya estaba compilado, continúa siendo visible, mientras que el body ahora fue ocultado y no está más disponible a la vista de todos.

Ver también:
Cómo extraer código en archivos separados

miércoles, 15 de julio de 2009

¿Cuál es el mejor tipo de datos para almacenar IPs?

Esta pregunta surgió en Oracle Wiki, foro en el cual habitualmente colaboro.

"Alguien ha desarrollado un tipo datos para IP address? Necesito que sea indexable y que pueda usar operaciones OR, AND, XOR para chequear que una IP está en el rango especificado por la red y la máscara."

Como todos sabemos, las IPs están compuestas por 4 números que van de 0 a 255, separados por un punto. Esta es una representación posible, la que hace fácil su lectura en términos de redes. Sin embargo, también podemos convertirlas a notación binaria, decimal o hexadecimal.
El modo que elijamos va a beneficiar un aspecto u otro, dependiendo el uso y aplicación que le estemos dando a la información.

Algunas formas de almacenar IPs

Hay muchas maneras de almacenar direcciones IP, y nos bastan los tipos de datos nativos que Oracle ofrece. Estas son las más comunmente utilizadas.

Notación de Punto
Es el típico formato de IP que todos conocemos.
Ejemplo: 127.0.0.1
Tipo de datos sugerido: VARCHAR2(15)
Ventajas: Fácil de leer y recuperar, listo para reportes relacionados a redes.
Desventajas: Requiere convertir para poder usar algebra booleana, no muy eficiente en uso de espacio.


Hexadecimal
Es el formato de IP removiendo los puntos y pasando cada componente a hexa.
Ejemplo:
7F000001
Tipo de datos sugerido: VARCHAR2(8)
Ventajas: Fácil de convertir a los otros modos, ocupa menos espacio que la notación de punto.
Desventajas: Requiere conversión para lectura y operaciones booleanas.


Decimal
Es la representación interna de la IP. Usado comúnmente por browsers y algunos protocolos, es la representacion decimal del formato binario.
Ejemplo:
2130706433 (equivale a 127.0.0.1)
Tipo de datos sugerido: NUMBER(10)
Ventajas: Uso de espacio muy optimizado, no requiere conversión para integración con algunos protocolos.
Desventajas: Es el formato más ilegible (para los humanos) de todos.

Nota: Multiplicando cada componente de la IP por una potencia 2^24, 2^16, 2^8 y 2^0, y sumándolos, obtendremos el entero equivalente.
Hagan la prueba de convertir una IP y colocarla en el browser!

Notación Binaria
Podemos tomar cada número de la representación por punto y convertirlo a un octeto binario, completando con ceros a la izquierda.
Ejemplo: 01111111000000000000000000000001 (equivale a 127.0.0.1)
Tipo de datos sugerido: VARCHAR2(32)
Ventajas: Listo para algebra booleana, fácil de convertir a otros formatos.
Desventajas: Poco legible, uso muy ineficiente de espacio.


Conclusiones

No hay formatos malos ni buenos, sólo que algunas opciones serán mejores que otras a la hora de decidir. La idea es minimizar ese impacto según nuestro objetivo.
  • El formato de punto es óptimo para lectura de redes, ya que con una vista rápida es posible consultar direcciones IP directamente y presentar reportes sin esfuerzo. Por su claridad, es recomendado para el desarrollo de aplicaciones que registran logs de tráfico.
  • El formato hexadecimal es una variante para intentar mejorar el uso de espacio, ya que solamente 8 bytes serán necesarios. Por otro lado, requiere convertir cada par para obtener el formato tradicional y hacerlo más comprensible en lenguaje de red.
  • El formato decimal es el más eficaz utilizador de espacio. En sistemas real-time, BD embebidas y dispositivos móviles, el uso de espacio es crítico. Nótese que el tipo sugerido ocuparía 6 bytes en el peor caso, contra 8 fijos que utiliza la forma hexadecimal.
  • Finalmente el formato binario es para programadores 'perezosos' que validan IPs, ya que no requieren convertirla. Por ejemplo, si nuestra IP es de tipo A, B o C, realizando la operación (IP) AND (Netmask correspondiente), obtenemos el ID de red. Del lado de las desventajas, ademas de ser difícil de leer, es un gran derrochador de espacio.

jueves, 9 de julio de 2009

Obtener totales con AWK

Hay veces que en nuestro trabajo somos solicitados para realizar un spool de datos (millones de registros) y finalmente informar el total de algunas columnas.

Dependiendo de varios factores como cantidad de registros, distribución de datos y relacionamientos, la ejecución de la consulta primaria para obtener los datos, puede ser costosa. Un tiempo similar puede tomarnos ejecutar la consulta para obtener el total de las columnas.

Sin embargo, el uso de AWK para obtener los totales, puede ser un diferencial. La performance de esta operación en el archivo de texto sobre la ejecución de un SQL (usando sum y group by) puede ser abismal (de muchos minutos a un par de segundos).

Ejemplo: como obtener el total de la segunda y tercera columna de un archivo spool con millones de registros y valores separados por pipe '|'.

awk -F"|" '{sum1+=$2; sum2+=$3} END {print sum1,sum2}' archivo_entrada.csv

Primero en la sección de procesamiento línea a línea (primer par de { }) sumamos los valores de la segunda y tercera columna ($2, $3) en dos variables, sum1 y sum2, por defecto inicializadas en cero.

Finalmente la sección de ejecución final muestra los valores de sum1 y sum2.

Si deseamos usar otro delimitador bastará con alterar el parámetro -F.

Así de simple y eficiente.

viernes, 12 de junio de 2009

Ordenar resultados por el alfabeto castellano

Por defecto, el ordenamiento de una consulta SQL en Oracle se rige según el valor numérico del mapa de caracteres, típicamente: A, B, C, D, E , F, G, H, I, etc. Este método, que recibe el nombre de ordenamiento binario, es el método más eficiente y conveniente para ordenar palabras en inglés.

Sin embargo, las palabras del castellano no serán ordenadas correctamente, y muchas veces debemos respetar las reglas de nuestro idioma (aunque la popularidad del inglés diga lo contrario).

Por ejemplo si listamos países, Chile aparecerá antes que Colombia, cuando la letra 'CH' debería suceder a la 'C'. Similarmente la palabra lluvia aparecerá antes que lotería, cuando no deberíamos ignorar que la 'LL' viene despues de la 'L'. Finalmente recordar que nuestra original letra eñe no puede ser dejada de lado.

Para ordenar usando las letras del español, bastará con alterar la sesión:

ALTER SESSION SET nls_sort=spanish_m;

Ejemplo

SQL> select * from paises order by nombre;

NOMBRE
------------
Camerún
Canadá
Chile
Colombia
Congo
Cuba

SQL> alter session set nls_sort=spanish_m;

Session altered

NOMBRE
------------
Camerún
Canadá
Colombia
Congo
Cuba
Chile

Acentos y mayúsculas

Otro efecto de setear el parametro nls_sort, es que suprimimos la precedencia de palabras acentuadas sobre no acentuadas, así como de mayúsculas sobre minúsculas.

Ejemplo 1: Problema de acentos

SQL> select letra from mialfabeto order by letra;

LETRA
--------
a
b
c
á

SQL> alter session set nls_sort=spanish_m;

SQL> select letra from mialfabeto order by letra;

LETRA
--------
a
á
b
c

Ejemplo 2: Problema de mayúsculas que anteceden a minúsculas

SQL> select texto from conceptos order by texto;

TEXTO
--------------
Integrado
distribuído
integración

SQL> alter session set nls_sort=spanish_m;

SQL> select texto from conceptos order by texto;

TEXTO
--------------
distribuído
integración
Integrado

miércoles, 13 de mayo de 2009

Un autodeadlock y algunas buenas prácticas

Este deadlock sí que es eficiente. Poco esfuerzo de programación, una única sesión y una espera de un recurso siendo usado por... uno mismo!

CREATE OR REPLACE PROCEDURE deadlock_suicida AS
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE deadlock_suicida';
END;
/

Procedure created.

SQL> exec deadlock_suicida;
Deadlock más comunes
El ejemplo de deadlock más frecuente es cuando dos sesiones intentan modificar registros de una tabla que la otra sesión está usando, y terminan esperando a que la otra libere primero. Un deadlock no ocurre cuando las operaciones son de lectura, sino que tienen que existir operaciones que produzcan locks implícitos o explícitos.

Los deadlocks no son un defecto de Oracle, sino que reponden a carencias de conocimiento de los mecanismos de locking por parte del programador, que no ha tenido en cuenta que su código es ejecutado por múltiples sesiones que comparten los recursos concurrentemente.

Afortunadamente, este problema es automáticamente manejado por Oracle. Cuando dos sesiones se encuentran bloqueadas, una de ellas recibirá el mítico ORA-00060 y será finalizada con rollback. La otra sesión sufrió algunos minutos de bloqueo, pero continuará ejecutando normalmente. Como es una duda común, aclaro que no existe manera alguna de ajustar el tiempo de timeout.

Cómo evitar deadlocks
No existen recetas infalibles para evitar los deadlocks sino buenas prácticas. Deshacerse de un deadlock puede ser muy trabajoso si no se conoce a fondo la aplicación que lo produjo. Requiere de un análisis exaustivo para determinar como los registros son obtenidos y liberados por una sesión, y luego extrapolar sobre un escenario de dos sesiones concurrentes. El primer paso de análisis, debe ser analizar la sesión generada en el directorio de dump de usuarios ubicada en $ORACLE_HOME/admin/udump.

Algunas buenas prácticas
1) Si no se trata del mismo código que generó deadlock, tratar que los registros de todas las tablas sean obtenidos en el mismo orden por las aplicaciones en conflicto.
2) Intentar obtener los recursos más exclusivos que van ser modificados en primer lugar. Puede usarse SELECT FOR UPDATE para este propósito. Es aconsejable tener nuestra aplicación bien tuneada de forma de no bloquear a todas las otras sesiones y enlentecer nuestro sistema.
3) Por ejemplo si vamos a actualizar tablas padre-hijo, podemos tomar la práctica de siempre bloquear primero la tabla padre.

miércoles, 1 de abril de 2009

Cómo crear una vista parametrizada

En Oracle, podemos crear vistas que retornen resultados dependientes de parámetros previamente seteados. La forma de lograr esto es usando un feature llamado Application Contexts.

¿Qué es un contexto de aplicación?
El contexto de aplicación es simplemente un espacio en memoria que nos permite almacenar valores para luego utilizarlos en SQL o PL/SQL, como cualquier otra variable definida en el entorno. De forma transparente, mis objetos pueden ser 'manipulados' externamente sin necesidad que mis aplicaciones o procesos batch se enteren.
El contexto puede ser definido tanto localmente (privado para cada sesión) como globalmente, compartiendo sus valores para todas las sesiones de la instancia.
Para poder alterar los valores del contexto, debemos crear un paquete especialmente autorizado para ese fin. Esto es un requerimiento por razones de seguridad.
También necesitamos tener el permiso especial de sistema CREATE ANY CONTEXT.

Ejemplo (con sqlplus)

Vamos a ver un sencillo ejemplo de cómo implementar una vista parametrizada con contextos de aplicación, usando el popular usuario SCOTT. El parámetro para la vista en este caso será el número de departamento.

Paso 1: Crear el contexto de aplicación
CREATE CONTEXT app_ctx_scott USING pk_scott_app_context
/
Paso 2: Crear el paquete para manipular el contexto
CREATE OR REPLACE PACKAGE pk_scott_app_context AS
-- El contexto tendra un unico valor deptno
PROCEDURE set_dept (p_deptno IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pk_scott_app_context AS
PROCEDURE set_dept (p_deptno IN NUMBER) IS
BEGIN
DBMS_SESSION.SET_CONTEXT('app_ctx_scott', 'deptno', p_deptno);
END;
END;
/
Paso 3: Setear el parámetro de contexto deptno para el departamento de ventas
BEGIN
pk_scott_app_context.set_dept(30);
END;
/
Paso 4: Crear la vista parametrizada
CREATE VIEW empleados AS
SELECT e.empno, e.ename, e.job, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.deptno = sys_context('app_ctx_scott','deptno');
Paso 5: Obtener los resultados consultando la vista
SELECT * FROM empleados;

EMPNO ENAME JOB DNAME
---------- ---------- --------- --------------
7499 ALLEN SALESMAN SALES
7521 WARD SALESMAN SALES
7654 MARTIN SALESMAN SALES
7698 BLAKE MANAGER SALES
7844 TURNER SALESMAN SALES
7900 JAMES CLERK SALES

6 rows selected.
Actualmente la vista retorna los empleados del departamento de ventas, ya que así está definida la variable en el contexto. Ahora cambiaré el valor del parámetro para que la vista retorne resultados únicamente del departamento contable:
BEGIN
pk_scott_app_context.set_dept(10);
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM empleados;


EMPNO ENAME JOB DNAME
---------- ---------- --------- --------------
7782 CLARK MANAGER ACCOUNTING
7839 KING PRESIDENT ACCOUNTING
7934 MILLER CLERK ACCOUNTING

3 rows selected.

De la misma forma podemos aplicar esta técnica a procedimientos y funciones, pudiendo manipular valores utilizados internamente o inclusive introduciendo fragmentos de código como sql dinámico.

domingo, 1 de marzo de 2009

Migrando MySQL a Oracle con SQL Developer

Siempre que evaluamos herramientas para realizar esta tarea, debemos considerar variables tales como rapidez, facilidad de uso y bajo costo, entre otras.

SQL Developer es un producto de costo cero, que permite migrar bases de datos MySQL, Access o SQL Server a Oracle con facilidad.
En este tutorial mostraré el método de migración rápida de esquemas MySQL 5 a Oracle XE, usando la versión 1.5.3 de SQL Developer.

Preparación del software

- Descargar e instalar SQL Developer:
La última versión está disponible en OTN.

- Instalar el plugin de conexión a MySQL:
Para esto, desde SQL Developer vamos al menú Ayuda, Verificar Actualizaciones, marcamos la casilla Third Party SQL Developer Extensions y descargamos el driver de conexión a MySQL. SQL Developer deberá ser reiniciado para que el plugin entre en efecto.

NOTA: Si la extensión no nos aparece entre las opciones de descarga, es posible que ya la tengamos instalada. Para verificar, vamos al menú Ayuda, Sobre, Extensiones, y verificamos que exista la entrada MySQL JDBC Driver.

Crear un usuario para la migración

El usuario de migración será el encargado del proceso de colección, transformación, carga y movimiento de datos, y necesitará un conjunto de permisos especiales para tales efectos.

Para crearlo, loguearse a sqlplus con el usuario system y ejecutar:

CREATE USER migration IDENTIFIED BY xxxxxxx DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp; GRANT create session, create view, resource, create user, create role, alter any trigger TO migration WITH ADMIN OPTION;

Crear las conexiones

- Crear una conexión para el esquema MySQL a ser migrado:
Colocar un nombre para la conexión y el usuario MySQL (en el ejemplo será root, sin password). Luego seleccionar la lengueta MySQL para verificar el hostname (o IP del servidor) y el puerto (generalmente 3306).
Testeando la conexión, el resultado debe ser SUCCESS para poder continuar, de lo contrario revisar el nombre del servidor Apache, usuario de MySQL, y el puerto configurado en el archivo de configuración my.ini.

- Crear una conexión Oracle para el repositorio y el esquema destino:
Similarmente como hicimos para MySQL, crearemos una conexión para nuestra base de datos destino, en este caso XE. Configurar el usuario system con su password, y el nombre del servicio de nuestra base de datos.
Como en el caso anterior, testear la conexión.

Una vez creadas, ambas conexiones deberán aparecer en el menú vertical izquierdo. A partir de ellas podremos ver la definición de los objetos antes y después de la migración.



Es recomendado crear una conexión separada para el repositorio y otra para el esquema destino, la idea es que sean usuarios diferentes. Para simplificar este ejemplo utilizaremos el mismo esquema para las dos cosas.

Crear un repositorio

SQL Developer usa un Repositorio para almacenar los packages y datos temporales para validar y convertir nuestros objetos. Si no lo creamos, el propio wizard lo creará automáticamente, pero siempre es mejor crearlo antes y verificar que quede todo correctamente instalado.

Vamos a la conexión de Oracle recientemente creada y con el botón derecho seleccionamos Migration Repository, y Associate Migration Repository.

En un minuto, el repositorio estará creado. Podemos verificar que las tablas y vistas del repositorio fueron creadas, con prefijo 'MD_' y 'MGV_' respectivamente. También es bueno verificar que estén correctamente compilados los 4 packages: MD_META, MIGRATION, MIGRATION_REPORT y MIGRATION_TRANSFORMER.

Iniciar el asistente

Teniendo todos los pasos previos completados, comenzamos con la migración propiamente dicha. Vamos al menú Migration, Quick Migrate, y seleccionamos la conexión de la base de datos orígen, es decir MySQL.

Si nuestra conexión fue correctamente creada, nos pide la conexión destino. Colocaremos la conexión XE creada para recibir los objetos.

El siguiente paso es la verificación del repositorio a utilizar. Debe aparecer el mensaje OK: Using [nombre conexión].

El cuarto paso es la verificación de pre-requisitos. Luego de presionar el botón Verify, una serie de tests son ejecutados, que incluyen conectividad de fuente y destino, y permisos de usuario. Cualquier observación que Oracle levante aquí tiene que ser resuelta antes de poder avanzar.

Luego que todos los pasos de la verificación de pre-requisitos resultan en SUCCESS, seguimos.

Finalmente debemos elegir el tipo de migración entre MIGRATE TABLES ONLY, MIGRATE TABLES AND DATA o MIGRATE EVERYTHING.

En este caso elegiremos migrate everything: usuarios, tablas, datos, índices, constraints, vistas y código.

Al presionar Finalizar, cruzaremos los dedos.

Resultados de la migración

La imagen a la izquierda muestra el resultado final de cada stream que realiza el movimiento final en paralelo, junto con la cantidad de filas migradas y errores sucedidos. Es de esperar no encontrar ningún error aquí!

Durante la migración, podemos pasar todas las etapas sin inconvenientes - el caso ideal- o, podemos encontrar errores generalmente durante la ejecución (Build) de los DDLs generados, lo cual abortará el proceso. La verdad verdadera, es que no siempre lograremos terminar exitosamente la migración en la primera vez, solamente si la base de datos orígen respeta ciertas reglas en su definición.

Diversos errores de conversión pueden ocurrir desde que MySQL permite amplia libertad de declaraciones fuera del ANSI/ISO SQL standard (además de que MySQL tiene varias extensiones propias de SQL).

En caso de error, debemos leer el dump de la ejecución y analizar de qué tipo fue la falla. Una vez resuelto, eliminaremos los objetos 'sucios' creados y volveremos a ejecutar el asistente.

Este proceso de ensayo y error puede ser repetitivo, y es común en la gran mayoría de las migraciones hasta que finalmente logramos una ejecución limpia. Necesitaremos paciencia y perseverancia para llegar al objetivo.

Tareas post-migración

Luego de la migración, es recomendable verificar que cada tabla fue copiada correctamente. Revisar como fueron transformados los datos, verificar los campos de tipo fecha o numéricos, o aquellos que tienen valores por defecto.

Es también un buen momento para reforzar la integridad del esquema con todas aquellas mejoras que Oracle ofrece como foreign keys, constraints, e índices. MySQL es poco restrictivo en ese aspecto, y eso puede generar vicios para algunos programadores.
Por ejemplo, el hecho de que las fks y transacciones son soportadas solamente en tablas InnoDB. En este tipo de escenarios, son las aplicaciones las responsables de forzar la integridad de datos, y no siempre logran ese objetivo.

Cuidado con aplicar restricciones sin analizar previamente su impacto, ya que podremos estar causando un mal peor del que queremos remediar. Un ejemplo común, es el uso de claves referenciales fantasma (o dummy) en la aplicación (sin clave valida en la tabla padre). Tendremos que evaluar si al incorporar las constraints estaremos afectando a la aplicación que tiene implementada esta práctica.

Una crítica que encuentro oportuna, es el hecho de que para cada campo autonumérico de MySQL, SQL Developer crea una secuencia y un trigger asociado para 'simular' la asignación automática. Entiendo que está a favor de la transparencia, pero por otro lado me parece una decisión interesante en términos de diseño, y me gustaría que fuera un feature opcional para tener el control.

De la misma forma, otra opción 'indeseada' para mí, es que genera un trigger por cada campo enumerado ('1', '2', '3',..) para asegurar la asignación de valores. Desearía que SQL Developer creara check constraints en lugar de estos triggers que sólo empeoran la performance y limitan la escalabilidad de las aplicaciones.

En conclusión

Por la facilidad de operación, visibilidad gráfica de cada una de las etapas, bajo costo -y pese a las críticas que he expuesto-, recomiendo que le den una oportunidad a SQL Developer como opción para migrar de MySQL a Oracle.

viernes, 20 de febrero de 2009

Soporte Oracle con VMWare?

Oracle establece según nota 249212.1 en Metalink, que no ha certificado ningún producto corriendo virtualizado con VMWare. Si un problema determinado ocurre y el usuario necesita soporte oficial Oracle, deberá probar que el error no se debe a estar corriendo bajo VMWare, por ejemplo reproduciéndolo bajo el sistema operativo nativo. Esto sin dudas complica la existencia de los administradores de sistemas y bases de datos, que deben preparar ambientes no virtualizados para intentar capturar el mismo error. En ese proceso, el tiempo puede ser el peor enemigo.

Desde el punto de vista del soporte, es entendible y sensato que Oracle no se responsabilice por productos de terceros como VMWare. Desde el punto de vista comercial, es una posición conveniente en tiempos que Oracle invierte en marketing para su propia infraestructura de virtualización (Oracle VM). Los potenciales compradores de una solución virtual deberán evaluar con especial cuidado el soporte que tendrán corriendo una base de datos Oracle virtualizada. Una decisión segura llevará a adoptar el producto que le garanta la máxima cobertura de soporte.

VMWare, INC comienza a experimentar un crecimiento desacelerado y ya tiene sus primeras bajas en la guerra de la competencia con Microsoft, Citrix, Sun, Oracle y otros. Su presidente ejecutivo fue recientemente demitido, y para empeorar los pronósticos, viene de recibir un duro golpe en el mercado de valores luego que sus acciones cayeran un 11%, pese al crecimiento del 53% en sus ganancias en el último balance de 2008.

jueves, 5 de febrero de 2009

Error al crear usuario en Oracle TimesTen

Cuando se intenta crear un usuario en Oracle TimesTen, se obtiene el siguiente error:

Command> create user lferTTadmin identified by '$ql450';
15007: Access control not enabled
The command failed.

Durante la instalación de TimesTen 7.0, se preguntó al usuario si se deseaba activar el access control (Do you want to enable Access Control? Yes/No). Si No fue la respuesta, aún hay una forma de poder activarlo 'post instalación'.

Abrir una consola del sistema operativo, y ejecutar:
ttmodinstall -enableAccessControl

Luego de eso, el control de acceso estará habilitado.

C:\Windows\system32>ttmodinstall -enableAccessControl

C:\Windows\system32>"C:\TimesTen\tt70_32\perl\bin\perl.exe" "C:\TimesTen\tt70_32
\bin\ttmodinstall" -enableAccessControl
Would you like to enable access control for this instance? [ no ] yes
NOTE: The daemon must be stopped before enabling access control.
Would you like to stop the daemon? [ yes ] yes
The TimesTen Data Manager 7.0 service is stopping...
The TimesTen Data Manager 7.0 service was stopped successfully.

Patching successful ...
Restarting the daemon ...
The TimesTen Data Manager 7.0 service is starting.
The TimesTen Data Manager 7.0 service was started successfully.

Access control is now enabled for this TimesTen instance.
C:\Windows\system32>ttisql TT_test

Copyright (c) 1996-2008, Oracle. All rights reserved.
Type ? or "help" for help, type "exit" to quit ttIsql.
All commands must end with a semicolon character.

connect "DSN=TT_dns_prod04";
Connection successful: DSN=TT_test;UID=sixbell;DataStore=C:\Users\dashboard\Deskto
p\temp\TT_store;DatabaseCharacterSet=AL32UTF8;ConnectionCharacterSet=AL32UTF8;DR
IVER=C:\TimesTen\tt70_32\bin\ttdv70.dll;TypeMode=0;
(Default setting AutoCommit=1)
Command> create user lferTTadmin identified by '$ql450';
Command>

Parches de seguridad Enero 2009

Oracle ha anunciado un nuevo Critical Patch Update que afectan a la mayoría de sus productos, y recomienda fuertemente su aplicación en todos los ambientes, para evitar la explotación de accesos indebidos.
El set incluye 20 patches para Oracle Database (a partir de 9i Release 2), 4 para Application Server (a partir de 10g Release 2), 1 para Collaboration Suite (a partir de 10g), 4 para Applications Suite (a partir de 11i), 1 para Enterprise Manager (a partir de 10g Release 4), 6 para PeopleSoft y JDEdwards Suite (a partir de 8.9), y 5 para BEA Products Suite (a partir de 7.0).
Desde luego que el download está disponible en OTN.

Ver también
Critical Patch Update Advisory - January 2009

miércoles, 4 de febrero de 2009

Cómo pasar valores desde sqlplus hacia Unix Shell

Hay ocasiones que dentro de un Shell script queremos consultar la base de datos y con dicho resultado continuar la lógica de nuestro Shell. Como logramos transferir ese resultado?

Tenemos dos enfoques para capturar valores luego de terminada la ejecución de Sql*plus: por archivos y por variables.

La primera de ellas es básicamente escribir la salida de la consulta en un archivo y luego levantarlo desde Shell. Este caso es ideal cuando nuestra consulta retorna varios registros, ya que el archivo servirá de entrada a algún comando Unix para procesarlo línea a línea. No veremos un ejemplo ya que es muy sencillo, basta usar el comando SPOOL de Sqlplus para obtener la salida en un archivo de texto. Si bien no es del todo prolijo, funciona.

Y cuando digo prolijidad, me refiero a que en realidad generar un archivo temporal no es necesario, ya que podemos recoger el resultado de la consulta en una variable Shell directamente. Lo veremos con algunos ejemplos hechos para Solaris 10. Algunos parámetros pueden variar respecto a Linux, se recomienda acudir a la documentación de man.

Resultado único
Supongamos que quiero leer el contenido de la columna Dummy de la tabla Dual y continuar manipulando el valor en Shell.

Unix lo hace fácil: ejecutamos la consulta en una sesión sqlplus, y la salida es capturada en la variable 'resultado'. Para simplificar este ejemplo no he considerado si hubo algún error en la consulta, pero puede agregarse lógica que trate mensajes de error dentro de la variable asignada.

#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
select * from dual;
exit;
EOF`

echo "El resultado es: $resultado"

Varios resultados en una línea
Qué hacer si necesitamos retornar más de un valor? Continuamos retornando una única línea, pero separamos los valores dentro del sql con algún caracter que no ocurra dentro de cada resultado, por ejemplo punto y coma. En el ejemplo obtenemos el usuario conectado, la fecha actual y el valor de la columna de Dual, todo al mismo tiempo.
#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
select user||';'||sysdate||';'||dummy from dual;
exit;
EOF`

echo "Los valores son: $resultado"
El valor retornado es: SCOTT;05-FEB-09;X

Con el comando cut separamos los valores fácilmente:

echo $resultado | cut -d';' -f1
SCOTT
echo $resultado | cut -d';' -f2
05-FEB-09
echo $resultado | cut -d';' -f3
X

Resultados multilínea
Si queremos obtener varias líneas en lugar de una sola, también podemos hacerlo de esta forma. Como en el caso anterior, si tenemos múltiples valores por línea es aconsejable usar separadores, ya que los espacios no son buenos a la hora de identificar strings que contengan espacios. Además, evitamos los incómodos espacios entre líneas al optimizar el tamaño de cada cadena y no llegar al fin de cada línea.

#!/bin/ksh
resultado=`sqlplus -s 'scott/tiger' << EOF
set serveroutput on
set feedback off
set head off
set linesize 131
set pagesize 9999

select empno||';'||ename||';'||job||';'||mgr||';'||deptno from emp;
exit;
EOF`

echo "El resultado es: $resultado"


La salida en este caso es:

7369;SMITH;CLERK;7902;20
7499;ALLEN;SALESMAN;7698;30
7521;WARD;SALESMAN;7698;30
7566;JONES;MANAGER;7839;20
7654;MARTIN;SALESMAN;7698;30
7698;BLAKE;MANAGER;7839;30
7782;CLARK;MANAGER;7839;10
7788;SCOTT;ANALYST;7566;20
7839;KING;PRESIDENT;;10
7844;TURNER;SALESMAN;7698;30
7876;ADAMS;CLERK;7788;20
7900;JAMES;CLERK;7698;30
7902;FORD;ANALYST;7566;20


Ahora, podemos usar cualquier comando para tratar las líneas. Uno de mis favoritos es awk, ya que nos provee de muchas funciones para tratamiento de cada tipo.
Para que awk consuma cada línea de la variable como si fuese un archivo, debemos incluir comillas dobles, de otro modo lo considerará como una única gran línea.
En el siguiente ejemplo, vemos como awk toma línea a línea e imprime un texto anexo.

echo "$resultado" | awk -F";" 'BEGIN {$cnt=1}
{print "Linea "$cnt, $0; $cnt=$cnt+1;}'


La salida generada por awk es:

Linea 1 7369;SMITH;CLERK;7902;20
Linea 2 7499;ALLEN;SALESMAN;7698;30
Linea 3 7521;WARD;SALESMAN;7698;30
Linea 4 7566;JONES;MANAGER;7839;20
Linea 5 7654;MARTIN;SALESMAN;7698;30
Linea 6 7698;BLAKE;MANAGER;7839;30
Linea 7 7782;CLARK;MANAGER;7839;10
Linea 8 7788;SCOTT;ANALYST;7566;20
Linea 9 7839;KING;PRESIDENT;;10
Linea 10 7844;TURNER;SALESMAN;7698;30
Linea 11 7876;ADAMS;CLERK;7788;20
Linea 12 7900;JAMES;CLERK;7698;30
Linea 13 7902;FORD;ANALYST;7566;20
Linea 14 7934;MILLER;CLERK;7782;10

viernes, 30 de enero de 2009

Oracle SQL Developer Data Modelling

Los días de Oracle Designer 10g están acabando. El cásico paquete de diseño, versionamiento e implementación de bases de datos, tiene un jóven sustituyo acorde a los tiempos que corren: Oracle SQL Developer Data Modelling.

Esta herramienta de nombre largo es totalmente gratuita, profesional y actualizada, y su propósito es quitarle el mercado que otros diseñadores compactos como Erwin o Toad Data Modeller han ganado.

Es que la comunidad Oracle reclamaba hace tiempo las limitaciones de la interfase, que no había cambiado mucho desde la versión Designer/2000 de 1995, y prácticamente nada desde 9i para 10g. También, la ubicación geográfica de los desarrolladores era un problema cada vez que debían movilizarse lejos del repositorio que Designer require para funcionar.

Oracle SQL Developer Data Modelling simplemente se copia en una carpeta y no require de instalador. Es un producto separado disponible para Windows, Linux y Mac.
Se conecta con Oracle a partir de 9.2.0.1, no necesita un repositorio (aunque opcionalmente puede usarlo), y tiene una abanico de funcionalidades muy atractivas: modelos lógico, relacional y físico, ingeniería reversa desde otras bases de datos, diseño multidimensional de tablas para DW, data flow diagrams, modelado gráfico de Oracle types, importación desde otros modeladores como Erwin, importación por ODBC, exportación a ddl, csv, xml, Oracle AW.

Mi impresión sobre la herramienta es muy buena. Luego de un buen tiempo cuesta dejar algunos condicionamientos que el arcaico Designer impone para sobrellevar los problemas de su propia interfase, pero parece que con Oracle Data Modelling no son más necesarios.
Uno típico era el nombrado automático de objetos, Designer tenía una forma particular de autogenerar nombres de FKs e índices, y cada vez que se generaban había que arreglarlos. Oracle Data Modeller permite personalizar todos esos prefijos que usa para ingeniería reversa, o pasaje de modelo lógico para relacional, facilitando el cumplimiento de estándares. Por ejemplo para la generación de índices puedo definir los nombres como: IX_{table}_{seq nr}.
El Navigator (pequeño cuadro que muestra un mapa de todo el modelo) es otra buena innovación que aporta navegabilidad en diagramas grandes.
Es posible especificar una vasta variedad de propiedades sobre cada objeto, para mejorar la generación de código final y evitar tener que repasar los ddls generados.
También cuenta con un analizador de impacto, para anticipar la propagación de cambios en el modelo existente.
La integración por ODBC permite conectarse con prácticamente cualquier base de datos existente.
La mayor ventaja de todas es, como dije anteriormente, que es gratuito.

Oracle SQL Developer Data Modeller está proyectado para ser lanzado sobre el correr de 2009, pero una serie de versiones beta están siendo publicadas por Oracle para evaluación.
La versión Early Adopter Release 2 (Nov 2008) está disponible para descargar en OTN.

ACTUALIZACIÓN (01-mar-2010): Este articulo comenta sobre la versión Early Adopter R2, la cual no está más disponible. Las versiones comerciales actuales no son gratuitas. Ver comentarios abajo.

Ver también:
OTN SQL Developer

viernes, 23 de enero de 2009

Combinando Microsoft Access y Oracle

En este artículo vamos a ver cómo implementar una sencilla aplicación que trabaje sobre una base de datos Oracle para actualizar datos y generar reportes, usando una herramienta amigable y disponible en la mayoría de los ordenadores como lo es Microsoft Access.

La idea es, en 5 pasos crear una interfase gráfica con poco esfuerzo y en tiempo récord, que facilite la vida de muchos usuarios no IT, como por ejemplo la del Gerente del Area de Recursos Humanos que necesita ingresar y consultar datos en su base de datos corporativa. No hay necesidad de comprar un caro producto comercial para esa sencilla tarea.

Qué software necesitamos para desarrollar y ejecutar nuestra aplicación?

- Microsoft Access, sino nuestro archivo .mdb no será reconocido por Windows.
- Un cliente Oracle, para que podamos conectarnos con la base de datos de destino.

(Este software debe estar presente ya sea en la máquina que usemos para desarrollar como en la máquina destino donde va a correr el programa. No está en el alcance de este artículo explicar cómo instalar estos dos productos)

Los 5 pasos para crear la aplicación

1) Crear una conexión ODBC
2) Vincular en Access las tablas de Oracle
3) Crear las relaciones existentes
4) Crear formularios
5) Crear un reporte

Nuestra aplicación va a conectarse y trabajar con el usuario Oracle SCOTT. Uso Windows Vista, Access 2003 y un cliente Oracle 10g.

Disculpas por las pantallas de Access en portugués y Windows en inglés, pero es todo lo que tengo en la vuelta :)

Paso 1. Crear una conexión ODBC

- En Windows, vamos al menú Start, Control Panel, Administrative Tools y seleccionamos Data Sources (ODBC)

- En la lengueta User DNS, presionar sobre el botón Add. Una lista de drivers disponibles aparecerá en un diálogo. Tenemos dos posiblidades para seleccionar, una es elegir el driver de Microsoft para Oracle (Microsoft ODBC for Oracle), y la otra es elegir el driver de Oracle que fue instalado con nuestro cliente (en mi caso Oracle em OraClient10g). Recomiendo usar el driver nativo de Oracle, ya que está optimizado y se corresponde exactamente con la versión de Oracle que acabamos de instalar.

Nota: En caso que se nos presente un mensaje de error con el título SQORAS32 y el mensaje An unsupported operation was attemped, se trata de un bug conocido cuya solución está en Metalink nota NR.5699495. Será necesario descargar un patch y reemplazar algunos archivos en el cliente Oracle. Es aplicable a los clientes 10.2.0.3.0 a 10.2.0.3.7 y las instrucciones vienen con el propio patch.

- En el diálogo siguiente, ingresar los datos de la conexión:

Data Source Name: Un nombre cualquiera describiendo nuestra conexión
Description: Opcional, puede ser nada
TNS Service Name: El mismo nombre de servicio en nuestro tnsnames.ora
User ID: Opcional, puede ser nada

- Pulsar Test Connection para ver si nuestra conexión funciona. Aparecerá un diálogo solicitando usuario y password. En mi caso colocaré scott/tiger. El mensaje Connection successfull deberá aparecer para poder continuar. De lo contrario, revisar el tnsnames.ora que está siendo usado, para ver si tiene la entrada correcta.



Luego de sele
ccionar OK en el diálogo principal, nuestra conexión ODBC está lista para ser usada.

Paso 2. Vincular en Access las tablas de Oracle

- Ingresar a Access y comenzar un nuevo proyecto en blanco.

- En el menú Insertar, elegir Tabla y Vinculación de tabla.

Aparecerá un diálogo como los de seleccionar archivos, pero tenemos primero que elegir en el combo Tipos de archivos, la opción Bases de datos (ODBC).





Luego se muestra el diálogo Select Data Source. Ir a la lengueta Machine Data Source y seleccionar la conexión ODBC creada en el paso anterior.

Somos solicitados para informar el usuario y password de la base de datos Oracle (scott/tiger).





Observar que se nos presentan todas las tablas y vistas a las que el usuario scott tiene acceso. Buscar aquellas que comienzan con el prefijo SCOTT y marcarlas: BONUS, DEPT, EMP y SALGRADE. Seleccionar OK.

- Access solicitará indicar para algunas tablas las columnas identificadoras (clave primaria). Eso dependerá si la clave está definida en la tabla original o no. En este caso vamos a ignorar el aviso simplemente seleccionando OK.

Listo! Las tablas ya están vinculadas en nuestro proyecto. Notar que el nombre de cada tabla asignado automáticamente por Access es [ESQUEMA]_[TABLA].

Paso 3. Crear las relaciones

Cuando se vinculan las tablas del esquema Oracle en Access, no se crean los relacionamientos entre ellas. Es necesario hacerlo manualmente, si queremos mantener el esquema de integridad de datos.

- En el menú Herramientas, ingresar a Relaciones.

- En el diálogo Mostrar Tabla, en la lengueta Tablas, seleccionar (presionando Control) las tablas SCOTT_EMP y SCOTT_DEPT, y pulsar Agregar. Las tablas se mostrarán en la ventana de relacionamientos, y podremos cerrar el diálogo Mostrar Tabla.

- Si la tabla SCOTT_EMP no muestra todas las columnas, agrandarla desde el vértice inferior derecho para permitir ver la columna DEPT.

- Hacer click (izquierdo) sobre la columna DEPT de SCOTT_EMP, y sin soltar, arrastrar la columna hacia la tabla SCOTT_EMP, soltándola encima de la columna DEPTNO (clave primaria de SCOTT_DEPT). Un nuevo diálogo se abrirá con la definición de la relación.



- Podemos elegir las propiedades de esta relación, en nuestro caso vamos a dejar todo por defecto, que corresponde a un INNER JOIN entre ambas tablas. Luego de seleccionar Crear, la relación entre las tablas será visible gráficamente en pantalla.

- Cerrar la ventana de relacionamientos y elegir Sí para guardar los cambios.


Paso 4. Crear formularios

Tenemos las tablas vinculadas en Access con sus relaciones, y de aquí para adelante depende exclusivamente de nuestros conocimientos en Access. Da lo mismo que sean tablas propias de Access o de Oracle con las que estemos trabajando. Podemos inclusive usar este mismo esquema para importar datos desde una tabla Oracle a una local.

Crearemos un formulario para cada una de las tablas importadas, para facilitar el mantenimiento de los datos a través de la aplicación. Explicaré cómo hacer los formularios de DEPT y EMP; para el resto es el mismo procedimiento.

- En la ventana principal de Access, ir a Formularios, y elegir la opción Crear formulario usando el asistente.

- Seleccionar la tabla SCOTT_DEPT en el combo de tablas, y seleccionar todos los campos disponibles (DEPTNO, DNAME, LOC) con el botón >>.

- Presionar Avanzar, elegir el layout 'Columna', el estilo 'Standard', y escribir el título 'Departamentos'. Seleccionar Concluir.

Ya está listo nuestro primer formulario donde daremos mantenimiento a los departamentos. Por defecto, todas las operaciones sobre los datos están permitidas. Si se desea, se pueden alterar las propiedades del formulario activando la caja Propiedades con Alt-Enter.


Ahora vamos al formulario de empleados.

- En la ventana Formularios, elegir la opción Crear formulario usando el asistente.

- Seleccionar la tabla SCOTT_EMP en el combo de tablas, y seleccionar todos los campos disponibles con el botón >>.

- Elegir las mismas opciones que en la tabla anterior, excepto por el título 'Empleados'.

Nuestro formulario de empleados está listo, pero vamos a introducir una pequeña mejora: que el campo DEPTNO en lugar de un texto numérico sea un combo list con los nombres de los departamentos, para evitar tener que recordar los números.

- Seleccionar en el menú Exibir, el modo Diseño.

- Marcar el campo DEPTNO en el formulario y borrarlo (pulsando la tecla DELETE)

- En la caja de herramientas (lateral izquierda) seleccionar la herramienta Caja de Combinación.



- Crear el combo en el lugar que eliminamos el campo numérico DEPTNO. Se abrirá automáticamente el asistente para caja de combinación.

- Seleccionar la primera opción para que el combo busque los datos a partir de una consulta y Avanzar.

- Elegir la tabla SCOTT_DEPT y Avanzar.

- Seleccionar los campos DEPTNO y DNAME y Avanzar

- Definir el orden de ordenamiento por DNAME (creciente) y Avanzar.

- Dejar seleccionado el checkbox Ocultar llave primaria y Avanzar.

- Dejar la opción Recordar valor para uso posterior y Avanzar.

- Si se desea modificar el rótulo, puede alterarse para 'DEPTO'.

- Concluir la operación.

Terminamos nuestro formulario para dar mantenimiento a los empleados.
Ahora nuestro campo de departamentos se actualiza dinámicamente a partir de la tabla SCOTT_DEPT.




Repetir el proceso de creación de formularios para el resto de las tablas, y estaremos aptos para insertar, borrar y alterar registros completamente a través de nuestra interface gráfica.

Paso 5. Crear un reporte

Crearemos un reporte para mostrar un ejemplo más de funcionalidad.

- En la ventana principal de Access, ir a Consultas, y elegir la opción Crear consulta en modo diseño.

- Cerrar la ventana Mostrar tabla y en el menú Exibir seleccionar SQL.

- Escribir la query:
SELECT e.empno, e.ename, e.job, e.sal, d.dname, s.grade
FROM scott_emp e, scott_dept d, scott_salgrade s
WHERE e.deptno=d.deptno
AND e.sal BETWEEN s.losal AND s.hisal
- Cerrar la ventana query y elegir Sí para grabar los cambios, asignando un nombre cualquiera para la consulta, por ejemplo 'Salarios de empleados por departamento'.

- En la ventana principal de Access, ir a Reportes, y elegir la opción Crear reporte usando el asistente.
- En el combo Tablas/Consultas elegir la consulta creada anteriormente, junto con todas las columnas disponibles en la consulta, y avanzar.




- En el nivel de agrupamiento, elegir DNAME (el departamento) y avanzar.





- Elegir el ordenamiento por orden de ENAME creciente.

- Dejar las opciones de diseño por defecto y avanzar hasta visualizar el reporte.
Nuestro reporte está listo luego de 10 minutos con cero esfuerzo de programación, simplemente usando una sencilla consulta SQL.







Combinamos la facilidad de uso y versatilidad de una herramienta como Access con el potencial de una base de datos Oracle. Las posibilidades dependen sólo de nuestra voluntad, pudiendo integrar planillas de Excel, gráficos, multimedia y mejorar la lógica de nuestra aplicacion con VBA (Visual Basic for Applications).

martes, 13 de enero de 2009

Aumento en esperas log file sync

Se puede encontrar mucha "blografía" acerca de este clásico problema de tunning. El parámetro log file sync está directamente ligado al proceso interno LGWR que sincroniza el log buffer (área de memoria interna de la SGA) y redo logs en disco (archivos donde son registradas las transacciones), y puede revelar uno de dos problemas (sino ambos): un sub dimensionamiento de la base de datos, o un uso inadecuado de transacciones en las aplicaciones que la usan. En ambos casos, el tiempo de respuesta puede experimentar picos, deteriorando la performance.

Si bien alguien puede pensar en alterar la configuración de Oracle, es mejor comenzar a buscar posibles sesiones ofensoras. Es sabido que el 98% de las veces las aplicaciones son las principales reponsables de los problemas de performance que los usuarios reclaman, y no la base de datos.

Recientemente tuve la oportunidad de ver un problema de performance en una base de datos de producción que comenzó a paralizarse sin explicación aparente. He notado una tendencia de los gerentes a pensar los problemas de performance como problemas de hardware, y que adquiriendo nuevos recursos el problema se soluciona. Hay veces que puede ser cierto -no descartemos posibilidades- pero antes de cualquier conjetura y gasto innecesario, un poco de investigación es primordial. Para eso, Oracle nos provee de algunas herramientas de gran utilidad para identificar comportamientos anómalos, y causas que los producen.

En este caso, fue la herramienta de diagnóstico Statspack quien reveló la verdadera causa del problema.

El primer indicio en el reporte fue en los Top 5 wait events: log file sync al tope de la lista.
Eso significaba que Oracle estaba generando mucho más REDO, y tan agresivamente que la instancia dificilmente podía procesarlo. Existen errores típicos de programación PL/SQL que generan más REDO del necesario, uno de ellos es el clásico commit dentro del loop:
FOR rec IN (SELECT * from TABLA_GRANDE)
-- ..
-- procesamiento del registro rec
COMMIT;
END LOOP;
-- no es aquí donde debería hacerse el commit?
Analisando los resultados del reporte de Statspack, se pudo percibir que no había habido un aumento de COMMITs sino de ROLLBACKs.

Extracto de Statspack:
Load Profile
-----------



% Blocks changed per Read: 4.09 Recursive Call %: 92.21
Rollback per transaction %: 64.85 Rows per Sort: 14.35

....
user commits 96,732 161.2 0.4
user rollbacks 175,390 292.3 0.6
...
El valor de Rollback per transaction indicaba que un 65% de las transacciones terminaban con rollback, y más adelante en el reporte se pudo verificar que efectivamente estaban ocurriendo el doble de rollbacks que de commits. Esto no es normal prácticamente en ninguna base de datos, al menos las que yo he visto. Leyendo reportes anteriores con actividad normal, se comprobó que efectivamente estos números había cambiado (anteriormente el índice era 4%). Sirvió para enfocarse y analisar de lleno las aplicaciones que se estaban ejecutando, y descubrir a través de las sesiones activas que una de ellas generaba mucho rollback. Sucede que el código que esta ejecutaba, había sido levemente modificado (3 líneas) y estaba generando errores inesperados con rollbacks de forma indiscriminada.

Las bases de datos deben servir a las aplicaciones y usuarios de la mejor forma posible. Para eso, no basta simplemente con ampliar recursos de memoria ni ajustar parámetros. Las aplicaciones que afectan la base de datos deben ser las primeras en ser tuneadas, y ni que hablar testeadas antes de colocarlas en producción. Ya sea en proyectos de desarrollo o mantenimiento, los tests de carga son necesarios. Un test de funcionalidad con una única sesión no revela este tipo de defectos, los cuales sumados en un ambiente real se convierten en un gran problema.

Haciendo las veces de investigador, el DBA debe recurrir a las herramientas de diagnóstico como Statspack, tkprof y vistas dinámicas. En caso de tratarse de código malicioso, será fundamental trabajar en equipo junto a los desarrolladores para hallar la fuente del problema.

viernes, 9 de enero de 2009