Implementación de una Réplica Standalone de Base de Datos Oracle 11g

Hace mucho que no escribo en este espacio, pero entre el trabajo y las actividades en el hogar, no queda mucho tiempo para escribir.

En esta entrega mostraré los pasos para implementar una réplica standby de Base de Datos sobre Windows. He de mencionar que esto lo hice en base a la documentación de Oracle para implementar replicas sin dataguard para la versión 9i, que estaba guardado en mi empresa.

Este tipo de réplica aún es utilizado en muchos lugares, ya que la implementación no es muy compleja y en cuestiones de licenciamiento, no tiene costo mas que el de la BD que se está creando.

Actualmente lo tengo aplicado con la versión 11g, y una de las ventajas es que se puede utilizar la modalidad snapshot database, útil para pruebas de tipo UAT (User Aceptance Test) sin necesidad de que entren a los entornos productivos o hacer carga de información a un entorno de desarrollo.

Las condiciones para implementar la réplica son las siguientes:

• Las version de la BD primaria y la réplica deben ser la misma
• La versión sistema operativo donde se encuentra la BD primaria debe ser del mismo tipo que la réplica (no se pueden mezclar linux en la primaria con un windows en la réplica, por ejemplo)

Ahora si, vamos a colocar los pasos (recuerden que la implementación que describo es para un ambiente con SO Windows Server 2003, en caso de utilizar otro hay que revisar en la documentación de oracle particularidades para su SO.

En el servidor de Producción

1.- Realizar un respaldo en Frío de la Base de Datos (Datafiles, Controlfiles, Redologs, PWDFile y SPFILE)

2.- Crear un PFILE a partir del SPFILE y colocarlo en la carpeta del respaldo en Frío
      SQL> create pfile = 'ruta\SIDPfile.ora' from spfile;

3.- Crear un STANDBY CONTROLFILE y colocarlo en la carpeta del respaldo en Frío
      SQL>alter database create standby controlfile as 'ruta\standby.ctl';

4.- Copiar la carpeta del respaldo en Frío en el servidor para la réplica.


En el servidor de Réplica

5.- Instalar software de Base de Datos Oracle con la misma versión que en el de Producción

6.-Crear una instancia con DBCA la cual deberá tener el mismo SID que la de Producción

7.- Modificar el PFILE de la carpeta del respaldo en frío

• Modificar rutas y nombres de los controlfiles por los de standby control files en el parametro *.control_files
• Modificar ruta de destino de archives (si aplica)
• Si los datafiles no van a quedar en la misma ruta que en el servidor de producción, utilizar el parametro *.db_file_name_convert para hacer las "traducciones de rutas"
• Si los redolog no estarán en la misma ruta que en el servidor de producción utilizar el parametro  *.log_file_name_convert (Se pueden utilizar "patrones" de rutas, por ejemplo si yo seteo en la ruta "C:\DIR\FILES\" se traduciran todos las carpetas que estén en la raíz de esa ruta [FILES] y lo que esté dentro de ella)

8.- En una consola de SQLPlus crear un SPFILE a partir del PFILE que se modificó, con ello se creará un nuevo SPFILE en la ruta del original

      SQL>create spfile from pfile='ruta\SIDpfile.ora';

      NOTA: En este paso asegurese que sus variables de entorno están configuradas para el SID de la base de datos que creó con DBCA

9.- Detener la instancia e iniciarla en modo mount

      SQL>shutdown immediate;

      SQL>startup mount;

       NOTA: Si al momento de montar la base no reporta ningún datafile faltante, quiere decir que vamos bien... pero pase lo que pase NO ABRIR LA BASE DE DATOS!

10.- Pasar la Base de Datos a modo Physical Standby, con ello al abrir la BD automáticamente se abre en modo SOLO LECTURA. Esta operación desmonta la base de datos.

       SQL>alter database convert to physical standby

Para revisar si la configuración aplicó realice la siguiente consulta:

select database_role from v$database;

11.- Reiniciar la Base de Datos en modo MOUNT

SQL>shutdown immediate;
      SQL>startup mount;

12.- Iniciar el proceso de recuperación

SQL>recover stanby database;

Con ello dará inicio el proceso de recuperación, el cual solicitará los archives que sean necesarios para completar los datafiles con las operaciones realizadas en la BD de Producción.

Este proceso se puede realizar por medio de archivos de procesamiento por lotes.

Activación de la Base de Datos Standby 

Para activar la Base de Datos en modo Lectura/Escritura en caso de perdida del servidor productivo, se realiza lo siguiente:

1.- Detener el proceso de sincronización

2.- Detener instancia de Base de Datos

3.- Iniciar la instancia en modo MOUNT

4.- Pasar la Base de Datos de Physical Standby a Primary

      SQL>alter database activate physical standby database;

5.- Verificar el rol de la base de datos ejecutando la consulta, de la cual es resultado debe ser PRIMARY

SQL>select database_role from v$database;

6.- Abrir la instancia de Base de Datos y verificar que este abierta para operaciones de lectura y escritura. El resultado de la siguiente consulta debe ser, para OPEN_MODE = READ WRITE y para DATABASE_ROLE=PRIMARY

SQL>select open_mode, database_role from v$database;

Y con ello concluimos la implementación de la réplica de Base de Datos.

Mi proceso de sincronización lo describo en la siguiente imagen

También les dejo los scripts que utilizo en .bat para transeferir los archives y hacer la recuperación en la BD.

Script de Transferencia de Producción a Réplica por FTP

TITLE Envio Replica CTS
:INICIO
cls
@ECHO OFF
cd G:\DatabaseProd\Arcs
if exist *.txt del *.txt
if not exist *.arc goto :FINAL
ren *.arc *.bck
dir /B *.bck >>ListaArchives.txt
echo open ftp-replica >>ftpenvio.txt
echo userftp>>ftpenvio.txt
echo pwd_userftp>>ftpenvio.txt
for /F "eol=t tokens=1" %%i in (ListaArchives.txt) do echo put %%i>>ftpenvio.txt
echo bye>>ftpenvio.txt
ftp -s:ftpenvio.txt
move *.bck G:\DatabaseProd\ArcsProcesados
ren  G:\DatabaseProd\ArcsProcesados\*.bck *.ARC
:FINAL
timeout 120
GOTO :INICIO


Scripts de Recuperación en Réplica


@ECHO OFF
TITLE Recover Replica CTS
:RECOVER
cd C:\Archives
if not exist *.bck goto :FINAL
ren *.bck *.ARC
sqlplus / as sysdba @SQLRecover.sql
:FINAL
timeout 120
if exist *.ARC move *.ARC O:\ArchivesCargados
goto :RECOVER


El script SQLRecover.sql tiene el siguiente contenido

recover standby database;
auto

quit

Esta ocasión si ha sido un post extenso, pero que espero sea de utilidad.

Posteriormente estaré probando Oracle Dataguard, ya que lo implemente registraré mi experiencia aqui.

Envío de Correos utilizando UTL_SMTP

Nueva Versión!

Se ha publicado una actualización al procedimiento en el siguiente enlace

Puedes seguir leyendo este post como referencia.

---

Desde que inicié en el mundo Oracle, me he encontrado con muchas implementaciones que te permiten hacer ciertos procesos desde la BD sin necesidad de hacerlos desde un programa externo. En esta ocasión veremos el envío de correos.

Tanto en la documentación de Oracle como en la web encontrarás muchos ejemplos y documentación, sin embargo ninguno (creo yo aplicado a un entorno real) y sin "partes ocultas".

A continuación mostraré un procedimiento que utiliza el package UTL_SMTP con el cual se pueden enviar correos a múltiples destinatarios en el Para, CC y CCO, así como múltiples adjuntos.

He de mencionar que tiene sus requerimientos.

1. La lista de destinatarios y de archivos debe estar separadas por punto y coma (;) y sin espacios entre ellos (esta condición la pueden cambiar modificando el fuente), Ejemplo: 'usuario.uno@dominio.com;usuario.dos@dominio.dos.mx;usuario.tres@otrodominio.com'
2. El procedimiento hace uso de un directorio lógico en donde se alojan los archivos a adjuntar, por lo que es necesario creen el directorio con el nombre que requieran en su BD y ahi coloquen los archivos.
3. Se debe especificar el tipo de correo, ya sea HTML (en el parametro se coloca 'HTML') o Texto, que es el valor por default.

Ya con esto aclarado y recordandoles que hay comentarios dentro del código, puedo decirles que con este ejemplo pueden basarse para hacer uno sin envío de adjuntos (innecesario para mi gusto, pues con el solo hecho de mandar la cadena vacía, no se manda nada).

create or replace procedure P_SendMailAttach

(

p_DestinatariosPara     VARCHAR2, 

p_DestinatariosCC       VARCHAR2 DEFAULT NULL,

p_DestinatariosBcc      VARCHAR2 DEFAULT NULL,

p_Mensaje               VARCHAR2 DEFAULT 'Haga caso omiso a este mensaje',

p_Remitente             VARCHAR2 DEFAULT 'usuario@midominio',

p_Asunto                VARCHAR2 DEFAULT 'Este correo no tiene asunto',

p_TipoMensaje           VARCHAR2 DEFAULT 'TEXT',

p_Directorio            VARCHAR2 DEFAULT 'ATTACHMENTS', -- Directorio lógico por default

p_ArchivosAdjuntos      VARCHAR2

)

AS

-- ----------------------------------------------------------------------------------------------

-- Nombre            : P_SendMailAttach

-- Autor             : Ing. Rogelio "Wazu" Rodriguez

-- Descripcion       : Envio de correos utilizando el package UTL_SMTP

-- Requerimientos    : UTL_SMTP

-- Restricciones     :

-- Revisiones:

--   Fecha        Desarrollador                 Cambio

--   ===========  ========================      =================================================

--   03-APR-2012  Rogelio Wazu Rodriguez        Creación Inicial
--   30-JUL-2012  Rogelio Wazu Rodriguez        Se modifica en el procedure
                                                file_attach en content transfer
                                                sea después de Disposition

-- ----------------------------------------------------------------------------------------------

c utl_smtp.connection;

--Procedimiento para escritura de Headers

-----------------------------------------------------------------

PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS 

BEGIN 

utl_smtp.write_data(c, name || ': ' || header || utl_tcp.CRLF); 

END;

-----------------------------------------------------------------

--Procedimiento para Adición de Destinatarios

-----------------------------------------------------------------------------------------------------------------------------------

PROCEDURE add_rcpt(p_Destinatarios in VARCHAR2) AS

l_Cadena varchar2(500) := p_Destinatarios;

l_LargoCadena number;

l_Comas number;

l_PosicionComa number := 0;

l_Destinatario varchar2(100);

BEGIN

l_LargoCadena := length(l_Cadena);

l_Comas := l_LargoCadena-length(replace(l_Cadena,';'));

--Bloque 1 Asginación del RCPT

    IF l_Comas > 0 THEN

          FOR l_segmento IN 1 .. l_Comas LOOP

            

              l_Destinatario := substr(l_Cadena, l_PosicionComa + 1, instr(l_Cadena,';',1,l_segmento) - (l_PosicionComa + 1));

            

              l_PosicionComa := instr(l_Cadena,';',1,l_Segmento);

              utl_smtp.rcpt(c, l_Destinatario);

              

          END LOOP;

    END IF;

-- Fin de Bloque 1

-- Bloque 2: Para inserción del ultimo recipient solicitado (o el primero, si es unico)

    l_Destinatario := substr(l_Cadena, l_PosicionComa + 1, l_LargoCadena);

    utl_smtp.rcpt(c, l_Destinatario);

-- Fin Bloque 2  

END;

---------------------------------------------------------------------------------------------------------------------------------

-- Procedimiento de adición de cabeceras para destinatarios

---------------------------------------------------------------------------------------------------------------------------------

PROCEDURE add_headers_rcpt(p_Destinatarios IN VARCHAR2, p_Type IN VARCHAR2) AS

l_Cadena varchar2(500) := p_Destinatarios;

l_LargoCadena number;

l_Comas number;

l_PosicionComa number := 0;

l_Destinatario varchar2(100);

BEGIN

l_LargoCadena := length(l_Cadena);

l_Comas := l_LargoCadena-length(replace(l_Cadena,';'));

--Bloque 1 Asginación del Destinatario al header

    IF l_Comas > 0 THEN

          FOR l_segmento IN 1 .. l_Comas LOOP

            

              l_Destinatario := substr(l_Cadena, l_PosicionComa + 1, instr(l_Cadena,';',1,l_segmento) - (l_PosicionComa + 1));

            

              l_PosicionComa := instr(l_Cadena,';',1,l_Segmento);

              

              -- Se generan los encabezados para envio, en caso de Bcc no se agrega el Header

              IF p_Type = 'TO' THEN

                send_header('To', l_Destinatario);

              ELSE

                send_header('Cc', l_Destinatario);

              END IF;

              

          END LOOP;

    END IF;

-- Fin de Bloque 1

-- Bloque 2: Para inserción del ultimo recipient solicitado (o el primero, si es unico)

    l_Destinatario := substr(l_Cadena, l_PosicionComa + 1, l_LargoCadena);

    IF p_Type = 'TO' THEN

       send_header('To', l_Destinatario);

    ELSE

       send_header('Cc', l_Destinatario);

    END IF;

-- Fin Bloque 2  

END;

----------------------------------------------------------------------------------------------------------------------------------------------

-- Procedimiento para adjuntar los archivos al stream del correo

---------------------------------------------------------------------------------------------------------------------

PROCEDURE file_attach(p_Archivo varchar2) AS

-- Variables para el procesamiento de Archivos

 rfile     RAW(57);

 flen      NUMBER;

 bsize     NUMBER;

 src_file  bfile;

 buffer_   integer := 57;

 i         integer := 1;

BEGIN

    -- Escribir cabecera MIME

    utl_smtp.write_data(c,'--MIME.Bound'||utl_tcp.CRLF);

    send_header('Content-Type','application/octet-stream; name="' || p_Archivo || '"');

    send_header('Content-Disposition', 'attachment; filename="' || p_Archivo || '"');
    send_header('Content-Transfer-Encoding', 'base64' );

    utl_smtp.write_data(c, utl_tcp.CRLF);

              

    -- Adición del Archivo 

    src_file := bfilename(p_Directorio, p_Archivo); 

              flen := dbms_lob.getlength(src_file);

              

              dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

              

              while i < flen loop

                    dbms_lob.read( src_file, buffer_, i, rfile );

                    utl_smtp.write_raw_data(c, utl_encode.base64_encode(rfile));

                    utl_smtp.write_data(c, utl_tcp.CRLF);

                    i := i + buffer_;

              end loop while_loop;

              

              dbms_lob.fileclose(src_file);

              utl_smtp.write_data(c, utl_tcp.CRLF||utl_tcp.CRLF);

END;

------------------------------------------------------------------------------------------------------------------------------------

--Procedimiento para separación y Adición de Archivos Adjuntos

------------------------------------------------------------------------------------------------------------------------------------

PROCEDURE add_attachments(p_Adjuntos in VARCHAR2) AS

l_Cadena varchar2(500) := p_Adjuntos;

l_LargoCadena number;

l_Comas number;

l_PosicionComa number := 0;

l_Archivo varchar2(100);

-- Variables para el procesamiento de Archivos

 rfile     RAW(57);

 flen      NUMBER;

 bsize     NUMBER;

 src_file  bfile;

 buffer_   integer := 57;

 i         integer := 1;

BEGIN

l_LargoCadena := length(l_Cadena);

l_Comas := l_LargoCadena-length(replace(l_Cadena,';'));

--Bloque 1 Adición del Archivo

    IF l_Comas > 0 THEN

          FOR l_segmento IN 1 .. l_Comas LOOP

          

              l_Archivo := substr(l_Cadena, l_PosicionComa + 1, instr(l_Cadena,';',1,l_segmento) - (l_PosicionComa + 1));

              file_attach(l_Archivo);

            

              l_PosicionComa := instr(l_Cadena,';',1,l_Segmento);

              

          END LOOP;

    END IF;

-- Fin de Bloque 1

-- Bloque 2: Para inserción del ultimo archivo (o el primero, si es unico)

    l_Archivo := substr(l_Cadena, l_PosicionComa + 1, l_LargoCadena);

    file_attach(l_Archivo);

              

-- Fin Bloque 2  

END;

---------------------------------------------------------------------------------------------------------------------------------

-- Inicio del Programa Core para el correo

BEGIN 

    -- Bloque de Apertura de Conexión

    c := utl_smtp.open_connection('165.254.254.32',25);  --Recuerden que puede ser el nombre NetBios o la IP

    utl_smtp.helo(c, '165.254.254.32'); 

    utl_smtp.mail(c, p_Remitente);-- Remitente 

    -- Bloque de Adición de Destinatarios

    IF p_DestinatariosPara IS NOT NULL THEN

        add_rcpt(p_DestinatariosPara);

    END IF;

    IF p_DestinatariosCC IS NOT NULL THEN

        add_rcpt(p_DestinatariosCC);

    END IF;

    IF p_DestinatariosBcc IS NOT NULL THEN

        add_rcpt(p_DestinatariosBcc);

    END IF;

    --Bloque de Apertura de Datos

    utl_smtp.open_data(c); 

    -- Bloque de Adición de Cabeceras de Mail

    IF p_DestinatariosPara IS NOT NULL THEN

        add_headers_rcpt(p_DestinatariosPara, 'TO');

    END IF;

    IF p_DestinatariosCC IS NOT NULL THEN

        add_headers_rcpt(p_DestinatariosCC, 'CC');

    END IF;

    send_header('Subject', p_Asunto); --Asunto

    send_header('From', p_Remitente); -- De

    send_header('MIME-Version','1.0');

    send_header('Content-Type','multipart/mixed; boundary="MIME.Bound"');

    utl_smtp.write_data(c,'--MIME.Bound'||utl_tcp.CRLF);

    send_header('MIME-Version','1.0');

    

    IF p_Mensaje IS NOT NULL THEN

    

        IF p_TipoMensaje = 'TEXT' THEN

            send_header('MIME-Version','1.0');

            send_header('Content-Type','text/plain; charset=us-ascii');

            send_header('Content-Disposition', 'inline');

        ELSE

            send_header('MIME-Version','1.0');

            send_header('Content-Type', 'text/html;charset=windows-1252');

            send_header('Content-Disposition', 'inline');

            send_header('Content-Transfer_Encoding', '8bit');

        END IF;

    

    END IF;

    -- Bloque de Escritura del Mensaje 

    utl_smtp.write_data(c, utl_tcp.CRLF ||p_Mensaje||utl_tcp.CRLF);

    utl_smtp.write_data(c, utl_tcp.CRLF);

    

    -- Bloque de Envio de Adjuntos

    add_attachments(p_ArchivosAdjuntos);

    utl_smtp.write_data(c, utl_tcp.CRLF||'--MIME.Bound--'||utl_tcp.CRLF);

    -- Bloque de Cierre de Datos y Envio del Mail

    utl_smtp.close_data(c); 

    -- Bloque de Cierre de Conexión

    utl_smtp.quit(c); 

    EXCEPTION

     WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then

        -- Bloque de Cierre de Conexión

        utl_smtp.quit(c); 

       raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

     WHEN OTHERS THEN

        -- Bloque de Cierre de Conexión

        utl_smtp.quit(c); 

        raise_application_error(-20000, 'Unable to send mail: '||sqlerrm);

    END;

END;

/ 

-- Ejemplo de uso

exec              P_SENDMAILATTACH(p_DestinatariosPara =>'usuario.uno@empresa1.com;usuario.dos@empresa2.com',

                                   p_DestinatariosCC => 'usuario@otraempresa.com',

                                   p_Remitente => 'InformesEmpresa1@empresa1.com',

                                   p_Mensaje =>'<h1> Informe </h1> <p>Cuerpo del Informe</p>',

                                   p_Asunto => 'Envío de Informe Empresa1',

                                   p_ArchivosAdjuntos => 'a6.pdf;a1.csv;A3.xml;A5.PDF;a6.txt',

                                   p_TipoMensaje => 'HTML');

Espero les sea de utilidad el código y les confirmo que funciona al 100%, cualquier pregunta o comentario, pueden dejarlo en este post.

Tareas Programadas con Oracle Schedule Jobs

Hace tiempo, en mi anterior trabajo necesitábamos una forma de ejecutar tareas programadas que no dependiesen completamente de los Jobs de Oracle, primero por que teníamos que solicitarle al DBA los creara, no podíamos monitorearlo y los logs en caso de errores los teníamos que llevar por nuestra cuenta y el tiempo programado de ejecución no era preciso.

Al final se creo una aplicación que guardaba todas las tareas por ejecutar, con las fechas de inicio y su programación y se mandaba a llamar desde un Job Oracle un procedimiento que leía todos los procedimientos programados para esa hora y los iba ejecutando uno a uno, generaba la siguiente fecha de ejecución y en caso de errores guardaba el log en una tabla, para con ello el trabajo no se desactivara en caso de error.

Cuando llego a mi nueva empresa, tengo la misma problemática con los Jobs en la versión 9i, había momentos en que los Jobs se empalmaban por mas que les asignara una fecha inicial diferente (los tiempos de ejecución intervenían en el cálculo de las nuevas fechas) y no tenía modo de monitorear si se había ejecutado correctamente o no, por lo que el error era detectado posteriormente por el desarrollador que recibía el reclamo de que no había llegado o no se había generado el reporte.

Ya con la migración de la Base de Datos, me puse a Investigar un poco sobre una característica existente desde la versión 10g, los Scheduled Jobs.

Los Scheduled Jobs, no es más que la programación de ejecución de algún programa PL/SQL, o comando externo desde la Base de Datos a una fecha y hora determinada.

¿Cual es la diferencia con los Jobs normales? Primero, que no le afecta el tiempo de ejecución del job, segundo, guardan un log de ejecuciones (programable para solo cuando sucedan errores, sea satisfactorio o ambos) y tercero, no le afecta la configuración de Database Vault al querer modificarlos/ejecutarlos con el usuario SYS si el dueño es otro usuario.

Lo que he observado, es que finalmente, un Scheduled Job internamente cuando llega la hora de ejecutarse, crea un Job, se ejecuta y lo elimina, por lo que se tiene que tener especial cuidado en la programación de tiempo, para evitar empalmes de ejecución del mismo procedimiento (posibles bloqueos masivos o sesiones colgadas con procesos a medio terminar).

Para implementar un Scheduled Job se ejecuta un script como este ejemplo:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'
      ,start_date      => TO_TIMESTAMP_TZ('2012/03/08 13:35:00.000000 -06:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5'
      ,job_type        => 'STORED_PROCEDURE'
      ,job_action      => 'SERVICIOS_BD.P_DesasignaPrivilegios'
      ,end_date        => NULL
      ,comments        => 'Desasignación de Privilegios asignados a Desarrolladores por el procedimiento de Autoasignación de Privilegios de Base de Datos'
    );
    
    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE
    ( name      => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'
     ,attribute => 'LOGGING_LEVEL'
     ,value     => SYS.DBMS_SCHEDULER.LOGGING_FULL);
    
    DBMS_SCHEDULER.ENABLE(name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB');
    
END;
/

La primer parte crea el Scheduled Job y la segunda lo activa, ya que por default estos no son activados al crearse.

Una observación es con la especificación de la fecha inicio de ejecución y el horario de verano, en el caso de mi país (México) y por mi uso horario (Ciudad de México) el uso horario que me corresponde es el GMT -6:00, sin embargo, cuando entra en rigor el horario de verano pasa a ser GMT -5:00 por lo que el Scheduled Job comienza a ejecutarse con una hora de retraso por su programación inicial.

En los países que utilicen el horario de Verano, cuando se especifique la fecha de inicio deberán utilizar el formato con el código de zona de tiempo (TIME ZONE) que le corresponda.

En mi caso, quedaría de la siguiente manera:

BEGIN

   DBMS_SCHEDULER.CREATE_JOB

    (

       job_name        => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'

      ,start_date      => TO_TIMESTAMP_TZ('2012/03/08 13:35:00 America/Mexico_City','yyyy/mm/dd hh24:mi:ss tzr')

      ,repeat_interval => 'FREQ=MINUTELY;INTERVAL=5'

      ,job_type        => 'STORED_PROCEDURE'

      ,job_action      => 'SERVICIOS_BD.P_DesasignaPrivilegios'

      ,end_date        => NULL

      ,comments        => 'Desasignación de Privilegios asignados a Desarrolladores por el procedimiento de Autoasignación de Privilegios de Base de Datos'

    );

    

    SYS.DBMS_SCHEDULER.SET_ATTRIBUTE

    ( name      => 'SERVICIOS_BD.Desasigna_Privilegios_JOB'

     ,attribute => 'LOGGING_LEVEL'

     ,value     => SYS.DBMS_SCHEDULER.LOGGING_FULL);

    

    DBMS_SCHEDULER.ENABLE(name => 'SERVICIOS_BD.Desasigna_Privilegios_JOB');

    

END;

/

Noten los cambios en la especificación de la hora, en la cual se agregó mi TIME ZONE y la máscara de formato, que se le agrega el TZR, con ello Oracle toma en cuenta que esa zona de Tiempo le es aplicado el horario de verano, y así considera en los cálculos de la próxima fecha de ejecución el cambio de horario.

Si necesitas consultar los códigos de Time Zone, puedes hacer un Select a la vista V$TIMEZONE_NAMES.

Lo del horario de verano lo encontré en esta página http://deryaoktay.wordpress.com/2010/01/21/oracle-job-scheduler-runs-my-jobs-one-hour-earlier/

Los scheduled jobs permiten muchas más configuraciones, pero para las necesidades que tengo ahora, con esto fué suficiente.

Me queda como tarea el habilitar las notificaciones automáticas vía correo electrónico de las ejecuciones de estas tareas.

Problema de bloqueos TM - Contention en Oracle Database 11gR2

En estos ya 15 días después de haber realizado la migración, han salido varios problemas que en la etapa de pruebas no fueron detectadas, una de ellas es que de pronto un simple DML (por ejemplo un INSERT) bloqueaba toda una tabla no permitiendo que otros usuarios tuvieran acceso ni con un SELECT a los registros.

Después de buscar un rato (resolviendo el problema mediante la muerte de la sesión del usuario problemático), la investigación me llevo a esta página http://www.confio.com/blog/solving-waits-on-enq-tm-contention , en la cual describen que para la versión 11g, si las FK's de una tabla no están indexadas, al insertar un valor bloquea todo el objeto y objetos relacionados.

La solución, como pueden deducir, es crear los índices, basado en ello construí este SQL que me permite obtener las instrucciones DDL para crearlos. Es necesario decir que ya con las sentencias me puse a revisar una a una para ver a que tablespace la debía dirigir ya que me heredaron una BD no muy organizada (esa es oooootro de mis proyectos, la reestructuración) y tenía que asignarlos a donde estaban los demás. En el caso de ustedes, si quieren evitarse esa talacha pueden hacer un tablespace nuevo para esos indices y ahí dirigirlos.


-- Solución a TM - Contention (Bloqueos masivos de tablas)
-- Indican en la página que al realizar un INSERT en una tabla con FK's
-- se hace un bloqueo completo de la tabla si estas columnas no están indexadas
-- Este script obtiene las columnas con FK pero que no están indexadas, dandonos
-- la sentencia con el SQL a ejecutar para crearlo
SELECT 'CREATE INDEX '||column_name||'_FK ON '||owner||'.'||table_name||'('||column_name||');'
FROM (
        SELECT c.owner owner, c.table_name table_name, cc.column_name, cc.position column_position
        FROM   dba_constraints c, dba_cons_columns cc
        WHERE  c.constraint_name = cc.constraint_name
        AND    c.constraint_type = 'R'
        and    c.owner='OWNER'
        MINUS
        SELECT i.owner, i.table_name, ic.column_name, ic.column_position
        FROM   dba_indexes i, dba_ind_columns ic
        WHERE  i.index_name = ic.index_name
        AND i.owner='OWNER'
)
ORDER BY table_name, column_position;


Ya con los índices creados, notarán que los bloqueos dejan de ser TM - EXCLUSIVE y pasan a TM - ROW EXCLUSIVE con su respectivo TX.

Se han realizado más modificaciones, y todas las iré incluyendo en este blog.

Actualización:

Esta situación ocurre con clientes no soportados según la matriz de certificación de Oracle, en mi caso el problema se presenta con clientes de la versión 8i. Ya que traté de reproducir el mismo error con clientes 10g - 11g y no se presentaron esos bloqueos aún cuando no se habían creado los índices para las llaves foráneas.

Otro tema es que si las FK corresponden a llaves combinadas, los índices deben crearse también combinados, esta consideración no se resuelve en la consulta mostrada anteriormente, por lo que en mi caso, tuve que realizarla manualmente en las tablas que seguían presentando el caso.

La emoción de llevar a final un gran Proyecto...

Cuando entré a la empresa donde estoy laborando actualmente, vine con la idea de hacer grandes cambios, tanto en ella como en mi persona. Antes de ello, solo me dedicaba a la programación y soporte técnico y ahí me hubiese "estancado" de no ser por esta oportunidad de ser DBA.

Si, no ha sido fácil, entre la falta de capacitación y apoyo en cuestiones críticas de mis homólogos, he tenido que picar piedra mucho tiempo. ¿Para qué? Mi proyecto actual es la migración de la base de datos de la empresa, de una versión ya veterana a una nueva.

En el momento de estar escribiendo este post, me encuentro a la mitad de ella, aún no puedo decir que es un éxito, falta lo principal, que todo funcione. Aunque para serles sincero, no tengo miedo de que no se cumplan las expectativas, ya que el proceso lo he hecho muchas veces, aprendiendo de cada vez que lo realizo. Al principio, como todo, tenía errores, pero con un poco de investigación, estos fueron mitigados.

Las pruebas con los usuarios aunque tardías, ya en las últimas ocasiones, se pusieron las pilas y se llevaron a cabo en tiempo y forma.

Hoy me encuentro con una enorme emoción, mi primer proyecto de gran impacto, tanto para mi área como para mi empresa, al fin podré terminarlo.

También he de agradecer a mi esposa que me ha dado gran apoyo moral, así como a mi jefe de área, que me dió la confianza de llevar ese proyecto a mi, que soy un novato en esto y a mis compañeros de área, que se han puesto las pilas para realizar este proyecto en conjunto.

Ya posteriormente les daré a conocer el proceso que se siguió para realizar la migración.

Consultas para obtener información de bloqueos de Objetos e Interbloqueos de Usuarios

Hay ocasiones que el abrir tu Console Manager para resolver un bloqueo o un interbloqueo, puedes mejor ejecutar unas sentencias para obtener la información que necesitas y así tomar las medidas que sean pertinentes.

Dejo las consultas que hasta ahora tengo para obtener esa información

-- Consulta de Bloqueo de Objetos por Sesiones
select substr(a.os_user_name,1,8) "OS User"
, substr(b.object_name,1,30) "Object Name"
, substr(b.object_type,1,8) "Type"
, substr(c.segment_name,1,10) "RBS"
, e.process "PROCESS"
, substr(d.used_urec,1,8) "# of Records"
, e.sid
, e.serial#
, e.username
, p.spid
from v$locked_object a
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session e
, v$process p
where a.object_id = b.object_id
and a.xidusn = c.segment_id
and a.xidusn = d.xidusn
and a.xidslot = d.xidslot
and d.addr = e.taddr
and p.addr = e.paddr;

-- Consulta de Objetos Bloqueados por usuarios
select
l.oracle_username,
l.session_id,
l.os_user_name,
o.owner,
o.object_name,
dl.lock_type,
dl.mode_held
from v$locked_object l, dba_objects o, dba_locks dl
where o.object_id = l.object_id
and dl.session_id = l.session_id;

--Consulta para Interbloqueos
select lock1.sid, ' BLOQUEA ', lock2.sid
from v$lock lock1, v$lock lock2
where lock1.block =1 and lock2.request > 0
and lock1.id1=lock2.id1
and lock1.id2=lock2.id2;


-- Consulta de SPID
select v.sid, a.SPID, v.serial#, v.username, v.OSUSER, v.program, v.MACHINE, v.LOGON_TIME
from dba_users u, v$session v, v$process a
where v.user# = u.user_id
and v.paddr = a.addr;

-- Consulta sesiones pendientes de rollback o commit
SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Matar sesiones a nivel Sistema Operativo (Windows)

A veces nos encontramos con que mandamos a matar una sesión y esta queda marcada para "eliminar", y ahí persiste.

Podemos eliminar la sesión a nivel sistema operativo (matar el proceso de SO de la sesión) de la siguiente forma:

Primero, verificar que la sesión no esté haciendo operaciones de rollback

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Si el campo used_urec tiene una cantidad distinta de cero y esta va decreciendo, hay que esperar para que termine de hacer rollback a sus operaciones.

Ya cuando no tenga operaciones de rollback pendientes (no aparecera como resultado del query), se procede a obtener el SPID (Server Process ID) de la sesión con la siguiente consulta:


select v.sid, a.SPID, v.serial#, v.username, v.OSUSER, v.program, v.MACHINE, v.LOGON_TIME
from user_users u, v$session v, v$process a
where v.user# = u.user_id
and v.paddr = a.addr;

Ya con el SPID y el nombre de la instancia de Base de Datos (que se obtiene con esto select sys_context('USERENV','DB_NAME') as Instance from dual;) se procede a ejecutar el comando ORAKILL desde una consola de MSDOS en el servidor.

El comando ORAKILL pide como parametros el nombre de la instancia y el SPID de la sesión a eliminar.

El comando quedaría de la siguiente manera:

C:\>orakill MiBaseDeDatos 7559

Al ejecutarse con éxito, muestra el siguiente mensaje


Kill of thread id  7559 in instance  MiBaseDeDatos  successfully signalled.

Fuentes:
http://cajondesastreoracle.wordpress.com/scripts-sql-curiosos/session_undo-sql/
http://vitocosan.wordpress.com/2009/06/03/orakill-matar-una-conexion-activa-de-oracle/