CUANDO LOS LOBS AFECTAN EL DESEMPEÑO DE LA BASE DE DATOS

Hola amigos !

El siguiente caso  (que no pretende ser una guía para la reubicación de LOBS)  fue originado por el llamado de un cliente reportando  problemas de lentitud de una base de datos Oracle 11.1.0.7 en configuración 'RAC' de cuatro nodos, con un aplicativo 'Oracle Business Process Management' operando en ambiente productivo. La primera actividad fue la obtención de un reporte de rendimiento por medio de la utilería 'AWR'  ejecutando el script 'awrrpti.sql' por cada nodo del cluster. 

Una vez generado el reporte, en la sección 'Top 5 Timed Foreground Events' del 'AWR' nos aparecio el evento 'enq HW -contention'  en todos los nodos del cluster, el cual no es muy común en este tipo de reportes: (solo muestro el top de un nodo)

Top 5 Timed Foreground Events

Event                                     Waits          Time(s)           Avg wait (ms)       %DB time    Wait Class

enq HW -contention           586,913         14,728                 25                        45.61       Configuration
db file sequential read      1,272,487          5,816                   5                        18.01        User I/O
DB CPU                                                         4,442                                            13.69
enq TX - row lock cont         3,973            1,310                330                         4.06        Application
direct path read                  948,724               943                    1                         2.92         User I/O

El evento 'enq HW -contention' nos hace referencia  a la 'marca de agua' o 'high water mark', la cual indica al manejador en que punto existen bloques libres y usados en un segmento de datos.  Que este evento aparezca en el 'Top 5'  indica que existe  un alto número de actualizaciones en algún segmento.  La nota Doc ID 9801919.8 de 'metalink' nos indica lo siguiente:

            "enq: HW - contention" may be seen for segments that have heavy  concurrency

              and often need to add an extent due to repeated SEG$ updates of dictionary data" 

Otras notas de 'metalink' nos sugieren que puede existir un problema con actualizaciones en campos tipo 'LOBs' (Large Objects) que contienen información no estructurada como archivos, imagenes o 'XMLs' entre otros.  Continuamos con  la búsqueda del ´culpable' de este evento en los 'tops' de las sentencias 'SQL'  en el reporte 'AWR'

En la sección 'sqls ordenados por tiempo de ejecución' nos aparece en primer lugar un 'UPDATE' sobre la tabla 'PPROCINSTANCE' el cual de forma notable ocupa el 75.91% del tiempo de la base de datos (por cuestiones de formato no incluí todas las columnas del 'AWR')

Elapsed Time(s)  CPU Time(s)   Executions   %Total DB Time     SQL text

  24,247                  1,259              84,911              75.91                        UPDATE PPROCINSTANCE SET .....
    1,832                     109              12,881                 5.74                        INSERT INTO PPROCINSTANCE ....

Continuamos en la sección 'sql ordenados por tiempo de CPU requerido'  y nuevamente aparece la sentencia 'UPDATE'  sobre la tabla 'PPROCINSTANCE':

CPU Time(s)   Elapsed Time(s)    %Total   %Total DB Time         SQL text

   1,259                 24,247                  34.20             75.91                       UPDATE PPROCINSTANCE SET ..
      174                      624                     4.73                1.95                      SELECT PPROCESSID, INSTID.... 

En la sección 'sql  ordenados por accesos a memoria' el 'UPDATE' de la tabla 'PPROCINSTANCE' aparece nuevamente

Buffer Gets      Gets per Exec   %Total     CPU Time (s)         SQL text

37,273,368          3,727.34             31.60        113.22                    DELETE FROM PCORRELATION ...
35,175,421             414.26             29.82      1259.44                    UPDATE PPROCINSTANCE SET ....

La sentencia tiene condiciones para los campos 'processid', 'instid' y 'threadid'. Sospechamos que puede existir un 'full scan'. Sin embargo, al revisar el 'explain plan'  se valida que el acceso es vía índice (aqui les debo el explain plan :-( )

     UPDATE pprocinstance SET (varios campos)..WHERE processid= ? AND instid= ? AND threadid=?

Para obtener el 'explain plan' sin necesidad de ejecutar nuevamente la sentencia, es muy útil el 'package' 'dbms_xplan.display_awr'.  Con el  'id query' obtenido en el reporte  'AWR'  se agrega como parámetro (en este caso el id 'g1jx24n27sykx' ) en el llamado del ´package'.

     SELECT plan_table_output FROM table (dbms_xplan.display_awr('g1jx24n27sykx'));

El siguiente paso es verificar si existe un 'LOB' en la tabla PPROCINSTANCE por medio de una consulta a la tabla  'DBA_LOBS'.   Identificamos al 'LOB'  'SYS_LOB0000108088C00003$$'  

     SELECT SUBSTR(table_name,1,40),  SUBSTR(segment_name, 1,30) ,  SUBSTR(tablespace_name,  1,30) 
           FROM dba_lobs 
       WHERE table_name = 'PPROCINSTANCE'
  
     SUBSTR(TABLE_NAME,1,40)                SUBSTR(SEGMENT_NAME,1,30)     SUBSTR(TABLESPACE_NAME,1,30)
       -------------------------------------------------       ------------------------------------------------    ---------------------------------------------------

          PPROCINSTANCE                                  SYS_LOB0000108088C00003$$                      USERS

Ahora que conocemos el segmento tipo 'LOB',  vamos a buscar en el reporte 'AWR' para validar si realmente tiene un impacto en el desempeño de la base de datos. En la sección de 'segmentos con más lecturas a disco' aparece el 'LOB'  de la tabla  PPROCINSTANCE con el 78% del total !

             Object Name                                               Obj. Type    Physical Writes    %Total DB Time   

           SYS_LOB0000108088C00003$$            LOB               993, 397                    73.40 
           SYS_LOB0000108088C00008$$            LOB                  31,622                      2.34 

Verificamos que índices tiene la tabla 'PPROCINSTANCE'  con la siguiente consulta. La tabla tiene tres índices que se encuentran en el tablespace 'USERS' que es el mismo que el utilizado por el 'LOB'.

            SELECT SUBSTR(index_name, 1,20), substr(tablespace_name,1,30) FROM dba_indexes
               WHERE table_name = 'PPROCINSTANCE';

             SUBSTR(INDEX_NAME,1,          SUBSTR(TABLESPACE_NAME,1,30)
            -------------------------------------          ------------------------------------------------
                   INSTPART                                                   USERS
                   ACT                                                               USERS
                   ASSIGPART                                                USERS

Ahora validamos el tablespace por default para la tabla PPROCINSTANCE  y no resulta una sorpresa que también sea USERS. Por alguna razón,  se generaron los datos, índices y lobs de la tabla maestra PPROCINSTANCE en el mismo tablespace, lo que genera problemas de contención.

          SELECT tablespace_name FROM dba_tables WHERE table_name = 'PPROCINSTANCE';

                  TABLESPACE_NAME
                  ------------------------------
                          USERS

La guía de Oracle 'Performance Tuning for Oracle Business Process Management Suite 10g' nos recomienda utilizar tablespaces independientes para los segmentos 'LOBS':

                     ' Use Separate Tablespaces for BLOBs If you are storing large

                        BLOBs in the database, you should consider a separate

                        tablespaces for the BLOBs'

Platicando con el cliente, le comentamos la necesidad de particionar la tabla y distribuirla en diferentes tablespaces. El regenerar la tabla PPROCINSTANCE impacta al resto del modelo de datos, implicando la recompilación de todos los objetos relacionados como stored procedures y packages entre otros. Por ser ambiente productivo, el cliente no desea que la tabla se vuelva a generar. Nos da como opción redistribuirla en diferentes tablespaces, pero sin regenerar ningún objeto. 

Para redistribuir la tabla PPROCINSTANCE se van a a generar tres tablespaces: para datos, índices y uno dedicado al segmento 'LOB'

                   CREATE TABLESPACE TBS_DATOS_PROC DATAFILE  '+BDBPMDATA' size 2048M;  

                   CREATE TABLESPACE TBS_IND_PROC DATAFILE   '+BDBPMINDEX' size 2048M;       

                   CREATE TABLESPACE TBS_LOBS_PROC DATAFILE  '+BDBPMLOG' size 10,000M   

Previo al proceso de reubicación es recomendable un 'full backup' de la base de datos y generar un listado que contenga el estatus de todos los objetos de la base de datos con la siguiente sentencia:

              SELECT object_name, object_status FROM dba_objects;

Una vez generados los tablespaces dedicados a la tabla PPROCINSTANCE es necesario reubicar los datos con la  siguiente secuencia :

1. Reubicar los datos de la tabla al tablespace 'TBS_DATOS_PROC'
2. Reubicar los índices de la tabla TBS_IND_PROC  al tablespace TBS_IND_PROC'
3. Reubicar el 'LOB'  ubicado en el campo INSTANCEDATA al tablespace TBS_LOBS_PROC
4. Reconstruir los índices inválidos

Ejecutamos el primer paso al reubicar los datos  de la tabla al tablespace 'TBS_DATOS_PROC'. Este paso va a invalidar todos los índices asociados a la tabla.

           ALTER TABLE OBPMENGORDERS.PPROCINSTANCE MOVE TABLESPACE TBS_DATOS_PROC;

 Continuamos con el segundo paso que es reubicar los índices al nuevo tablespace 'TBS_IND_PROC':

           ALTER INDEX OBPMENGORDERS.INSTPART REBUILD TABLESPACE TBS_IND_PROC;

              ALTER INDEX OBPMENGORDERS.ACT REBUILD TABLESPACE TBS_IND_PROC;

              ALTER INDEX OBPMENGORDERS.ASSIGPART REBUILD TABLESPACE TBS_IND_PRO;

Realizamos la etapa de reubicación del lob del campo INSTANCEDATA.  Por ser únicamente un campo el reubicado, no podemos utilizar la opción  'MOVE TABLESPACE'. Utilizaremos la opción 'MOVE LOB' indicando el campo a mover. Esta opción es la más lenta de todas, por lo que si se desea realizar una reubicación de 'LOBS', es recomendable realizar una prueba en ambiente pre-productivo para calcular ventanas de tiempo para el mantenimiento.

   ALTER TABLE OBPMENGORDERS.PPROCINSTANCE                                                                                                                                               MOVE LOB(INSTANCEDATA) STORE AS (TABLESPACE TBS_LOBS_PROC);

En caso de que el campo 'LOB'  a reubicar sea del tipo 'XML Type',  se puede utilizar el siguiente comando
donde se incluye la sentencia 'XMLDATA'

 ALTER TABLE xxxxxx move lob (CAMPO.XMLDATA) store as (TABLESPACE tablespace_destino);

Procedemos a  reconstruir los índices inválidados de la tabla PPROCINSTANCE

           ALTER INDEX OBPMENGORDERS.INSTPART REBUILD;

               ALTER INDEX OBPMENGORDERS.ACT REBUILD;

               ALTER INDEX OBPMENGORDERS.ASSIGPART REBUILD;

Por último volvemos a ejecutar el query para validar los objetos de la base de datos, asi como su estatus, que debe coincidir con el conteo previo a la reubicación.

              SELECT object_name, object_status FROM dba_objects;

Una vez que el conteo  es el correcto, finalizamos la reubicación.

Conclusiones

En el caso de tablas que contenga campos tipo 'LOB' y observan un elevado número de actualizaciones, es recomendable generar 'tablespaces' en discos independientes para reducir la contención y de ser posible generar la tabla particionada.  Se puede programar el uso de la sentencia 'alter table xxx shrink space compact' para reclamar el espacio eliminado.  Si se genero la tabla y ya cuenta con información, se puede realizar la reubicación por medio de la opción 'MOVE LOB', sin embargo hay que tener en cuenta los tiempos requeridos con esta opción.

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México

APLICANDO ARCHIVES EN UN FULL RECOVERY

El respaldo y recuperación de bases de datos Oracle es uno de los puntos más criticos dentro de la labor de un DBA.

Este ejercicio tiene como objetivo realizar un respaldo completo en frio  o 'cold full backup' .  Posterior a la realización del respaldo se van a generar dos tablas 'TEST1' y 'TEST2'  las cuales deberán ser respaldadas en automático por los 'ARCHIVE LOGS'.

Se va a realizar un 'full recovery', posteriormente se aplicaran  los  'ARCHIVE LOGS'. Oracle recomienda realizar los respaldos por medio de la herramienta 'RECOVER MANAGER' o 'RMAN'.   Este ejercicio se va a realizar por medio de copias de 'datafiles', 'redo logs' y 'control files' a nivel sistema operatívo.

El primer paso es  verificar que la instancia se encuentre en modo 'ARCHIVE LOG'

SQL> archive log list
Database log mode                      Archive Mode
Automatic archival                      Enabled
Archive destination                      USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence          6
Next log sequence to archive      8
Current log sequence                  8

Verificamos el nombre de la instancia a respaldar, en este caso 'test'

SQL> SELECT   instance_name FROM v$instance;

           INSTANCE_NAME
           ----------------
            test

Generamos un listado de todos los 'datafiles' a respaldar y que componen la instancia 'test'

SQL> SELECT NAME from v$datafile order by name;

NAME
--------------------------------------------------------------------------------
C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_9OX3J7DO_.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_9OX3LLWQ_.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_9OX3OB5M_.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_USERS_9OX3O8J6_.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_01.DBF
C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_02.DBF

6 rows selected.

Se genera un listado con los 'REDO LOGS' de la instancia, donde se tienen 3 grupos de 'redo logs' con dos miembros por grupo

SQL> SELECT group#, status, substr(member,1,100) FROM  v$logfile order by 1;

    GROUP# STATUS  SUBSTR(MEMBER,1,100)
---------- ------- ----------------------------------------------------------------------------------------------------
         1         C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_1_9OX3ST80_.LOG
         1         C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_1_9OX3SVGR_.LOG
         2         C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_2_9OX3SXC6_.LOG
         2         C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_2_9OX3SYL4_.LOG
         3         C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_3_9OX3T03B_.LOG
         3         C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_3_9OX3T4SM_.LOG

6 rows selected.

Se genera un listado con los 'CONTROL FILES' de la instancia. Tenemos dos controlfile 'multiplexados' para fines de disponibilidad

SQL> SELECT substr(NAME, 1,80) FROM  v$controlfile;

SUBSTR(NAME,1,80)
--------------------------------------------------------------------------------
C:\APP12\BD\ORADATA\TEST\CONTROLFILE\O1_MF_9OX3SPDX_.CTL
C:\APP12\BD\FAST_RECOVERY_AREA\TEST\CONTROLFILE\O1_MF_9OX3SPQK_.CTL

Como se va realizar un respaldo en frío o 'COLD BACKUP' se da de baja la base de datos para que el respaldo sea consistente.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Finalizado el  shutdown respaldamos los 'datafiles'

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_9OX3J7DO_.DBF C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_9OX3LLWQ_.DBF C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_9OX3OB5M_.DB C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_USERS_9OX3O8J6_.DBF C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_01.DBF C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_02.DBF C:\BACKUP_TEST
        1 file(s) copied.

Se respaldan los seis 'redo logs':

C:\>COPY  C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_1_9OX3ST80_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_1_9OX3SVGR_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_2_9OX3SXC6_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY  C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_2_9OX3SYL4_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\ORADATA\TEST\ONLINELOG\O1_MF_3_9OX3T03B_.LOG C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ONLINELOG\O1_MF_3_9OX3T4SM_.LOG C:\BACKUP_TEST
        1 file(s) copied. 

Respaldamos los 'control files'

C:\>COPY C:\APP12\BD\ORADATA\TEST\CONTROLFILE\O1_MF_9OX3SPDX_.CTL C:\BACKUP_TEST
        1 file(s) copied.

C:\>COPY C:\APP12\BD\FAST_RECOVERY_AREA\TEST\CONTROLFILE\O1_MF_9OX3SPQK_.CTL C:\BACKUP_TEST
        1 file(s) copied.

Posterior al respaldo vamos a generar las tablas 'TEST1' y 'TEST2' con 8860 y 8880 registros respectivamente

SQL> show user
USER is "TEST"

SQL> CREATE TABLE test1 AS SELECT * FROM dba_extents;

Table created.

SQL> CREATE TABLE test2 AS SELECT * FROM dba_extents;

Table created.

SQL> SELECT count(*) FROM test1;

    COUNT(*)
   ----------
      8860

SQL> SELECT count(*) FROM test2;

  COUNT(*) 
   ----------
      8880

Unicamente se generaron dos 'archive logs'. El 'archive log' 'O1_MF_1_8_9P2HOYM9_.ARC' va a contener los datos de las tablas 'TEST1' y 'TEST2', que fueron creadas posterior al respaldo de los 'datafiles'

 Directory of C:\app12\BD\fast_recovery_area\TEST\ARCHIVELOG\2014_04_30

30/04/2014  01:21 p.m.    <DIR>          .
30/04/2014  01:21 p.m.    <DIR>          ..
30/04/2014  01:19 p.m.        13,605,888 O1_MF_1_8_9P2HOYM9_.ARC
30/04/2014  01:21 p.m.            12,288 O1_MF_1_9_9P2HRH8F_.ARC
               2 File(s)     13,618,176 bytes
 
Vamos a simular la pérdida de todos los 'datafiles' por lo que tenemos que realizar un 'FULL RECOVERY' realizando los siguientes pasos:

 1.- Se copian los 'datafiles' del directorio respaldo al directorio origen
 2.- Se copian los 'redo logs' del directorio respaldo al directorio origen
 3.- Se copian los 'control files' del directorio respaldo al directorio origen

Copia de 'datafiles'

C:\APP12\BD\ORADATA\TEST\DATAFILE\

C:\>cd back*

C:\BACKUP_TEST>copy *.dbf c:\app12\BD\ORADATA\TEST\DATAFILE\
O1_MF_SYSAUX_9OX3J7DO_.DBF
O1_MF_SYSTEM_9OX3LLWQ_.DBF
O1_MF_UNDOTBS1_9OX3OB5M_.DBF
O1_MF_USERS_9OX3O8J6_.DBF
TBS_01.DBF
TBS_02.DBF
        6 file(s) copied.

Copia de 'redo logs'

C:\BACKUP_TEST>copy *.LOG c:\app12\BD\ORADATA\TEST\DATAFILE\
O1_MF_1_9OX3ST80_.LOG
O1_MF_1_9OX3SVGR_.LOG
O1_MF_2_9OX3SXC6_.LOG
O1_MF_2_9OX3SYL4_.LOG
O1_MF_3_9OX3T03B_.LOG
O1_MF_3_9OX3T4SM_.LOG
        6 file(s) copied.

Copia de 'controlfiles'

C:\BACKUP_TEST>copy *.CTL c:\app12\BD\ORADATA\TEST\DATAFILE\
O1_MF_9OX3SPDX_.CTL
O1_MF_9OX3SPQK_.CTL
        2 file(s) copied.

Reiniciamos la instancia recordando que Oracle realiza las siguientes pasos durante el proceso de 'startup'

1.- NOMOUNT .- Ejecutada con el comando 'STARTUP NOMOUNT' .-  Oracle lee el archivo de inicio 'init.ora' y únicamente se inicia la memoria de la instancia.

2.- MOUNT.- Se lee el archivo de control o 'control file'. Todas las recuperaciones 'o recoverys' de una instancia Oracle se realizan en este modo.

3.- OPEN.- Todos los 'datafiles' se encuentran sincronizados y la base de datos es consistente y se encuentra operativa

Iniciamos la instancia en modo 'MOUNT'

SQL> startup mount
ORACLE instance started.

Total System Global Area  417546240 bytes
Fixed Size                  2403352 bytes
Variable Size             322962408 bytes
Database Buffers           83886080 bytes
Redo Buffers                8294400 bytes
Database mounted.

Vamos a suponer el que el 'full backup fue realizado a las 10:00 AM y la creación de las tablas 'TEST1' y 'TEST2' a las 11:00 AM,  por lo que el 'full backup' no contiene las transacciones generadas entre las 10:00 AM y las 11:AM.

Una vez recuperados los 'datafiles', se realiza la siguiente consulta sobre la vista dinámica del sistema 'v$datafile_header', donde se muestra que los 'datafiles' se encuentra sincronizados con el número 2330727 que corresponde  a las 10:00 AM 

Este número es originado por medio del 'System Change Number'  o 'SCN'.  Los datafiles tienen en su cabecero o 'header' el 'SCN' que indica la última vez que fueron actualizados por medio del 'checkpoint'.

EL 'SCN' es indispensable para el recovery, ya que le permite conocer al manejador el punto en el tiempo la base de datos es consistente. En este caso, los 6 datafiles tienen el mismo número, por lo cual son consistentes. El 'SCN' también es almacenado en el archivo de control o 'control file'

SQL> SELECT checkpoint_change#,  substr(name, 1,70) FROM v$datafile_header ORDER BY 1;

CHECKPOINT_CHANGE# SUBSTR(NAME,1,70)
------------------ ----------------------------------------------------------------------
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_9OX3LLWQ_.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_01.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_USERS_9OX3O8J6_.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_02.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_9OX3OB5M_.DBF
           2330727      C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_9OX3J7DO_.DBF

6 rows selected.

Una vez finalizada la copia para recuperar todos los 'datafiles', 'redo logs' y 'control files' tendriamos una recuperación completa hasta las 10:00 AM que es la hora en que se realizo el respaldo 'full backup'. Sin embargo nos faltan las transacciones  (principalmente las tablas 'TEST1' y 'TEST2') que se crearon despues de las 10:00 AM. Estas transacciones faltantes, estan contenidas en los 'ARCHIVE LOGS', por lo cual hay que aplicarlos, para complementar el 'full recovery'

En modo 'MOUNT' damos el comando 'RECOVER DATABASE' , sin embargo vemos que no se realiza la recuperación o aplicación de los 'ARCHIVE LOGS' y el manejador dice 'no necesito ningún recovery, debido a que los datafiles son de las 10:00 AM y el archivo de control me indica que el respaldo corresponde  a las 10:00 AM'

SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Como la función 'RECOVER DATABASE' no aplico 'archive logs', hay que agregar la claúsula 'USING BACKUP CONTROLFILE', lo que le indica al manejador 'no hagas caso que el controlfile tiene un SCN de las 10:00 AM y aplica las transacciones posteriores a ese hora'. 

La sintáxis del comando 'USING BACKUP CONTROLFILE' se puede prestar a confusión, ya que en realidad no se esta utilizando ningún backup del  controlfile, lo único que se le esta indicando es que 'ignora la secuencia del controlfile '

  

Se ejecuta el comando y el manejador indica que 'archive logs' necesita aplicar para tener las transacciones faltantes entre las 10:00 y las 11:00 AM.  Se requiere el 'archive log' 'O1_MF_1_8_9P2HOYM9_.ARC'   para complementar el respaldo,  recordando que que las tablas 'TEST1' y 'TEST2'  se encuentran contenidas en este 'archive log'.  

Se aplica el 'archive log' y 'O1_MF_1_8_9P2HOYM9_.ARC' y nos indica que los demas 'archives' no son necesarios para la recuperación . Se puede autilizar la opción 'AUTO' para que se apliquen de forma automática los 'logs archive logs' requeridos, sin necesidad de especificarlos uno por uno.

SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: el cambio 2330727 generado en 04/30/2014 12:29:32 es necesario para el thread 1
ORA-00289: sugerencia: C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_8_9P2HOYM9_.ARCORA-00280: el cambio 2330727 para el thread 1 estß en la secuencia n·mero 8

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: el cambio 2346630 generado en 04/30/2014 13:19:41 es necesario para el thread 1
ORA-00289: sugerencia: C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_9_9P2HRH8F_.ARC
ORA-00280: el cambio 2346630 para el thread 1 estß en la secuencia n·mero 9
ORA-00278: el archivo log 'C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_8_9P2HOYM9_.ARC' ya no es necesario para esta recuperaci¾n

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00279: el cambio 2346667 generado en 04/30/2014 13:21:02 es necesario para el thread 1
ORA-00289: sugerencia: C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_10_%U_.ARC
ORA-00280: el cambio 2346667 para el thread 1 estß en la secuencia n·mero 10
ORA-00278: el archivo log 'C:\APP12\BD\FAST_RECOVERY_AREA\TEST\ARCHIVELOG\2014_04_30\O1_MF_1_9_9P2HRH8F_.ARC' ya no es necesario para esta recuperaci¾n

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Para iniciar la base de datos en modo 'OPEN' se da la opción 'RESETLOGS' para que los 'REDO LOGS'  se reinicien.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

En la  vista dinámica del sistema 'v$datafile_header' se observa que la secuencia basada en el 'SCN' se incremento de 2330727   a 2346671  con la aplicación de los 'archive logs' y el reinicio de la instacia.


SQL> SELECT checkpoint_change#,  substr(name, 1,70) FROM v$datafile_header ORDER BY 1;

CHECKPOINT_CHANGE# SUBSTR(NAME,1,70)
------------------ ----------------------------------------------------------------------
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSTEM_9OX3LLWQ_.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_01.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_USERS_9OX3O8J6_.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\TBS_02.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_UNDOTBS1_9OX3OB5M_.DBF
           2346671 C:\APP12\BD\ORADATA\TEST\DATAFILE\O1_MF_SYSAUX_9OX3J7DO_.DBF

6 rows selected.

Validamos la existencia de las tablas 'TEST1' y 'TEST2' , confirmando que ya fueron recuperadas a partir de los 'archive logs', con lo cual ya tenemos un recovery completo.

SQL> SELECT COUNT(*) FROM TEST.TEST1;

  COUNT(*)
----------
      8860

SQL> SELECT COUNT(*) FROM TEST.TEST2;

  COUNT(*)
----------
      8880

Conclusiones

Los 'archive logs' son un elemento clave en la recuperación de una base de datos Oracle.  Se debe tener especial cuidado en respaldarlos,  ya que en ocasiones, son el único mecanismo para lograr que la base de datos sea consistente en un momento en el tiempo.


José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com

5532439143 Ciudad de México