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