martes, 30 de noviembre de 2010

Tablespace Temporal y el error ORA-01652

Uno de los errores mas odiados por cualquier programador, es el ORA-01652, esto es por que muchos de ellos no alcanzan a comprender cual es la causa de este, para muchos programadores lo atribuyen a un error de la Base de datos y sencillamente piensan en añadir mas espacio al tablespace temporal, cuando en muchas de las ocasiones es causado por ellos mismos al tener operaciones de tipo SORT (GROUP BY, ORDER BY) que estan causando este error.

Para comprender un poco mas de que se trata este error, primero hay que comprender que es el tablespace Temporal (Temporary Tablespace).
El tablespace temporal contiene datos en transito que permanecen unicamente por la duracion de la sesion, esto significa que una vez que la sesion termina, estos datos dejan de existir. Un tablespace temporal ayuda a mejorar la concurrencia de multiples operaciones tipo SORT que no caben en la memoria.
Un tablespace temporal se usa para guardar lo siguiente
  • Tablas temporales e indices temporales
  • LOBs temporales
  • Resultados temporales de tipo SORT
  • Arboles B temporales
Los tablespaces temporales utilizan un tipo de segmento llamado segmento temporal, pero algo importante a saber es que dentro de un tablespace temporal, todas las operaciones tipo SORT comparten un solo segmento SORT. Un segmento SORT es creado por la primer sentencia que, despues de iniciar la base de datos utiliza el tablespace temporal para hacer una operacion tipo SORT y es liberado unicamente cuando la base de datos es apagada.
Ya que sabemos esto, tambien es importante saber que cualquier escritura a un segmento SORT no genera ni redo o undo, este se debe a que los datos que existen en el tablespace temporal no necesitan existir mas alla que para la sesion que los creo.

Una sentencia de SQL puede hacer multiples operaciones de tipo SORT y una sesion puede tener multiples sentencias de SQL activas a la vez, cada una con la potencia de tener multiples operaciones SORT a disco. Cuando los resultados de una operacion SORT a disco ya no son necesitados, los bloques en el segmento SORT son marcados como "no utilizados" y pueden ser usados por otra operacion tipo SORT.

Aqui es donde entra el error ORA-01652, ya que una operacion tipo SORT va a fallar con este error cuando no existen bloques "no utilizados" en el segmento SORT o cuando el tablespace temporal no tenga espacio suficiente para alojar un extent mas.

Una vez que suceda cualquiera de los dos casos anteriores, se va a registrar este error en el log de la base de datos, pero el verdadero problema viene en que, solamente se registra el error, mas no la sentencia que causo el error.

De aqui lo mas recomendable es tener un monitoreo activo, podemos crear en Grid control/database control una metrica con el query que tenemos abajo

select count(1) from (
select trunc(100*(u.tot/d.tot),2) PCTUSED from
     (select sum(u.blocks) tot from v$tempseg_usage u) u,
     (select sum(d.blocks) tot from dba_temp_files d) d )
where PCTUSED > 50;


Si PCTUSED es mayor a 50, nos va a permitir ingresar a la base de datos y poder detectar que sesion es la que esta haciendo una operacion de tipo SORT mayor y de ahi poder detectar la sentencia de SQL que esta consumiendo un segmento de tipo SORT mayor.

Este query de abajo no me pertenece, y no tomo credito por el, de la misma manera debe de ser revisado y ejecutado en un ambiente de prueba antes de ejecutarlo en produccion , me lo encontre en internet hace rato, pero es el que utilizo para hacer una busqueda de las sesiones y los Mbs de sort que estan usando

--------Temp Space Usage
column SID_SERIAL format a15
column OSUSER format a15
column USERNAME format a15
column MODULE format a10
column PROGRAM format a10
column TABLESPACE format a20
set pages 100 lines 130

SELECT   S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
         S.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
         COUNT(*) sort_ops,T.segtype
FROM     v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE    T.session_addr = S.saddr
AND      S.paddr = P.addr
AND      T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
         S.program, TBS.block_size, T.tablespace,T.segtype
ORDER BY mb_used,sid_serial
/


Conclusion
Lo que si debemos de saber es que una vez que sucede el error ORA-01652 con un tablespace temporal, lo que tenemos que hacer es identificar que sesion es la que esta consumiendo mas SORT, si la sesion que estaba consumiendo mas SORT desaparece , no podremos saber que sentencia de SQL estaba consumiendo nuestra tablespace temporal, de ahi que lo mejor que debemos y tenemos que hacer es tener un monitoreo proactivo no reactivo ante esta situacion.

miércoles, 24 de noviembre de 2010

Respaldos con RMAN - Parte II

En la entrada anterior (Parte I) platicamos un poco de los componentes basicos de RMAN para poder hacer un respaldo.
En esta entrada vamos a configurar un esquema de Catalogo para recuperacion, asi como conectarnos a el y hacer un respaldo de nuestra base de datos.

Conectarnos a RMAN
Existen varias maneras de conectarnos a RMAN, aqui hay varias, la que vamos a utilizar es la tercera, ya nos vamos a estar conectando a un esquema de catalogo de recuperacion.
  • rman TARGET /                       # Autorización via OS
  • rman TARGET SYS@prod NOCATALOG      # RMAN pregunta por el password de SYS de la BD prod
  • rman TARGET / CATALOG rco@catdb     # RMAN pregunta por el password de rco de la BD catdb
Configuracion del esquema de Catalogo de Recuperacion
Este esquema lo vamos a crear en una instancia distinta a la que queremos respaldar, en este caso creamos previamente la BD va a llevar el nombre de catdb y el esquema de rco.
  1. En la BD catdb, nos conectamos via SQL*Plus y vamos a crear un tablespace llamado "catalogo_recup"
    CREATE TABLESPACE "catalogo_recup"
    DATAFILE ' /mount/u01/oracle/PROD/data/catalogo_recup01.dbf'
    SIZE 100M
    EXTENT MANAGEMENT LOCAL
    SEGMENT SPACE MANAGEMENT AUTO
    /
  2. Todavia en catdb, vamos a crear el esquema rco, asi como definir el tablespace defualt, asignarle una cuota indefinida y por ultimo asignarle el rol de RECOVERY_CATALOG_OWNER
    CREATE USER rco IDENTIFIED BY rco
    DEFAULT TABLESPACE catalogo_recup
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON catalogo_recup
    /
    GRANT RECOVERY_CATALOG_OWNER TO rco
    /
  3. Una vez que creamos el esquema rco, nos salimos del ambiente de SQL*Plus y nos vamos a conectar con el cliente de RMAN al esquema que acabamos de crear, mencionando que este es un catalogo y vamos a crear el catalogo en este esquema.
    rman catalog rco/rco@catdb
    Recovery Manager: Release 10.2.0.4.0 - Production on Wed Nov 24 06:07:39 2010
    Copyright (c) 1982, 2007, Oracle.  All rights reserved.

    connected to recovery catalog database

    RMAN> create catalog;

    recovery catalog created

    RMAN> exit
  4. Algo que es importante saber, cuando registramos dos o mas BDs en el catalogo, es necesario que cada una de estas tengan un identificador unico, este es manejado por Oracle, y es el DBID, que lo podemos encontrar en la vista de v$database
    prod >select DBID from v$database;

          DBID
    ----------
     949818943
    Si quieres registrar otra base de datos que tenga el mismo DBID, lo vas a tener que hacer en otro catalogo o vas a tener que cambiar el DBID de la base de datos con la utileria de Oracle $ORACLE_HOME/bin/nid (DBNEWID), que esto lo veremos en otra entrada, pero por lo pronto puedes ir a la documentacion oficial de como utilizar esta.
  5. Lo ultimo que nos falta de hacer es registrar la base de datos prod en el catalogo que acabamos de crear, esto lo hacemos desde el cliente de rman, conectandonos a la BD que queremos respaldar y al catalogo que vamos a utilizar.

    rman target system/system_password@prod catalog rco/rco@catdb

    RMAN> register database;
    Una vez que esta registrada la BD prod, podemor correr el comando report schema, para verificar que el registro se llevo a cabo.
    RMAN> report schema;


    Report of database schema for database with db_unique_name prod

    List of Permanent Datafiles
    ===========================
    File Size(MB) Tablespace           RB segs Datafile Name
    ---- -------- -------------------- ------- ------------------------
    1    500      SYSTEM               ***     /mount/u01/oracle/prod/data/system.dbf
    2    800      UNDOTBS1             ***     /mount/u01/oracle/prod/data/undotbs1.dbf
    3    475      SYSAUX               ***     /mount/u01/oracle/prod/data/sysaux.dbf
    4    100      USERS                ***     /mount/u01/oracle/prod/data/users01.dbf

    List of Temporary Files
    =======================
    File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
    ---- -------- -------------------- ----------- --------------------
    1    5000     TEMP                 5000        /mount/u01/oracle/prod/data/temp01.dbf
Respaldando nuestra base de Datos
Una vez configurando nuestro catalog y registrando nuestra BD en el catalogo, existen varias maneras de hacer un respaldo, pero a continuacion es un ejemplo de un respaldo incremental nivel 0, a su vez respaldando el controlfile, los archivelogs y el spfile.
rman target system/system_password@prod catalog rco/rco@catdb
Recovery Manager: Release 11.2.0.2.0 - Production on Wed Nov 24 23:30:21 2010

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

connected to target database: prod (DBID=949818943)
connected to recovery catalog database
RMAN> RUN
2> {
3> ALLOCATE CHANNEL CH1 DEVICE TYPE DISK
4>  FORMAT '/mount/copy01/prod/oracle/prod/incr/%d_HOT_%M%D%Y_%p_%s';
5>  BACKUP AS COMPRESSED BACKUPSET
6>  INCREMENTAL LEVEL = 0
7>  DATABASE TAG prod_HOTINCR_1124_2321;
8>  BACKUP FORMAT '/mount/copy01/prod/oracle/prod/control/%d_%M_%D_%Y_%t.ctl'
9>  CURRENT CONTROLFILE
10>  TAG prod_CONTROLFILE_1124_2321;
11>  BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL TAG prod_ARCH_1124_2321 DELETE INPUT;
12>  BACKUP SPFILE;
13> }

released channel: ORA_DISK_1
allocated channel: CH1
channel CH1: SID=151 device type=DISK

Starting backup at 24-NOV-10
channel CH1: starting compressed incremental level 0 datafile backup set
channel CH1: specifying datafile(s) in backup set
input datafile file number=00002 name=/mount/u01/oracle/prod/data/undotbs1.dbf
input datafile file number=00001 name=/mount/u01/oracle/prod/data/system.dbf
input datafile file number=00003 name=/mount/u01/oracle/prod/data/sysaux.dbf
input datafile file number=00004 name=/mount/u01/oracle/prod/data/users01.dbf
channel CH1: starting piece 1 at 24-NOV-10
channel CH1: finished piece 1 at 24-NOV-10
piece handle=/mount/copy01/prod/oracle/prod/incr/prod_HOT_11242010_1_10 tag=prod_HOTINCR_1124_2321 comment=NONE
channel CH1: backup set complete, elapsed time: 00:02:35
channel CH1: starting compressed incremental level 0 datafile backup set
channel CH1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel CH1: starting piece 1 at 24-NOV-10
channel CH1: finished piece 1 at 24-NOV-10
piece handle=/mount/copy01/prod/oracle/prod/incr/prod_HOT_11242010_1_11 tag=prod_HOTINCR_1124_2321 comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-NOV-10

Starting backup at 24-NOV-10
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current control file in backup set
channel CH1: starting piece 1 at 24-NOV-10
channel CH1: finished piece 1 at 24-NOV-10
piece handle=/mount/copy01/prod/oracle/prod/control/prod_11_24_2010_735952466.ctl tag=prod_CONTROLFILE_1124_2321 comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-NOV-10

Starting backup at 24-NOV-10
current log archived
channel CH1: starting compressed archived log backup set
channel CH1: specifying archived log(s) in backup set
input archived log thread=1 sequence=250 RECID=487 STAMP=735952469
channel CH1: starting piece 1 at 24-NOV-10
channel CH1: finished piece 1 at 24-NOV-10
piece handle=/mount/copy01/prod/oracle/prod/incr/prod_HOT_11242010_1_13 tag=prod_ARCH_1124_2321 comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
channel CH1: deleting archived log(s)
archived log file name=/mount/arch01/oracle/DBADG/prod_250_1_735160286.dbf RECID=487 STAMP=735952469
Finished backup at 24-NOV-10

Starting backup at 24-NOV-10
channel CH1: starting full datafile backup set
channel CH1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel CH1: starting piece 1 at 24-NOV-10
channel CH1: finished piece 1 at 24-NOV-10
piece handle=/mount/copy01/prod/oracle/prod/incr/prod_HOT_11242010_1_14 tag=TAG20101124T231431 comment=NONE
channel CH1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-NOV-10
released channel: CH1

RMAN> exit
Recovery Manager complete.
Conclusión
RMAN es una herramienta que ha evolucionado a traves de las versiones de Oracle, y que hoy en dia es una manera recomendable de tener un punto de respaldo de nuestras BDs, asi como ayudarnos para cuando tenemos un bloque corrupto y hasta verificar si el respaldo sirve o no. Te recomiendo que pruebes RMAN lo antes posible y le saques todo el jugo que se pueda.

Respaldos con RMAN - Parte I

El dia de hoy me encontre un blog en donde mencionaba la mejor manera de hacer un respaldo, cuando lo empecé a leer , lo primero que menciona es crear varios scripts, uno para definir donde de encuentran nuestros datafiles, otro donde se encuentran nuestros controlfiles y otro que hace lo corre todo de modo serial, y me dejo pensando en que la persona que escribio este blog, nunca escucho de que Oracle tiene una herramienta llamada RMAN, que para lo que sirve es exactamente ahorrarnos estos pasos y a la vez brindarnos una seguridad de que nuestros respaldos sirven.
Esta entrada la dividi en varias partes, ya que aunque es una herramienta bastante sencilla de utilizar, es de gran importancia saber de que se compone, ya que eso es lo que nos va a permitir tener la seguridad de los respaldos que y puntos de recuperacion de nuestra base de datos.

Componentes Basicos
Aunque RMAN tiene mas componentes de los que voy a mencionar, los siguientes yo los consideraria como los minimos necesarios para poder tener una infraestructura segura para nuestros respaldos.
  • Cliente de RMAN
    El cliente de RMAN es la aplicacion de Oracle que maneja las operaciones de respaldos y recuperacion, este puede utilizar Oracle Net para conectarse a traves de la red, asi que puede encontrarse en un servidor distinto a la Base de Datos primaria.
  • Base de Datos Primaria (Target Database)
    Es la base de Datos que contiene los controlfiles,datafiles y en dado caso los archived redo logs que RMAN va a respaldar o restaurar.
  • Base de Datos de Catalogo y Esquema de Catalogo (Recovery and Schema Catalog Database)
    Este componente es la base de datos que contiene el catalogo de recuperacion, este catalogo contiene metadata que RMAN utiliza para respaldar y recuperar la base de datos primaria.
    Aunque este componente no es obligatorio, ya que podemos respaldar utilizando nada mas el controlfile,si diria que es una buena practica tenerlo en nuestro ambiente de respaldos y recuperacion.
  • Manejador de Medios (Media Manager)
    Para ponerlo de manera sencilla,este manejador es una aplicacion de un vendor para mandar nuestros respaldos a cinta, como por ejemplo Netbackup o Tivoli.
Canales de RMAN
Una de las cosas mas importantes a entender es que el cliente de RMAN no hace los respaldos, restauraciones o recuperaciones, si no lo que hace es asigna sesiones de servidor en la Base de Datos Primaria y las dirige para que estas hagan las operaciones correspondientes.
En Unix un canal es un proceso de servidor, en Windows es un thread dentro del servicio de la Base de Datos.
En la figura que sigue puedes ver un pequeño ejemplo de como funcionan estos, estos canales son los que cuando hagamos nuestros respaldos, podamos hacer un respaldo en paralelo.
Algo importante a saber, los dispositivos soportados por RMAN para respaldos son disco y SBT (System Backup to Tape), un SBT es manejado por un vendor y normalmente estan conformadas por las librerias de cinta y los drives de cinta.

Una vez que logramos comprender lo minimo necesario de la arquitectura de RMAN, en la siguiente entrada (Parte II) vamos a ver directamente como configurar nuestro ambiente de respaldo de RMAN, incluyendo la base de datos de catalogo y como hacer respaldos desde RMAN.

jueves, 11 de noviembre de 2010

Un Bloque corrupto (ORA-01578) y como repararlo con RMAN en 11g

Uno de los errores mas odiados por cualquier DBA es el ORA-01578, un bloque corrupto en nuestra base de datos, y por que de los mas odiados, por que si no tienes un buen respaldo de tus datos, significa tener perdida de datos y tratar de explicar eso a un cliente no es nada facil.
El buen sentido comun te dice que toda base de datos de produccion debe de tener un respaldo, y mil veces mejor si este es en la herramienta de Oracle RMAN, ya que poder recuperar un bloque sin tener que restaurar el datafile, o tener que restaurar tu respaldo a una BD temporal y de ahi tener que exportar/importar tus datos, es una muy buena manera de reducir nuestro MTTR (Mean Time To Recover).
Vamos a poner en practica como poder hacer esto con RMAN y 11g.
Previo a empezar este ejercicio, debemos de tener un hot backup de nuestra base de datos y este debe no debe de estar obsoleto.
Para poder hacer este ejercicio, necesitamos corromper un bloque, hago incapie en que esto se debe de hacer un ambiente de pruebas, no en produccion.
Vamos a buscar el bloque que corresponde a la tabla EMPLOYEES de nuestro esquema HR.
TESTDB >SELECT dbms_rowid.rowid_block_number(rowid) from employees where EMPLOYEE_ID=101;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
                                 205

Una vez que tenemos el bloque, vamos a utilizar el comando "dd" de Unix para corromper el bloque, sabiendo que la tabla de EMPLOYEES se encuentra en el datafile "users01.dbf"
dd if=/dev/zero of=users01.dbf bs=8192 conv=notrunc seek=205 count=1

Ya que corrompimos el bloque, vamos a validar nuestra base de datos con RMAN, y este comando nos va a mostrar que el datafile "users01.dbf" tiene un bloque corrupto.
rman target=/
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
.
.
.
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    FAILED 0              7123         12800           11188245359861
  File Name: /mount/u01/oracle/TESTDB/data/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              4683
  Index      0              332
  Other      1              662
Si checamos la vista V$DATABASE_BLOCK_CORRUPTION, vamos a ver que el bloque 205 del archivo 4 se encuentra corrupto
TESTDB >select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ---------------------------
         4        205          1                  0 ALL ZERO

RMAN  en 11g trae una nueva funcionalidad de mostrar que errores tiene nuestra base de datos, relacionados a recuperabilidad, asi como asesorarte que puedes hacer para recuperar tus datos y hasta poder aplicar la opcion que te da.
Con el comando "list failure" podemos ver que error tenemos asi como el detalle de este error.
RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
402        HIGH     OPEN      11-NOV-10     Datafile 4: '/mount/u01/oracle/TESTDB/data/users01.dbf' contains one or more corrupt blocks


RMAN> list failure 402 detail;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
402        HIGH     OPEN      11-NOV-10     Datafile 4: '/mount/u01/oracle/TESTDB/data/users01.dbf' contains one or more corrupt blocks
  Impact: Some objects in tablespace USERS might be unavailable
  List of child failures for parent failure ID 402
  Failure ID Priority Status    Time Detected Summary
  ---------- -------- --------- ------------- -------
  405        HIGH     OPEN      11-NOV-10     Block 205 in datafile 4: '/mount/u01/oracle/TESTDB/data/users01.dbf' is media corrupt
    Impact: Object EMPLOYEES owned by HR might be unavailable

Una vez que detectamos el error, podemos decirle a RMAN que nos asesore con el error detectado.
RMAN> advise failure 402;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
402        HIGH     OPEN      11-NOV-10     Datafile 4: '/mount/u01/oracle/TESTDB/data/users01.dbf' contains one or more corrupt blocks

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=95 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Perform block media recovery of block 205 in file 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /mount/dump01/oracle/TESTDB/diag/rdbms/TESTDB/TESTDB/hm/reco_1373000937.hm

Si queremos ver unicamente que recimendacion nos esta haciendo RMAN para este error, podemos utilizar el comando "repair failure preview", este solamente te dara la recomendacion, mas no ejecutara la reparacion.
RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /mount/dump01/oracle/TESTDB/diag/rdbms/TESTDB/TESTDB/hm/reco_2285570611.hm

contents of repair script:
   # block media recovery
   recover datafile 4 block 205;

Si estamos satisfechos con lo que nos recomienda Oracle, el comando "repair failure" hara la recuperacion de nuestros datos.
RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /mount/dump01/oracle/TESTDB/diag/rdbms/TESTDB/TESTDB/hm/reco_2285570611.hm

contents of repair script:
   # block media recovery
   recover datafile 4 block 205;

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

Starting recover at 11-NOV-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_11102010_1_11
channel ORA_DISK_1: piece handle=/mount/copy01/TESTDB/oracle/TESTDB/incr/TESTDB_HOT_11102010_1_11 tag=TESTDB_HOTINCR_1110_2321
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:25

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 11-NOV-10
repair failure complete

Ya que termino de ejecutarse el comando, podemos volver a verificar nuestra BD y veremos que ya no existe el bloque corrupto
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
.
.
.

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4    OK     0              7123         12800           11188245359861
  File Name: /mount/u01/oracle/TESTDB/data/users01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              4684
  Index      0              332
  Other      0              661


TESTDB >select * from V$DATABASE_BLOCK_CORRUPTION;


no rows selected

TESTDB >select FIRST_NAME,LAST_NAME from employees where EMPLOYEE_ID=101;

FIRST_NAME
------------------------------------------------------------
LAST_NAME
---------------------------------------------------------------------------
Neena
Kochhar

Conclusion
RMAN y 11g trae una gran funcion para poder apoyarnos en nuestra recuperacion de datos, de la misma manera el tener nuestros respaldos con RMAN nos permite poder recuperarnos de un bloque corrupto con un MTTR muy bajo, ya que no tenemos que hacer una recuperacion completa de nuestra base de datos para unicamente obtener los bloques perdidos.

lunes, 8 de noviembre de 2010

Identificar en donde estamos trabajando

A mi me gustaria saber que DBA no ha dado ENTER a un comando para que en el siguiente segundo darse cuenta que estaba en el ambiente incorrecto. Esto no solo te puede causar horas interminables de trabajo extra, puede ser hasta la causa de que pierdas un proyecto o tu trabajo.
Tenemos que entender que el trabajo de DBA es totalmente destructivo no constructivo, creo que el mejor ejemplo es al crear una simple vista:

create or replace view dummy_view as select * from all_objects;
Si dummy_view tenia otra definicion y corriste el comando anterior en una instancia distinta a donde querias, antes de 11g esto lo veremos despues con versiones, realmente estabas frito, tenias que ir a un respaldo para poder recuperar la definicion de esa vista.
Aqui te van algunas de mis recomendaciones cuando trabajes con tus sessiones y sobre todo para produccion:

PUTTY
La siguiente configuracion en Putty te va a dar una pantalla de Color Rojo, que facilmente puedes distinguir de una sesion de desarrollo o stage
En Windows --> Colours --> Default Foreground
Red ->255, GREEN ->0 , BLUE->0

Toad
En Toad, es un poco mas discreto, pero es lo mejor que puedes hacer, en la ventana que te aparece cuando vas a conectarte a una Sesion, busca la columna de "Color" y cambiala por el color "Rojo".

SQLPLUS
Lo mas sencillo que puedes hacer, es en el archivo $ORACLE_HOME/sqlplus/admin modificar el archivo glogin.sql y agregar la siguiente linea
SET SQLPROMPT '&_connect_identifier >'
esta linea te mostrara el nombre de la instancia en la que estas trabajando.

Conclusion
Como te mencionaba en nuestro trabajo destruimos mas que construimos, asi que mi mejores tips son:
  1. Estar totalmente seguro en que ambiente/instancia estamos trabajando
  2. Conectarte como "sysdba" nada mas para las tareas especificas que requiere ese rol.
  3. Tener un plan de rollback para cualquier trabajo que estemos haciendo.
  4. Si no estas seguro de que hacer, no lo hagas, pregunta.
  5. Cuando trabajes en produccion, no hagas multitareas, dedicate unica y exclusivamente a el trabajo en produccion.
  6. Y finalmente, no dejes que la presion te llegue, si lo requieres, tomate 2 minutos, levantate y respira profundo y continua tu trabajo.

Script para Extraer la Definicion de un Usuario y Mas!!!

Cuantas veces no te han pedido que extraigas la definicion de un usuario para luego crearlo en otra instancia o recrearlo en la mismas despues de varias tareas administrativas. Al principio lo que hacia era ir al diccionario de datos en donde veia como estaba definido el usuario, y luego a cada uno de las tablas que contenian los privlegios del usuario, de ahi le tenia que dar el formato adecuado para poder ejecutarlo.
Despues de un buen rato de hacerlo de esta manera, voilà, llegue a descubrir el paquete de DBMS_METADATA, este paquete lo que nos extraer , como dice su nombre, la definicion de objetos.

Con el siguiente script podras obtener la definicion de un usuario y sus privilegios

column metadata format a200 WORD_WRAPPED
set long 10000000
execute dbms_metadata.set_transform_param (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);

select dbms_metadata.get_ddl ('USER', username) metadata
from dba_users
where username in ('&&USERNAME');



select dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', grantee) metadata
from (select distinct grantee
from dba_role_privs
where default_role = 'YES'
intersect
select username
from dba_users
where username in ('&&USERNAME'));

select dbms_metadata.get_granted_ddl ('ROLE_GRANT', grantee) metadata
from (select distinct grantee
from dba_role_privs
intersect
select username
from dba_users
where username in ('&&USERNAME'));

select dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', grantee) metadata
from (select distinct grantee
from dba_sys_privs
intersect
select username
from dba_users
where username in ('&&USERNAME'));

select dbms_metadata.get_granted_ddl ('OBJECT_GRANT', grantee, 'COMPATIBLE','ORACLE','DDL',100000) metadata
from (select distinct grantee
from dba_tab_privs
intersect
select username
from dba_users
where username in ('&&USERNAME'));

Conclusion
No solo este paquete te sirve para un usuario, te sirve tambien para extraer definicion de cualquier objeto de nuestra base de datos, una vez que te sientas agusto con este paquete, vas a ver el gran beneficio que tiene.

domingo, 7 de noviembre de 2010

Script para facilitar el renombrado de datafiles al Clonar una DB en RMAN

Varios de nosotros tenemos que clonar varias veces al mes una o mas instancias. Algo que es muy tedioso en el aspecto es cambiar el nombre de los datafiles cuando no lo definimos esto en el init file. Este script es para facilitarte el renombrado de los datafiles al mount point donde van a residir. Algo a tomar en cuenta con este script, es que la DB ya tuvo que haber sido respaldada con RMAN.

SELECT    'set newname for datafile '
       || a.file#
       || ' to ''&aux_data_mnt'
       || SUBSTR (a.NAME, INSTR (a.NAME, '/', -1, 1) + 1)
       || ''';'
  FROM v$datafile a, v$tablespace b
 WHERE a.ts# = b.ts# and
   a.file# IN (SELECT DISTINCT file#
                              FROM v$backup_datafile)
UNION ALL
SELECT    'set newname for tempfile '
       || a.file#
       || ' to ''&aux_data_mnt'
       || SUBSTR (a.NAME, INSTR (a.NAME, '/', -1, 1) + 1)
       || ''';'
FROM v$tempfile a;



Una vez que obtuvimos los datafiles y tempfiles del script de arriba, podemos usar algo similar al template de abajo para poder clonar la instancia, reemplazando las partes del set new name, con el resultado del script

RUN
{
set until scn SCN_NUMBER;
ALLOCATE AUXILIARY CHANNEL CH1 TYPE DISK;
set newname for datafile 1 to '/data_location/datafile01.dbf';
set newname for datafile 2 to '/data_location/datafile02.dbf';
.
.
.
set newname for datafile nn to '/data_location/datafilenn.dbf';
set newname for tempfile 01 to '/data_location/tempfile01.dbf';
.
.
.
set newname for tempfile nn to '/data_location/tempfilenn.dbf';
duplicate target database to CLONED_DB open restricted
logfile
group 1 (
'/log_location/redo01g01.log',
'/log_location/redo02g01.log'
) size 100M,
group 2 (
'/log_location/redo01g02.log',
'/log_location/redo02g02.log'
) size 100M,
group 3 (
'/log_location/redo01g03.log',
'/log_location/redo02g03.log'
) size 100M
;
}

viernes, 5 de noviembre de 2010

Clonar un Oracle Home version 11.2.0.2 en Solaris 10

Una de las tareas que tenemos como DBAs cuando estamos haciendo un upgrade es asegurarte que a traves de tus etapas de pruebas (Dev-->QA-->Prod)  manejes los mismos binarios de Oracle. Una manera sencilla de hacer esto es instalar una version "Maestra" de los binarios y esa utilizarla para las demas instalaciones.

  1. Lo primero que debemos hacer es hacer una verificacion de que el Sistema Operativo cumpla con todos los prerequisitos.
    http://download.oracle.com/docs/cd/E11882_01/install.112/e17163/pre_install.htm
  2. Una vez que cumplimos los prerequisitos, y tenemos localizados el ORACLE_HOME y el ORACLE_BASE, creamos un response file,llamado db_11gOptions.rsp, con los componentes que vamos a instalar, basandonos en el que nos provee oracle.
    Este lo puedes encontrar en el directorio donde descomprimiste los binarios que bajaste de OTN, en la siguiente ruta.
    ?/database/response/db_install.rsp
  3. Una ves que modificaste el response file con los parametros necesarios y componentes a instalar, procedemos a instalarlo.
    ?/database/runInstaller -silent -noconfig -responseFile '?/database/response/db_11gOptions.rsp'
  4. Corre cualquier script que te mencione al final de la instalacion.
  5. Verifica que el ORACLE_HOME que acabas de instalar, fue de manera correcta, revisando el log  installActionsdate_time.log que normalmente se encuentra en el directorio /orainventory_location/logs
  6. Ya que verificamos que el ORACLE_HOME fue instalado correctamente, nos cambiamos al directorio donde fue instalado y vamos a hacer un tar del mismo en el directorio temp, o donde te sea conveniente.
    eg
    cd $ORACLE_HOME
    tar -cvf /tmp/copia_maestra.tar .
  7. Ya que tenemos el tar, lo copiamos al servidor donde lo vas a clonar.
  8. Una vez que creaste el directorio donde va a ser el ORACLE_HOME en el nuevo servidor, nos cambiamos a el y de ahi descomprimimos el tar que acabamos de copiar.
    eg
    cd /app/oracle/product/11.1.0_clone
    tar -xvf /tmp/source.tar
  9. Debemos de tener en cuenta que el servidor donde vamos a clonar los binarios, debe de cumplir los prerequisitos mencionados anteriormente.
  10. En el servidor donde vamos a clonar, definimos las variables ORACLE_HOME y ORACLE_BASE , asi como cualquier otra mencionada en los prerequisitos.
  11. Nos cambiamos al directorio $ORACLE_HOME/clone/bin y con el script de perl clone.pl vamos a clonar la base de datos.
    $ORACLE_HOME/perl/bin/perl clone.pl ORACLE_BASE="/app/oracle" ORACLE_HOME="/app/oracle/product/11.1.0_clone" ORACLE_HOME_NAME=11gR202v1
  12. Una vez que finaliza el script de perl, corre cualquier script que te mencione.
  13. Para verificar cualquier log correspondiente a este tipo de instalador, lo puedes encontrar en el siguiente archivo cloneActionstimestamp.log, que normalmente se encuentra en este directorio /orainventory_location/logs.

Conclusion
Esta manera de clonar unos binarios de Oracle, te puede ahorrar bastante minutos en tu instalacion a traves de varios servidores, asi como asegurarte que vas a utilizar la misma version asi como cualquier patch que tengas aplicado.

jueves, 4 de noviembre de 2010

Indices Invisibles

En este proceso de actualizacion en el que estoy , hemos detectado que varios queries que antes corrían con un costo relativamente bajo, ahora están corriendo con un costo, bastante alto, esto nos ha llevado a probar con Indices Invisibles, que es una nueva función de 11g.

Un índice invisible por default es, como su nombre lo menciona, invisible para el optimizador, esto te permite probar quitar o añadir un índice sin que afecte el actual plan de ejecución del query.
Con el esquema de HR de ejemplos de oracle vamos a probar como funciona:

1.-Vamos a Ver el plan de ejecución del siguiente query

select * from employees where employee_id=101;
TESTDB >EXPLAIN PLAN FOR
2 select * from employees where employee_id=101;

Explained.

TESTDB >SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 293722525

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

2 - access("EMPLOYEE_ID"=101)

14 rows selected.

2.-Como podemos ver, el query esta utilizando el índice "EMP_EMP_ID_PK" y tiene un acceso por "INDEX ROWID"

3.- Ahora vamos a convertir el índice de visible a invisible


TESTDB >alter index EMP_EMP_ID_PK invisible;

Index altered.

4.-Ya que convertimos el índice en invisible,vamos a volver a ver el plan de ejecución del query


TESTDB >EXPLAIN PLAN FOR
2 select * from employees where employee_id=101;

Explained.

TESTDB >SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1342275408

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

1 - filter("EMPLOYEE_ID"=101)

13 rows selected.

5.-Como podemos ver arriba, el optimizador ya no esta utilizando el índice "EMP_EMP_ID_PK" y tiene un acceso "FULL TABLE".
Podemos ver abajo que el índice todavía existe, solamente esta en modo invisible. Esto te permite dejar de utilizar el índice, sin tener que tirarlo, ya que existe la posibilidad de que recrearlo te tome tiempo y esfuerzo. Toma en cuenta que el índice todavía va a estar ocupando espacio en la base de datos.


TESTDB >select status from all_objects where object_name='EMP_EMP_ID_PK' and object_type='INDEX';

STATUS
---------------------
VALID


TESTDB >select visibility from user_indexes where index_name = 'EMP_EMP_ID_PK';

VISIBILITY
---------------------------
INVISIBLE

6.-De la misma manera, ni diciéndole al optimizador que utilice el índice, lo va a utilizar.


TESTDB >EXPLAIN PLAN FOR
2 select /*+ index(EMP_EMP_ID_PK) */ * from employees where employee_id=101;

Explained.

TESTDB >SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1342275408

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 69 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

1 - filter("EMPLOYEE_ID"=101)

13 rows selected.

7.- Lo que si podemos hacer es habilitar el parámetro optimizer_use_invisible_indexes para nuestra sesión para que el optimizador lo vuelva a utilizar.


TESTDB >alter session set optimizer_use_invisible_indexes = true;

Session altered.

TESTDB >EXPLAIN PLAN FOR
2 select * from employees where employee_id=101;

Explained.

TESTDB >SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 293722525

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 69 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------

2 - access("EMPLOYEE_ID"=101)

14 rows selected.

TESTDB >alter session set optimizer_use_invisible_indexes = true;

Session altered.

TESTDB >select visibility from user_indexes where index_name = 'EMP_EMP_ID_PK';

VISIBILITY
---------------------------
INVISIBLE

Conclusión
Como podemos ver, esto puede ser de gran utilidad cuando trabajamos con índices, ya que también desde que creamos el índice lo podemos hacer a este invisible para el optimizador, permitiéndonos crear un índice y utilizarlo hasta que deseemos hacerlo. Toma en cuenta de que aunque un índice sea invisible, este se va a comportar de la misma manera que un índice visible.

martes, 2 de noviembre de 2010

Capturar un SQL Tuning Set del Repositorio AWR

Hace unos dias me puse a la tarea de empezar a usar SQL Performance Analyzer (SPA) para una migracion de 10.2.0.4 a 11.2.0.2. Una de las tareas principales para poder utilizar esta funcion de Oracle, es capturar la carga de trabajo de la instancia de produccion y moverla a una instancia de purebas con SQL Tuning Sets. Aqui les dejo un ejemplo de como poder hacerlo, filtrando unicamente al usuario SCOTT del AWR y que el tiempo de ejecucion sea mayor a 600 segundos.
TEST_INST >set echo on
TEST_INST >
TEST_INST >alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss';
TEST_INST >


TEST_INST >
TEST_INST >-- Seleccionar el SNAP_ID de las ultimas 24hrs del AWR.
TEST_INST >pause

TEST_INST >
TEST_INST >set pagesize 9999
TEST_INST >COL snapdate FORMAT a30
TEST_INST >COL snap_id FORMAT 999999
TEST_INST >select s.snap_id snap_id
2 , to_char(s.end_interval_time,'dd Mon YYYY HH24:mi') snapdate
3 from dba_hist_snapshot s
4 where s.end_interval_time > sysdate-1
5 order by snapdate desc;

SNAP_ID SNAPDATE
------- ------------------------------
16092 02 Nov 2010 22:00
16091 02 Nov 2010 21:00
16090 02 Nov 2010 20:00
16089 02 Nov 2010 19:00
16088 02 Nov 2010 18:00
16087 02 Nov 2010 17:00
16086 02 Nov 2010 16:00
16085 02 Nov 2010 15:00
16084 02 Nov 2010 14:00
16083 02 Nov 2010 13:00
16082 02 Nov 2010 12:00
16081 02 Nov 2010 11:00
16080 02 Nov 2010 10:00
16079 02 Nov 2010 09:00
16078 02 Nov 2010 08:00
16077 02 Nov 2010 07:00
16076 02 Nov 2010 06:00
16075 02 Nov 2010 05:00
16074 02 Nov 2010 04:00
16073 02 Nov 2010 03:00
16072 02 Nov 2010 02:00
16071 02 Nov 2010 01:00
16070 02 Nov 2010 00:00
16069 01 Nov 2010 23:00

24 rows selected.

TEST_INST >
TEST_INST >pause

TEST_INST >--Teclea el nombre del SQL TUNING SET.
TEST_INST >--Teclea el rango de SNAPSHOTS que deseas capturar en el STS de la carga de trabajo del AWR.
TEST_INST >--Teclea el nombre del STS.
TEST_INST >
TEST_INST >
TEST_INST >pause

TEST_INST >
TEST_INST >declare
2 own VARCHAR2(30) := '&owner';
3 bid NUMBER := '&begin_snap';
4 eid NUMBER := '&end_snap';
5 stsname VARCHAR2(30) :='&stsname';
6 sts_cur dbms_sqltune.sqlset_cursor;
7 begin
8 dbms_sqltune.create_sqlset(sqlset_name => stsname, sqlset_owner =>
9 own);
10 open sts_cur for
11 select value(P) from table(dbms_sqltune.select_workload_repository(bid, eid,'UPPER(PARSING_SCHEMA_NAME) = ''SCOTT'' AND ELAPSED_TIME >= 6.0E8', null, null, null, null, 1, null, 'ALL')) P;
12 dbms_sqltune.load_sqlset(sqlset_name => stsname,
13 populate_cursor => sts_cur,
14 load_option => 'MERGE');
15 end;
16 /
Enter value for owner: SYSTEM
old 2: own VARCHAR2(30) := '&owner';
new 2: own VARCHAR2(30) := 'SYSTEM';
Enter value for begin_snap: 16069
old 3: bid NUMBER := '&begin_snap';
new 3: bid NUMBER := '16069';
Enter value for end_snap: 16092
old 4: eid NUMBER := '&end_snap';
new 4: eid NUMBER := '16092';
Enter value for stsname: SCOTT_TEST
old 5: stsname VARCHAR2(30) :='&stsname';
new 5: stsname VARCHAR2(30) :='SCOTT_TEST';

PL/SQL procedure successfully completed.

TEST_INST >
TEST_INST >--Verifica que el STS existe.
TEST_INST >
TEST_INST >pause

TEST_INST >select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME
--------------------------------------------------------------------------------
OWNER
--------------------------------------------------------------------------------
CREATED STATEMENT_COUNT
--------------- ---------------
SCOTT_TEST
SYSTEM
02-NOV-10 24