Datafiles de Undo corruptos sin backup

Ultimamente me ha ocurrido con una base de datos oracle 11gR2 sobre Solaris 10, que tenia un datafile corrupto de UNDO y la bd no conseguia abrir. Estaba montada sobre un zpool y voy a proceder a contaros como identificar el problema y como poder solucionarlo.

-IDENTIFICACION DEL PROBLEMA:

Hay varios metodos de identificar el problema, de hecho en la misma apertura de la base de datos me soltaba el suiguiente error:

Slave exiting with ORA-1115 exception
Errors in file /opt/oracle/diag/rdbms/dbprepro/DBPREPRO/trace/DBPREPRO_p009_1009.trc:
ORA-27063: number of bytes read/written is incorrect
Solaris-AMD64 Error: 5: I/O error
Additional information: -1
Additional information: 286720
ORA-27063: number of bytes read/written is incorrect
Solaris-AMD64 Error: 5: I/O error
Additional information: -1
Additional information: 286720
ORA-27063: number of bytes read/written is incorrect
Solaris-AMD64 Error: 5: I/O error
Additional information: -1
Additional information: 286720
ORA-27063: number of bytes read/written is incorrect
Solaris-AMD64 Error: 5: I/O error
Additional information: -1

Ya con esto se intuye un error de disco, con lo cual, al ser una plataforma solaris y sobre un zpool, se ejecuta un zpool status -v como root y se puede ver si hay algun fichero corrupto (en mi caso el datafile de undo).

Imaginemos que tenemos un filesystem normal y necesitamos identificar que ocurre. Tenemos dos formas de hacerlo muy sencillas:

-Con dbverify (dbv file=”ruta completa de cada datafile”) datafile a datafile.
-Con RMAN ejecutando un validate database y viendo que errores nos da, los cuales se registraran despues en la vista de sistema V$DATABASE_BLOCK_CORRUPTION. En mi caso daba este resultado con muy mala pinta:

SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
3 103304 1 0 CORRUPT
3 92184 1 0 CORRUPT
3 91928 1 0 CORRUPT
3 91944 1 0 CORRUPT
3 91960 1 0 CORRUPT
3 91976 1 0 CORRUPT
3 91992 1 0 CORRUPT
3 92008 1 0 CORRUPT
3 92024 1 0 CORRUPT
3 92040 1 0 CORRUPT
3 92056 1 0 CORRUPT

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
3 92104 1 0 CORRUPT
3 92120 1 0 CORRUPT
3 92136 1 0 CORRUPT
3 94896 1 0 CORRUPT
3 52669 1 0 CORRUPT
3 104488 1 0 CORRUPT
3 104504 1 0 CORRUPT
3 112228 1 0 CORRUPT
3 91520 1 0 CORRUPT
3 91552 1 0 CORRUPT
3 91584 1 0 CORRUPT

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
———- ———- ———- —————— ———
3 91608 1 0 CORRUPT
3 91624 1 0 CORRUPT
3 91640 1 0 CORRUPT
3 103320 1 0 CORRUPT
3 91640 1 0 CORRUPT
3 103320 1 0 CORRUPT

Ya sabemos que tenemos bloques corruptos en el datafile 3 (en mi caso UNDO).

Estamos en un punto en que nuestra base de datos esta montada pero no podemos abrirla porque nos da error.

Nuestro siguiente paso para poder abrir nuestra bd y curiosear para saber que se ha perdido, que segmentos de rollback estan bloqueados o simplemente intentar hacer un simple drop del UNDO y crear otro, es poner offline el datafile afectado:

ALTER DATABASE DATAFILE ‘DATAFILE_NAME’ OFFLINE FOR DROP;

Ahora vamos a intentar abrir la base de datos con un alter database open y voila¡¡¡

Nuestro siguiente paso, ya que no tenemos backup, sera borrar el tablespace undo, pero intentamos hacerlo y nos da error porque tenemos transacciones pendientes en bloques corruptos.

Bien, no nos asustamos y vamos con el siguiente punto, que es identificar los segmentos de rollback que tenemos corruptos con la siguiente query:

SQL> select
segment_name,
status
from
dba_rollback_segs
where
tablespace_name=’UNDOTBS1′
and
status = ‘NEEDS RECOVERY’;

SEGMENT_NAME STATUS
—————————— —————-
_SYSSMU11_225855755$ NEEDS RECOVERY
_SYSSMU12_788027690$ NEEDS RECOVERY
_SYSSMU13_467211303$ NEEDS RECOVERY
_SYSSMU14_961692820$ NEEDS RECOVERY
_SYSSMU15_462544116$ NEEDS RECOVERY
_SYSSMU16_3968649556$ NEEDS RECOVERY
_SYSSMU17_1105570464$ NEEDS RECOVERY
_SYSSMU18_3090047449$ NEEDS RECOVERY
_SYSSMU19_2337317979$ NEEDS RECOVERY
_SYSSMU20_1625732447$ NEEDS RECOVERY
_SYSSMU21_4031066151$ NEEDS RECOVERY

SEGMENT_NAME STATUS
—————————— —————-
_SYSSMU22_328030495$ NEEDS RECOVERY
_SYSSMU23_449864886$ NEEDS RECOVERY
_SYSSMU24_1685170541$ NEEDS RECOVERY
_SYSSMU25_2388683727$ NEEDS RECOVERY
_SYSSMU26_2015766445$ NEEDS RECOVERY
_SYSSMU27_1977203815$ NEEDS RECOVERY
_SYSSMU30_2996367655$ NEEDS RECOVERY
_SYSSMU31_1346685827$ NEEDS RECOVERY
_SYSSMU32_4188890455$ NEEDS RECOVERY
_SYSSMU35_2050781695$ NEEDS RECOVERY
_SYSSMU42_1492316650$ NEEDS RECOVERY

SEGMENT_NAME STATUS
—————————— —————-
_SYSSMU45_2664745054$ NEEDS RECOVERY
_SYSSMU46_2952576209$ NEEDS RECOVERY
_SYSSMU47_4219170473$ NEEDS RECOVERY
_SYSSMU48_742215572$ NEEDS RECOVERY
_SYSSMU49_937300175$ NEEDS RECOVERY
_SYSSMU52_511639027$ NEEDS RECOVERY
_SYSSMU54_764726606$ NEEDS RECOVERY

posiblemente todos recordareis que en versiones anteriores a la 10G el undo se administraba manualmente en los segmentos de rollback dentro del tablespace SYSTEM, pues vamos a modificar el parametro UNDO_MANAGEMENT de la siguiente forma:

-Creamos un pfile a partir de nuestro spfile:

Create pfile from spfile;

Editamos ese pfile y buscamos si tenemos el parametro UNDO_MANAGEMENT, si no es asi, lo escribimos nosotros de la siguiente forma:

*.undo_management=’MANUAL’

Tambien deberemos setear el parametro oculto _OFFLINE_ROLLBACK_SEGMENTS con el nombre de todos los segmentos de rollback corruptos que hemos obtenido con la query anterior, quedando en mi caso del siguiente modo:

*._OFFLINE_ROLLBACK_SEGMENTS=’_SYSSMU11_225855755$’,’_SYSSMU12_788027690$’,’_SYSSMU13_467211303$’,’_SYSSMU14_961692820$’,’_SYSSMU15_462544116$
‘,’_SYSSMU16_3968649556$’,’_SYSSMU17_1105570464$’,’_SYSSMU18_3090047449$’,’_SYSSMU19_2337317979$’,’_SYSSMU20_1625732447$’,’_SYSSMU21_403106615
1$’,’_SYSSMU22_328030495$’,’_SYSSMU23_449864886$’,’_SYSSMU24_1685170541$’,’_SYSSMU25_2388683727$’,’_SYSSMU26_2015766445$’,’_SYSSMU27_197720381
5$’,’_SYSSMU30_2996367655$’,’_SYSSMU31_1346685827$’,’_SYSSMU32_4188890455$’,’_SYSSMU35_2050781695$’,’_SYSSMU42_1492316650$’,’_SYSSMU45_2664745
054$’,’_SYSSMU46_2952576209$’,’_SYSSMU47_4219170473$’,’_SYSSMU48_742215572$’,’_SYSSMU49_937300175$’,’_SYSSMU52_511639027$’,’_SYSSMU54_76472660
6$’

Ahora llega el momento de crear nuestro tablespace de undo nuevo que le llamaremos por ejemplo UNDOTBS2 y de paso modificamos el tablespace de undo por defecto de la bd:

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘ruta’ SIZE 1G;
ALTER SYSTEM SET undo_tablespace=’UNDOTBS2′;

Hacemos un shutdown immediate de la bd y si nos da error, hacemos un shutdown abort y procedemos a abrir la bd con el pfile que hemos creado anteriormente:

STARTUP PFILE=’ruta/initSID.ora’;

Ahora tenemos offline los segmentos de rollback corruptos y la administracion del tablespace UNDO en manual, con lo que ya podemos proceder a borrar sin error el tablespace corrupto:

DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Hacemos un shutdown immediate y ejecutamos un nuevo startup para que esta vez si nos arranque con nuestro spfile y ya tenemos la bd fina para poder seguir trabajando con ella.

Anuncios

Estructuras de datos lógicas y físicas

Introducción:

En una base de datos hay estructuras lógicas y estructuras físicas. Vamos a ver las que componen el almacenamiento de los datos en si:

Estructura lógica:

Una base de datos está formada por una o varias unidades lógicas llamadas tablespaces. Un tablespace es la unidad de almacenamiento lógico. Además, cada una de estos tablespaces está formada por uno o varios ficheros físicos que son los datafiles. Un datafile solamente puede pertenecer a un tablespace. Por lo tanto, los datafiles de una base de datos son todos los datafiles que forman parte de todos los tablespaces de la base.

Cuando se crea una base de datos, lleva implicita la creacion de 3 o 4 tablespaces que son:

SYSTEM: es el tablespace principal de la bd, donde se almacena el diccionario de datos y el tablespace por defecto de todos los demas usuarios si no les asignamos otro distinto.

SYSAUX: Es principalmente donde se almacenan las metricas del sistema, los snapshots y demas estadisticas. Si no tenemos cuidado con la configuracion de nuestra base de datos a nivel de tiempo de retencion de toda esta recopilacion de metricas, podemos tener problemas en el futuro con el tamaño de este tablespace.

UNDO: Es el tablespace de rollback de la base de datos y es obligatorio crearlo siempre y cuando tengamos el parametro UNDO_MANAGEMENT=AUTO, si su valor es MANUAL, se crearan segmentos de rollback en el tablespace system en lugar del tablespace UNDO.

TEMP: Es un tablespace temporal, el cual se utiliza para realizar las ordenaciones y agrupaciones en las queries, dejando luego libre el espacio que se ha utilizado. Se pueden crear varios tablespaces TEMP en la base de datos, habiendo un parametro global que indica cual va a ser utilizado por defecto, pero teniendo tambien la posibilidad de definir un temporary tablespace a nivel de schema.

En la base de datos tenemos dos tablas en las que podemos consultar la informacion de nuestros tablespaces, V$TABLESPACE y DBA_TABLESPACES.

Segmentos:

Un segmento almacena la información de una estructura lógica de Oracle dentro de un Tablespace. Está formado por una o más extensiones y, a medida que va creciendo el segmento se van asignando nuevas extensiones al mismo. Hay cuatro tipos de segmentos: de datos, de índices, temporales y de rollback.

Tendremos segmentos de datos para tablas, segmentos de índices para índices, segmentos de rollback para poder deshacer o rehacer cambios por transacciones y segmentos temporales.

Un segmento de datos es el lugar donde se almacenan todos los datos de una tabla, de una partición de una tabla o de un cluster de tablas. Se crea el segmento de datos a la hora de ejecutar la sentencia create que crea la tabla, cluster o partición. En dicha sentencia se indican también los valores de la cláusula storage, en el cuál se va a determinar la forma en que dicho segmento va a ir asignando y desasignando las extensiones.

Podemos consultar la informacion de los segmentos en la tabla DBA_SEGMENTS.

Extensiones:

Una extensión es una unidad lógica de almacenamiento que está formada por un número determinado de bloques de datos contiguos. La agrupación de una o varias extensiones forman un segmento que puede ser una tabla, un índice, un segmento de rollback o un segmento temporal. Por lo tanto, datos de una tabla, sabemos que están en un solo segmento de tipo tabla, que a su vez estará formado por una o varias extensiones y que, cada una de esas extensiones está formada por un número determinado de bloques de datos.

Bloques:

El bloque es la unidad mínima de almacenamiento en Oracle y podemos elegir el tamaño en el momento de la creación de nuestra base de datos, dependiendo del uso que vayamos a dar a la misma y no pudiendo modificar este dato, mas que en ciertos upgrades a versiones mas modernas del producto.

Para una mayor optimizacion del rendimiento de I/O, Oracle recomienda que el tamaño del bloque sea multiplo del tamaño del OS block (bloque del sistema operativo).

Todos los bloques de una base de datos poseen la siguiente estructura:

Cabecera:

Contiene información general sobre el bloque como el tipo de segmento al que pertenece (índice, tabla, rollback) o la dirección del bloque.

Directorio de Tablas:

Contiene información acerca de las tablas que tienen datos en el bloque.

Directorio de Filas:

Contiene información sobre las filas que se encuentran en cada momento en el bloque. Esta información incluye la dirección de la fila dentro de la subzona “Datos de Filas” del bloque en la que debe buscar Oracle los datos.

El espacio ocupado por esta subzona va aumentando a medida que se insertan nuevas filas en el bloque, sin embargo nunca se libera el espacio. Si se borran filas de datos que estaban en el bloque, en el directorio de filas desaparecerá la entrada que apuntaba a ellas, pero el espacio permanecerá reservado aunque vacío. A medida que se insertan nuevas filas de datos en el bloque, también se insertan registros en el Directorio de Filas, pero antes de aumentar el tamaño de esta subzona para la nueva entrada, se comprueba si alguna de las entradas que hay está vacía y en ese caso se “ocupa” y no hace falta que crezca más la subzona.

Espacio Libre:

Esta subzona está reservada para la inserción de nuevas filas en el bloque o, para la modificación de campos que requieren más espacio que el que tenían con anterioridad. Esto último ocurre, por ejemplo, con los campos que son de tipo varchar2.

Si en el bloque se están almacenando datos de segmentos de tipo tabla o índice, en la subzona de Espacio Libre también se utiliza para llevar un registro de las transacciones que en cada momento acceden a datos del bloque. Se necesita una entrada de transacción siempre que se realice una insert, update, delete o select for update sobre filas del bloque. El tamaño necesario para cada una de las entradas de transacciones depende del sistema operativo.

Datos de Filas:

En esta subzona se almacenan los datos de las tablas o de los índices del bloque. Se puede dar el caso de que una fila no entre completa en el bloque y tenga que ocupar más de un bloque. Este caso especial se comentará más a fondo en el apartado de encadenamiento y migración de filas.

Estructura física:

Es la estructura que nos vamos a encontrar directamente en nuestros volúmenes de almacenamiento, ya sean locales o una cabina de discos, un NAS, NFS, ZPOOL, etc.

Generalmente en una base de datos en producccion es aconsejable trabajar con una cabina de almacenamiento dedicada para evitar concurrencia en los discos y con ASM (automatic storage management), pero hablaremos de esto en otra ocasión.

Datafiles:

Son ficheros que componen los tablespaces y que los vamos a poder encontrar en nuestro almacenamiento generalmente con extension .dbf.

Su tamaño maximo puede variar dependiendo del tipo de tablespace:

Smallfiles:

Pueden llegar hasta un maximo de 32 Gb y un tablespace puede contener los que sean necesarios

Bigfiles:

Pueden llegar hasta un maximo de 128 Tb dependiendo del tamaño del bloque que seleccionemos y un tablespace solamente puede contener uno. Estos tablespaces pueden mejorar el rendimiento de la base de datos si alojamos en ellos tablas que sepamos que van a tener un crecimiento considerable, pero tambien tienen el problema que ante una corrupcion del fichero, el tiempo de recuperacion de la base de datos puede hacerse tambien mayor.

En cuanto a su crecimiento, los datafiles pueden ser autoextensibles o no, pudiendo indicar con la clausula MAXSIZE el tamaño que pueden llegar a alcanzar segun lo vayan requiriendo.

Podemos consultar informacion sobre los datafiles en la tabla DBA_DATA_FILES.

Instalacion de Software Oracle database sin entorno gráfico

En algunas ocasiones nos encontramos con maquinas linux/unix que por determinados motivos no nos permiten levantar las X para poder realizar una instalacion grafica guiada de nuestro software de Oracle.

En esta entrada, vamos a explicar como podemos instalar mediante la consola tanto software de grid como de base de datos, cosa que posiblemente muchos ya sabreis pero no viene mal recordar. La instalacion que realizaremos será solo de software, sin crear la base de datos, ni la instancia de asm y de modo desatendido (la manera óptima para realizar instalaciones masivas).

Para estas situaciones, el software de oracle nos provee de un archivo llamado response file con extension .rsp y el cual contiene dentro una plantilla muy sencilla de completar, poniendo los datos que nos solicite en cada apartado.

Nos pide informacion como la ruta de nuestro ORACLE_HOME, que tipo de instalacion deseamos realizar, la ruta del orainventory, el idioma o los usuarios del sistema operativo que usaremos para la instalación.

Este fichero lo podemos encontrar dentro de la ruta en la que hemos descomprimido nuestro software, en la carpeta /response

Editamos el fichero con nuestro editor preferido, en mi caso vi y en la cabecera encontraremos instrucciones acerca de como rellenar el fichero. Una vez lo tengamos, salimos guardando los cambios y nos vamos a la ruta del fichero de instalación runInstaller.

No voy a explicar todas las opciones de instalacion que nos provee runInstaller, pero podemos ejecutarlo con el parametro -help y nos listara una ayuda muy util.

Ahora vamos a instalar:

/directory_path/runInstaller -silent -noconfig -responseFile /response_path/response_file_name.rsp

Una vez ejecutado, se nos devolvera el control del prompt y cuando termine la instalación nos saldra un aviso con la ruta del log de la instalacion y los ficheros .sh que deberemos ejecutar como root, grid o lo que nos indique.

Si nos muestra algun error, como si de una instalacion grafica se tratase, deberemos revisar que error nos ha devuelto y repasar los prerrequisitos, ver si nos falta algun paquete por instalar, si alguna ruta ya existe o no tiene permisos, etc.

Si todo ha salido correctamente, ya podemos crear nuestra base de datos o nuestra pila de recursos con el crs control.

Uso del SQL TUNNING ADVISOR

Declaramos la variable de la tarea

SET LONGCHUNKSIZE 30000
SET LINESIZE 30000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF
variable stmt_task VARCHAR2(64);

Modificamos el parámetro del time limit del autotunning task porque si la query es muy pesada nos puede dar el evento de interrupted (me ha ocurrido :-))

exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(‘TIME_LIMIT’,9000);

Ejecutamos la creacion de la tarea de tunning y la apuntamos con el print para luego poder consultarla en la tabla user_advisor_tasks (por defecto se almacena 30 dias pero se puede modificar)

EXEC :stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ‘3kjtfcfr8gvwx’)
print stmt_task
TASK_295900

Aqui ya si ejecutamos el tunning para que nos aconseje el mejor plan de ejecucion

exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task)

Comprobamos que la tarea ha salido completa (recordad el time limit)

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = :stmt_task;

COMPLETED

Enviamos el resultado a un fichero de texto
para su posterior análisis

spool sqltunerpt.txt
SET LINES 300 PAGES 30000 LONG 20000000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:stmt_task) FROM DUAL;

Cerramos el spool y leemos el advisor por si alguna accion nos ayuda a mejorar el plan de ejecucion (creacion de índices, aplicar un profile, etc)

spool off

Editamos el fichero que nos ha dado como resultado y en este ejemplo podemos comprobar que nos recomienda la creacion de un índice.
La estructura del fichero sera la siguiente:

 

  • Resumen e informacion de la query.
  • Plan de ejecucion actual.
  • Plan o planes de accion a tomar, con su porcentaje de beneficio y a continuacion su explain plan.

 

En este ejemplo podemos ver como nos sugiere aplicar un profile (nos da ya la sentencia para aplicarlo) o la creacion de un índice (tambien nos facilita la sentencia para crearlo), con lo que solo nos quedaria mirar que beneficio nos indica para cada acción y comparar los planes de ejecución para saber con cual quedarnos.

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task) FROM DUAL;
—————————————————————–
— Script generated by DBMS_SQLTUNE package, advisor framework —
— Use this script to implement some of the recommendations —
— made by the SQL tuning advisor. —
— —
— NOTE: this script may need to be edited for your system —
— (index names, privileges, etc) before it is executed. —
—————————————————————–
execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_48969’, replace => TRUE);
create index UMDP.IDX$$_BF490001 on UMDP.ACTIVIDADES(“ACT_ANEXO_IV”,”ACT_PRIORIDAD”);
create index UMDP.IDX$$_BF490002 on UMDP.INDICE_DOCUMENTOS(“IND_DNI_PERSONAL”,”IND_DNI_LABORAL”,”IND_ACTIVIDAD”);

Parámetros a modificar por si fuera necesario (recordad el TIME_LIMIT que modificamos al principio para que no diera timeout el proceso):

SET lines 200 pages 100
col DESCRIPTION FOR a100
col parameter_value FOR a15
SELECT parameter_name,parameter_value,is_default,description
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name=’SYS_AUTO_SQL_TUNING_TASK’
ORDER BY parameter_name;

Arreglando fallos en Oracle 11g con DRA y RMAN

El Data Recovery Advisor fue introducido en Oracle 11g, una característica que ofrece recomendaciones respecto a fallas de bases de datos y también las repara si es posible. El DRA depende del Health Monitor para diagnósticos y puede ser usado por medio de RMAN o Enterprise Manager.

A continuacion vamos a ver como recuperar con DRA y RMAN una base de datos en la que hemos borrado un datafile y no levanta la instancia:

oracle@test:~$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 27 13:35:56 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1335892 bytes

Variable Size 213913004 bytes

Database Buffers 41943040 bytes

Redo Buffers 6447104 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 – see DBWR trace file

ORA-01110: data file 5: ‘/opt/oracle/oradata/test/example01.dbf’

La base de datos no se abrió como era de esperarse, por lo que el siguiente paso es ejecutar RMAN para usar el DRA. Como quizás sepas, es necesario tener al menos montada la base de datos para poder usar RMAN:

oracle@test:~$ rman target /

Recovery Manager: Release 11.2.0.4.0 – Production on Wed Mar 27 13:36:56 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: TEST (DBID=821368574, not open)

El siguiente paso es listar las fallas actuales conocidas:

RMAN> list failure;

using target database control file instead of recovery catalog

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

42 HIGH OPEN 27-MAR-15 One or more non-system datafiles are missing

Si quieres saber más detalles acerca de cierta falla también puedes hacerlo:

RMAN> list failure 42 detail;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

42 HIGH OPEN 27-MAR-15 One or more non-system datafiles are missing

Impact: See impact for individual child failures

List of child failures for parent failure ID 42

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

145 HIGH OPEN 27-MAR-15 Datafile 5: ‘/opt/oracle/oradata/test/example01.dbf’ is missing

Impact: Some objects in tablespace EXAMPLE might be unavailable

El Health Monitor corre diagnósticos automáticamente cuando una falla elevada o crítica aparece, pero si por alguna razón no hay primero un diagnóstico hecho entonces no puedes obtener consejo respecto a esa falla. El siguiente paso es obtener consejo acerca de las fallas abiertas:

RMAN> advise failure;

List of Database Failures

=========================

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

42 HIGH OPEN 27-MAR-15 One or more non-system datafiles are missing

Impact: See impact for individual child failures

List of child failures for parent failure ID 42

Failure ID Priority Status Time Detected Summary

———- ——– ——— ————- ——-

145 HIGH OPEN 27-MAR-15 Datafile 5: ‘/opt/oracle/oradata/test/example01.dbf’ is missing

Impact: Some objects in tablespace EXAMPLE might be unavailable

analyzing automatic repair options; this may take some time

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=133 device type=DISK

analyzing automatic repair options complete

Mandatory Manual Actions

========================

no manual actions available

Optional Manual Actions

=======================

1. If file /opt/oracle/oradata/test/example01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options

========================

Option Repair Description

—— ——————

1 NOARCHIVELOG mode restore datafile 5

Strategy: The repair includes complete media recovery with no data loss

Repair script: /opt/oracle/diag/rdbms/test/test/hm/reco_1400180561.hm

Como podrás imaginar, el DRA primero verifica prerequisitos relevantes e indispensables antes de dar cualquier opción de reparación automática, como respaldos de bases de datos. Las acciones manuales opcionales son recomendaciones sobre cómo resolver las fallas a mano, en lugar de dejar que el ADR las arregle automáticamente. Agunas veces tienes que llevar a cabo acciones manuales obligatorias antes de poder hacer cualquier reparación manual o automática, y en este caso no hay acciones manuales obligatorias a realizar.

Además, hay dos tipos de opciones de reparación: sin pérdida de datos y con pérdida de datos, asegúrate de revisar la estrategia para saber si la opción de reparación seleccionada no implica pérdida de datos, o por lo menos entender qué vas a perder si seleccionas esa opción en caso de que no haya mejor opción.

Adicionalmente, si quieres puedes revisar y modificar el script de reparación listado antes de hacer cualquier reparación, o puedes probar el procedimiento de reparación sin llevar a cabo ninguna reparación de esta forma:

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /opt/oracle/diag/rdbms/orcl11g/orcl11g/hm/reco_1400180561.hm

contents of repair script:

# NOARCHIVELOG mode restore datafile

restore datafile 5;

recover datafile 5;

Si te convencen las opciones de reparación automáticas entonces puedes reparar las fallas abiertas de la base de datos de esta manera:

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss

Repair script: /opt/oracle/diag/rdbms/test/test/hm/reco_1400180561.hm

contents of repair script:

# NOARCHIVELOG mode restore datafile

restore datafile 5;

recover datafile 5;

Do you really want to execute the above repair (enter YES or NO)? YES

executing repair script

Starting restore at 27-MAR-15

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00005 to /opt/oracle/oradata/test/example01.dbf

channel ORA_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/TEST/backupset/2015_03_26/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp

channel ORA_DISK_1: piece handle=/opt/oracle/flash_recovery_area/TEST/backupset/2015_03_26/o1_mf_nnndf_TAG20130305T165413_8mdxwq5t_.bkp tag=TAG20130305T165413

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:06

Finished restore at 27-MAR-15

Starting recover at 27-MAR-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 27-MAR-15

repair failure complete

Do you want to open the database (enter YES or NO)? YES

database opened

RMAN> exit

Recovery Manager complete.

Tienes que confirmar que realmente quieres ejecutar las reparaciones, y como paso final podrías querer abrir la base de datos antes de salir de RMAN. Y eso es todo! En este caso todo salió bien y la base de datos fue reparada sin pérdida de datos:

oracle@test:~$ sqlplus ‘/ as sysdba’

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 27 13:43:10 2015

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> column INSTANCE_NAME format a20

SQL> column STATUS format a20

SQL> column DATABASE_STATUS format a20

SQL> select INSTANCE_NAME, STATUS, DATABASE_STATUS from v$instance;

INSTANCE_NAME STATUS DATABASE_STATUS

——————– ——————– ——————–

TEST OPEN ACTIVE

Particionar una tabla ya existente en caliente

En esta entrada vamos a hablar acerca de como particionar en caliente una tabla ya existente con el paquete DBMS_REDEFINITION.

Dedicaremos otra entrada a explicar todo acerca de las tablas particionadas, indices globales o locales, etc.

-Lo primero es crear la tabla igual que la origen pero particionada. (si no creamos las constraints luego las copiara automáticamente, si las copiamos despues dará un warning al ya existir).

En nuestro ejemplo tenemos una tabla llamada FICHA en el esquema TEST y la tabla particionada que crearemos se llamará FICHA_PRU.

-Luego empezamos el proceso de copiar los datos de una tabla a otra:

BEGIN DBMS_REDEFINITION.start_redef_table( uname => ‘TEST’, orig_table => ‘FICHA’, int_table => ‘FICHA_PRU’, options_flag => dbms_redefinition.cons_use_pk ); END; /

Hacemos un select count de las dos tablas y comprobamos que tienen los mismos registros.

-Sincronizamos las tablas para permitir la transparencia del proceso para el usuario.

BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘TEST’, ‘FICHA’, ‘FICHA_PRU’); END; /

-Sincronizamos todos los objetos, si queremos particionar los indices, ponemos el valor copy_indexes a 1 para luego particionarlos con mas tiempo:

set serveroutput on; DECLARE num_errors PLS_INTEGER; BEGIN DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS( uname => ‘TEST’, orig_table => ‘FICHA’, int_table => ‘FICHA_PRU’, copy_indexes => 1, copy_triggers => TRUE, copy_constraints => TRUE, copy_privileges=> TRUE, ignore_errors => TRUE, num_errors => num_errors); dbms_output.put_line (‘Errores: ‘||num_errors); END; /

-Revisamos los errores en el proceso:

col ddl_txt for a60 set pages 200 select object_name, object_type, base_table_name, ddl_txt from DBA_REDEFINITION_ERRORS;

Si hay errores, revisamos los objetos registrados en el proceso y los errores obtenidos en la query anterior:

select OBJECT_TYPE, OBJECT_NAME, INTERIM_OBJECT_NAME from DBA_REDEFINITION_OBJECTS where BASE_TABLE_OWNER = ‘TEST’ and BASE_TABLE_NAME = ‘FICHA’;

-En el caso de que hayamos decidido crear algun objeto a mano, ahora es el momento de hacerlo y una vez creado lo registramos de la siguiente forma:

BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT( uname => ‘TEST’, orig_table => ‘FICHA’, int_table => ‘FICHA_PRU’, dep_type => DBMS_REDEFINITION.CONS_INDEX, dep_owner => ‘TEST’, dep_orig_name => ‘INDICE_EN_TABLA_ORIGEN’, dep_int_name => ‘INDICE_EN_TABLA_DESTINO’); END; /

-Comparamos las constraints de ambas tablas con la siguiente query:

select constraint_type, count (9) from ( select CONSTRAINT_TYPE from dba_constraints where table_name = ‘FICHA’ minus select CONSTRAINT_TYPE from dba_constraints where table_name = ‘FICHA_PRU’) group by constraint_type;

-Nuevamente sincronizamos los datos de ambas tablas para que no se acumulen registros:

BEGIN DBMS_REDEFINITION.SYNC_INTERIM_TABLE(‘TEST’, ‘FICHA’, ‘FICHA_PRU’); END; /

-Una vez llegados a este paso, debemos decidir si ha salido todo bien y finalizar el proceso, o si algo ha fallado, dar marcha atras en el proceso (aun estamos a tiempo):
-Marcha atras (queda todo como antes de iniciar el proceso):

BEGIN dbms_redefinition.abort_redef_table( uname => ‘TEST’, orig_table => ‘FICHA’, int_table => ‘FICHA_PRU’); END; /

-Finalizar el proceso(la tabla origen queda particionada y con los datos actualizados, se recomienda recompilar objetos invalidos):

BEGIN DBMS_REDEFINITION.FINISH_REDEF_TABLE(‘TEST’, ‘FICHA’, ‘FICHA_PRU’); END; /

UNDO Tablespace

En esta nueva entrada vamos a ver que es el tablespace UNDO, como configurarlo, como se optimiza y como resolver errores asociados al mismo.

El tablespace UNDO sirve para retener los cambios que se realizan sobre los datos en Oracle y asi poder deshacerlos, con un tiempo limite de retencion configurable y siempre y cuando los bloques del dato que queramos recuperar no hayan sido sobreescritos en el UNDO.

Este tablespace existe desde la version 10G de oracle, usandose en versiones anteriores segmentos del tablespace SYSTEM llamados “segmentos de rollback”.

Este tablespace se utiliza en sentencias dml, tanto en lectura (select) como en escritura (insert, update, delete y merge). Cuando ejecutamos una transaccion de escritura contra la base de datos, los datos sobre los que modificamos se escriben en los bloques del tablespace UNDO y dependiendo de la retencion y del tamaño del tablespace podremos o no recuperar esos datos una vez hagamos commit.

Pueden existir varios tablespaces UNDO al mismo tiempo en una base de datos, pero solamente uno de ellos puede estar activo y es accedido por todos los schemas.

Existen varios parametros importantes en la base de datos que afectan al tablespace UNDO y son los siguientes:

  • UNDO_MANAGEMENT:

Este parametro tiene 2 posibles valores, AUTO que es el valor por defecto en bases de datos superiores a la version 9i de Oracle y MANUAL, el cual no utilizaria el tablespace UNDO y usaria los segmentos de rollback que hemos mencionado anteriormente en el tablespace SYSTEM.

  • UNDO_TABLESPACE:

El valor de este parametro es un “string” y nos indicara el nombre del tablespace UNDO activo en la base de datos.

  • UNDO_RETENTION:

Este parametro indica en segundos el tiempo de retencion que Oracle mantendra los bloques sin marcar como “expired” siempre y cuando tenga espacio en el tablespace y pueda reutilizar otros bloques. Oracle no nos garantizara este tiempo de retencion excepto si configuramos el tablespace con la opcion “GUARANTEE”, de la cual hablaremos mas adelante y daremos algunos consejos para no caer en errores frecuentes ya que nuestro UNDO se podria quedar sin espacio y no podria sobreescribir bloques que tengan garantia de retencion y no hayan cumplido el tiempo configurado.

La instruccion completa mas comun para la creacion de un tablespace undo es la siguiente:

CREATE UNDO TABLESPACE UNDOTBS1

DATAFILE ‘/u02/oradata/PRUEBA001/rbs/UNDOTBS1_01.dbf’ SIZE 200M;

Esta sentencia nos crearia un tablespace UNDO llamado UNDOTBS1 en el filesystem /u02… y de 200 Mb de tamaño.

Tenemos la opcion de configurar el tablespace autoextensible con la clausula “AUTOEXTEND ON” e indicarle el tamaño maximo al que se puede extender el datafile con la clausula “MAXSIZE=xxM” en Mb.

Acerca de la garantia de retencion, si no indicamos nada, por defecto lo crea sin ella, pero podemos crearlo con garantia de retencion añadiendo la clausula “RETENTION GUARANTEE” y desactivarla ejecutando un alter del tablespace con la clausula “RETENTION NOGUARANTEE”.

El error mas frecuente que nos encontramos en nuestra base de datos debido a una mala configuracion del tablespace UNDO o bien a una transaccion que tarda demasiado es el ORA-01555: snapshot too old y lo podemos encontrar en varias situaciones:

  • En lecturas (select o export consistentes):

En este caso estamos intentando hacer una lectura consistente de la base de datos y si la sentencia tarda demasiado y se producen modificaciones en la tabla que estamos consultando y despues se eliminan los bloques escritos en el tablespace UNDO, oracle considera que la lectura ya no es consistente y termina la select o el export con este error.

  • En escrituras (insert, update, delete, merge):

El error en este caso surge cuando las sentencias sin comitear se sin espacio en el tablespace undo y no hay bloques expirados para poder sobreescribirlos o bien duran tanto que no se puede garantizar su retencion en el tiempo indicado en el parametro UNDO_RETENTION y al tener garantia de retencion, los bloques se marcan como expirados y necesitan ser sobreescritos por otra sentencia. En este ultimo caso Oracle muestra el error porque no puede garantizar un rollback seguro.

En ambos casos de escritura se hace rollback de la sentencia automaticamente y nos muestra el error de snapshot too old.

Los modos de evitar este error, seria en lectura intentar mejorar el plan de nuestra query para que sea mas rapida o en el caso del export, introducirle paralelismo para poder terminar antes. Otra solucion para la lectura seria ampliar el espacio de nuestro tablespace UNDO.

En escritura dependiendo de si tenemos o no garantia de retencion, lo resolveriamos de varios modos:

  • Sin garantia de retencion;

Intentariamos optimizar las sentencias o introducirles paralelismo. Tambien podriamos ampliar el tablespace UNDO. En el caso de que estemos ejecutando muchas sentencias dml, introducir commit cada ciertas rows.

  • Con garantia de retencion:

Podemos ejecutar esta sentencia:

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”, SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,

ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) “OPTIMAL UNDO RETENTION [Sec]”

FROM (SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c

WHERE c.contents = ‘UNDO’ AND c.status = ‘ONLINE’ AND b.name = c.tablespace_name AND a.ts# = b.ts#) d,

v$parameter e,

v$parameter f, (SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat) g

WHERE e.name = ‘undo_retention’ AND f.name = ‘db_block_size’;

El valor obtenido es el recomendado para el parametro UNDO_RETENTION dependiendo del tamaño de nuestro tablespace UNDO.

O bien tenemos esta otra sentencia:

SELECT d.undo_size/(1024*1024) “ACTUAL UNDO SIZE [MByte]”, SUBSTR(e.value,1,25) “UNDO RETENTION [Sec]”,

(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) “NEEDED UNDO SIZE [MByte]”

FROM (SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c

WHERE c.contents = ‘UNDO’ AND c.status = ‘ONLINE’ AND b.name = c.tablespace_name AND a.ts# = b.ts#) d,

v$parameter e, v$parameter f,

(SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat) g

WHERE e.name = ‘undo_retention’ AND f.name = ‘db_block_size’;

El valor obtenido de esta sentencia nos indicaria el tamaño necesario del tablespace UNDO para poder garantizar la retencion de tiempo configurada en el parametro UNDO_RETENTION.

En las nuevas versiones de Oracle, el tablespace UNDO tambien se ve involucrado en nuevas funcionalidades como “flashback transaction query” para ejecutar sentencias de recuperacion de estados anteriores de la base de datos, pero hablaremos de ello en el apartado de flashback.