Enfoque para medir la disponibilidad efectiva de una base de datos Oracle

La tarea de los dba's es poco conocida y a veces mal entendida por la mayoria de la gente que no esta especificamente en el tema de base de datos. Es por eso que muchas veces es dificil estimar o medir nuestra eficiencia si no se conoce nada o poco del tema. Dado que la gestión y mantenimiento de bases de datos es un tema muy técnico, resulta ser todo un misterio, incluso para algunos gerentes de sistemas con perfil más de gestión que técnico, que se conforman con no tener sobresaltos y que las bases productivas esten disponibles y con un tiempo de respuesta decente, obviamente esto es deseable en cualquier ambito, pero no permite medir eficiencia con detalle.

Una forma de medir nuestra eficiencia con una métrica entendible por el público en general, podría ser mostrar en numeros la disponibilidad de las bases de datos que gestionamos. Con disponibilidad (availability) me refiero al tiempo total que las bases están arriba, es decir que se pueden usar. Esto en alguna medida (al margen de temas de rendimiento) podría mostrar o cuantificar cuan bien administramos ya que si la base esta arriba las aplicaciones que usan dicha base están operativas. Quien no escucho alguna vez en la cola de un banco o realizando algún trámite municipal la frase: "el sistema esta caido, vuelva mas tarde..."?, en la gran mayoria de los casos es por algun problema en la base de datos o bien porque se tuvieron que realizar tareas de mantenimiento de urgencia, por mala administración, por errores de los operadores o dba's, etc. Es cierto que las bases de datos, y sobre todo Oracle, hoy en día son muy robustas y se han minimizado muchos de los potenciales problemas. Tambien el hardware es mas confiable y, si se tiene presupuesto, se puede redundar en componentes lo cual hace mucho mas improbable una indisponibilidad.

Para poder medir el tiempo de disponibilidad total de una base de datos armé un script que reporta la disponibilidad de los ultimos n dias. En el ejemplo lo hice para los ultimos 4 meses (120 dias). Hay que tomar en cuenta que el reporte se basa de ciertas premisas:

• Debe estar disponible la data a procesar en el alert de la base a analizar. Si se realiza alguna tarea de depuracion diaria, semanal, etc, se deberá concatenar de antemano en un archivo toda la info grabada en un archivo de alert que contenga la totalidad de la información necesaria.
• Si no existen registros con información de fecha y hora entre dos startups se toma como que la base estuvo arriba todo ese tiempo.
• Cuando la base se baja abruptamente, ya sea por un shutdown abort, por un error grave de HW o bien porque se desconectó el cable de alimentación no se registra el shutdown en el alert por lo cual se pierde esa referencia y se usará el último registro disponible en el alert.

Para armar poder correr el reporte primero hay que generar una tabla externa (recordemos que esto existe a partir de 9i por lo cual este metodo no funcionara en versiones anteriores) que permite leer con la sentencia sql el archivo alert.


create directory BDUMP as '/u01/app/oracle/admin/ROP102/bdump'

CREATE TABLE "ALERT_LOG"
(  "TEXT" VARCHAR2(400)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "BDUMP"
ACCESS PARAMETERS
( records delimited by newline
nobadfile
nodiscardfile
nologfile
  )
LOCATION
( 'alert_ROP102.log'
)
)
REJECT LIMIT UNLIMITED;

Una vez creada la tabla externa ya puedo ejecutar la sentencia. La lógica que tomé se basa en buscar el string en el alert que registra el startup de la base y luego (usando funciones análiticas) obtener el registro próximo anterior. Con esto infiero que el tiempo entre el ultimo registro anterior al startup y el registro de startup es el tiempo en el que estuvo baja la base. Esto es una aproximación y puede tener cierta imprecisión pero creo que se acerca bastante a la realidad en la mayoria de los casos. Existen registros en el catalogo de Oracle de cuando una base levanta, pero no se tiene información de cuando se baja, incluso consideremos que si existiera esto, seria complicado registrar los shutdown abort. Por ese motivo recalco que el script que realicé es una aproximación, dado que no se puede saber con exactitud el momento preciso del en el que la base se bajó. Considerando todo lo expuesto anteriormente ahora si vemos como funciona el reporte:

select min_date,
max_date,
round((max_date-min_date)*24,2) total,
down,
round(100-((down*100)/((max_date-min_date)*24)),3) pct_up
from
(select min(to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english')) min_date,
max(to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english')) max_date,
sum(round((to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english')-
to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english'))*24,2)) down
from
(select start_time,
last_time
from
(select text,
lag(text,1) over (order by r) start_time,
lag(text,2) over (order by r) last_time
from ( select rownum r, text
from alert_log
where text like '___ ___ __ __:__:__ 20__'
  or text like 'Starting ORACLE instance (normal)'))
where text like 'Starting ORACLE instance (normal)'
and last_time like '___ ___ __ __:__:__ 20__'
and to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY','NLS_DATE_LANGUAGE=english') > sysdate-120))


MIN_DATE   MAX_DATE   TOTAL   DOWN  PCT_UP
--------   --------   ------  ----  ------
02/10/2009 29/01/2010 2858.22 22.45 99,214

Como se ve en la salida se muestra el intervalo de fechas analizadas, el tiempo total transcurrido entre las dos fechas (medido en horas), el tiempo que estuvo baja (medido en horas) y el porcentaje que estuvo arriba la base. Como vemos esta en valores esperados ya que la disponibilidad esta por arriba del 99%.

Concatenar valores de una columna en 11g R2 (usando la función LISTAGG)

Varias veces me han preguntado como hacer para concatenar los valores de una columna en una sola fila agrupados por otra cierta columna. Para eso se necesita un operador de concatenación tal como existe el operador SUM() o el AVG() para sumar o sacar el promedio de un conjunto de columnas con cierto criterio de agrupamiento. Para solucionar esto lo que siempre sugeria era crear el operador STRAGG, que es una función que pueden encontrar en la pagina asktom.oracle.com. Abajo voy a crear la función para mostrarles como funciona:

rop@ROP92> create or replace type string_agg_type as object
2  (
3     total varchar2(4000),
4
5     static function
6          ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7          return number,
8
9     member function
10          ODCIAggregateIterate(self IN OUT string_agg_type ,
11                               value IN varchar2 )
12          return number,
13
14     member function
15          ODCIAggregateTerminate(self IN string_agg_type,
16                                 returnValue OUT  varchar2,
17                                 flags IN number)
18          return number,
19
20     member function
21          ODCIAggregateMerge(self IN OUT string_agg_type,
22                             ctx2 IN string_agg_type)
23          return number
24  );
25  /

Type created.


rop@ROP92> create or replace type body string_agg_type
2  is
3
4  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5  return number
6  is
7  begin
8      sctx := string_agg_type( null );
9      return ODCIConst.Success;
10  end;
11
12  member function ODCIAggregateIterate(self IN OUT string_agg_type,
13                                       value IN varchar2 )
14  return number
15  is
16  begin
17      self.total := self.total || ',' || value;
18      return ODCIConst.Success;
19  end;
20
21  member function ODCIAggregateTerminate(self IN string_agg_type,
22                                         returnValue OUT varchar2,
23                                         flags IN number)
24  return number
25  is
26  begin
27      returnValue := ltrim(self.total,',');
28      return ODCIConst.Success;
29  end;
30
31  member function ODCIAggregateMerge(self IN OUT string_agg_type,
32                                     ctx2 IN string_agg_type)
33  return number
34  is
35  begin
36      self.total := self.total || ctx2.total;
37      return ODCIConst.Success;
38  end;
39  end;
40  /

Type body created.

rop@ROP92>
rop@ROP92> CREATE or replace
2  FUNCTION stragg(input varchar2 )
3  RETURN varchar2
4  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5  /

Function created.

rop@ROP92>
rop@ROP92> select deptno, stragg(ename)
2    from scott.emp
3   group by deptno
4  /

DEPTNO   STRAGG(ENAME)
-------------------------------
   10   CLARK,KING,MILLER
   20   SMITH,FORD,ADAMS,SCOTT,JONES
   30   ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

En el ejemplo concatené los empleados en una sola fila agrupados por el departamento al cual pertenecen. Si tuvieran que concatenar mas valores y no le alcanza con varchar2 tambien pueden encontrar en el sitio de Tom una variante para usar CLOB, aunque es bastante mas lenta.

Ahora bien, en 11g R2 hubo una actualizacion importante de la funciones analiticas, tambien llamadas Analytic Functions II, y una de estas nuevas funciones (LISTAGG)se puede utilizar para hacer lo mismo que stragg en forma nativa evitando tener que crear el tipo stragg y demás. Les muestro un ejemplito para listar los tipos de trabajo que hay en cada departamento:

rop@ROP112> select deptno, listagg(job,',') within group (order by job) jobs
2    from (select distinct deptno, job from scott.emp)
3   group by deptno
4   order by deptno
5  /

DEPTNO JOBS
---------- ------------------------------
   10 CLERK,MANAGER,PRESIDENT
   20 ANALYST,CLERK,MANAGER
   30 CLERK,MANAGER,SALESMAN

Como siempre digo, es importante leer los manuales cada vez que se libera una nueva versión, sobre todo el "Oracle New Features" que es un resumen de las nuevas caracteriticas. Me sucede a menudo que veo codigos o formas de administración antiguas que insumen muchas horas para hacer lo mismo que ya esta resuelto en forma nativa o mas simple a partir de una nueva versión.