Copiar una base de datos.

Últimamente he tenido la necesidad de copiar bases de datos.

La petición textual era la creación de una base de datos igual que otra, pero con otro nombre. En algunos casos se trataba de replicar el entorno de producción para usarlo como test. En otros, simplemente, testear que el backup permitía recuperar la base de datos sobre otra máquina y dejar esa base de datos operativa como entorno auxiliar.

Cuando la base de datos puede conservar el mismo nombre y mismo SID, es tan fácil como copiar datafiles, redolog, controlfiles y archivos ORA sobre los mismos directorios originales en una máquina con un servidor Oracle instalado*.
* Por supuesto, misma plataforma O.S., versión y release de Oracle.

No obstante, en los casos en los que el nombre debe ser diferente, hay que ser muy preciso con los pasos a seguir. Incluyo dos pasos previos como opcionales por si son de utilidad.

En este ejemplo se asume que disponemos de la posibilidad de hacer un backup en frío de la base de datos.

Pasos preliminares (recomendado):

  - Backup frío, backup caliente, export, Transport Tablespaces, backup del backup, backup de todo lo posible.
  - Reducción de tamaño de los datafiles. (Tom Kyte tiene un fabuloso script para ello). Y otra vez backup.

Pasos a seguir:

  1.- Backup en frío de la BBDD original
  2.- Generación del pfile para la nueva BBDD
  3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...
  4.- Definir el nuevo ORACLE_SID
  5.- Crear el nuevo servicio.
  6.- Creación del fichero de passwords (sólo si el pfile tiene REMOTE_LOGIN_PASSWORDFILE=exclusive)
  7.- Conexión a sqlplus como SYSDBA
  8.- Arranque de la instancia y creación del fichero de parámetros SPFILE
  9.- Backup del controlfile de la BBDD original a traza
  10.- Recreación del controlfile con la cláusula SET NAME.
  11.- Abrir la base de datos con modo OPEN RESETLOGS.




1.- Backup en frío de la BBDD original.
    Restauración del backup sobre nueva ubicación.

-- Ejecutar el resultado de la siguiente sentencia como script.
-- NOTA: cuidado con los nombres duplicados de fichero.
-------------------------------------------------------------
select 'shutdown immediate;' from dual
union all
select 'host copy '||name||' &&directorio_destino' from v$controlfile
union all
select 'host copy '||member||' &directorio_destino' from v$logfile
union all
select 'host copy '||name||' &directorio_destino' from v$datafile
union all
select 'startup' from dual;

-- copia de los ficheros a los directorios destino
---------------------------------------------------



2.- Generación del pfile para la nueva base de datos

SQL> create pfile='?\admin\sid\pfile\inittest.ora' from spfile;

Archivo creado.



3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...



4.- Definir el nuevo ORACLE_SID

    c:\>set ORACLE_SID=test



5.- Crear el nuevo servicio.

    c:\>oradim -NEW -SRVC OracleServicetest  -startmode auto



6.- Creación del fichero de passwords

    c:\>orapwd file=C:\orant\ora92\database\PWDtest.ora password=xxxxxxxxx



7.- Conexión a sqlplus como SYSDBA

    C:\>sqlplus

    SQL*Plus: Release 9.2.0.6.0 - Production on Vie Jul 29 16:41:22 2005

    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

    Introduzca el nombre de usuario: sys/xxxxxxxxxx as sysdba
    Conectado a una instancia inactiva.



8.- Arranque de la instancia y creación del fichero de parámetros SPFILE

    SQL> STARTUP NOMOUNT PFILE='C:\DIRECTORIO_DESTINO\inittest.ora'
    Instancia ORACLE iniciada.

    Total System Global Area 1074866044 bytes
    Fixed Size                   456572 bytes
    Variable Size             905969664 bytes
    Database Buffers          167772160 bytes
    Redo Buffers                 667648 bytes
    SQL> create spfile from pfile='C:\DIRECTORIO_DESTINO\inittest.ora';

    Archivo creado.



9.- Backup del controlfile de PROD a trace.

    SQL> alter database backup controlfile to trace;

    Base de datos modificada.



10.- A partir de la traza del fichero de control.
     Recreación del controlfile con el SET NAME al nuevo nombre.

     CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS  NOARCHIVELOG REUSE
        MAXLOGFILES 50
        MAXLOGMEMBERS 5
        MAXDATAFILES 133
        MAXINSTANCES 1
        MAXLOGHISTORY 453
     LOGFILE
       GROUP 2 'C:\DATA\TEST\REDO\REDO02.LOG'  SIZE 100M,
       GROUP 3 'C:\DATA\TEST\REDO\REDO03.LOG'  SIZE 100M,
       GROUP 4 'C:\DATA\TEST\REDO\REDO04.LOG'  SIZE 150M,
       GROUP 5 'C:\DATA\TEST\REDO\REDO05.LOG'  SIZE 150M,
       GROUP 6 'C:\DATA\TEST\REDO\REDO06.LOG'  SIZE 200M
      -- STANDBY LOGFILE
     DATAFILE
        (... ficheros...)
        'C:\DATA\TEST\SYSTEM\SYSTEM01.DBF',
        'C:\DATA\TEST\DATA\DATA01.DBF'
     CHARACTER SET WE8MSWIN1252
     ;

     Archivo de control creado.



11.- Abrir la base de datos con modo OPEN RESETLOGS.

     SQL> alter database open resetlogs;

     Base de datos modificada.



12.- Verificación.

     SQL> select instance_name from v$instance;

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

     SQL> select name from v$database;

     NAME
     ---------
     TEST

     SQL> select status from v$thread;

     STATUS
     ------
     OPEN



12.- Shutdown y Open de la base de datos.

     SQL> shutdown immediate;
     Base de datos cerrada.
     Base de datos desmontada.
     Instancia ORACLE cerrada.

     SQL> startup
     Instancia ORACLE iniciada.

     Total System Global Area 1074866044 bytes
     Fixed Size                   456572 bytes
     Variable Size             905969664 bytes
     Database Buffers          167772160 bytes
     Redo Buffers                 667648 bytes
     Base de datos montada.
     Base de datos abierta.

NOT IN y NOT EXISTS no son lo mismo.

Aunque no lo parezca, NOT IN y NOT EXISTS no son cláusulas equivalentes. IN y EXISTS devuelven el mismo conjunto de resultados, pero su semántica es completamente distinta. Aun así, los programadores la utilizan indistintamente ya que se han acostumbrado a trabajar con una de las dos y realizan de igual modo la negación.

Por un lado, IN solicita aquellos registros cuyo valor exista dentro de un conjunto de valores. Por otro lado, la cláusula EXISTS incluye el registro si la subconsulta siguente devuelve un valor, sea cual sea, incluído NULL.

De este modo, la sintaxis de IN y EXISTS podrían aprobar los mismos registros si la consulta está bien construída, aunque los planes de ejecución sean completamente distintos.

NOT IN y NOT EXISTS no son sinónimos. El valor NULL determina la diferencia.

Dada la siguiente consulta con NOT IN:

SQL> select count(*) from clientes
  2  where ciu_id_ciudad not in (select ciu_id_ciudad from agencias);

  COUNT(*)
----------
         0

Y su equivalente con NOT EXISTS

SQL> select count(*) from clientes c
  2  where not exists (select a.ciu_id_ciudad from agencias a where c.ciu_id_ciudad=a.ciu_id_ciudad);

  COUNT(*)
----------
         0

Los resultados son idénticos: Efectivamente, no existe ningún cliente que viva en una ciudad donde no haya una agencia. En este caso, hay clientes y agencias en todas las ciudades de la base de datos.

No obstante, si las tablas contienen elementos nulos, las dos operaciones devolverán conjuntos de resultados distintos.

NOT IN y NOT EXISTS no son la misma cláusula con sintaxis distinta.

SQL> update clientes
  2  set ciu_id_ciudad=null
  3  where round(rownum/1000)=0;

499 filas actualizadas.

SQL> select count(*) from clientes
  2  where ciu_id_ciudad not in (select ciu_id_ciudad
  3                              from agencias);

  COUNT(*)
----------
         0

SQL> select count(*) from clientes c
  2  where not exists (select a.ciu_id_ciudad
  3   from agencias a where c.ciu_id_ciudad=a.ciu_id_ciudad);

  COUNT(*)
----------
       499

Así, NOT IN se basa en la consulta de todos los valores respecto un valor concreto para determinar que no está incluido en la lista. Los valores nulos se computan como FALSO y no incluyen el elemento en la selección.

NOT EXISTS se basa en la consulta de un valor y su no existencia en un subconjunto de registros. Los valores nulos se computan como VERDADERO, e incluyen el elemento en la selección.

PL/SQL y ejecuciones en host.

Por un motivo de seguridad, desde PL/SQL no es posible ejecutar llamadas al host. En principio, ningún módulo programado en PL/SQL debería ejecutar nada en sistema operativo. No obstante, seguro que cualquiera es capaz de recordar alguna situación concreta en la que poder invocar un "copy" o quizás ejecutar algún fichero .exe hubiera sido de gran ayuda.

Existe una forma de dar un rodeo a este escenario.

Oracle permite la llamada a procedimientos externos mediante un servicio extproc que debe configurarse en el fichero de alias de servicios "tnsnames.ora" para el cliente, y en el de configuración del listener "listener.ora" en el lado del servidor.

Los ficheros tendrán este aspecto.

Nota: puede haber cambios por la versión del servidor. El ejemplo se implementó sobre Oracle 8.1.7.

Fichero LISTENER.ORA
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = extproc_agent)
      (ORACLE_HOME = E:\oracle\ora92)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ANY")
    )
  )


Fichero TNSNAMES.ORA
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = extproc_agent)
    )
  )

Con esta parametrización del listener, y la definición de un servicio para llamadas a 'extproc', Oracle es capaz de invocar desde PL/SQL librerías externas programadas en C o PASCAL.

Pasos:

      1.- Descarga de un compilador de C++. Este ejemplo utiliza C++Compiler de Borland.
      2.- Creación de un programa en C que ejecuta en S.O. un comando dado.
      3.- Compilación del programa e implementación de las librerías.
      4.- Desde Oracle, creación del objeto LIBRARY (biblioteca).
      5.- Creación de un procedimiento PL/SQL que invoque a una función de esa biblioteca.



Creación del programa shell.c
-----------------------------
   #include <windows.h>    
   #include <stdio.h>    
   #include <stdlib.h>      

   void __declspec(dllexport) sh(char *);    
   void sh(char *cmd)    
   {    
       system(cmd);    
   }


Compilación del código C y creación de las librerías  .lib y .dll
-----------------------------------------------------------------
  bcc32 -WD shell.c    
  implib shell.lib shell.dll    



Creación del objeto LIBRARY, en la consola de SQL.
--------------------------------------------------
SQL> create library shell_lib is 'C:\Borland\BCC55\shell.dll';
  2  /

Biblioteca creada.


De modo que ya es posible crear un procedimiento PL/SQL que referencie externamente a esa librería. Ésta se encargará de ejecutar en host el parámetro que le incluyamos. Por ejemplo, un "copy".
--------------------------------------------------------------------------------------------------
SQL> create or replace procedure shell (
  2         cmd IN varchar2)
  3  as external
  4       library shell_lib
  5       name "_sh"
  6       language C
  7       parameters (cmd string);
  8  /

Procedimiento creado.

SQL>


El testeo podrá hacerse con un simple 'exec' desde SQL*Plus, ya que equivale a "BEGIN shell('parametro'); END;"
---------------------------------------------------------------------------------------------------------------
SQL> exec shell('copy c:\test\*.* c:\destino\');

Procedimiento PL/SQL terminado correctamente.


Funciona.

Búsquedas fuzzy, índices context y gestión documental con Oracle. Parte I.

(Continua en parte II).

En muchas ocasiones he podido ver como el uso de las funcionalidades de gestión documental de Oracle era un misterio para los directores de proyecto, que solían confiar para estas tareas en otras herramientas documentales, analistas y programadores.

Éstos últimos, tenían serios problemas en realizar búsquedas por contenido sobre campos almacenados como CLOB, ya que en la mayoría de casos utilizaban la cláusula LIKE con comodines a ambos lados, lo cual impedía el uso por parte del servidor de cualquier índice asociado a esa columna. Otras veces la solución implementada era aun peor (lowers, uppers, substr, instr... ¡sobre un campo que puede llegar a tener hasta 4Gb de capacidad!). Terrible.

Oracle dispone de una potencia de gestión documental principalmente basada en el uso de índices domain. Se trata de índices por palabras (también conocidos como índices CONTEXT) que permiten a Oracle, en una columna de CLOBs o BFILEs, tener una relación del contenido de estos objetos.

El siguiente ejemplo es un pequeño ejercicio basado en el uso de las funcionalidades documentales del servidor Oracle9i.

Pasos:

       1.- Creación de una tabla con columna BFILE
       2.- Creación de un DIRECTORY oracle para vincular los documentos Word.
       3.- Concesión de privilegios sobre el DIRECTORY al usuario CTXSYS, propietario de Oracle Context.
       4.- Inserción de las filas en la tabla DOCUMENTOS.
       5.- Indexación de la columna BFILE.
       6.- Ejemplos de consultas.




SQL> -- Inicialmente creo una tabla DOCUMENTOS donde vincularé los documentos en formato word
SQL> -- para su posterior indexación

SQL> create table documentos(
  2        id number constraint pk_documentos primary key,
  3        tipo char(4),
  4        titulo varchar2(60) not null,
  5        documento bfile);

Tabla creada.

SQL> -- Es preciso crear un objeto DIRECTORY, vinculado al directorio de sistema operativo
SQL> -- donde se encuentran los documentos word.

SQL> create or replace directory documentos_word as 'c:\documentos';

Directorio creado.

SQL> grant read on directory documentos_word to CTXSYS;

Concesión terminada correctamente.

SQL> host dir c:\documentos
El volumen de la unidad C no tiene etiqueta.
El número de serie del volumen es: 2416-B169

Directorio de c:\documentos

09/05/2005  09:31       <DIR>          .
09/05/2005  09:31       <DIR>          ..
25/04/2005  13:21              271.360 EntornosNoProd_Permisos extendidos.doc
25/04/2005  13:21               31.232 normas_dba.doc
25/04/2005  13:21              379.904 creacion_entorno_bbdd.doc
25/04/2005  13:21              252.928 CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc
               4 archivos        935.424 bytes
               2 dirs   4.859.199.488 bytes libres


SQL> -- Inserción de los cuatro documentos sobre la tabla
SQL> -- y creación del índice CONTEXT (la cláusula es INDEXTYPE IS ctxsys.context)
SQL> -----------------------------------------------------------------------------------
SQL> insert into documentos
  2  values (1,'WORD','Entornos no producción. Permisos extendidos.',
  3          BFILENAME('DOCUMENTOS_WORD','EntornosNoProd_Permisos extendidos.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (2,'WORD','Normativa de DBA.',
  3          BFILENAME('DOCUMENTOS_WORD','normas_dba.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (3,'WORD','Creación de un entorno de BBDD.',
  3          BFILENAME('DOCUMENTOS_WORD','creacion_entorno_bbdd.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (4,'WORD','Creación de instancia Oracle en UNIX.',
  3          BFILENAME('DOCUMENTOS_WORD','CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc'));

1 fila creada.

SQL> CREATE INDEX idx_documentos_doc ON documentos(DOCUMENTO) INDEXTYPE IS ctxsys.context;

Índice creado.

-- Las búsquedas CONTEXT sobre índices domain se realizan con la cláusula CONTAINS
-- más documentación al respecto en   http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm )
------------------------------------------------------------

-- documentos con la palabra 'tablespace' cerca de 'storage'
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'tablespace near storage') > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Normativa de DBA.

-- documentos con una palabra parecida a 'locally' en un 70%
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'fuzzy(locally, 70, 6, weight)', 1) > 0;

TITULO
------------------------------------------------------------
Creación de un entorno de BBDD.

-- documentos con una palabra parecida a 'locally' en un 60%
-- nota: la palabra LOCAL aparece en estos dos nuevos documentos
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'fuzzy(locally, 60, 6, weight)', 1) > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Creación de un entorno de BBDD.
Entornos no producción. Permisos extendidos.

-- documentos que hablen sobre 'listener'
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'about(listener)') > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Normativa de DBA.

-- relevancia de documentos que hablan de 'listener'
-- nota: en 'Normativa de DBA', efectivamente, se habla bastante menos sobre el listener.
SQL> select CONTAINS(documento, 'about(listener)')||'%' relevancia, titulo
  2  from documentos
  3  where CONTAINS(documento, 'about(listener)') > 0;

RELEVANCIA TITULO
---------- ------------------------------------------------------------
78%        Creación de instancia Oracle en UNIX.
20%        Normativa de DBA.


Más información en http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/csql.htm#21732

Uso de cursores y bulk insert.

Uno de los muchos mitos en oracle es que el uso de cursores perjudica seriamente el rendimiento. Incluso he visto algunos proyectos en los que se había prohibido el uso de esta técnica.

El impacto sobre el rendimiento es cierto en parte, ya que el servidor trata individualmente cada registro y, por tanto, esto debería implementarse únicamente cuando fuera estrictamente necesario (sql dinámico, iteraciones dentro del fetch, etc.) utilizando, siempre que sea posible, SQL estándar.

No obstante, hay situaciones en las que no es posible el uso de SQL convencional. Por ejemplo, cuando en una inserción es preciso controlar los errores e insertarlos en otra tabla. Ante este caso, los procedimientos ETL filtran previamente las filas, las mueven a otra tabla, y cuando las filas ya están totalmente "limpias" de errores, entonces realizan
INSERT INTO tabla SELECT * ...

Oracle, a partir de la versión 9i, dispone de la funcionalidad de BULK sql, es decir, tratamiento de

Quien soy.

Hola,

Mi nombre es Javier Morales. Desde el año 1999 estoy enteramente dedicado al desarrollo y la administración de bases de datos Oracle. En el junio de 2003 me certifiqué en Administración de Oracle8i y en la actualidad trabajo como consultor, administrador y formador senior.

Tengo la opinión personal de que el conocimiento es un valor que aumenta cuanto más se comparte. También pienso que existe mucho desconocimiento en plataformas Oracle, debido a que muchos expertos no comparten su conocimiento. Quizás alguien podría cuestionarles al respecto.

Mi intención al abrir este blog es compartir conclusiones, tests, comparativas, artículos, etc. con la idea no sólo de publicar parte de mi trabajo, sino también de poder contrastar opiniones con otros usuarios de Oracle, bien administradores, desarrolladores o analistas.

Espero que estos artículos puedan servir de ayuda para los lectores, y que éste sea un inicio a una nueva experiencia personal.