¿Archive log perdido? No reconstruya su Standby

Otro problema recurrente con las bases de datos standby es la pérdida de uno o más archive logs aún no aplicados. La solución era usualmente el reconstruir nuevamente la base de datos standby, lo cual si bien es relativamente fácil, puede consumir bastante tiempo. Esto fue lo que algunos sugirieron a un afligido DBA que pedía ayuda en el foro de Oracle Technet, afortunadamente si estás usando Oracle 10gR2 la solución es mucho más simple y rápida, ¿suena bien? entonces vamos con el procedimiento.

1. Detener la sincronización de la base de datos standby
2. STDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3. Obtener el SCN al cual se ha llegado en la base de datos standby
4. STDB> SELECT CURRENT_SCN FROM V$DATABASE;
5. Obtener un backup incremental de la base de datos primaria con RMAN, a partir del SCN obtenido en el paso previo
6. RMAN> BACKUP INCREMENTAL FROM SCN
7. DATABASE FORMAT '/tmp/ForStandby_%U' tag 'FORSTANDBY';
8. Catalogar el backup del paso previo en la base de datos standby
9. RMAN> CATALOG START WITH '/tmp/ForStandby';
10. Recuperar la base de datos standby con el backup ya catalogado
11. RMAN> RECOVER DATABASE NOREDO;
12. En la base de datos primaria crear un nuevo standby controlfile
13. RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';
14. Detener la base de datos standby y levantarla con nomount
15. RMAN> SHUTDOWN;
16. RMAN> STARTUP NOMOUNT;
17. Restaurar el standby controlfile obtenido en el paso (6) en la base de datos standby
18. RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';
19. Detener la base de datos standby levantarla con mount
20. RMAN> SHUTDOWN;
21. RMAN> STARTUP MOUNT;
22. Limpiar los standby redo logs en la base de datos standby
23. STDB> ALTER DATABASE CLEAR LOGFILE GROUP 1;
24. STDB> ALTER DATABASE CLEAR LOGFILE GROUP 2;
25. STDB> ALTER DATABASE CLEAR LOGFILE GROUP 3;
26. Si estaba activo Flashback Database, reiniciarlo
27. STDB> ALTER DATABASE FLASHBACK OFF;
28. STDB> ALTER DATABASE FLASHBACK ON;
29. Reiniciar el recovery de la base de datos standby
30. STDB> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Este procedimiento aplica no sólo cuando un archive log se ha perdido, sino también cuando se han realizado operaciones con nologging en la base de datos principal o cuando hay un gran retraso en la sincronización. Puedes revisar el texto completo en el manual Oracle Data Guard Concepts and Administration.

Oracle PL/SQL - Debug usando tabla de apoyo

Hoy les traigo una pequeña utilidad que les permitira incluir de una forma sencilla un pequeño sistema de Debug para Oracle PL/SQL que este basado además de en el archiconocido dbms, en una tabla de apoyo que sirva para poder registrar el desempeño de un determinado bloque de código a lo largo del tiempo, facilitandonos las tareas de depuración.

CREATE OR REPLACE PROCEDURE "P_DEBUG_LOG" (donde IN VARCHAR2,texto IN VARCHAR2) IS

-- Raul Carrillo Garrido aka metsuke - www.metsuke.com
-- Released Under LGPL License v3 (http://www.gnu.org/copyleft/lesser.html)

withDbms BOOLEAN := TRUE;
withTable BOOLEAN := FALSE;
cuando DATE := SYSDATE;

BEGIN

IF (withTable) THEN
INSERT INTO DEBUG_LOG (CUANDO,TEXTO,DONDE) VALUES (cuando,texto,donde);
END IF;

IF (withDbms) THEN
DBMS_OUTPUT.put_line('(' TO_CHAR(cuando,'DD/MM/YY HH24:MI:SS') ') - ' donde ' - ' texto );
END IF;

EXCEPTION

WHEN OTHERS THEN

-- Do nothing. Es para debug, si falla algo no quiero que el debug interrumpa el funcionamiento de la aplicación.
-- en todo caso si ejecutamos al menos un dbms para aprovechar y que el proceso informe.
DBMS_OUTPUT.put_line('Se ha producido un error en P_DEBUG_LOG ' SQLERRM);

END;


La tabla de DEBUG_LOG se puede crear mediante el uso del siguiente script:


CREATE TABLE DEBUG_LOG
(
CUANDO DATE,
TEXTO VARCHAR2(2000),
DONDE VARCHAR2(2000)
)

Hints en PL/SQL para determinar el método de acceso

Ya hemos hablado de los hints para el modo optimización. En este segundo artículo continuaré hablando de los hints pero, en concreto, de aquellos que permiten indicar al optimizador Oracle el modo en que se debe acceder a los datos de las tablas. Este tipo de hints resultan extremadamente eficaces a la hora de optimizar una sentencia SQL.

En su día ya indiqué cual es la sintaxis de los hints pero creo que no está de más que la muestre de nuevo:

{ DELETE | INSERT | SELECT | UPDATE } /*+ HINT (parámetros) */

o

{ DELETE | INSERT | SELECT | UPDATE } --+ HINT (parámetros)

Los hints básicos que sirven para determinar el metodo de acceso a los datos de una tabla Oracle son los siguientes:

/*+ FULL (nombre_tabla) */ - Fuerza a que se realice la búsqueda accediendo a todos los registros de la tabla indicada. Cuando las tablas tienen un número reducido de registros puede resultar bueno para el rendimiento de una sentecia DML el forzar un escaneado completo de la tabla en lugar de que el optimizador decida acceder a dicha tabla mediante un índice, ya que, en estos casos, el acceso por índice suele ser más lento.

/*+ ROWID (nombre_tabla) */ - Fuerza a que se acceda a la tabla utilizando el ROWID (identificativo único de los registros de una tabla). Este tipo de hint, por si solo, no es muy útil.

/*+ INDEX (nombre_tabla [nombre_índice] ...) */ - Fuerza a que se acceda a la tabla utilizando, en sentido ascendente, el índice indicado. Muchos problemas de rendimiento vienen causados por el hecho de que el optimizador Oracle decide acceder a una tabla utilizando un índice incorrecto. Mediante este hint podemos indicarle al optimizador que utilice el índice que nosotros consideremos adecuado.

/*+ INDEX_DESC (nombre_tabla [nombre_índice] ...) */ - Idéntico al anterior hint pero en este caso el acceso a través del índice se hace en sentido descendente.

/*+ AND_EQUAL (nombre_tabla [nombre_índice] ...) */ - Este hint se utiliza para forzar el uso de más de un índice (se utilizarían los índices indicados como parámetros) y, después, fusionar los índices quedándose con los registros encontrados en todas las búsquedas por índice realizadas.

/*+ INDEX_FFS (nombre_tabla [nombre_índice] ...) */ - Fuerza el acceso a los datos de la tabla mediante una búsqueda (Scan) rápida (Fast) y total (Full) sobre el índice indicado. Es parecido a utilizar el hint FULL pero sobre un índice en lugar de una tabla, lo cual, difícilmente, puede ser bueno para el rendimiento de una sentencia DML.

/*+ NO_INDEX (nombre_tabla [nombre_índice] ...) */ - Indica al optimizador que no se utilicen los índices indicados. Puede ser útil cuando no tengamos claro cual es el mejor índice que debe ser utilizado para acceder a una tabla pero, por contra, sepamos que podemos tener problemas de rendimiento si se accede a la tabla por un determinado índice y queramos evitar que esto ocurra.

Cómo obtener el plan de ejecución de una sentencia SQL o PL/SQL

Una de las formas más usuales de mejorar el rendimiento de una sentencia SQL o PL/SQL es analizar el plan de ejecución que devuelve el optimizador Oracle. En SQL*Plus se puede obtener dicho plan de ejecución, además de algunas estadísticas referentes al resultado de la ejecución de la sentencia SQL o PLSQL, utilizando el comando AUTOTRACE. Para obtener el plan de ejecución no hay necesidad de ejecutar dicho comando pero, ciertamente, si no lo utilizamos, la poca amigabilidad del comando que debemos ejecutar (EXPLAIN PLAN), el formato de dicho comando y lo complejo que resulta analizar el contenido de la tabla V$SQL_PLAN, hacen que, por mi parte, recomiende encarecidamente el uso del comando SQL*Plus AUTOTRACE.

En mi opinión, AUTOTRACE es una buenísima herramienta de diagnóstico y una excelente ayuda para optimizar sentencias SQL y PL/SQL. El comando AUTOTRACE es puramente declarativo, por lo que es mucho más fácil de utilizar que el comando EXPLAIN PLAN. La sintaxis del comando AUTOTRACE es como sigue:

SET AUTOTRACE OFF - Deshabilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE ON - Habilita el análisis (traceado) de las sentencias SQL.

SET AUTOTRACE TRACEONLY - Habilita el análisis (traceado) de las sentencias SQL pero no devuelve la salida de dicha sentencia. Su uso es recomendable si sólo estamos analizando el rendimiento de la sentencia y no nos interesa conocer los registros que pueda devolver.

SET AUTOTRACE ON/TRACEONLY EXPLAIN - Muestra el plan de ejecución de la sentencia pero no muestra las estadísticas.

SET AUTOTRACE ON STATISTICS - Muestra las estadísticas pero no muestra el plan de ejecución de la sentencia.

Nota: Si se omiten las opciones EXPLAIN y STATISTICS, entonces al ejecutar una sentencia SQL se mostrarán tanto el plan de ejecución como las estadísticas.

Para poder utilizar la opción EXPLAIN del comando AUTOTRACE, es necesario crear la tabla PLAN_TABLE en el esquema del usuario, es por eso que este comando sólo puede ser ejecutado por determinados usuarios, aquellos para los que la mencionada tabla ya ha sido creada. Es importante pues, conocer los usuarios Oracle que han sido configurados para poder ejecutar el comando AUTOTRACE.

Por otro lado, para acceder a las estadísticas, hay que tener acceso a varias tablas del sistema en las que se almacenan los datos del rendimiento de las sentencias SQL. Los DBA pueden dar este acceso utilizando el script plustrce.sql. El nombre de este script puede variar dependiendo del sistema operativo. El DBA tiene que ejecutar dicho script como usuario SYS y, asignar al usuario en cuestión, el papel (role) correspondiente.

Una vez que se ha configurado convenientemente un usuario para que pueda acceder al plan de ejecución y a las estadísticas, basta habilitar el AUTOTRACE para que, al ejecutar una sentencia SQL, nos aparezca el plan de ejecución así como los correspondientes valores estadísticos.

Los valores estadísticos más importantes mostrados por la base de datos Oracle, una vez activado el comando AUTOTRACE, son los siguientes:

- DB block gets: Número de operaciones de entrada/salida realizadas sobre la memoria caché.
- Consistent gets: Número de operaciones de entrada/salida realizadas sobre los segmentos de rollback debido a cambios en la memoria caché.
- Physical reads: Número de bloques leídos desde el disco.
- Sorts (memory): Número de operaciones realizadas en memoria para ordenar los datos.
- Sorts (disk): Número de operaciones realizadas en disco para ordenar los datos.

A la hora de mejorar el rendimiento de una sentencia SQL o PL/SQL, debemos conseguir que el número de db block gets, consistent gets y physical reads sea bajo comparado con el número de registros devueltos por dicha sentencia. Por otro lado, la ordenación de los datos debe realizarse, siempre que sea posible, en memoria.

En cuanto a lo que se refiere al plan de ejecución, desde este enlace podéis acceder a un ejemplo de sentencia SQL con su correspondiente plan de ejecución y una breve interpretación de dicho plan: Ejemplo de plan de ejecución.

Como un primer consejo a la hora de analizar un plan de ejecución, me gustaría indicar que lo primero que hay que evitar son los FULL SCAN (recorrido de todos los registros de una tabla). No obstante, hay determinadas circunstancias bajo las que un FULL SCAN puede ser recomendable; así, cuando una tabla tiene pocos registros, puede ser conveniente realizar un FULL SCAN, en vez de acceder a la misma a través de un índice.

Ya he escrito algunos artículos que pueden ayudar a interpretar un plan de ejecución desarrollado por el optimizador Oracle:

- Puesta a punto de sentencias SQL.
- Bucles y problemas de rendimiento.
- Hints en PL/SQL para el modo de optimización.
- Hints en PL/SQL para determinar el método de acceso.

Objetos bloqueados en una base de datos (1)

Todo el mundo ha intentado hacer alguna llamada desde su móvil y dicha llamada no ha podido realizarse por problemas de congestión, es decir, otros usuarios han copado los canales disponibles y nosotros no hemos podido tomar posesión de ninguno de ellos. Algo parecido puede ocurrir con las sesiones de Oracle (o de cualquier otra base de datos), ya que una sesión puede bloquear (mantener un "lock") un objeto de la base de datos (tabla, columna, etc) evitando que otra sesión pueda utilizarlo.

En esta nota voy a contar como identificar que sesión es la causante del bloqueo y en posteriores mensajes iré más allá y contaré como identificar el objeto que está bloqueado.

Creación de un “lock”

Para empezar voy a simular una situación de bloqueo entre dos sesiones:

Sesión 1

SQL> create table test_bloqueo (test number, bloqueo varchar2(10));

Table created.

SQL> insert into test_bloqueo values (1, 'Hola');

1 row created.

SQL> insert into test_bloqueo values (2, 'Adios');

1 row created.

SQL> select * from test_bloqueo;

TEST BLOQUEO
---- ----------
1 Hola
2 Adios

2 rows selected.

SQL> commit;

A continuación creamos el "lock" sobre la tabla:

SQL> select * from test_bloqueo for update;

La claúsula “for update” es la encargada de creae el "lock" sobre la tabla test_bloqueo.

Sesión 2

Ahora intentaremos actualizar un registro de la tabla test_bloqueo:

SQL> update test_bloqueo set test=3 where bloqueo='Hola';

Este último comando se quedará colgado bloqueado por el "lock" establecido en la primera sesión.

¿Cómo identificar la sesión bloqueadora?

Oracle proporciona una vista (view), DBA_BLOCKERS, que muestra la lista de SIDs de todas las sesiones que están bloqueando algún objeto. El problema es que consultar esta “view” es, según mi experiencia, bastante más lento que consultar directamente la tabla V$LOCK, y además dicha “view” da mucha menos información que la mencionada tabla.

Esto es lo que podemos ver consultando la tabla:

SQL> select * from v$lock ;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- --- -- ------ ------ ----- ------- ----- ----------
B765B198 B765B1AC 779 TM 6442 0 3 0 11543 0
01AA2F00 01AA2FCC 611 TX 262160 653305 6 0 54537 1
B6047A58 B6047A68 699 TX 262160 653305 0 6 54535 0
...............................................................

Si una sesión mantiene un “lock” que está bloqueando a otra sesión entonces la columna BLOCK mostrará el valor 1. Además, se puede determinar que sesión está siendo bloqueada si comparamos las columnas ID1 e ID2, ambas columnas mostrarán los mismos valores tanto para la sesión bloqueadora como para la sesión bloqueada. Ésta última además, puesto que esta solicitando un “lock” que no puede conseguir, tendrá un valor en la columna REQUEST mayor que cero.

Los resultados del comando “select” nos muestran que la sesión con SID 611 esta bloqueando la sesión con SID 699. La sesión 611 se corresponde con la sesión 1 de nuestro ejemplo y la 699 se corresponde con la sesión 2.

Pero existe una solución todavía más eficaz para determinar que sesión bloquea a cual. Podemos construir el siguiente comando “select”:

SQL> select lock1.sid, ' BLOQUEA ', lock2.sid
2 from v$lock lock1, v$lock lock2
3 where lock1.block =1 and lock2.request > 0
4 and lock1.id1=lock2.id1
5 and lock1.id2=lock2.id2;

SID 'BLOQUEA' SID
---------- --------- ----------
611 BLOQUEA 699

1 row selected.

Incluso podemos construir una “query” mucho mas legible:

SQL> select s1.username '@' s1.machine
2 ' ( SID=' s1.sid ' ) esta bloqueando '
3 s2.username '@' s2.machine ' ( SID=' s2.sid ' ) ' AS bloqueos
4 from v$lock l1, v$session s1, v$lock l2, v$session s2
5 where s1.sid=l1.sid and s2.sid=l2.sid
6 and l1.BLOCK=1 and l2.request > 0
7 and l1.id1 = l2.id1
8 and l2.id2 = l2.id2 ;

BLOQUEOS
----------------------------------------------------------------
TEST@chessy ( SID=611 ) esta bloqueando TEST@chessy ( SID=699 )

1 row selected.

Con esto doy por terminada esta primera nota acerca de los bloqueos en una base de datos. No obstante, todavía hay más informacion útil en la tabla V$LOCK pero esto será objeto de otra nota en mi blog.

Fases durante el procesamiento de una sentencia SQL

Durante el procesamiento de una sentencia SQL, ya sea mediante un script o un programa PL/SQL, se distinguen cuatro fases: análisis de la sintaxis (parsing), análisis de las variables (binding), ejecución (executing) y recuperación de datos (fetching).

Fase de parsing

Durante esta fase el servidor de la base de datos Oracle realiza las siguientes acciones:

- Busca la sentencia SQL en la memoria compartida (shared pool).

- Chequea la sintaxis de la sentencia siguiendo las especificaciones y la gramática del lenguaje SQL.

- Chequea la semántica, asegurando que los objetos Oracle referenciados en la sentencia SQL son válidos (existen en la base de datos) y satisfacen las restricciones de seguridad (es decir, el usuario que ejecuta la sentencia tienen los permisos adecuados sobre dichos objetos).

- Determina si el proceso que lanza la sentencia SQL tiene los permisos apropiados para ejecutarlo.

- Si la sentencia SQL incluye una vista (view) o una subquery (una subquery no es más que una sentencia SELECT que está dentro de otra sentencia SQL), transforma dicha sentencia en una sentencia SQL equivalente e intenta simplificar la sentencia resultante.

- Determina y almacena el plan de ejecución o, si es posible, utiliza un plan de ejecución existente.

Fase de binding

En esta fase el servidor de la base de datos Oracle:

- Identifica las variables (bind variables) en la sentencia SQL.

- Asigna o reasigna un valor a cada variable.

Esto quiere decir que en el momento de la optimización del plan de ejecución, el servidor Oracle no conoce los valores de estas variables. Esto facilita una re-ejecución de la sentencia más rápida, ya que no es necesario volver a hacer el parsing de la sentencia. No obstante, está el inconveniente de que el optimizador puede seleccionar un plan de ejecución equivocado causando problemas de rendimiento, cosa que no ocurriría si en vez de variables utilizamos constantes y, además, se recolectan estadísticas. El tema de la recolección de estadísticas para mejorar el rendimiento de las bases de datos Oracle requiere un análisis muy detallado y, quizás, escriba algún artículo más adelante sobre el tema.

Fase de ejecución

En la fase de ejecución el servidor Oracle realiza las siguientes acciones:

- Ejecuta la sentencia SQL siguiendo el plan de ejecución determinado durante la fase de parsing.

- Realiza las operaciones de entrada/salida necesarias para la ejecución de las sentencias de manipulación de datos (DML) y ordena los datos en caso de ser necesario.

Fase de fetching

Durante esta fase el servidor Oracle devuelve los registros de una sentencia SELECT. En cada iteración (fetch) el servidor devuelve múltiples registros. El número de registros que Oracle devuelve por iteración es configurable, es decir, se puede cambiar el tamaño de la matriz de registros. En SQL*Plus podemos cambiar dicho tamaño utilizando el comando SET ARRAYSIZE. Por ejemplo:

SQL> show arraysize
arraysize 15
SQL> set arraysize 1

Tras la ejecución de este comando, el servidor Oracle procesará un solo registro en cada iteración (fetch). El valor por defecto es quince.

Tablas externas en PL/SQL

Las tablas externas permiten hacer consultas desde una base de datos Oracle sobre datos almacenados en un fichero de texto como si dicho fichero fuera una tabla de la base de datos. En Oracle 9i, sólo se pueden realizar operaciones de lectura con las tablas externas; en cambio, en Oracle 10g, se puede también escribir datos en una tabla externa que será creada en ese momento, es decir, no se puede utilizar una tabla externa que ya existe para realizar esta operación.

Aunque se pueden hacer consultas sobre las tablas externas, éstas no permiten todas las funcionalidades que permite Oracle sobre tablas normales. Por ejemplo, no es posible realizar algunas operaciones DDL (sentencias de definición de objetos como revoke, grant, etc.) sobre tablas externas aparte de la creación y actualización de la definición de la misma; por lo tanto no es posible crear índices sobre una tabla externa.

Oracle utiliza el SQL*Loader a través del driver ORACLE_LOADER para cargar datos desde un fichero de texto en la base de datos; y, por otro lado, el driver Data Pump (Bombeo de Datos) permite pasar datos desde la base de datos a un fichero de texto utilizando un formato propietario de Oracle, y, obviamente, dicho fichero de texto se puede cargar de nuevo en la misma u otra base de datos. Existen diferentes restricciones y maneras de proceder, pero se puede pensar que las tablas externas son otra opción alternativa al SQL*Loader y al Data Pump.

Por ejemplo, suponiendo que recibimos un informe .csv diariamente. En vez de escribir un script en SQL*Loader para importar los datos todos los días, se puede simplemente crear una tabla externa y escribir una sentencia SQL "insert ... select" para insertar los datos directamente en las tablas de la base de datos. Así que, diariamente podríamos colocar el fichero CSV en el directorio correspondiente, ejecutar la sentencia insert y tendríamos los datos cargados en nuestra base de datos.

Creación de una tabla externa

Puesto que los datos de las tablas externas están en ficheros de texto, estos ficheros deben estar en un lugar al que Oracle tenga acceso. Así pues, el primer paso es crear un directorio y dar acceso de lectura y escritura al usuario del sistema operativo que se encarga de ejecutar los procesos de la base de datos Oracle. Dicho directorio no puede ser un link simbólico, debe ser un directorio real.

$ cd $ORACLE_HOME
$ mkdir texternal
$ mkdir data
$ ls -l $ORACLE_HOME/texternal
total 30
drwxr-x--- 2 oracle dba 5120 Jul 17 2006 data

Después pondremos el fichero de texto en dicho directorio. En el presente ejemplo utilizaremos un fichero CSV (proyectos.csv):

3002508,ESP Pinto,Calidad,RZAPA001
3002509,ESP Pinto,Supervisión,CGAMI001
3002510,ESP Humanes,Calidad,RZAPA001
3002511,ESP Humanes,Supervisión,GDIAZ001
3002512,ESP Humanes,Instalación,HPERE001

El siguiente paso es crear el directorio en Oracle y dar permiso de lectura/escritura sobre dicho directorio al usuario de Oracle que creará la tabla externa. A la hora de crear el directorio hay que estar seguro de que se usa el path completo y que no se utiliza ningún link simbólico. En nuestro ejemplo supondremos que la variable $ORACLE_HOME tiene el valor /u01/app/oracle/ y por lo tanto el nombre del directorio con el path completo sería /u01/app/oracle/texternal/data.

SQL> connect sys as sysdba
Enter password:
Connected.

SQL> create or replace directory texternal_data
2 as '/u01/app/oracle/texternal/data';

Directory created.

SQL> grant read,write on directory texternal_data to userexte;

Grant succeeded.

El último paso es crear la tabla externa. El comando es exactamente el mismo que se utiliza para crear tablas normales, CREATE TABLE, pero incluye un bloque con sentencias específicas que informan a Oracle de como debe interpretar los datos almacenados en el fichero de texto.

SQL> connect userexte
Enter password:
Connected.

SQL> create table text_proyectos
2 (
3 orden number,
4 proyecto varchar2(30),
5 actividad varchar2(15),
6 supervisor varchar(8)
7 )
8 organization external
9 (
10 default directory texternal_data
11 access parameters
12 (
13 records delimited by newline
14 fields terminated by ','
15 )
16 location ('proyectos.csv')
17 );

Table created.

El comando CREATE TABLE no realiza ninguna validación de los datos que hay cargados en el fichero de texto, de hecho el comando va a funcionar incluso si el fichero de texto con los datos no está en el directorio. Con el comando CREATE TABLE sólo hemos creado los metadatos de la tabla externa en el diccionario de datos y le hemos indicado a Oracle como utilizar el driver ORACLE_LOADER para analizar los datos del fichero de texto.

Una vez que colocamos el fichero de texto en el directorio correcto, ya podemos acceder a los datos mediante la ejecución de un comando select:

SQL> select * from text_proyectos;

Orden Proyecto Actividad Supervisor
------- ----------- ----------- ----------
3002508 ESP Pinto Calidad RZAPA001
3002509 ESP Pinto Supervisión CGAMI001
3002510 ESP Humanes Calidad RZAPA001
3002511 ESP Humanes Supervisión GDIAZ001
3002512 ESP Humanes Instalación HPERE001

5 rows selected.

Oracle utiliza el driver ORACLE_LOADER para procesar el fichero y, de igual forma que la utilidad SQL*Loader, crea un fichero de log en el que se almacena lo que ha ocurrido al procesar el fichero. El fichero de log se habrá creado en el directorio por defecto especificado en la sentencia CREATE TABLE que utilizamos para definir la tabla externa, el nombre del fichero vendrá determinado por el nombre de la tabla seguido por el ID del proceso del sistema operativo que procesó los datos de la tabla externa:

$ ls -l
total 45
-rw-r--r-- 1 oracle dba 1031 Jul 17 2006 TEXT_PROYECTOS_12345.log
-rw------- 1 oracle dba 298 Jul 17 2006 proyectos.csv

Si Oracle detecta algún problema a la hora de procesar el fichero de texto, Oracle generará un error que será mostrado en la línea de comando así como en el fichero de log, además se crearán también un par de ficheros más, el badfile y/o el discardfile. Uno de los errores más típicos es dejar una línea en blanco al final del fichero de texto, Oracle intentará procesar esta última línea y, al no tener ningún dato, se producirá un error.

Se pueden configurar directorios separados para los distintos ficheros (LOG, BAD y DISCARD) así como para el fichero de texto con los datos. En mi opinión, es una buena idea utilizar un directorio para los datos y otro para los ficheros de log. Así pues, igual que tuvimos que crear un directorio para guardar los ficheros de texto con los datos, ahora tenemos que crear un nuevo directorio para almacenar los "logs", tanto en el sistema operativo (por ejemplo, /u01/app/oracle/texternal/log), como en Oracle (en este caso lo llamaremos texternal_log). Ahora utilizando el comando ALTER TABLE podemos cambiar la definición de la tabla externa:

SQL> alter table text_proyectos
1 access parameters
2 (
3 records delimited by newline
4 badfile texternal_log:'text_proyectos.bad'
5 logfile texternal_log:'text_proyectos.log'
6 discardfile texternal_log:'text_proyectos.dsc'
7 fields terminated by ','
8 );

También existe la opción de definir la tabla externa de forma que no se genere ningún fichero de log, bad o discard. En este caso la sentencia select va a fallar cuando se exceda el número máximo de registros rechazados por existir algún tipo de problema con los mismos, es exactamente lo mismo que ocurre con la utilidad SQL*Loader. Este límite se puede cambiar también con el comando ALTER TABLE:

SQL> alter table text_proyectos set reject_limit 250;

Carga de datos en una tabla de la base de datos

Ahora viene la parte interesante de este artículo y es donde voy a mostrar como podemos cargar los datos almacenados en una tabla externa en una tabla real de la base de datos. Además, es importante reseñar que podemos utilizar funciones PL/SQL para transformar los datos de la tabla externa antes de que sean cargados en las tablas de la base de datos.

Por ejemplo, suponiendo que en nuestra base de datos existe una tabla llamada "proyectos" con las columnas proyecto, actividad, nombre_supervisor y de que disponemos de una función PL/SQL denominada get_nombre_supervisor() que permite obtener el nombre de un supervisor en base al campo supervisor de nuestra tabla externa, entonces podemos construir la siguiente sentencia SQL para cargar los datos de nuestra tabla externa en la tabla proyectos de la base de datos:

SQL> insert into proyectos
1 (
2 select proyecto
3 , actividad
4 , get_nombre_supervisor(supervisor) nombre_supervisor
5 from text_proyectos
6 );

5 rows inserted.

Descarga de datos a una tabla externa

Oracle 10g permite crear una tabla externa utilizando datos existentes en la base de datos, estos datos se descargan a un fichero de texto utilizando el driver ORACLE_DATAPUMP. Este fichero de texto es almacenado con un formato propietario de Oracle que puede ser leído por el driver Data Pump. En este caso, es importante indicar en el comando CREATE TABLE, el tipo de driver a utilizar, ORACLE_DATAPUMP, ya que el driver por defecto es ORACLE_LOADER. La sintaxis del comando sería:

SQL> create table text_export_proy
2 organization external
3 (
4 type oracle_datapump
5 default directory texternal_data
6 location ('export_proy.dmp')
7 ) as select * from proyectos;

Evidentemente ahora podemos coger el fichero que acabamos de crear, export_proy.dmp, llevarlo a otro sistema y crear una tabla externa para leer los datos.

SQL> connect userexte@otradb
Enter password:
Connected.

SQL> create table text_proyectos
2 (
4 proyecto varchar2(30),
5 actividad varchar2(15),
6 nombre_supervisor varchar(30)
7 )
8 organization external
9 (
10 type oracle_datapump
10 default directory texternal_data
16 location ('export_proy.dmp')
17 );

Table created.

Conclusión

En este artículo hemos visto como podemos cargar y descargar datos en y hacia una base de datos utilizando tablas externas. Las tablas externas en 9i y 10g permiten mover datos integrando las funcionalidades de Oracle, SQL*Loader y Data Pump, con la mejora añadida de que se pueden utilizar sentencias SQL en el proceso.

Selects y commits a través de DB Links

Me he encontrado en muchas ocasiones problemas de desempeño por un mal diseño de la programación, generalmente por el no usar bind variables, algunas por usar bind variables cuando se podría usar una literal en histogramas. Pero generalmente los problemas más comunes son los excesos de commits.

El "Commit" es un serializador en oracle, es algo que por un pequeño instante frena todo lo que está sucediendo para lograr un:

"Log File Sync"


Una vez que se realiza el log file sync, oracle a través de redo logs nos permite recuperar una base de datos en caso de un crash en el momento hasta el punto en donde dimos commit.

Pero hay desarrolladores que abusan del uso del commit, no sé si sea que vienen desarrollando para otros manejadores de bases de datos que requieren commits a cada rato para poder permitir lecturas a más usuarios sobre bloques de datos o simplemente porque desconocen cómo ejecutar código de forma óptima dentro de oracle.

Hay un punto que no siempre es considerado por los desarrolladores, los "SELECTS" a través de db links son transacciones y que el estar dando commits muy frecuentes, puede ocasionar un problema de desempeño. Para ejemplificarlo, haremos a prueba enb un ciclo que tiene un commit dentro del ciclo, y otro ejemplo con commit fuera del ciclo:

SQL> CREATE DATABASE link LOOPBACK
2 CONNECT TO system
3 identified by alanis11
4 USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hugo-win)
5 (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))';

Enlace con la base de datos creado.

SQL> oradebug setmypid
Sentencia procesada.
SQL> oradebug event 10046 trace name context forever, level 8;
Sentencia procesada.

SQL> set timing on
SQL> declare
2 numero number:=0;
3 begin
4 for registro in (select object_id from dba_objects)
5 loop
6 select /*+ Sin Commit*/
7 count(1) into numero
8 from dba_objects@loopback
9 where object_id = registro.object_id;
10 end loop;
11 commit;
12 end;
13 /

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:19.84

SQL> declare
2 numero number:=0;
3 begin
4 for registro in (select object_id from dba_objects)
5 loop
6 select /*+ Con Commit*/
7 count(1) into numero
8 from dba_objects@loopback
9 where object_id = registro.object_id;
10 commit;
11 end loop;
12 end;
13 /

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:02:53.26


Como se puede ver, sólo estamos haciendo un select, no existe update,delete o insert, sin embargo, al ser a través de un dblink, entra como transacción y al tener los commits, se pierde mucho tiempo en esperas.

Al revisar las esperas encontramos lo siguiente. Para el commit fuera del ciclo:

SELECT /*+ Sin Commit*/ COUNT(1)
FROM
DBA_OBJECTS@LOOPBACK WHERE OBJECT_ID = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 50660 5.73 8.83 0 0 0 0
Fetch 50660 5.75 9.95 0 0 0 50660
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101321 11.48 18.80 0 0 0 50660

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 101322 0.00 0.24
SQL*Net message from dblink 101322 0.00 14.17


Y para el caso del commit dentro del ciclo:

SELECT /*+ Con Commit*/ COUNT(1)
FROM
DBA_OBJECTS@LOOPBACK WHERE OBJECT_ID = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 50660 33.84 142.56 50630 14211 182788 0
Fetch 50660 7.18 15.94 0 0 0 50660
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101321 41.03 158.50 50630 14211 182788 50660

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to dblink 151981 0.00 0.46
SQL*Net message from dblink 151981 0.00 28.84
db file sequential read 50754 0.26 59.72
latch: cache buffers lru chain 1 0.00 0.00
rdbms ipc reply 621 0.21 5.01
control file sequential read 2294 0.13 6.28
Data file init write 684 0.26 11.90
flashback buf free by RVWR 63 0.03 0.06
control file parallel write 434 0.06 0.77
db file single write 62 0.16 0.21
latch: object queue header operation 1 0.00 0.00
enq: CF - contention 1 0.01 0.01


El manual de conceptos dice lo siguiente:

"Cuando un objeto es referenciado, o el uso de una función remota es ejecutada, los database links establecen una conexión a una sesión en la base de datos remota a nombre de la sesión local. La conexión remota y la sesión son creadas únicamente si no han sido previamente creadas para el usuario que la solicitó.

Las conexiones y sesiones establecidas, persisten durante la existencia de la sesión local, a menos que la aplicación, o el usuario force de manera manual la terminación de la mima.

Hay que notar, que cuando se ejecuta en select a través de un database link, un bloqueo transaccional es requerido en los segmentos de undo. Para liberar los segmentos de undo de estos bloqueos, es necesario dar un commit o rollback."

Y efectivamente las esperas por "db file sequential read" son todas por algún segmento de undo:

WAIT #4: nam='db file sequential read' ela= 222 file#=2 block#=54448 blocks=1 obj#=0 tim=88041128812

SQL> SELECT /*+RULE*/
2 segment_name, segment_type
3 FROM dba_extents
4 WHERE file_id = 2
5 AND 54448 BETWEEN block_id
6 AND block_id + blocks -1;

SEGMENT_NAME SEGMENT_TYPE
----------------- ------------------
_SYSSMU2$ TYPE2 UNDO


Cuando por primera vez me topé con algo similar con un cliente, la optimización de su proceso consistió en eliminar los commits innecesarios, y dejar sólo un commit al final de su procedimiento, de esta forma se respetó la atomicidad del procedimiento, y se logró tener un desempeño óptimo.

Funciones determinísticas y Group by vs. Distinct(2)

Recientemente un comentario sobre la entrada de Group by vs. Distinct, me hizo dudar un poco sobre lo que había escrito en relación a group by vs. distinct, pero a la vez me obligó a investigar un poco más, y estas son mis observaciones.

Intentando plasmar lo que se tiene en el comentario, tenemos lo siguiente:

Comenzamos creando una tabla

SQL> CREATE TABLE valores(valor NUMBER(1))
2 TABLESPACE users;

Tabla creada.


Una vez que se tiene la tabla, insertamos 100 registros con sólo 10 valores distintos

SQL> INSERT
2 INTO valores
3 SELECT MOD(rownum, 10)
4 FROM dba_objects
5 WHERE rownum <>
100 filas creadas.
Creamos una función "tardada" para probar los ejemplos
CREATE OR REPLACE FUNCTION tardada(numero IN NUMBER)
RETURN NUMBER IS salida NUMBER;
BEGIN
SELECT COUNT(1)
INTO salida
FROM dba_tables
WHERE MOD(rownum, 10) = numero;
RETURN salida;
END;
/ Primero vamos a evaluar el tiempo y estadísticas sin agrupar la información SQL> set autot traceonly stat
SQL> set timing on

SQL> SELECT tardada(valor)
2 FROM valores;

100 filas seleccionadas.

Transcurrido: 00:00:05.67

Estadísticas
----------------------------------------------------------
100 recursive calls
246414 consistent gets
100 sorts (memory)
100 rows processed


Se procesó la información en más de 5 segundos consumiendo 246,000 bloques. Se procesan 100 registros, y se hacen 100 sorts

Si lo hacemos con un distinct, estas son las estadísticas

SQL> SELECT DISTINCT tardada(valor)
2 FROM valores;

Transcurrido: 00:00:05.68

Estadísticas
----------------------------------------------------------
100 recursive calls
246407 consistent gets
100 sorts (memory)
2 rows processed


Como vemos, se usan prácticamente los mismos recursos, pero al final nos regresa sólamente 2 registros. La función se ejecuta 100 veces.

Ahora lo intentamos hacer con un group by:

SQL> SELECT tardada(valor)
2 FROM valores
3 GROUP BY tardada(valor);

Transcurrido: 00:00:05.76

Estadísticas
----------------------------------------------------------
100 recursive calls
246407 consistent gets
100 sorts (memory)
2 rows processed


En este caso son las mismas estadísticas que con el distinct. En el siguiente ejemplo, que fue el que me llegó a confundir, se tiene lo siguiente:

SQL> SELECT tardada(valor)
2 FROM valores
3 GROUP BY valor;

10 filas seleccionadas.

Transcurrido: 00:00:00.65

Estadísticas
----------------------------------------------------------
10 recursive calls
24647 consistent gets
10 sorts (memory)
10 rows processed


El tiempo de ejecución fue menor a un segundo, la lectura de bloques se redujo a un 10% (24,000) bloques. El resultado nos puede llevar a la conclusión de que de esta forma, sólo se evalúa la función 10 veces debido a que sólo hay 10 valores distintos y no las 100 veces que son los registros totales de la tabla. Esto es correcto de cierta forma, es decir, la función se evalúa 10 veces porque al agrupar por "valor", nos quedamos con 10 registros a evaluar en la función. Aparentemente no hay forma de reescribir de forma simple este ejemplo de group by con un distinct, pero creo que se podría hacer de la siguiente forma:

SQL> SELECT tardada(valor)
2 FROM
3 (SELECT DISTINCT valor valor
4 FROM valores)
5 ;

10 filas seleccionadas.

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
10 recursive calls
24647 consistent gets
10 sorts (memory)
10 rows processed


Como se puede ver, se tiene el mismo consumo de recursos que en la primera situación, sin embargo creo que son dos cosas distintas los primeros ejemplos a estos últimos, ya que los últimos ejemplos, primero agrupan los registros y luego evalúan la función, es por eso que regresan 10 registros; los primeros ejemplos, evalúan la función y después agrupan, por esto, sólo regresan 2 registros.

Para poder comprobar lo que digo, voy a generar una función que siempre regrese un valor distinto:

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> CREATE OR REPLACE FUNCTION rapida(numero IN NUMBER) RETURN NUMBER IS salida NUMBER;
2 BEGIN
3 SELECT secuencia.nextval
4 INTO salida
5 FROM dual;
6 RETURN salida;
7 END;
8 /

Función creada.


Si nosotros ejecutamos los primeros ejemplos del distinct o del group by, las respuestas serán similares a la siguiente:


SQL> SELECT DISTINCT rapida(valor)
2 FROM valores;

RAPIDA(VALOR)
-------------
1
22
25
30
34
42
43
51
54
57
...
82
92
98

100 filas seleccionadas.

SQL> DROP sequence secuencia;

Secuencia borrada.

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> SELECT rapida(valor)
2 FROM valores
3 GROUP BY rapida(valor);

RAPIDA(VALOR)
-------------
1
22
25
30
34
42
43
51
54
57
...
82
92
98

100 filas seleccionadas.

Con esto, vemos que se comportan igual el distinct y el group by de mis primeros ejemplos, e incluso podemos observar que la función hash que se utiliza para evaluar duplicados es la misma, ya que regresa en el mismo orden los registros.

Ahora vamos con el ejemplo que ejecuta solamente 10 veces la función:

SQL> DROP sequence secuencia;

Secuencia borrada.

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> SELECT rapida(valor)
2 FROM valores
3 GROUP BY valor;

RAPIDA(VALOR)
-------------
1
2
3
4
5
6
7
8
9
10

10 filas seleccionadas.


Esto nos ayuda a entender que la agrupación se realiza antes de entrar a la función hash del group by o del distinct, y pudiera ser que no obtengamos el resultado deseado (aunque es un resultado muy válido, simplemente hay que cuidar el sentido).

Ahora, esto nos pone a pensar en que si tenemos una función que siempre que se introduzca un valor se obtendrá el mismo resultado, oracle debería de ser capaz de optimizar nuestro query para ejecutar menos veces la función.

Es aquí donde se tiene la función determinística:

SQL> CREATE OR REPLACE FUNCTION tardada2(numero IN NUMBER) RETURN NUMBER
2 DETERMINISTIC
3 IS salida NUMBER;
4 BEGIN
5 SELECT COUNT(1)
6 INTO salida
7 FROM dba_tables
8 WHERE MOD(rownum, 10) = numero;
9 RETURN salida;
10 END;
11 /

Función creada.


Y ahora probamos nuestros ejemplos iniciales

SQL> SELECT DISTINCT tardada2(valor)
2 FROM valores;

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
10 recursive calls
24647 consistent gets
10 sorts (memory)
2 rows processed

SQL> SELECT tardada2(valor)
2 FROM valores
3 GROUP BY tardada2(valor);

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
10 recursive calls
24647 consistent gets
10 sorts (memory)
2 rows processed


Como se puede observar, tanto en el distinct como en el group by, oracle puede hacer uso de un "cache" de resultados de funciones.

Ya como último ejemplo, si una función no es determinística, Oracle puede usar un caché. Es algo que alguna vez vi en un simposio de de Hotsos. Desconozco en su totalidad la forma de evaluar si usa o no un cache, pero el ejemplo es el siguiente:

SQL> SELECT DISTINCT
2 (SELECT tardada(valor)
3 FROM dual)
4 FROM valores;

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
58 recursive calls
24680 consistent gets
10 sorts (memory)
2 rows processed


Como se puede ver, la llamada a la función se mete dentro de un query, y con eso, oracle puede hacer uso del cache. Hay un poco más de llamadas recursivas, quizá porque oracle debe determinar si puede o no hacer uso del cache de funciones.

Así que de todo esto, me quedo con la misma impresión que en el fondo, group by y distinct son prácticamente iguales, pero lo que sí es importante, es el definir correctamente nuestras funciones, si sabemos que es una función determinística, crearla como tal, en caso de que no, pues simplemente omitir "DETERMINISTIC".

Operadores de Conjunto (caso práctico)

En la compañía, se está desarrollando una aplicación para un cliente. Ésta sirve para "clonar" esquemas dentro de una base de datos, con la flexibilidad de decidir los objetos, campos, constraints, etc. que quieres llevarte.

Una problemática que se presentó, fue la siguiente:

¿Cómo saber qué constraints se pueden replicar con una tabla que no se migra con todas las columnas?

Suponiendo que tenemos una tabla original con 3 campos, y la tabla destino, sólo contiene dos campos. Imaginemos un constraint de tipo "check" que valida que el (campo1 = 'true') or (campo2 = 'false') or (campo3 = 'lo que sea'). No podemos replicar ese constraint al nuevo esquema, ya que marcaría un error. Así que ¿cómo pudiéramos revisar, basado en nuestras columnas seleccionadas, los constraints que se pueden migrar sin problema?

La solución se puede lograr fácilmente con lógica de conjuntos


WITH registros AS
(SELECT --+materialize
constraint_name,
column_name
FROM dba_cons_columns
WHERE TABLE_NAME = 'SDO_COORD_REF_SYS')
SELECT constraint_name,
COUNT(1) numero
FROM registros
WHERE column_name IN('GEOG_CRS_DATUM_ID',
'SOURCE_GEOG_SRID')
GROUP BY constraint_name
INTERSECT
SELECT constraint_name,
COUNT(1)
FROM registros
GROUP BY constraint_name;


Primero explicaré brevemente sobre la clausula WITH.

La clausula with, que nace con Oracle 9i Release 1, en lugar de repetir código complejo, o pesado, nos permite darle un nombre, y reusar la referencia a través del nombre múltiples veces dentro de una sentencia SQL. Forma parte del estándar SQL-99. Jonathan Lewis, adicionalmente incluye el hint de "materialize" en las cláusulas with, ya que de esta forma, se materializa el resultado en una tabla temporal y al seleccionarse en múltiples partes, el resultado materializado se accede de forma más rápida.

En este ejemplo, como sé que voy a trabajar con los constraints de una sola tabla, mi universo de datos son todos los registros contenidos en "dba_cons_columns" para la tabla en cuestión. De ahí el with

WITH registros AS
(SELECT --+materialize
constraint_name,
column_name
FROM dba_cons_columns
WHERE TABLE_NAME = 'NOMBRE_TABLA')


De ahí, necesito obtener los constraints que incluyan mis columnas que voy a replicar, o por lo menos alguna de las columnas. Añado un "count" para saber cuántas de mis columnas son incluidas en el constraint.

SELECT constraint_name,
COUNT(1) numero
FROM registros
WHERE column_name IN('CAMPOS',
'A',
'BUSCAR')
GROUP BY constraint_name


Finalmente para terminar, necesito saber cuántas columnas en total tiene cada constraint.

SELECT constraint_name,
COUNT(1)
FROM registros
GROUP BY constraint_name


Teniendo esos sets de datos, usamos lógica de conjuntos y hacemos una intersección con la palabra "INTERSECT"

SQL> WITH registros AS
2 (SELECT --+materialize
3 constraint_name,
4 column_name
5 FROM dba_cons_columns
6 WHERE TABLE_NAME = 'SDO_COORD_REF_SYS')
7 SELECT constraint_name,
8 COUNT(1) numero
9 FROM registros
10 WHERE column_name IN('GEOG_CRS_DATUM_ID',
11 'SOURCE_GEOG_SRID')
12 GROUP BY constraint_name
13 INTERSECT
14 SELECT constraint_name,
15 COUNT(1)
16 FROM registros
17 GROUP BY constraint_name;

CONSTRAINT_NAME NUMERO
------------------------------ ----------
COORD_REF_SYS_FOREIGN_GEOG 1

SQL>


Esto nos da el resultado que si voy a incluir sólo las columnas 'GEOG_CRS_DATUM_ID', 'SOURCE_GEOG_SRID', sólo puedo llevarme el contraint COORD_REF_SYS_FOREIGN_GEOG.

Pasando ya de lleno a la lógica de conjuntos, Oracle cuenta con tres operadores de conjuntos

UNION [ALL]
MINUS
INTERSECT

Se pueden usar para unir varios queries y llevar a cabo la operación de conjunto deseada, llevan orden de precedencia a menos que vengan entre paréntesis.

SQL> SELECT *
2 FROM
3 (SELECT 1 FROM dual
4 UNION ALL
5 SELECT 1 FROM dual)
6 INTERSECT
7 (SELECT 1 FROM dual
8 UNION ALL
9 SELECT 2 FROM dual)
10 ;

1
----------
1


Las expresiones correspondientes en las sentencias select, deben de coincidir en número y tipo, si no se sigue esto, oracle genera un error.

Hay algunas observaciones por considerar.

No se pueden usar operadores de conjuntos en columnas de tipo BLOB, CLOB, BFILE, VARRAY, o "nested tables".

No son válidos en columnas de tipo long.

Los operadores MINUS, UNION e INTERSECT terminan agrupando los resultados. Union All no agrupa y sólo junta los resultados de las dos fuentes de datos.

Y la más importante de todas las consideraciones es que Oracle para apegarse a un estándar, cambiará (no sé si el release 11g ya lo tenga) la precedencia del comando INTERSECT para que se evalue primero, por esta razón, Oracle recomienda siempre usar paréntesis cuando se use "INTERSECT".

Rman y cómo catalogar Backups

Los esquemas de respaldo de todo mundo suelen ser muy variados, muy rara vez te encuentras con configuraciones iguales (BCVs, scripts, rman, veritas, HP, Tivoli, cartucheras virtuales, etc...). De manera personal, creo que lo más sencillo o fácil de llevar como DBA, es tener un software que administre la parte de backups y de restore de forma integrada con RMAN.

Suponiendo que tengamos Dataprotector, TSM, Netbackup, etc. integrado a RMAN, lo más sencillo es correr scripts de backup hacia canales de cinta de forma directa, mantener nuestras políticas de retención en RMAN y poder expirar y borrar de las cintas desde RMAN. Esto como DBAs nos da mucha visión de cómo se van llevando nuestros Backups, podemos generar los reportes necesarios con comandos sencillos de RMAN, etc...

Me he encontrado con algunos clientes, y recientemente a través de unos correos, con que mucha gente hace los respaldos a disco, una vez que el respaldo está en disco, se utiliza una cartuchera para llevarse el respaldo a cinta.

En este ejemplo en particular, imaginemos que el espacio en disco es reducido, y que sólo se mantiene el último backup en disco, y como se tiene respaldado en cinta la información, se pueden expirar y borrar los backups de disco anteriores.

Este es un ejemplo práctico de cómo llevar un backup, pero hay que tener varias consideraciones al hacerlos:

Debes de asegurarte de llevar un backup del controlfile. Recuerda que si tienes activado el backup automático de controlfile, y no incluyes el controlfile en tu backup, el resplado del controlfile pudier estar en otro PATH distinto a tus backupsets, considera añadir ese path a tu respaldo en cinta. Si ejecutas

RMAN> backup database include current controlfile;


El controlfile estará en el mismo path que tus backupsets.

Una vez expirado y eliminado el backup del día anterior, imaginemos que nos damos cuenta que un error de datos se tiene desde ayer y queremos recuperar la base de datos a un punto anterior (hace dos días), por lo cual el backup del día de hoy no nos sirve... ¿Qué se puede hacer para recuperar la información?, vamos a poner el ejemplo que se va a restaurar el respaldo de hace 2 días en un servidor nuevo para consultar la información sin afectar producción.

Como primer punto es, en un area de storage (en el server de desarrollo) hay que bajar el respaldo de cinta, eso lo puedes hacer con herramientas de sistema operativo o el software que usas.

Una vez que tienes tus backupsets en disco, existen 2 posibilidades, que hayas respaldado el controlfile o que no lo hayas respaldado.

En caso de que lo hayas respaldado, lo más sencillo es restaurarlo del backupset:

RMAN> SET DBID 524232147;

ejecutando el comando: SET DBID

RMAN> RUN
2> {
3> RESTORE CONTROLFILE FROM 'D:\RESPALDO\TAG20080823T172009_48DQF74D_.BKP
4> }


Este comando nos restaurará el controlfile contenido en el backupset (revisen sus logs de RMAN para poder saber cual es el backupset piece correcto), el destino del o de los controlfiles será el asignado en el parámetro control_files del archivo de inicialización (init o spfile).

Si no se respaldó el controlfile, se puede usar uno de producción, no importa que nuestro respaldo de hace 2 días no esté en el catálogo.

Una vez que ya tenemos un controlfile (el restaurado, o bien, el copiado de producción), tenemos 2 posibles escenarios:

El primero es que tengamos el controlfile del respaldo y que el path usado en el respaldo, sea el mismo usado para depositar los archivos en el server de desarrollo (en caso de Linux o Unix se puede usar un link simbólico). Si este es el caso, procedemos a montar la base de datos y empezar nuestro restore y recover:

RMAN> alter database mount;

base de datos montada
canal liberado: ORA_DISK_1

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;


Pero no siempre podemos tener la suerte de tener el mismo path, o tampoco contamos con un controlfile sin el registro de nuestro backup. ¿Qué podemos hacer en este caso?

Catalogar nuestro backup

No importa si se usa una base de datos de catálogo de rman o no, lo importante es que nuestro controlfile sepa que el respaldo de nuestra base de datos existe, la forma es la siguiente.

Reviso que no tenga un backup registrado, o por lo menos no uno que me sirva en mi controlfile:

RMAN> list backup of database;

RMAN>


Y ahora usando el controlfile actual de mi base de datos (restaurado o copiado), registro la copia del respaldo que tengo.


RMAN> CATALOG START WITH 'd:\RESPALDO';

buscando todos los archivos que coincidan con el patr¾n C:\oracle\product\flash_recovery_area\ORCL\BAC

Lista de Archivos Desconocidos para la Base de Datos
=====================================
Nombre de Archivo: d:\RESPALDO\respaldo.bkp

+Seguro que desea catalogar los archivos anteriores (introduzca SÝ o NO)? y
catalogando archivos...
catalogaci¾n realizada

Lista de Archivos Catalogados
=======================
Nombre de Archivo: d:\RESPALDO\respaldo.bkp


En este punto reviso que mi backup exista en la base de datos

RMAN> list backup of database;


Lista de Juegos de Copias de Seguridad
===================

Clave BS Tipo LV Tama±o Tipo de Dispositivo Tiempo Transcurrido Hora de Finalizaci¾n
------- ---- -- ---------- ----------- ------------ --------------------
56 Full 1.01G DISK 00:02:17 23/08/08
Clave BP: 58 Estado: AVAILABLE Comprimido: NO Etiqueta: TAG20080823T120433
Nombre de Parte: d:\RESPALDO\RESPALDO.BKP
Lista de Archivos de Datos en el juego de copias de seguridad 56
Tipo de Archivo LV SCN Pto. Ctrl. Hora de Punto de Control Nombre
---- -- ---- ---------- ------------------------ ----
1 Full 4921504 23/08/08 C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
2 Full 4921504 23/08/08 C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
3 Full 4921504 23/08/08 C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
4 Full 4921504 23/08/08 C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
5 Full 4921504 23/08/08 C:\ORACLE\PRODUCT\ORADATA\ORCL\EXAMPLE01.DBF
6 Full 4921504 23/08/08 C:\CRYPTO\ORCL\DATAFILE\O1_MF_ORCL_42K6N37T_.DBF
7 Full 4921504 23/08/08 C:\PRUEBA01.DBF


Aquí puedo comenzar con mi restore desde mis backupsets recientemente registrados

RMAN> shutdown abort

RMAN> startup mount

conectado a la base de datos destino (no iniciada)
instancia Oracle iniciada
base de datos montada

Total del -rea Global del Sistema 314572800 bytes

Fixed Size 1290328 bytes
Variable Size 142610344 bytes
Database Buffers 163577856 bytes
Redo Buffers 7094272 bytes

RMAN> restore database;

Iniciando restore en 23/08/08
canal asignado: ORA_DISK_1
canal ORA_DISK_1: sid=540 devtype=DISK

canal ORA_DISK_1: iniciando restauraci¾n del juego de copias de seguridad de archivos de datos
canal ORA_DISK_1: especificando archivo(s) de datos para restaurar del juego de copias de seguridad
restaurando el archivo de datos 00001 en C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
restaurando el archivo de datos 00002 en C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
restaurando el archivo de datos 00003 en C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
restaurando el archivo de datos 00004 en C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
restaurando el archivo de datos 00005 en C:\ORACLE\PRODUCT\ORADATA\ORCL\EXAMPLE01.DBF
restaurando el archivo de datos 00006 en C:\CRYPTO\ORCL\DATAFILE\O1_MF_ORCL_42K6N37T_.DBF
restaurando el archivo de datos 00007 en C:\PRUEBA01.DBF
canal ORA_DISK_1: leyendo desde la parte de copia de seguridad d:\RESPALDO\RESPALDO.BKP


Consideración adicional, usen el comando set new name en caso de que deban restaurar a un path distinto.

Espero que les sea de utilidad, ya sea para clonar una base de datos o simplemente recuperar la información.

Valores nulos NVL

Evaluar valores nulos y ejecutar una sola sentencia SQL, este tipo de condiciones las he visto de dos formas principalmente:

haciendo uso de un "OR"

(campo1 IS NULL OR campo1 = :b1)

o bien el uso de NVL o decode

campo1 = nvl(:b1, campo1)
campo1 = decode(:b1, null, campo1,:b1)

Pero ¿cuál de los dos pudiera ser la mejor opción?, a mi forma de ver, el NVL (o decode) es la mejor opción.

Para poder crear nuestro ejemplo, tenemos una tabla con 4 campos, el primero de baja cardinalidad, el segundo de muy alta cardinalidad y dos campos restantes con la misma distribución de datos. Crearemos unos índices y generaremos estadísticas.

SQL> CREATE TABLE prueba AS
2 SELECT owner campo1,
3 object_name campo2,
4 created campo3,
5 created campo4
6 FROM dba_objects;

Tabla creada.

SQL> CREATE INDEX indice1 ON prueba(campo1, campo2);

Índice creado.

SQL> CREATE INDEX indice3 ON prueba(campo3);

Índice creado.

SQL> CREATE INDEX indice4 ON prueba(campo4);

Índice creado.

SQL> BEGIN
2 dbms_stats.gather_table_stats(ownname => 'SYS', tabname => 'PRUEBA', cascade => TRUE);
3 END;
4 /

Procedimiento PL/SQL terminado correctamente.

SQL> explain plan for
2 SELECT *
3 FROM prueba
4 WHERE(campo1 IS NULL OR campo1 = :b1)
5 AND(campo2 IS NULL OR campo2 = :b2);

------------------------------------
Id Operation Name
------------------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL PRUEBA
------------------------------------
En este caso el optimizador de costos, decide simplemente hacer un full table scan a la tabla. e incluso si se usa el hint "USE_CONCAT", el plan de ejecución no cambia.

Para nuestro segundo ejemplo usamos el NVL

---------------------------------------
Id Operation
---------------------------------------
0 SELECT STATEMENT
1 CONCATENATION
* 2 FILTER
* 3 TABLE ACCESS FULL
* 4 FILTER
5 TABLE ACCESS BY INDEX ROWID
* 6 INDEX SKIP SCAN
---------------------------------------

2 - filter(:B2 IS NULL)
3 - filter("CAMPO1"=NVL(:B1,"CAMPO1") AND "CAMPO2" IS NOT NULL)
4 - filter(:B2 IS NOT NULL)
6 - access("CAMPO2"=:B2)
filter("CAMPO2"=:B2 AND "CAMPO1"=NVL(:B1,"CAMPO1"))

En este caso podemos notar dos cosas interesantes; la primera va relacionada con la descomposición de un sólo query a dos sentencias SQL. La primera opción que muestra, es el "peor de los casos", y la segunda opción, es el caso que tiene la más grande cardinalidad y por lo tanto, nos regresaría menos registros, en este caso un index skip scan parece ser la mejor opción. Lo segundo a notar, es la operación "FILTER", que significa, que sólo si se cumple la condición de filtro, se ejecutá la parte del plan que depende de esa operación. Un ejemplo claro de la situación de filter es:

SQL> SELECT *
2 FROM prueba
3 WHERE 1=2;
----------------------------
Id Operation
----------------------------
0 SELECT STATEMENT
* 1 FILTER
2 TABLE ACCESS FULL
----------------------------

1 - filter(NULL IS NOT NULL)

Estadísticas
----------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
461 bytes sent via SQL*Net to client
389 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Se puede ver el "FILTER" que dice "null is not null" lo cual siempre evalúa a Falso y por consiguiente, el Full Table Scan nunca se ejecuta (0 gets).

Volviendo a nuestro ejemplo del NVL, oracle decide expandir el plan de ejecución, apostando a que pudiera tener suerte e ir por pocos datos.

No hay forma de controlar de forma sencilla el plan de ejecución, ya que oracle siempre intentará obtener la menor cantidad de registros basado en las estadísticas.

El parámetro "_or_expand_nvl_predicate" es el que regula esta expansión de plan de ejecución:

SQL> alter session set "_or_expand_nvl_predicate"=false;Sesión modificada.

SQL> explain plan for
2 SELECT *
3 FROM prueba
4 WHERE campo1 = nvl(:b1, campo1)
5 AND campo2 = nvl(:b2, campo2);

Explicado.
---------------------------
Id Operation
---------------------------
0 SELECT STATEMENT
* 1 TABLE ACCESS FULL
---------------------------

Al ser un parámetro escondido, no recomiendo por ningún motivo cambiarlo de true a false. Si por alguna razón quieren deshacerse del plan extendido, se puede usar el hint "NO_EXPAND".

Ahora, ¿qué sucede cuando tienes dos campos con la misma cardinalidad, y oracle tiene que expandir el query?, ¿qué índice o plan de ejecución va a tomar?

En primera instancia, hubiera creído que tomaría el primer índice en orden alfabético (ya que el optimizador en muchos casos selecciona de esa forma), pero la realidad es que para la expansión de predicados, lo que importa es el orden inverso en el predicado del query:

SQL> explain plan for
2 SELECT *
3 FROM prueba
4 WHERE campo3 = nvl(:b1, campo3)
5 AND campo4 = nvl(:b2, campo4);

-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 CONCATENATION
* 2 FILTER
* 3 TABLE ACCESS FULL PRUEBA
* 4 FILTER
* 5 TABLE ACCESS BY INDEX ROWID PRUEBA
* 6 INDEX RANGE SCAN INDICE4
-------------------------------------------------


SQL> explain plan for
2 SELECT *
3 FROM prueba
4 WHERE campo4 = nvl(:b1, campo4)
5 AND campo3 = nvl(:b2, campo3);

-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 CONCATENATION
* 2 FILTER
* 3 TABLE ACCESS FULL PRUEBA
* 4 FILTER
* 5 TABLE ACCESS BY INDEX ROWID PRUEBA
* 6 INDEX RANGE SCAN INDICE3
-------------------------------------------------
Hasta aquí, ya pudimos ver que de alguna forma podemos hacer que oracle tome un índice que nos interesa, pero en realidad, muchas veces el plan que oracle decide, no nos es suficiente, por ejemplo:

Imaginemos que tenemos un reporte que va hacia una tabla de facturación. El reporte permite la entrada de un rango de fecha y/o un rango de número de facturas. En este ejemplo, al usar NVL, oracle hará la expansión (seguramente) basado en el rango de número de facturas, ya que este campo suele tener mayor cardinalidad. Pero si nosotros sabemos de antemano, que el 90% de las ejecuciones son con un rango de fechas y no con un rango de número de facturas, ¿qué se puede hacer?

Podemos expandir manualmente el query.

Suponiendo que tenemos el siguiente query:

SQL> explain plan for
2 SELECT *
3 FROM prueba
4 WHERE campo2 = nvl(:b1, campo2)
5 AND campo3 = nvl(:b2, campo3);

-------------------------------------------------
Id Operation Name
-------------------------------------------------
0 SELECT STATEMENT
1 CONCATENATION
* 2 FILTER
* 3 TABLE ACCESS FULL PRUEBA
* 4 FILTER
* 5 TABLE ACCESS BY INDEX ROWID PRUEBA
* 6 INDEX SKIP SCAN INDICE1
-------------------------------------------------

Podemos observar que se hace un skip scan del índice 1 o bien un FTS a la tabla, pero si sabemos de antemano que la mayoría de las veces la variable :b1 vendrá nula y no así :b2, lo mejor sería rehacer nuestro query para que al evaluar las variables, pueda tomar un mejor plan de ejecución.

Esto se puede lograr de la siguiente manera


SQL> explain plan for
2 SELECT *
3 FROM prueba
4 WHERE :b1 is null
5 AND campo3 = nvl(:b2, campo3)
6 union all
7 SELECT *
8 FROM prueba
9 WHERE :b1 is not null
10 AND campo2 = nvl(:b1, campo2)
11 AND campo3 = nvl(:b2, campo3);

--------------------------------------------------
Id Operation Name
--------------------------------------------------
0 SELECT STATEMENT
1 UNION-ALL
2 CONCATENATION
* 3 FILTER
* 4 TABLE ACCESS FULL PRUEBA
* 5 FILTER
6 TABLE ACCESS BY INDEX ROWID PRUEBA
* 7 INDEX RANGE SCAN INDICE3
8 CONCATENATION
* 9 FILTER
* 10 TABLE ACCESS FULL PRUEBA
* 11 FILTER
* 12 TABLE ACCESS BY INDEX ROWID PRUEBA
* 13 INDEX SKIP SCAN INDICE1
--------------------------------------------------


3 - filter(:B1 IS NULL AND :B2 IS NULL)
4 - filter("CAMPO3" IS NOT NULL)
5 - filter(:B1 IS NULL AND :B2 IS NOT NULL)
7 - access("CAMPO3"=:B2)
9 - filter(:B1 IS NOT NULL AND :B1 IS NULL)
10 - filter("CAMPO3"=NVL(:B2,"CAMPO3") AND "CAMPO2" IS NOT NULL)
11 - filter(:B1 IS NOT NULL AND :B1 IS NOT NULL)
12 - filter("CAMPO3"=NVL(:B2,"CAMPO3"))
13 - access("CAMPO2"=:B1)
filter("CAMPO2"=:B1)Y de esta forma tenemos 4 filters. El primero, si :b1 y :b2 son nulos, entonces ejecuta un FTS. El segundo, si :b1 es nulo y :b2 no es nulo, utiliza el índice 3. El tercero, si :b1 es nulo y :b1 no es nulo, siempre se iguala a false por lo cual no se ejecuta. El cuarto evalúa que :b1 no sea nulo, siendo este, el mejor plan de ejecución y presenta el acceso a través del Index Skip Scan del índice 1.

Me ha tocado ver a diversos programadores que les es más fácil programar código dinámico y presentar las sentencias SQL con los predicados que no tienen un valor nulo. Así que si alguien tiene un código que maneje un cursor de referencia dinámico, o un armado de un query de forma dinámica y lo quiere compartir con nosotros, es más que bienvenido.

Recuperación de una base de datos en modo noarchivelog

1. Verificar la existencia de una copia de seguridad de base de datos
La copia de seguridad que se debe de tener para este tipo de recuperaciones se hace con la base de datos cerrada por lo tanto:
Paramos la base de datos:
SQL> shutdown inmediate;
SQL> exit
Copiamos los archivos de la base de datos para realizar la copia:
$ cp $BASE_DE_DATOS $BACKUP_BASE_DE_DATOS
Siendo $BASE_DE_DATOS, la ubicación de los archivos de base de datos y
$BACKUP_BASE_DE_DATOS, la ubicación del backup de base de datos
Nos conectamos a la base de datos y la volvemos a levantar:
$ sqlplus /nolog
SQL> connect /as sysdba
SQL> startup
2. Recuperar base de datos modo noarhivelog
Teniendo un backup correcto de la base de datos, la recuperación requiere los siguientes pasos:
Nos conectamos a la base de datos y tiramos la instancia:
$ sqlplus /nolog
SQL> connect /as sysdba
SQL> shutdown abort
Copiamos nuestro backup a la ubicación de los archivos de base de datos:
SQL> !cp -r $BACKUP_BASE_DE_DATOS $BASE_DE_DATOS
Iniciamos la instancia y montamos la base de datos:
SQL> connect /as sysdba
SQL> startup
3. Notas finales
!!Se ha llevado a cabo una recuperación de base de datos. Las cosas que hay que tener en cuenta es que si el backup que teníamos era de las 9 de la mañana y el error se produjo a las 3 de la tarde, las operaciones realizadas en ese tiempo al realizar la restauración de la base de datos se pierden.!!

Conceptos básicos tunning Oracle

Top-Down
Cuando realizamos una puesta a punto de nuestros sistemas, ORACLE recomienda una metodología Top-Down.
Ejemplo:
Prioridad Área que hay que examinar y realizar Tuning
• Tuning the Data Design
• Tuning the Aplication Design
• Tuning Memory Allocation
• Tuning I/O and Physical Structure
• Tuning Resource Contention
• Tuning the underlying Platform(s)
Alert log
Los “Alert logs” son registros que contienen la información de “mensajes de errores” obtenidos por la variedad de actividades que se realizan en la base de datos. Estas actividades y registros están almacenados cronológicamente del mas antiguo al más reciente. Este registro se encuentra en el directorio que hayamos fijado en nuestro init.ora bajo el parámetro BACKGROUND_DUMP_DEST. En una arquitectura OFA se recomienda que el directorio donde se encuentren estos archivos sea el siguiente: $ORACLE_BASE/adin/SID/bdump en sistemas UNIX. En sistemas tales como Windows 2000 según este estándar podría encontrarse en %ORACLE_BASE%\admin\SID\bdump El nombre de este alert log será alert_ seguido de la instancia de la base de datos.

Una de las cosas que podemos hacer para tener un seguimiento del alert log es mantener en un archivo las últimas 1000 líneas de este registro. Para hacer esto podemos echar mano del comando tail
Ejemplo
cd $ORACLE_BASE/admin/ALUMNOS/bdump
tail –1000 alert_alumnos.log > alert_alumnos.log.ultimas
mv alert_alumnos.log.ultimas alert_alumnos.log

TRACE FILES
Oracle trace files son archivos de texto que contienen información de la sesión para el proceso que han creado. “Trace files” pueden ser generados por procesos background . Muchos de estos “trace files” contienen información sobre el tuning que se le debe hacer a una base de datos.

- Background Trace Files:
Los “trace files” ( ficheros de traza ) generados por los procesos background pueden ser encontrados en el directorio especificado en el init.ora bajo el parámetro de BACKGROUND_DUMP_DEST . En sistemas que sigan el modelo OFA, $ORACLE_BASE/adin/SID/bdump en sistemas UNIX. En sistemas tales como Windows 2000 según este estándar podría encontrarse en %ORACLE_BASE%\admin\SID\bdump
Ejemplo de trace files para los procesos background:

Nombre del proceso Sistemas UNIX Sistema Windows
PMON Pmon_xxxx.trc sidPMON.trc
SMON Smon_xxxx.trc sidSMON.trc
DBW0 Dbw0_xxxx.trc sidDBW0.trc
LGWR Lgwr_xxxx.trc sidLGWR.trc
CPT Cpt_xxxx.trc sidCPT.trc
ARC0 Arc_xxxx.trc sidARC0.trc

- User trace files
Los ficheros “user trace files” se encuentran también en el directorio especificado en el init.ora mediante el parámetro BACKGROUND_DUMP_DEST. Este fichero también incorpora en nombre de la instancia en los sistemas UNIX
Ejemplo:
Siendo alumnos el nombre de la instancia de nuestra base de datos ora_alumnos_4327.trc (sistemas UNIX) ora04327.trc (Windows 2000) , para identificar a qué usuario corresponde este trace file debemos de recurrir a dos vistas: V$PROCESS y v$SESSION.
Con la siguiente consulta podríamos obtener el usuario cuyo proceso corresponde al 4327 :
SQL > SELECT s.username,p.spid FROM v$session s, v$process p
• WHERE s.paddr = p.addr AND p.background is null;
USERNAME SPID ----------- ---- -- -------------------- -- - ----------------------- ---------
USER1 4282
USER2 5436
USER3 4327
USER4 4678
Activando las trazas de usuario:
Cuando ocurre un error, el archivo de traza se genera automáticamente, no obstante si el administrador de base de datos quiere que este archivo no solo se genere cuando haya un error entonces deberá realizar lo siguiente:
Instance-Level Tracing:
Si ponemos en init.ora el parámetro SQL_TRACE=TRUE, todos los procesos generarán sus archivos de traza. El parámetro por defecto para SQL_TRACE es FALSE.

User Level Self-Tracing Un usuario puede activar o desactivar en su propia sesión su trace file utilizando los siguientes comandos de SQL :
SQL > ALTER SESSION SET SQL_TRACE=TRUE
SQL > ALTER SESSION SET SQL_TRACE=FALSE

User Level DBA Tracing También podemos iniciar el user trace file mediante PL/SQL haciendo una llamada al paquete DBMS_SYSTEM. Este paquete de PL/SQL contiene un procedimiento que nos permite activar el user trace file de algún usuario simplemente sabiendo el sid y el serial ( serial# )
1. Identificamos el sid y el serial# de un usuario llamado DAVID :
SQL> SELECT username, sid, serial#
FROM v$session
WHERE username = ‘DAVID';
USERNAME SID SERIAL
---------------------------------------- --------------------- ------------
DAVID 10 2642
2. Activamos el trace file para la sesión de DAVID usando el paquete DBMS_SYSTEM PL/SQL y los valores para el sid y el serial que hemos obtenido en el punto 1.
SQL > exec sys.dbms_system.set_sql_trace_in_session(10,2642,TRUE);

Hemos utilizado el procedimiento set_sql_trace_in_session del paquete dbms_system
3. La sesión de DAVID generará un trace file que estará especificado en el parámetro USER_DUMP_DEST de nuestro init.ora En caso de que queramos para el trace file para el usuario DAVID ejecutaremos lo siguiente:
SQL > exec sys.dbms_system.set_sql_trace_in_session(10,2642,FALSE);

Cómo interpretar User trace file
Una vez que estos trace file se han generado hay que aprender a interpretarlos. Se puede interpretar el contenido de un user_trace_file usando la utilidad TKPROF
Gestionando trace files
Podemos gestionar el tamaño de estos archivos mediante una serie de parámetros en el INIT.ora
Parámetro especificado Tamaño máximo para User Trace
MAX_DUMP_FILE_SIZE=10000 10000 OS bloques
MAX_DUMP_FILE_SIZE=500K 500000 bytes
MAX_DUMP_FILE_SIZE=10M 10 megabytes
MAX_DUMP_FILE_SIZE=unlimited No limits on file size

Performance Tuning Views
Hay dos tipos de vistas de ORACLE que nos dan información:
o Las v$ dynamic performance views
o Las DBA Data dictionary views

Los nombres de las vistas v$ son generalmente singulares de las DBA views que utilizamos con nombres en plural. Un ejemplo para esto es V$datafile vs DBA_DATA_FILES.
Muchas de las vistas están disponibles cuando la base de datos está en estado nomount ó mount. Las DBA views sólo están disponibles cuando la base de datos está abierta (open)

Hay aproximadamente unas 225 V$, estas vistas están basadas en tablas dinámicas conocidas colectivamente como X$ tablas. Estas tablas existen en memoria con nombres encriptados como X$KSMPS.
Ejemplo de v$dynamic performance views
Nombre de la vista Descripción de la vista
V$SGASTAT Tamaño de todas las estructuras de memoria
V$STATNAME Estadísticas del V$SESSTAT
V$SYSSTAT Estadísticas del uso de cpu para todas las sesiones activas
V$SESSTAT Estadísticas de las sesiones activas
V$SESSION Sesiones activas.
V$WAITSTAT Refleja la contención en términos del número de esperas en cuatro tipos de bloques de rollback

Ejemplo de DBA Views
Nombre de la vista Descripción de la vista :
DBA_TABLES Tablas, líneas e información de bloques
DBA_INDEXES Índices, líneas e información de bloques
DBA_DATA_FILES Ubicación de los datafiles, nombre e información del tamaño.
DBA_SEGMENTS Información sobre el espacio consumido en los segmentos de base de datos

Ejemplo de consultas (query) para este tipo de vistas
V$:
SQL > Select s.username,n.name,t.value
from v$session, v$statname n,v$sesstat t
where s.sid=t.sid
and t.statistic#=n.statistic#
and s.username ='DAVID';

DBA VIEW:
SQL > Select table_name, chain_cnt
from dba_tables
where owner = ‘DAVID'
and chain_cnt !=0;

Error ORA-00257

instrucciones que explican cómo activar y desactivar el modo de archivelog (archivado automático) de una base de datos Oracle. Utilizando "SQL Plus" y el comando "alter database archivelog".

Cómo activar el modo de archive log (archivado automático) de una base de datos Oracle

Este manual muestra y explica cómo activar el modo de archivelog de una base de datos Oracle

Abriremos la aplicación SQL Plus de Oracle desde una ventana de MS-DOS ("Inicio" - "Ejecutar" - "cmd"):

sqlplus /nolog

Nos conectamos con un usuario con suficientes privilegios a la base de datos Oracle a la que queramos activarle el modo Archive Log (ARCHIVELOG), con el comando:

connect usuario/contraseña@NOMBRE_BASE_DATOS as sysdba

Si ejecutamos este comando y la base de datos está en modo OPEN (abierta):

alter database archivelog;

Nos dará el siguiente error:

alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted EXCLUSIVE and not open for this operation

Indicando que para poder cambiar la base de datos Oracle a modo archivado (archive log) hay que iniciarla en modo "mount" (montado). Para ello hay que detenerla e iniciarla con los siguientes comandos:

shutdown immediate;

Resultado comando:

Database closed.
Database dismounted.
ORACLE instance shut down.

La iniciamos en modo "mount" con el comando:

startup mount;


Resultado comando:

ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.

Para cambiar a modo archivado ejecutaremos el siguiente comando:

alter database archivelog;

Resultado comando:

Database altered.

Volveremos a inciar la base de datos:

alter database open;

Resultado comando:

Database altered.

Para activar el archivado automático comprobamos el valor del parámetro "log_archive_start", si está a "false" lo pondremos a "true". Para consultar el valor actual del parámetro ejecutaremos el siguiente comando:

show parameter log_archive_start;

Devolverá el siguiente resultado:

NAME TYPE VALUE
------------------- -------- --------
log_archive_start boolean FALSE

Modificaremos el parámetro "log_archive_start" a "true" con el siguiente comando:

alter system set LOG_ARCHIVE_START=TRUE SCOPE=spfile;

Resultado comando:

System altered.

Al ejecutar "SCOPE=spfile" haremos que los cambios se guarden definitivamente.

Para que los cambios tengan efecto es recomendable parar y volver a iniciar la base de datos:

shutdown immediate;

Iniciaremos la base de datos en modo normal:

startup;

Notas:

* Para comprobar en qué modo está la base de datos:

select log_mode from v$database;

Resultado:

LOG_MODE
-------------------------
NOARCHIVELOG (está en modo no archivado)

select log_mode from v$database;

LOG_MODE
--------------------
ARCHIVELOG (está en modo archivado)

Otra forma:

archive log list;

Resultado:

Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:/oracle/ora92/RDBMS
Oldest online log sequence 395
Next log sequence to archive 397
Current log sequence 397

* Para ver el valor parámetro de archivado automático:

show parameter log_archive_start;

NAME TYPE VALUE
------------------- ----------- -----
log_archive_start boolean FALSE

DESACTIVAR EL MODO ARCHIVE LOG

Abriremos la aplicación SQL Plus de Oracle desde una ventana de MS-DOS ("Inicio" - "Ejecutar" - "cmd"):

sqlplus /nolog

Nos conectamos con un usuario con suficientes privilegios a la base de datos Oracle a la que queramos desactivarle el modo Archive Log (ARCHIVELOG), con el comando:

connect usuario/contraseña@NOMBRE_BASE_DATOS as sysdba

Ejecutamos el siguiente comando para desactivar el modo archive log:

alter system archive log stop;

Mostrará el siguiente resultado:

System altered.

Para comprobar que se ha desactivado correctamente:

archive log list;

Mostrará el siguiente resultado:

Database log mode Archive Mode
Automatic archival Disabled
Archive destination C:/oracle/oradata/bdtest/archive
Oldest online log sequence 70
Next log sequence to archive 72
Current log sequence 72

A continuación os mostramos todo el contenido de la ventana de MS-DOS:

C:>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Jul 14 12:30:36 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect /@BDLOCAL as sysdba;
Connected.
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

SQL> show parameter log_archive_start;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted EXCLUSIVE and not open for this operation
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> alter database open;

Database altered.

SQL> show parameter log_archive_start;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
SQL> alter system set LOG_ARCHIVE_START=TRUE SCOPE=spfile;

Sistema modificado.

SQL> show parameter log_archive_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_2 string
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string enable
log_archive_dest_state_10 string enable
log_archive_dest_state_2 string enable
log_archive_dest_state_3 string enable
log_archive_dest_state_4 string enable
log_archive_dest_state_5 string enable
log_archive_dest_state_6 string enable
log_archive_dest_state_7 string enable
log_archive_dest_state_8 string enable
log_archive_dest_state_9 string enable
SQL> show parameter log_archive_dest

SQL> ALTER SYSTEM ARCHIVE LOG STOP;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Disabled
Archive destination C:/oracle/oradata/bdtest/archive
Oldest online log sequence 70
Next log sequence to archive 72
Current log sequence 72






Para realizar este manual hemos utilizado:
* Oracle Enterprise 9.2.
* Oracle XE.
* Windows XP.

Bloquear el acceso a base de datos por IP

OBJETIVO

Cómo bloquear desde el sqlnet.ora el acceso a la base de datos desde una ip concreta.

BLOQUEAR EL ACCESO A ORACLE DESDE UNA IP

En caso de no tener un firewall para bloquear el acceso de ciertas ips a una base de datos lo podemos realizar a través del sqlnet.ora.

El “secreto” para bloquear o restringir el acceso por IP a la base de datos se realiza en el archivo sqlnet.ora. Este archivo lo podemos encontrar en el directorio $ORACLE_HOME/network/admin junto con los archivos tnsnames.ora y listener.ora

Editamos el archivo sqlnet.ora y añadimos las siguientes líneas:

tcp.validnode_checking = yes

Con esto conseguimos chequear los listeners que tengamos activos.

A continuación escribimos lo siguiente:

tcp.invited_nodes = (hostnameA, hostnameB)
tcp.excluded_nodes = (192.168.2.15)

Con tcp.invited_nodes puedo especificar qué máquinas quiero que su conexión sea aceptada por las base de datos.

Con tcp.excluded_nodes excluimos las máquinas que no queremos que se conecten a las bases de datos.

La idea de este mecanismo es realizar una lista de las máquinas que queremos que se conecten o realizar una lista de las máquinas que no queremos que se conecten a nuestra base de datos.

A pesar de este mecanismo de seguridad, no podemos decir que estemos totalmente exentos de recibir ataques.

Algunas reglas a tener en cuenta para generar la lista de IPS / hostnames invitados o excluidos pueden ser la siguiente:

• Poner todos los nodos excluidos en una única línea.
• Poner todos los nodos invitados en una única línea.
• Se debería incluir en el listado de nodos invitados localhost.

Después de introducir estas reglas en nuestro sqlnet.ora debemos de reiniciar los listeners de la máquina.

$ lsnrctl stop nb_listener
$ lsnrctl start nb_listener

Siendo nb_listener el nombre del listener que tengamos iniciado en la máquina y que podemos comprobar en el archivo listener.ora.
Con nuestro usuario oracle realizamos lo siguiente:

Privilegios SYSOPER y SYSDBA

OBJETIVOS

Breve articulo para referenciar a los usuarios con privilegios sysoper,sysdba el archivo de contraseñas de oracle y el rol DBA.

INTRODUCCIÓN

Cuando nos conectamos a oracle, podemos diferenciar entre dos grupos de usuarios:grupo 1: Usuarios que sólo se pueden conectar a la base de datos cuando está abierta. grupo 2: Usuarios que se pueden conectar a la base de datos tanto cuando esté abierto como cuando esté cerrada.

Para saber sobre tipos de arranque de base de datos

Los usuarios del Grupo 2 son aquellos que tiene el privilegio SYSOPER y/o el SYSDBA.Estos dos privilegios caracterizan a los usuarios de oracle puesto que pueden hacer operaciones de "seguridad" como arranque y parada de base de datos

Para poder conectarte con estos privilegios sysdba y sysoper tienes que conectarte indicándo que quieres conectarte con esos privilegios.

1. “sys as sysdba”
2. “sys as sysoper

Archivo o fichero de contraseñas

En oracle existe un fichero de contraseñas donde se limita cual es el número de usuarios que se pueden crear en la base de datos con estos privilegios.Este fichero de oracle contiene las contraseñas de los usuarios que tienen los privilegios de SYSDBA y/o SYSOPER. Este archivo se crea cuando creamos la base de datos, sin embargo también puede ser recreado mediante la utilidad ORAPWD.Para utilizar este comando y recrear el archivo de contraseñas tenemos en cuenta lo siguiente:

El ejecutable orapwd se encuentra en $ORACLE_HOME/bin/orapwd

Ejemplo:

$orapwd file=$ORACLE_HOME/dbs/orapwdorasite.ora password=oracle entries=12

File= nombre del fichero de contraseñas.

Password= contraseña para SYS AS SYSDBA.

Entries= número máximo de DBA.

Los argumentos file y password son obligatorios.

Como vemos mediante entries limitamos el número de usuarios máximos que pueden tener ese privilegio en la base de datos...

ROL DBA

Existe un rol predefinido llamado DBA que se crea con cada base de datos Oracle. Este rol contiene TODOS los privilegios del sistema. Sólo debe concederse a los que vayan a ser superusuarios ("administradores de base de datos").

Los usuarios con este privilegio son los siguientes:

1. SYS
2. SYSTEM

SYS: Cuando instalamos por primera vez oracle ( versión 9i ) en caso de que el instalador no te permita cambiar las clave de este usuario la clave por defecto es CHANGE_ON_INSTALL. Con este usuario se gestion el diccionario de datos
SYSTEM: Cuando instalamos por primera vez oracle ( versión 9i ) en caso de que el instalador no te permita cambiar las clave de este usuario la clave por defecto es MANAGER. Bajo este usuario se suelen instalar las tablas de las aplicaciones, como Forms, reports .. etc
Por seguridad es conveniente cambiar estas claves