A partir de Oracle 10g R1 aparece un recurso muy interesante en la base de datos, destinado a guardar y organizar fácilmente todos aquellos archivos que hacen a la recuperación de la base de datos: Flash Recover Area (FRA). En ella se almacenan backups, copias de datafiles, controlfiles, archivelogs. Esta forma de manejar los archivos hace la tarea del DBA más eficiente, ya que permite recuperar una falla más rapidamente y realiza algunas funciones de limpieza automáticamente.
Como esta opción no viene activada por defecto, es necesario alterar un par de parámetros con ALTER SYSTEM para que entre en efecto. Nada complicado. No necesitaremos reiniciar la base, a menos que no la tengamos en ARCHIVELOG mode. Este último modo es requerido.
1) Colocar la base de datos en modo ARCHIVELOG (en caso que no lo esté)
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
2) Crear un directorio destino para los archivos via sistema operativo. En nuestro caso será: /u04/fra
En caso de estar usando ASM es exactamente igual, crearemos un grupo llamado por ejemplo +FRA.
3) Configurar los siguientes parámetros con usuario sys:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/fra' SCOPE=BOTH SID='*';
El tamaño (10G) es un ejemplo y a criterio de cada uno, dependiendo del uso que le vayamos a dar. Se recomienda reservar al menos 2 veces el tamaño actual de la base de datos.
En próximos articulos veremos ejemplos para sacar provecho de nuestra flash recovery área.
jueves, 30 de septiembre de 2010
viernes, 27 de agosto de 2010
Volumenes Netbackup vuelven a scratch
Esta es de Veritas Netbackup 6.5, y me consta que para versiones 5 también funciona.
Solo para enmarcar el problema, tengo configurado un robot de cintas TLD para backup, que administra el respaldo diario de una base de datos Oracle y algunos file systems. El tiempo de retención de los respaldos está definido en 1 mes.
Ocurrió que algunos volúmenes del pool 'Oracle' volvían al estado indefinido 'scratch' luego que expiraba el tiempo de retención, y debía reasignarlos cada vez que sucedía, volviéndolos al pool original con la Consola de Administración de Netbackup.
Luego de buscar un poco e intentar alterando archivos de configuración (sin suerte), encontré en un foro de Symantec el siguiente comando online que solucionó mi problema:
(con usuario root)
nbemmcmd -changesetting -return_unassigned_media_to_scratch_pool no -machinename nombre_del_host
NBEMMCMD, Version:6.5
Command completed successfully.
nombre_del_host es la máquina donde el Netbackup está instalado.
Solo para enmarcar el problema, tengo configurado un robot de cintas TLD para backup, que administra el respaldo diario de una base de datos Oracle y algunos file systems. El tiempo de retención de los respaldos está definido en 1 mes.
Ocurrió que algunos volúmenes del pool 'Oracle' volvían al estado indefinido 'scratch' luego que expiraba el tiempo de retención, y debía reasignarlos cada vez que sucedía, volviéndolos al pool original con la Consola de Administración de Netbackup.
Luego de buscar un poco e intentar alterando archivos de configuración (sin suerte), encontré en un foro de Symantec el siguiente comando online que solucionó mi problema:
(con usuario root)
nbemmcmd -changesetting -return_unassigned_media_to_scratch_pool no -machinename nombre_del_host
NBEMMCMD, Version:6.5
Command completed successfully.
jueves, 22 de julio de 2010
Recuperando dispositivos failed en Solaris
Esta semana tuve que aprender a la fuerza algunas pisadas avanzadas con Solaris, para intentar recuperar una unidad de cinta SCSI que no era reconocida. Tanto esfuerzo y sufrimiento merece un post que espero ayude a alguien algún dia.
La unidad en cuestión aparece entre los dispositivos como failed:
(como usuario root en Solaris 10)
En varias fuentes se recomienda desconfigurar el dispositivo para volverlo a configurar, pero el comando no es reconocido:
El comando mágico es:
Si esto no resuelve, los caminos alternativos son dos:
1) Sacar el cable de datos que llega al drive y reconectarlo luego de 2 minutos, verificando el listado de dispositivos.
2) Reiniciar el servidor (obviamente la opción menos deseada en un ambiente en producción).
La unidad en cuestión aparece entre los dispositivos como failed:
(como usuario root en Solaris 10)
cfgadm -al
Ap_Id Type Receptacle Occupant Condition
N0.IB6 PCI-X_I/O_B connected configured ok
N0.IB6::pci0 io connected configured ok
N0.IB6::pci1 io connected configured ok
N0.IB6::pci2 io connected configured ok
N0.IB6::pci3 io connected configured ok
N0.SB0 CPU_V3 connected configured ok
N0.SB0::cpu0 cpu connected configured ok
....
(varias entradas)
...
c4 fc-private connected configured unknown
c4::500110a0008c4980 tape connected configured unknown
c5 fc-private connected configured unknown
c5::202800a0b82ae608 disk connected configured unknown
c6 fc-private connected configured unknown
c6::500110a0008c497a med-changer connected configured failedDe las dos unidades de backup (c4 y c6), la última aparece como failed, y en vez del tipo 'tape' se muestra 'med-changer'.En varias fuentes se recomienda desconfigurar el dispositivo para volverlo a configurar, pero el comando no es reconocido:
cfgadm -f -c unconfigure c6::500110a0008c497aCuando intentamos ver la configuración del drive, obtenemos un error de comunicación:
cfgadm: Configuration operation not supported
luxadm display 500110a0008c497aEl problema responde a un desentendimiento entre el SO y el dispositivo. El hardware ha quedado en un estado inconsistente pero el SO no fue actualizado en tal sentido. Es necesario resetear el link que comunica con el dispositivo. Tener precaución con usar el siguiente comando, asegurarse de informar el id correcto!
Error: SCSI failure. - 500110a0008c497a.
El comando mágico es:
luxadm -e forcelip /dev/cfg c6Verificar con el comando cfgadm -al que el dispositivo ya no tiene el estado 'failed'. Probar el comando luxadm display como se muestra anteriormente, debería mostrar la configuración del dispositivo correctamente.
Si esto no resuelve, los caminos alternativos son dos:
1) Sacar el cable de datos que llega al drive y reconectarlo luego de 2 minutos, verificando el listado de dispositivos.
2) Reiniciar el servidor (obviamente la opción menos deseada en un ambiente en producción).
jueves, 24 de junio de 2010
Keep alive en PL/SQL Developer
La mayoría de los clientes telnet y ftp tienen una función que nos permite "refrescar" nuestra conexión con un servidor y evitar que la sesión sea expirada. Comúnmente se encuentra como 'keep alive', y básicamente consiste en el envío de paquetes nulos para reiniciar el cronómetro de timeout.
Son pocas las ocasiones en las cuales he necesitado de un keep alive para PL/SQL Developer, pero me ha ocurrido y para mi desgracia no he encontrado ninguna opción del estilo en la herramienta. Eso dificulta enormemente correr un debug por ejemplo, donde necesitamos avanzar paso a paso y ocasionalmente podemos demorar más que el tiempo de timeout.
Comentario aparte, una de las cosas que me molestan de PL/SQL Developer es el tiempo que queda trabado cuando se ha desconectado, a veces más de 1 minuto! Pero hay que reconocer que es una herramienta excelente cuando podemos trabajar normalmente.
Como PL/SQL Developer no permite más de 2 scripts ejecutando al mismo tiempo, debemos descartar la idea de crear un simple script con un loop y un SELECT para refrescar la conexión. Eso no nos permitiría por ejemplo ejecutar un debug sobre nuestra aplicación.
Workaround
Aunque ignoro si Allaround Automations planea en algún momento incluir algo de ese tipo (tengo la versión 8.0 y por ahora nada), hay una forma de sortear el problema y mantener la conexión activa, permitiéndonos trabajar normalmente. La opción es 'Sessions...' bajo el menú Tools.
Dentro de Sessions, hay un switch para activar el auto-refresh, como se ve en la imagen a la izquierda.
Si presionamos botón derecho del mouse sobre ese botón, podemos definir el intervalo de refresh entre 1 y 60 segundos. Un valor de 30 debería ser suficiente la gran mayoría de los casos.
Me tardé un poco en descubrirlo pero espero que ayude a alguien.
Son pocas las ocasiones en las cuales he necesitado de un keep alive para PL/SQL Developer, pero me ha ocurrido y para mi desgracia no he encontrado ninguna opción del estilo en la herramienta. Eso dificulta enormemente correr un debug por ejemplo, donde necesitamos avanzar paso a paso y ocasionalmente podemos demorar más que el tiempo de timeout.
Comentario aparte, una de las cosas que me molestan de PL/SQL Developer es el tiempo que queda trabado cuando se ha desconectado, a veces más de 1 minuto! Pero hay que reconocer que es una herramienta excelente cuando podemos trabajar normalmente.
Como PL/SQL Developer no permite más de 2 scripts ejecutando al mismo tiempo, debemos descartar la idea de crear un simple script con un loop y un SELECT para refrescar la conexión. Eso no nos permitiría por ejemplo ejecutar un debug sobre nuestra aplicación.
Workaround
Aunque ignoro si Allaround Automations planea en algún momento incluir algo de ese tipo (tengo la versión 8.0 y por ahora nada), hay una forma de sortear el problema y mantener la conexión activa, permitiéndonos trabajar normalmente. La opción es 'Sessions...' bajo el menú Tools.
Dentro de Sessions, hay un switch para activar el auto-refresh, como se ve en la imagen a la izquierda.Si presionamos botón derecho del mouse sobre ese botón, podemos definir el intervalo de refresh entre 1 y 60 segundos. Un valor de 30 debería ser suficiente la gran mayoría de los casos.
Me tardé un poco en descubrirlo pero espero que ayude a alguien.
jueves, 27 de mayo de 2010
Una de funciones analíticas
En esta oportunidad veremos una técnica interesante en SQL para relacionar valores de 2 filas consecutivas, para todos los registros de una tabla. El criterio de 'consecutividad' puede estar dado por alguna de las columnas de la tabla, como el id, la fecha de creación del registro, etc.
En el ejemplo a seguir, tenemos un sistema de registro de tickets, con una tabla histórica que registra el momento en que un ticket cambió de estado.
Lo que queremos hacer es un reporte con el tiempo total en cada uno de esos estados, con un único SQL.
Esta no es una consulta trivial, ya que cada registro contiene únicamente una fecha, por tanto tendremos que asociar al menos dos de ellos para obtener un intervalo de tiempo.
Para complicar un poco, lo resolveremos primero con MySQL, que tiene bastante menos potencial que Oracle para este tipo de consultas. De hecho este ejercicio surgió a partir de la necesidad de un colega trabajando con ese motor.
Comenzamos creando la tabla:
Observando los datos de la tabla, deducimos que la diferencia de tiempo entre el segundo registro 'B' y el primero 'A' es tiempo transcurrido en estado 'A' (en el diagrama representado como tA1). Lo mismo para el último intervalo tA2. La suma de tA1 y tA2 representará el tiempo total que el ticket estuvo en estado 'A'.
La estrategia será usar un auto-join y relacionar los registros n y n+1. Para eso necesitamos tener un campo secuencial y sin huecos para que la igualdad funcione.
Veremos primero el caso simple donde cada registro tiene un id consecutivo, y luego nos enfocaremos en resolver el problema de los huecos.
CASO 1 - IDs continuos (1,2,3,4,5....)
En este caso, sabemos que cada id está a una distancia 1 del id siguiente (vale también para otras diferencias), por tanto haremos el join usando la condición id=id+1 para obtener las fechas y la diferencia entre ellas.
Para manipular la diferencia entre dos fechas, usamos primero TIMEDIFF que me retorna un tipo TIME (HH:MI:SS) y luego TIME_TO_SEC para obtener la cantidad total de segundos.
Obtenemos las siguientes tuplas:
Cabe observar que aparecen dos instancias del estado A, que en el diagrama anterior equivalen a tA1 y tA2. Para obtener la suma total, agrupamos por estado y utilizamos la función SUM:
Finalmente el resultado deseado:
En el caso anterior, podiamos usar la condicion id=id+1, ya que estabamos seguros que siempre iba a haber una correspondencia (con excepción del último registro). En el caso de existir huecos, el id ya no nos sirve; necesitamos otro campo para ese fin. En Oracle, esto sería muy fácil con la columna virtual rownum, pero en MySQL no tenemos ese recurso.
Hay sin embargo, una forma de simular rownum, y es usando una variable declarada en SQL, inicializada en un select interior. La sintáxis no queda muy amigable pero sirve al propósito de este problema, que es tener un campo secuencial sin huecos.
Columna virtual ROWNUM con MySQL:
Para probarlo con nuestro ejemplo, agregaremos un nuevo cambio de estado, con id=8:
La consulta queda así:
El resultado es:
La solución Oracle con Funciones Analíticas
Como parte final, voy a mostrar cómo el poder de las funciones analíticas de Oracle nos permiten resolver este tipo de problemas con elegancia y sencillez, sin preocuparnos con auto-joins, rownums ni huecos.
La función ideal en este caso es LEAD. Esta función nos retorna el siguiente registro basado en algun criterio de ordenación, que puede ser una columna.
Todo se resume a usar lo siguiente:
Solo resta agrupar los estados y sumar los totales en una query exterior:
Tengo que agregar la condición total_horas IS NOT NULL, ya que el uso de LEAD no impide que se muestre el estado D con tiempo NULL (recuerdan el tercer parámetro de LEAD?), que en este caso no nos interesa.
Como comentario, la función LAG nos retorna el registro anterior usando la misma sintáxis que LEAD.
Conclusiones
Las funciones analíticas son útiles y necesarias en muchos casos. Además de ser más eficientes, nos permiten resolver rápidamente el problema de agrupar y buscar relaciones entre filas de una misma consulta.
Bases de datos como MySQL no poseen estos recursos, y nos obligan a resolver el problema con mucho más esfuerzo. Esperemos que con la nueva adquisición, Oracle piense en incorporarlas, para beneficio de quienes trabajan con SQL :)
Mientras tanto, en el caso de MySQL lo importante es determinar cuál es la condición que define la consecutividad entre dos registros, teniendo cuidado si hay huecos. En el caso de Oracle, simplemente es encontrar una relación de precedencia entre dos registros.
Ver también:
Documentación de Funciones Analíticas en Oracle 10g
En el ejemplo a seguir, tenemos un sistema de registro de tickets, con una tabla histórica que registra el momento en que un ticket cambió de estado.
Lo que queremos hacer es un reporte con el tiempo total en cada uno de esos estados, con un único SQL.
Esta no es una consulta trivial, ya que cada registro contiene únicamente una fecha, por tanto tendremos que asociar al menos dos de ellos para obtener un intervalo de tiempo.
Para complicar un poco, lo resolveremos primero con MySQL, que tiene bastante menos potencial que Oracle para este tipo de consultas. De hecho este ejercicio surgió a partir de la necesidad de un colega trabajando con ese motor.
Comenzamos creando la tabla:
CREATE TABLE status_historyIncluyamos nuestro primer ticket, con algunos cambios de estado entre A, B y C. Los estados tienen un orden cronológico y es posible volver al mismo repetidas veces.
(id INTEGER PRIMARY KEY,
ticket_id INTEGER,
estado VARCHAR(1),
fecha DATETIME);
INSERT INTO status_history VALUES (1, 1, 'A', '2010-01-01 14:00:00');
INSERT INTO status_history VALUES (2, 1, 'B', '2010-01-03 18:30:00');
INSERT INTO status_history VALUES (3, 1, 'C', '2010-01-07 10:00:00');
INSERT INTO status_history VALUES (4, 1, 'A', '2010-01-11 12:10:00');
INSERT INTO status_history VALUES (5, 1, 'C', '2010-01-14 15:00:00');
Observando los datos de la tabla, deducimos que la diferencia de tiempo entre el segundo registro 'B' y el primero 'A' es tiempo transcurrido en estado 'A' (en el diagrama representado como tA1). Lo mismo para el último intervalo tA2. La suma de tA1 y tA2 representará el tiempo total que el ticket estuvo en estado 'A'.
La estrategia será usar un auto-join y relacionar los registros n y n+1. Para eso necesitamos tener un campo secuencial y sin huecos para que la igualdad funcione.
Veremos primero el caso simple donde cada registro tiene un id consecutivo, y luego nos enfocaremos en resolver el problema de los huecos.
CASO 1 - IDs continuos (1,2,3,4,5....)
En este caso, sabemos que cada id está a una distancia 1 del id siguiente (vale también para otras diferencias), por tanto haremos el join usando la condición id=id+1 para obtener las fechas y la diferencia entre ellas.
SELECT s1.estado,
TIME_TO_SEC(timediff(s2.fecha,s1.fecha))/60/60 AS total_horas
FROM
status_history s1, status_history s2
WHERE s1.id+1 = s2.id
AND s1.ticket_id=1
AND s2.ticket_id=1
Para manipular la diferencia entre dos fechas, usamos primero TIMEDIFF que me retorna un tipo TIME (HH:MI:SS) y luego TIME_TO_SEC para obtener la cantidad total de segundos.
Obtenemos las siguientes tuplas:
ESTADO TOTAL_HORAS
====== ============
'A' 52.50000000
'B' 87.50000000
'C' 98.16666667
'A' 74.83333333
Cabe observar que aparecen dos instancias del estado A, que en el diagrama anterior equivalen a tA1 y tA2. Para obtener la suma total, agrupamos por estado y utilizamos la función SUM:
SELECT s1.estado,
SUM(TIME_TO_SEC(timediff(s2.fecha,s1.fecha)))/60/60 AS total_horas
FROM
status_history s1, status_history s2
WHERE s1.id+1 = s2.id
AND s1.ticket_id=1
AND s2.ticket_id=1
GROUP BY estado
Finalmente el resultado deseado:
ESTADO TOTAL_HORASCASO 2 - IDs con huecos (1,2,4,5,9....)
====== ============
'A' 127.33333333
'B' 87.50000000
'C' 98.16666667
En el caso anterior, podiamos usar la condicion id=id+1, ya que estabamos seguros que siempre iba a haber una correspondencia (con excepción del último registro). En el caso de existir huecos, el id ya no nos sirve; necesitamos otro campo para ese fin. En Oracle, esto sería muy fácil con la columna virtual rownum, pero en MySQL no tenemos ese recurso.
Hay sin embargo, una forma de simular rownum, y es usando una variable declarada en SQL, inicializada en un select interior. La sintáxis no queda muy amigable pero sirve al propósito de este problema, que es tener un campo secuencial sin huecos.
Columna virtual ROWNUM con MySQL:
SELECT @rownum:=@rownum+1 rownum, t.*Ahora si, podemos sustituir las tablas por este código y usar rownum como condición.
FROM (SELECT @rownum:=0) r, <tabla o consulta> t
Para probarlo con nuestro ejemplo, agregaremos un nuevo cambio de estado, con id=8:
INSERT INTO status_history VALUES (8, 1, 'D', '2010-01-16 11:00:00');La secuencia de ids ahora es 1, 2, 3, 4, 5, 8
La consulta queda así:
SELECT t1.estado,Observar que cada subconsulta utiliza su propia variable @rownum, ya que si usarámos la misma, se incrementaría incorrectamente.
SUM(TIME_TO_SEC(timediff(t2.fecha,t1.fecha)))/60/60 AS total_horas
FROM
(SELECT @rownum1:=@rownum1+1 rownum, s1.*
FROM (SELECT @rownum1:=0) r, status_history s1
WHERE s1.ticket_id=1
ORDER BY s1.id) t1,
(SELECT @rownum2:=@rownum2+1 rownum, s2.*
FROM (SELECT @rownum2:=0) r, status_history s2
WHERE s2.ticket_id=1
ORDER BY s2.id) t2
WHERE t1.rownum+1 = t2.rownum
GROUP BY t1.estado
El resultado es:
ESTADO TOTAL_HORASSolamente el tiempo de 'C' se vio incrementado con el pasaje de 'C' a 'D'. El estado 'D' no tiene tiempo asociado por tratarse del estado terminal.
====== ============
'A' 127.33333333
'B' 87.50000000
'C' 142.16666667
La solución Oracle con Funciones Analíticas
Como parte final, voy a mostrar cómo el poder de las funciones analíticas de Oracle nos permiten resolver este tipo de problemas con elegancia y sencillez, sin preocuparnos con auto-joins, rownums ni huecos.
La función ideal en este caso es LEAD. Esta función nos retorna el siguiente registro basado en algun criterio de ordenación, que puede ser una columna.
Todo se resume a usar lo siguiente:
LEAD(fecha, 1, NULL) OVER(ORDER BY id)
- El primer parámetro es la columna que voy a retornar, en mi caso, fecha.
- El segundo es el offset que quiero recuperar, es decir cuantas filas 'adelante'.
- El tercer parámetro es el valor que quiero mostrar si no hay siguiente registro.
- Por último, la columna que voy a usar como criterio de ordenación.
SELECT t1.estado,Estoy restando la fecha del registro siguiente (recuperado por LEAD) al registro actual. Ver que tampoco necesito funciones de fechas, basta con restarlas y multiplicar por 24.
(LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 AS total_horas
FROM status_history t1
ORDER BY id
Solo resta agrupar los estados y sumar los totales en una query exterior:
SELECT estado,El resultado es el mismo, lo pueden comprobar si se animan a hacer la prueba.
SUM(total_horas) total_horas
FROM (SELECT t1.estado,
(LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 total_horas
FROM status_history t1
ORDER BY id)
WHERE total_horas IS NOT NULL
GROUP BY estado
ORDER BY estado;
Tengo que agregar la condición total_horas IS NOT NULL, ya que el uso de LEAD no impide que se muestre el estado D con tiempo NULL (recuerdan el tercer parámetro de LEAD?), que en este caso no nos interesa.
Como comentario, la función LAG nos retorna el registro anterior usando la misma sintáxis que LEAD.
Conclusiones
Las funciones analíticas son útiles y necesarias en muchos casos. Además de ser más eficientes, nos permiten resolver rápidamente el problema de agrupar y buscar relaciones entre filas de una misma consulta.
Bases de datos como MySQL no poseen estos recursos, y nos obligan a resolver el problema con mucho más esfuerzo. Esperemos que con la nueva adquisición, Oracle piense en incorporarlas, para beneficio de quienes trabajan con SQL :)
Mientras tanto, en el caso de MySQL lo importante es determinar cuál es la condición que define la consecutividad entre dos registros, teniendo cuidado si hay huecos. En el caso de Oracle, simplemente es encontrar una relación de precedencia entre dos registros.
Ver también:
Documentación de Funciones Analíticas en Oracle 10g
martes, 13 de abril de 2010
Listar parámetros indocumentados en Oracle
Los parámetros indocumentados son controles especiales que modifican el comportamiento de la base de datos, y pueden generar problemas inesperados si no se usan adecuadamente. Deben ser utilizados bajo recomendación específica del soporte. Se identifican fácilmente ya que comienzan típicamente con "_".
El comando de SQL*Plus show parameters lista los valores estandar de la base de datos, y también de aquellos parámetros indocumentados que hemos alterado explícitamente.
Hay veces que queremos experimentar con algún parámetro indocumentado (no en producción obviamente!), pero necesitamos conocer el valor por defecto que está actualmente en uso. Aquí va una consulta para listarlos, con el valor actual para la sesión y para la instancia.
El comando de SQL*Plus show parameters lista los valores estandar de la base de datos, y también de aquellos parámetros indocumentados que hemos alterado explícitamente.
Hay veces que queremos experimentar con algún parámetro indocumentado (no en producción obviamente!), pero necesitamos conocer el valor por defecto que está actualmente en uso. Aquí va una consulta para listarlos, con el valor actual para la sesión y para la instancia.
set linesize 131Para volver al valor estándar del parámetro, bastará eliminarlo del archivo de parámetros de la base, si lo hemos incluído en el mismo.
set pages 9999
col parameter for A30
col description for A40
col "Session Value" for A15
col "Instance Value" for A15
SELECT
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
a.ksppinm LIKE '/_%' escape '/'
/
jueves, 4 de marzo de 2010
Expresiones regulares en SQL con 9i
Recibi un comentario de Laura con el siguiente problema:
"Estoy empezando a trabajar con oracle 9.2 y tengo que cambiar una sentencia que esta en mysql con expresiones regulares a oracle. La sentencia que tengo que pasar a oracle es la siguiente:
SELECT * FROM prueba WHERE (content REGEXP ?) AND (codigo = ?) AND (nombre = ?) GROUP BY mes
¿Alguien sabe como puedo pasar a oracle esta sentecia?"
A diferencia de sus sucesores, Oracle 9i tiene un limitado manejo de expresiones regulares, el cual está mas enfocado hacia PL/SQL y no tanto a SQL.
El paquete para usar expresiones regulares se llama owa_pattern. Basta hacer un desc owa_pattern en la consola para saber si tenemos acceso a el, en caso contrario solicitarlo al administrador.
La función para buscar ocurrencias en expresiones regulares dentro del paquete es:
match([cadena_entrada],[expresion_regular])
Retorna un valor booleano true o false. Y aquí está el dilema, en SQL no existe el tipo booleano como tal, por tanto el motor no 'entiende' que es lo que la función está retornando.
SQL> select owa_pattern.match('ejemplo','ej*o') from dual;
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
La solución es crear una función traductora en PL/SQL para convertir el booleano retornado por match a un tipo válido en SQL, como por ejemplo un numérico. Este wrapper recibirá los mismos parámetros que la función original.
SQL> select sql_match('ejemplo','ej.*o') res from dual;
RES
----------
1
Ahora esta función es perfectamente compatible con SQL, y podemos usarla para evaluar el valor de una columna:
Ejemplo: nombres de empleados que comienzan con 'A'
SELECT * FROM prueba WHERE (sql_match(content, ?)=1) AND (codigo = ?) AND (nombre = ?) GROUP BY mes
Como dije anteriormente, en versiones a partir de 10g, contamos con expresiones regulares prontas para SQL, como la función REGEXP_LIKE. El ejemplo aplicado a este caso quedaría de esta forma:
SELECT * FROM prueba WHERE regexp_like(content, ?) AND (codigo = ?) AND (nombre = ?) GROUP BY mes
"Estoy empezando a trabajar con oracle 9.2 y tengo que cambiar una sentencia que esta en mysql con expresiones regulares a oracle. La sentencia que tengo que pasar a oracle es la siguiente:
SELECT * FROM prueba WHERE (content REGEXP ?) AND (codigo = ?) AND (nombre = ?) GROUP BY mes
¿Alguien sabe como puedo pasar a oracle esta sentecia?"
A diferencia de sus sucesores, Oracle 9i tiene un limitado manejo de expresiones regulares, el cual está mas enfocado hacia PL/SQL y no tanto a SQL.
El paquete para usar expresiones regulares se llama owa_pattern. Basta hacer un desc owa_pattern en la consola para saber si tenemos acceso a el, en caso contrario solicitarlo al administrador.
La función para buscar ocurrencias en expresiones regulares dentro del paquete es:
match([cadena_entrada]
Retorna un valor booleano true o false. Y aquí está el dilema, en SQL no existe el tipo booleano como tal, por tanto el motor no 'entiende' que es lo que la función está retornando.
SQL> select owa_pattern.match('ejemplo','ej*o') from dual;
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
La solución es crear una función traductora en PL/SQL para convertir el booleano retornado por match a un tipo válido en SQL, como por ejemplo un numérico. Este wrapper recibirá los mismos parámetros que la función original.
create or replace function sql_match (exp varchar2, regexp varchar2) return number asLa ponemos en práctica:
begin
if (owa_pattern.match(exp, regexp)) then
return 1;
else
return 0;
end if;
end;
SQL> select sql_match('ejemplo','ej.*o') res from dual;
RES
----------
1
Ahora esta función es perfectamente compatible con SQL, y podemos usarla para evaluar el valor de una columna:
Ejemplo: nombres de empleados que comienzan con 'A'
SQL> select ename, sql_match(ename,'^A.*') res from scott.emp;Ahora si, volviendo al problema original planteado por nuestra amiga, la traducción de MySQL a Oracle 9i queda:
ENAME RES
---------- ----------
SMITH 0
ALLEN 1
WARD 0
JONES 0
MARTIN 0
BLAKE 0
CLARK 0
SCOTT 0
KING 0
TURNER 0
ADAMS 1
JAMES 0
FORD 0
MILLER 0
14 rows selected
SELECT * FROM prueba WHERE (sql_match(content, ?)=1) AND (codigo = ?) AND (nombre = ?) GROUP BY mes
Como dije anteriormente, en versiones a partir de 10g, contamos con expresiones regulares prontas para SQL, como la función REGEXP_LIKE. El ejemplo aplicado a este caso quedaría de esta forma:
SELECT * FROM prueba WHERE regexp_like(content, ?) AND (codigo = ?) AND (nombre = ?) GROUP BY mes
miércoles, 10 de febrero de 2010
Critical Patch Update Febrero 2010
Oracle lanzó el último 4 de febrero una actualización de seguridad para sus servidores Weblogic, para las siguientes versiones:
Este patch es de extrema urgencia y se recomienda la inmediata aplicación. Tal es así que se adelantó a la próxima actualización agendada (Oracle lanza patches regulares a mitad de abril, julio, octubre y enero). En este caso, una vulnerabilidad en el manejador de nodos permite acceder sin autenticación.
Recientemente un experto de seguridad había afirmado en una convención de hackers que 9 de cada 10 bases de datos Oracle podían ser violadas externamente, gracias a una vulnerabilidad que el encontró (aunque no mostró pruebas de hechos concretados). Este experto inglés, autor de varios libros, ya ha detectado decenas de bugs en productos como SQL Server, DB2, Informix, y Oracle. La companía aparentemente no se manifestó sobre este asunto.
Cierto o no, lo más recomendable es estar precavidos.
Ver también:
http://www.oracle.com/technology/deploy/security/alerts.htm
http://www.reuters.com/article/idUSTRE6125FB20100203?type=technologyNews
| • Oracle WebLogic Server 11gR1 releases (10.3.1 and 10.3.2) | |
| • Oracle WebLogic Server 10gR3 release (10.3.0) | |
| • Oracle WebLogic Server 10.0 through MP2 | |
| • Oracle WebLogic Server 9.0, 9.1, 9.2 through MP3 | |
| • Oracle WebLogic Server 8.1 through SP6 | |
| • Oracle WebLogic Server 7.0 through SP7 |
Este patch es de extrema urgencia y se recomienda la inmediata aplicación. Tal es así que se adelantó a la próxima actualización agendada (Oracle lanza patches regulares a mitad de abril, julio, octubre y enero). En este caso, una vulnerabilidad en el manejador de nodos permite acceder sin autenticación.
Recientemente un experto de seguridad había afirmado en una convención de hackers que 9 de cada 10 bases de datos Oracle podían ser violadas externamente, gracias a una vulnerabilidad que el encontró (aunque no mostró pruebas de hechos concretados). Este experto inglés, autor de varios libros, ya ha detectado decenas de bugs en productos como SQL Server, DB2, Informix, y Oracle. La companía aparentemente no se manifestó sobre este asunto.
Cierto o no, lo más recomendable es estar precavidos.
Ver también:
http://www.oracle.com/technology/deploy/security/alerts.htm
http://www.reuters.com/article/idUSTRE6125FB20100203?type=technologyNews
jueves, 7 de enero de 2010
Como exportar e importar código PL/SQL en 9i
A medida que avanzan las versiones de Oracle, cada vez se hace más dificil encontrar documentación para las anteriores, como por ejemplo 8i y 9i. Por suerte existen los blogs :)
En tiempos de 9i, EXP e IMP eran las opciones para exportar e importar objetos en nuestro esquema de base de datos. A partir de Oracle 10g, existen versiones mejoradas como Data Pump, que repasaré en otro momento.
Una duda que surge es como exportar e importar el código PL/SQL de un esquema para tener un rápido backup/restore. Hay algunos métodos que se basan en capturar el código en archivos (ver artículo), pero a la hora de llevar el código rápidamente con sus grants, todo de una vez a otro lugar, lo mejor es usar EXP/IMP.
Primero que nada, debemos saber que EXP/IMP trabaja en cuatro modos:
Ahora, la única forma que IMP recupera el código PL/SQL, es importando en modo FULL. Aunque parezca extraño, el modo de importación USER no lo recupera aunque hayamos hecho el export de la misma forma.
Hecha esta aclaración, pasamos a los comandos.
Generar el dump (en modo USER)
exp usuario/password file=/tmp/bck_usuario.dmp owner=usuario rows=N triggers=Y indexes=N constraints=N log=/tmp/bck_usuario.log
Importar el código (en modo FULL)
imp usuario/password file=/tmp/bck_usuario.dmp full=Y ignore=y indexes=N log=/tmp/bck_usuario.log
Observaciones
En tiempos de 9i, EXP e IMP eran las opciones para exportar e importar objetos en nuestro esquema de base de datos. A partir de Oracle 10g, existen versiones mejoradas como Data Pump, que repasaré en otro momento.
Una duda que surge es como exportar e importar el código PL/SQL de un esquema para tener un rápido backup/restore. Hay algunos métodos que se basan en capturar el código en archivos (ver artículo), pero a la hora de llevar el código rápidamente con sus grants, todo de una vez a otro lugar, lo mejor es usar EXP/IMP.
Primero que nada, debemos saber que EXP/IMP trabaja en cuatro modos:
- TABLE
- TABLESPACE
- USER
- FULL
Ahora, la única forma que IMP recupera el código PL/SQL, es importando en modo FULL. Aunque parezca extraño, el modo de importación USER no lo recupera aunque hayamos hecho el export de la misma forma.
Hecha esta aclaración, pasamos a los comandos.
Generar el dump (en modo USER)
exp usuario/password file=/tmp/bck_usuario.dmp owner=usuario rows=N triggers=Y indexes=N constraints=N log=/tmp/bck_usuario.log
Importar el código (en modo FULL)
imp usuario/password file=/tmp/bck_usuario.dmp full=Y ignore=y indexes=N log=/tmp/bck_usuario.log
Observaciones
- Junto con el export, estamos capturando las tablas también (sin datos). El método EXP no es muy flexible en este sentido. Sin embargo a la hora de importar, con el parámetro ignore=Y, estaremos ignorando los intentos de creación si las mismas ya existen en el destino.
- Como IMP únicamente puede utilizarse en modo FULL, si queremos traer solamente un usuario entonces deberemos generar un export de tipo USER.
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:
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 "
Espero que ayude!
- ¿Qué versión de Linux es?
- ¿Cuál es la versión de Oracle que debo descargar?
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.
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
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.
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
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.
"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.
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
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!
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.
CREATE OR REPLACE PROCEDURE deadlock_suicida ASDeadlock más comunes
BEGIN
EXECUTE IMMEDIATE 'DROP PROCEDURE deadlock_suicida';
END;
/
Procedure created.
SQL> exec deadlock_suicida;
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
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
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.
¿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_contextPaso 2: Crear el paquete para manipular el contexto
/
CREATE OR REPLACE PACKAGE pk_scott_app_context ASPaso 3: Setear el parámetro de contexto deptno para el departamento de ventas
-- 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;
/
BEGINPaso 4: Crear la vista parametrizada
pk_scott_app_context.set_dept(30);
END;
/
CREATE VIEW empleados ASPaso 5: Obtener los resultados consultando la vista
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');
SELECT * FROM empleados;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:
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.
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.
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.
Suscribirse a:
Entradas (Atom)
