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.

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.

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.

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
;
}

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.

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.