jueves, 30 de septiembre de 2010

Flash Recovery Area

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.

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.

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)
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 failed
De 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::500110a0008c497a
cfgadm: Configuration operation not supported
Cuando intentamos ver la configuración del drive, obtenemos un error de comunicación:
luxadm display 500110a0008c497a
Error: SCSI failure. - 500110a0008c497a.
El 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!

El comando mágico es:
luxadm -e forcelip /dev/cfg c6
Verificar 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.

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:
CREATE TABLE status_history
(id INTEGER PRIMARY KEY,
ticket_id INTEGER,
estado VARCHAR(1),
fecha DATETIME);
Incluyamos 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.
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');
Como se puede ver, el ticket 1 comenzó en el estado A, luego pasó al B, al C, volvió a A y finalizó en C. Todos esos cambios registraron únicamente la fecha de inserción.

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_HORAS
====== ============
'A' 127.33333333
'B' 87.50000000
'C' 98.16666667
CASO 2 - IDs con huecos (1,2,4,5,9....)

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.*
FROM (SELECT @rownum:=0) r, <tabla o consulta> t
Ahora si, podemos sustituir las tablas por este código y usar rownum como condición.

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,
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
Observar que cada subconsulta utiliza su propia variable @rownum, ya que si usarámos la misma, se incrementaría incorrectamente.

El resultado es:
ESTADO   TOTAL_HORAS
====== ============
'A' 127.33333333
'B' 87.50000000
'C' 142.16666667
Solamente 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.

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.
La consulta principal que me da tiempos por estado (no agrupados) queda así:
SELECT t1.estado,
(LEAD(fecha, 1, NULL) OVER(ORDER BY id) - t1.fecha)*24 AS total_horas
FROM status_history t1
ORDER BY id
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.

Solo resta agrupar los estados y sumar los totales en una query exterior:
SELECT estado,
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;
El resultado es el mismo, lo pueden comprobar si se animan a hacer la prueba.
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.
set linesize 131
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 '/'
/
Para 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.

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.
create or replace function sql_match (exp varchar2, regexp varchar2) return number as
begin
if (owa_pattern.match(exp, regexp)) then
return 1;
else
return 0;
end if;
end;
La ponemos en práctica:

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;

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
Ahora si, volviendo al problema original planteado por nuestra amiga, la traducción de MySQL a Oracle 9i queda:

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:

• 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:
  • TABLE
  • TABLESPACE
  • USER
  • FULL
Si queremos exportar el código, deberemos usar el modo USER, o FULL (todos los esquemas).
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.