Clonar una base de datos Oracle.

Seguro que a todos los que trabajais con Oracle (o con Sap y como base de datos Oracle como es mi caso), os ha surgido la necesidad de replicar una base de datos productiva (o de un entorno de pruebas), en otro sistema. En mi caso, trabajando con Sap, regularmente realizo una copia de la base de datos real en el sistema de desarrollo (integración), para que la pruebas de los cambios, desarrollos o parametrizaciones sean lo mas completas posibles (y lo mas fáciles posible de cara al usuario si dispone de los datos «reales» más actualizados).

Acabo de terminar la instalación de un nuevo entorno de desarrollo Sap (por un cambio de hardware), en el que hemos aprovechado para hacer una replica del sistema productivo a nivel de base de datos. Os voy a explicar los pasos seguidos por si alguno de vosotros tiene la misma necesidad, aunque seguro que hay otras formas de hacerlo (export de base de datos, utilizando RMAN, etc). En mi caso, he partido de un backup Online (aunque es recomendable realizarlo con uno Offline), y he copiado una base de datos que se llama MG1 en otra en una máquina distinta que se llama DE1. El procedimiento que os voy a explicar esta validado con Oracle 9i.

1) Realización del backup de la base de datos origen: como os he indicado, he realizado un backup online utilizando el software de backup Veritas Netbackup, que tiene sus herramientas especificas para trabajar con Sap y Oracle, pero podriamos igualmente haberlo realizado con un script sql para poner los tablespaces en modo backup y copiar los datafiles correspondientes a la nueva ubicación. Una vez terminada la copia, se vuelven a poner los tablespaces en modo normal. También podriamos haber realizado un backup offline con la base de datos parada (aunque esto no es siempre posible, pues podemos estar en un sistema cuya ventana de backup sea muy pequeña y no se pueda hacer de esta manera).

Los scripts sql para poner los tablespaces en modo backup(al comenzar este), asi como para ponerlos en modo normal(a la finalización del backup), los podeis generar con las sentencias SQL que os detallo a continuación:

set lines 999 pages 999
set verify off
set feedback off
set heading off

spool begin_backup.sql

select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb
from    dba_tablespaces
where   contents != 'TEMPORARY'
order by tablespace_name
/
spool off

spool end_backup.sql

select 'alter tablespace ' || tablespace_name || ' end backup;' tseb
from    dba_tablespaces
where   contents != 'TEMPORARY'
order by tablespace_name
/
spool off

Al ejecutar la sentencia se habrán generado dos ficheros: begin_backup.sql y end_backup.sql, cada uno con el cometido indicado anteriormente.

2) Preparación de la copia: en mi caso, en el sistema destino ya tengo instalado Oracle y creada toda la estructura de carpetas para las ubicaciones de los datafiles. En caso contrario, habrá que realizar la instalación de Oracle y crear las carpetas correspondientes donde se van a ubicar los ficheros de datos. Una vez preparado todo en el sistema destino, vamos a realizar un par de tareas mas de preparación.

• Ajuste del fichero de parametros de oracle: el fichero de parametros de oracle es imprescindible para poder arrancar la base de datos en el sistema destino (fichero init<SID>.ora, donde <SID> es el nombre de la base de datos). Habremos de ajustar el contenido de este fichero para que corresponda a los directorios del nuevo sistema (destino de redologs, rollback segments en el caso de que los utilicemos, etc), pero siempre tomando como modelo el mismo fichero del sistema origen. Igualmente, puede que tengamos que ajustar parametros de este fichero conforme a las características físicas de la maquina destino (memoria, procesadores, etc).

• Creación de la sentencia SQL para recrear los ficheros de control:  ejecutaremos la sentencia SQL en el sistema origen: alter database backup controlfile to trace as ‘/tmp/crea_bd.sql’; Con la ejecución de esta sentencia, hemos creado un fichero con sentencias sql, que nos van a permitir recrear los ficheros de control en el sistema destino una vez hayamos recuperado el backup. En mi ejemplo, el contenido del fichero creado es el siguiente:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "MG1" RESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
 MAXLOGFILES 16
 MAXLOGMEMBERS 3
 MAXDATAFILES 1022
 MAXINSTANCES 50
 MAXLOGHISTORY 5445
LOGFILE
 GROUP 11 (
 '/oracle/MG1/mirrlogA/log_g11m2.dbf',
 '/oracle/MG1/origlogA/log_g11m1.dbf'
 ) SIZE 20M,
 GROUP 12 (
 '/oracle/MG1/mirrlogB/log_g12m2.dbf',
 '/oracle/MG1/origlogB/log_g12m1.dbf'
 ) SIZE 20M,
 GROUP 13 (
 '/oracle/MG1/mirrlogA/log_g13m2.dbf',
 '/oracle/MG1/origlogA/log_g13m1.dbf'
 ) SIZE 20M,
 GROUP 14 (
 '/oracle/MG1/mirrlogB/log_g14m2.dbf',
 '/oracle/MG1/origlogB/log_g14m1.dbf'
 ) SIZE 20M
-- STANDBY LOGFILE

DATAFILE
 '/oracle/MG1/sapdata1/system_1/system.data1',
 '/oracle/MG1/sapdata5/btabd_1/btabd.data1',
 '/oracle/MG1/sapdata2/btabi_1/btabi.data1',
 '/oracle/MG1/sapdata6/clud_1/clud.data1',

………………………..

CHARACTER SET WE8DEC
;

Habremos de realizar algunos cambios en el scritpt SQL contenido en este fichero para poder utilizarlo de forma correcta en el sistema destino:

• Nombre de la base de datos: el nuevo nombre de la base de datos va a ser DE1. Para ello, cambiaremos la parte inicial del script, sustituyendo MG1 por DE1. Igualmente, cambiaremos el valor REUSE por SET. La sintaxis quedaría como sigue:

CREATE CONTROLFILE SET DATABASE "DE1" RESETLOGS  ARCHIVELOG..........

• Ubicación de ficheros de logs y de datafiles: todas las rutas de los logs y de los datafiles que aparecen en el fichero han de ser modificadas para que correspondan con las ubicaciones reales que van a tener en el sistema destino. En mi caso, voy a cambiar la carpeta /oracle/MG1 por /oracle/DE1, que es la ubicación en mi sistema de todos esos elementos.
• Resto de sentencias SQL en el fichero: todas las sentencias SQL que hay a partir del valor CHARACTER SET … las borramos, pues las lanzaremos manualmente una vez recreados los controlfile (borrar RECOVER DATABASE…. y ALTER DATABASE OPEN RESETLOGS).

3) Restauración de ficheros de datos en el sistema destino: realizamos una recuperación de la copia de seguridad en las ubicaciones correspondientes del sistema destino. Las localizaciones de los datafiles deberan de coincidir con las que hayamos descrito en el script crea_bd.sql (tal y como hemos visto en el punto anterior), pues al lanzarlo, debera de encontrar todos los ficheros para poder realizar correctamente el proceso. En el caso de que hayamos realizado un backup offline, no hará falta traerse los ficheros de redologs del sistema origen, pero si en el caso de un backup online. Esto es necesario para dejar la base de datos en un punto consistente, pues durante el backup, todos los cambios que se producen en los tablespaces se van quedando registrados en los redologs. Es una forma de asegurar la consistencia de la base de datos tras el proceso  de recuperación.

4) Proceso final: recreación de control file y arranque de la base de datos. Una vez ha terminada la recuperación, vamos a realizar la parte final del proceso, y las más crítica. Para ello, ya ubicados en el sistema destino, arrancaremos el listener (puede que también haya que realizar algun ajuste en el para adaptarlo al nombre de la nueva base de datos), y nos conectaremos a la base de datos con sqlplus. La base de datos no esta arrancada, y ejecutaremos el script crea_bd.sql que hemos creado y ajustado anteriormente:

tm-des:orade1 1> sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 22 16:00:10 2010

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

Enter user-name: / as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.8.0 - Production

SQL> @/tmp/crea_bd.sql

La sentencia arranca la base de datos en modo NOMOUNT y realiza la recreación de los ficheros de control (las ubicaciones de estos ficheros estan definidas en el fichero init<SID>.ora, en el parametro control_files). Durante este proceso, se verifica que todos los ficheros existan fisicamente. En el caso de presentarse algun error y se pare el proceso (no existe alguna carpeta o los ficheros estan en una ubicación distinta), corregiremos los errores y volveremos a lanzar el proceso.

Una vez concluida la creación de los ficheros de control, arrancaremos la base de datos. La forma variara según si partimos de un backup offline o online. Por ejemplo, para el backup offline, que es un backup consistente pues se ha parado toda la actividad de la base de datos cuando se realizo, ejecutaremos la siguiente sentencia sql:

ALTER DATABASE OPEN RESETLOGS;

En el caso de haber realizado un backup online, pueden variar las sentencias a ejecutar. En mi caso, quiero dejar la base de datos consistente en un determinado momento del tiempo. Para ello, ejecutare las siguientes sentencias:

RECOVER DATABASE UNTIL TIME '2010-06-10:01:00:00' USING BACKUP CONTROLFILE;
ALTER DATABASE OPEN RESETLOGS;

He recuperado la base de datos a un momento determinado del tiempo (la 1 de la madrugada del 10 de junio de 2010). Para el proceso, ha sido necesario tener en el directorio de redologs los logs archivados necesarios (provenientes del sistema origen), para que su contenido sea aplicado sobre los tablespaces y llegar de forma coherente al punto del tiempo indicado.

En este momento, la base de datos ya esta recuperada y abierta. Como paso final, ejecutaremos la siguiente sentencia para cambiar el global database name (podemos ver el valor actual ejecutando la sentencia select * from global_name;):

ALTER DATABASE RENAME GLOBAL_NAME TO DE1.world

En mi caso, arranco además Sap y realizo una serie de acciones para dejar el sistema preparado para trabajar con el. En el caso de que estemos solo con una base de datos de pruebas en cualquier otro tipo de instalación, ya tendriamos los datos del sistema origen online y disponibles para poder realizar cualquier operativa sobre ellos. También puede ser una forma útil de replicar un sistema de Business Intelligence para sistema de test o de formación.

Os dejo algunos links donde podeis ampliar información de la forma de realizar todos los procesos descritos, pues mi explicación ha sido bastante resumida y según la forma de realizar el proceso, puede que tengaís que tener muchas mas cosas en cuenta:

Oracle Dba Quick Guide.

Sap System Copy: para el caso de que esteis trabajando con Sap (se explican aspectos adicionales en la copia homogenea de un sistema a otro).

15.4.1. Particionado de tablas en Oracle.

En una entrada anterior del blog vimos los conceptos básicos del particionado de tablas y como se podian llevar a la práctica utilizando MySql. Incluso hicimos una comparativa de tiempos de respuesta con una tabla de 1 millón de registros con y sin particionado. Vamos a ver ahora como implementa Oracle el particionado y algunos ejemplos prácticos de creación de tablas particionadas. Como ya vimos, el particionado es una técnica de optimización que pretende mejorar los tiempos de respuesta de las consultas, y que puede ser especialmente útil en un sistema DW donde las tablas de hechos pueden ser muy grandes.

Tipos de Particionado en Oracle

El particionado fue introducido por primera vez en la versión 8 de Oracle, como una nueva característica DW para la gestión de grandes cantidades de información, y para facilitar la tarea de los administradores de bases de datos. Dependiendo de la versión de Oracle en la que estemos, tenemos diferentes tipos de particionado disponibles:

• Oracle 8.0: particionado Range.
  
• Oracle 8i: además del particionado Range se añaden los tipos Hash y Composite.
• Oracle 9iR2/10g: se amplian con el tipo List y se permiten nuevas combinaciones de tipos en el particionado Composite.
• Oracle 11g: se introducen las columnas virtuales para particionar(que no existen fisicamente en la tabla), así como el particionado de Sistema (donde podemos gestionar directamente en que partición de la tabla se insertan los registros) y el particionado por Intervalos.

Particionado de Tablas en Oracle

Basicamente, el particionado se realiza utilizando una clave de particionado (partitioning key), que determina en que partición de las existentes en la tabla van a residir los datos que se insertan. Oracle también permite realizar el particionado de indices y de tablas organizadas por indices. Cada partición ademas puede tener sus propias propiedades de almacenamiento. Las tablas particionadas aparecen en el sistema como una única tabla, realizando el sistema la gestión automatica de lectura y escritura en cada una de las particiones (excepto  para el caso de la partición de Sistema introducida en la versión 11g). La definición de las particiones se indica en la sentencia de creación de las tablas, con la sintaxis oportuna para cada uno de los tipos.

• Particionado Range: la clave de particionado viene determinada por un rango de valores, que determina la partición donde se almacenara un valor.
• Particionado Hash: la clave de particionado es una función hash, aplicada sobre una columna, que tiene como objetivo realizar una distribución equitativa de los registros sobre las diferentes particiones. Es útil para particionar tablas donde no hay unos criterios de particionado claros, pero en la que se quiere mejorar el rendimiento.
• Particionado List: la clave de particionado es una lista de valores, que determina cada una de las particiones.
• Particionado Composite: los particionados anteriores eran del tipo simples (single o one-level), pues utilizamos un unico método de  particionado sobre una o mas columnas. Oracle nos permite utilizar metodos de particionado compuestos, utilizando un primer particionado de un tipo determinado, y luego para cada particion, realizar un segundo nivel de particionado utilizando otro metodo. Las combinaciones son las siguientes (se han ido ampliando conforme han ido avanzando las versiones): range-hash, range-list, range-range, list-range, list-list, list-hash y hash-hash (introducido en la versión 11g).
• Particionado Interval: tipo de particionado introducido igualmente en la versión 11g. En lugar de indicar los rangos de valores que van a determinar como se realiza el particionado, el sistema automáticamente creara las particiones cuando se inserte un nuevo registro en la b.d. Las técnicas de este tipo disponible son Interval, Interval List, Interval Range e Interval Hash (por lo que el particionado Interval es complementario a las técnicas de particionado vistas anteriormente).
• Particionado System: se define la tabla particionada indicando las particiones deseadas, pero no se indica una clave de particionamiento. En este tipo de particionado, se delega la gestión del particionado a las aplicaciones que utilicen la base de datos (por ejemplo, en las sentencias sql de inserción deberemos de indicar en que partición insertamos los datos).

Referente al particionado, y como característica interesante, Oracle nos permite definir sentencias SQL del tipo DML haciendo referencia a las particiones. Es lo que llaman nombres de tabla con extension de partición (partition-extended table names). Por ejemplo, podremos hacer un select sobre una tabla particionada indicando en la sintaxis la partición de la queremos que se haga lectura. Por ejemplo:

SELECT * FROM schema.table PARTITION(part_name);

Esto es igualmente válido para las sentencias INSERT, UPDATE, DELETE, LOCK TABLE. Esta sintaxis nos proporciona una forma simple de acceder a las particiones individuales como si fueran tablas, y utilizarlas, por ejemplo, para la creación de vistas (utilizando la vista en lugar de la tabla), lo que nos puede ser util en muchas situaciones.

Vamos a ver un ejemplo de construcción de cada uno de los tipos de particionado.

Particionado Range

Esta forma de particionamiento requiere que los registros estén identificado por un “partition key”  relacionado por un predefinido rango de valores. El valor de las columnas “partition key” determina la partición a la cual pertenecerá el registro.

CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE(’01-APR-2006′,’dd-MON-yyyy’)) TABLESPACE tsa
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE(’01-JUL-2006′,’dd-MON-yyyy’)) TABLESPACE tsb
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE(’01-OCT-2006′,’dd-MON-yyyy’)) TABLESPACE tsc
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE(’01-JAN-2007′,’dd-MON-yyyy’)) TABLESPACE tsd
);

Este tipo de particionamiento esta mejor situado cuando se tiene datos que tienen rango lógicos y que pueden ser distribuidos por este. Ej. Mes del Año o un valor numérico.

Particionado Hash

Los registros de la tabla tienen su localización física determinada aplicando un valor hash a la columna del partition key. La funcion hash devuelve un valor automatico que determina a que partición irá el registro. Es una forma automática de balancear el particionado. Hay varias formas de construir este particionado. En el ejemplo siguiente vemos una definición sin indicar los nombres de las particiones (solo el número de particiones):

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
PARTITION BY HASH(deptno) PARTITIONS 16;

Igualmente, se pueden indicar los nombres de cada particion individual o los tablespaces donde se localizaran cada una de ellas:

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
STORAGE (INITIAL 10K)
PARTITION BY HASH(deptno)
(PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,
PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);

Particionado List

Este tipo de particionado fue añadido por Oracle en la versión 9, permitiendo determinar el particionado según una lista de valores definidos sobre el valor de una columna especifica.

CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES(‘California’, ‘Hawaii’),
PARTITION sales_east VALUES (‘New York’, ‘Virginia’, ‘Florida’),
PARTITION sales_central VALUES(‘Texas’, ‘Illinois’)
PARTITION sales_other VALUES(DEFAULT)
);

Este particionado tiene algunas limitaciones, como que no soporta múltiples columnas en la clave de particionado (como en los otros tipos), los valores literales deben ser únicos en la lista, permitiendo el uso del valor NULL (aunque no el valor MAXVALUE, que si puede ser utilizado en particiones del tipo Range). El valor DEFAULT sirve para definir la partición donde iran el resto de registros que no cumplen ninguna condición de las diferentes particiones.

Particionado Composite

Este tipo de particionado es compuesto, pues se conjuga el uso de dos particionados a la vez. Veamos un ejemplo utilizando el tipo RANGE y el HASH. En primer lugar, hace un particionado del tipo RANGE utilizando rangos de años. En segundo lugar, para cada partición definida por cada año, hacemos un segundo particionado (subparticion) del tipo aleatorio (HASH) por el valor de otra columna:

CREATE TABLE TAB2 (ord_id     NUMBER(10), ord_day    NUMBER(2), ord_month  NUMBER(2),  ord_year   NUMBER(4) )
PARTITION BY RANGE(ord_year)
SUBPARTITION BY HASH(ord_id)
SUBPARTITIONS 8
( PARTITION q1 VALUES LESS THAN(2001)
( SUBPARTITION q1_h1 TABLESPACE TBS1, SUBPARTITION q1_h2 TABLESPACE TBS2,
SUBPARTITION q1_h3 TABLESPACE TBS3,SUBPARTITION q1_h4 TABLESPACE TBS4 ),
PARTITION q2 VALUES LESS THAN(2002)
( SUBPARTITION q2_h5 TABLESPACE TBS5,SUBPARTITION q2_h6 TABLESPACE TBS6,
SUBPARTITION q2_h7 TABLESPACE TBS7,SUBPARTITION q2_h8 TABLESPACE TBS8),
PARTITION q3 VALUES LESS THAN(2003)
( SUBPARTITION q3_h1 TABLESPACE TBS1,SUBPARTITION q3_h2 TABLESPACE TBS2,
SUBPARTITION q3_h3 TABLESPACE TBS3,SUBPARTITION q3_h4 TABLESPACE TBS4),
PARTITION q4 VALUES LESS THAN(2004)
( SUBPARTITION q4_h5 TABLESPACE TBS5,SUBPARTITION q4_h6 TABLESPACE TBS6,
SUBPARTITION q4_h7 TABLESPACE TBS7,SUBPARTITION q4_h8 TABLESPACE TBS8) )

Las combinaciones permitidas son las siguientes (se han ido ampliando conforme han ido avanzando las versiones de Oracle): range-hash, range-list, range-range, list-range, list-list, list-hash y hash-hash (introducido en la versión 11g).

Particionado Composite en Oracle

Particionado Interval

El particionado Interval ha sido introducido en la versión 11g para habilitar un mantenimiento de particiones desasistido. Normalmente, cuando realizamos un particionado sobre una tabla, indicamos una lista de valores o rangos para crear de antemano las particiones.  Posteriormente, ajustamos la definición de las particiones para incluir nuevas para nuevos rangos o valores. Con las particiones Interval, preparamos  para que Oracle cree las particiones de forma automática cuando lo necesite. Básicamente, se define un intervalo y una directiva para decirle a Oracle como se tiene que comportar. Veamos un ejemplo:

CREATE TABLE T_11G(C1 NUMBER(38,0),
C2 VARCHAR2(10),
C3 DATE)
PARTITION BY RANGE (C3) INTERVAL (NUMTOYMINTERVAL(1,’MONTH’))
(PARTITION P0902 VALUES LESS THAN (TO_DATE(‘2009-03-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’)));

Hemos creado una partición base, y con lo especificado en Interval definimos como gestionar la creación automática de nuevas particiones. La posibilidad de definir un intevalo y que Oracle se encargue de crear las particiones a medida que se vayan necesitando resulta muy interesante para facilitar el mantenimiento y administración de particiones.

Particionado System

Una de las nuevas funcionalidades introducida en la version 11g es el denominado partitioning interno o de sistema. En este particionado Oracle no realiza la gestión del lugar donde se almacenaran los registros, sino que seremos nosotros los que tendremos que indicar en que partición se hacen las inserciones.

CREATE TABLE T ( C1 INT,
C2 VARCHAR2(10),
C3 DATE)
PARTITION BY SYSTEM
(PARTITION P1,
PARTITION P2,
PARTITION P3);

Si hicieramos un insert sobre la tabla (por ejemplo, INSERT INTO T VALUES (1,’A’,SYSDATE);), daría error, siendo la instrucción a ejecutar correcta la siguiente:

INSERT INTO T PARTITION (P3) VALUES  (1,’A’,sysdate);

Puede ser util este particionado para aplicaciones donde nos interesa ser nosotros lo que gestionamos la forma en la que se realiza el particionado (lógica de aplicación).

Uso de columnas virtuales para particionar

En la versión 11g se pueden definir en las tablas columnas virtuales (no existen físicamente). Ademas estas columnas se pueden utilizar para realizar particionado sobre ellas. La forma de crear una tabla con columnas de este tipo sería la siguiente:

CREATE TABLE T (C1 INT,
C2 VARCHAR2(10),
C3 DATE,
C3_V CHAR(1)
GENERATED ALWAYS AS
(TO_CHAR(c3,’d’)) VIRTUAL)     )
PARTITION BY LIST(C3_V)
(PARTITION p1 VALUES (‘1’),
PARTITION p2 values (‘2’),
PARTITION p3 values (‘3’),
PARTITION p4 values (‘4’) );

Os recomiendo la lectura de la entrada del blog OraMDQ donde Pablo Rovedo habla en profundidad sobre las nuevas funcionalidades de particionado de la version 11g de Oracle, incluyendo unos completos ejemplos prácticos.

Gestión del particionado.

La gestión del particionado es totalmente dinámica, de forma que se podrán añadir particiones a una tabla particionada existente, juntar o borrar particiones, convertir una particion en una tabla no particionada, partir una partición en dos (Splitting), hacer un truncate (borra los datos de la partición pero deja la estructura). También podemos mover una partición de un tablespace a otro, renombrarla, etc. Os recomiendo la lectura de blog Bases de Datos y Tecnología donde se explican en detalle algunas de estas operaciones, así como el  blog Database Design que también habla sobre el tema).

El particionado en Oracle tiene muchas mas funcionalidades de las que podeis ampliar información en la propia documentación online del fabricante (Oracle 10g y Oracle 11g)

Ver este documento en Scribd

Vistas Materializadas

Otra funcionalidad muy interesante de Oracle y que nos puede ser util cuando estemos construyendo un sistema de Business Intelligence son las Vistas Materializadas. Las vistas materializadas son un tipo de vistas especiales, en las que, ademas de guardarse la definición de esta (como es habitual en todos los sistemas de gestión de bases de datos), también se guardan los datos. También se determina en su definición cuando se va a realizar el refresco del contenido de la «vista». Nos pueden ser muy utiles para la creación de tablas sumarizadas en un DW o para la creación de tablas intermedias. Os recomiendo la lectura de la entrada en dataprix.com donde se habla de este tema.

Bases de Datos Express. Una forma de empezar con las grandes.

En una entrada anterior del blog (Bases de Datos OpenSource. ¿Porque elegimos Mysql para nuestro proyecto?), hablamos de las bases de datos Open Source como opción interesante y fiable para el desarrollo de proyectos de Business Intelligence. Vimos diferentes productos y algunas comparativas entre ellos.

Pero hay otras alternativas (con limitaciones en la mayoria de casos), que nos permiten empezar a trabajar con «las grandes» de una forma gratuita. Son las llamadas versiones Express. Son versiones pensadas para pequeños sistemas, para labores de desarrollo o formación, para la preparación de prototipos o evaluaciones, que nos permiten una «iniciación» con los grandes gestores de bases de datos relacionales. En la mayoría de los casos, el producto se ofrece totalmente funcional, pero con limitaciones (en el tamaño máximo de la base de datos, uso de memoria Ram o procesadores de la máquina, etc). También puede haber funcionalidades que no esten activas en estas versiones (como el particionado en la versión Express de Oracle). Os dejo el link a las webs de los fabricantes donde os podeís descargar estas versiones:

• Oracle: Oracle ha apostado claramente por proyectos Open Source, como podeis ver en la web (proyecto Eclipse, PHP, Phyton, etc). A nivel de SGBDR, nos ofrece la Oracle Database 10g Express Edition, en las plataformas Windows y Linux. Se puede distribuir libremente y tiene las siguientes limitaciones: solo 1 instancia de Bd por servidor, tamaño maximo de esta de 4 Gb, utilización máxima de 1Gb de Ram y 1 procesador (aunque tengamos varios). Incluye las herramientas de administración. Mas información aquí.

• Microsoft SQL Server: SQL Server 2008 Express, tiene un limite de 16 instancias, con un máximo de 4 GB de tamaño, uso de 1 Gb de Ram y 1 procesador. Disponible en entorno Windows unicamente, se puede distribuir libremente también. Como opción interesante para el mundo BI, una de las versiones Express incluye los Reporting Services. Mas información sobre la versión en el blog Sqlserversi.com

• IBM DB2: IBM también ofrece su versión express de la archiconocida DB2. Nos ofrecen la DB2 Express-C, en las plataformas Linux y Windows (32 y 64 bits), Solaris y Mac, con la única limitación de uso de 2 Gb de Ram y 2 Procesadores. Podeis ampliar información aquí. También se puede distribuir libremente. Parece que IBM apuesta fuerte con esta versión de «entrada». Incluso nos ofrecen un libro electrónico gratuito y una colección de videos de formación.
• Sybase: la versión Express de Sybase esta disponible para Linux (la llamada Adaptive Server Enterprise Linux x86 Express Edition). Nos permite trabajar con una unica instancia de base de datos, utilizando como máximo 2 Gb de Ram y 5 Gb de tamaño de la base de datos. Fue liberada para el mundo Linux alla por 1999, por lo que fueron uno de los pioneros en este tipo de versiones.
• IBM Informix: aunque es un producto de IBM al igual que DB2, siguen un estrategia diferente. Nos ofrecen el producto Informix Dynamic Server Developer Edition, en una evaluación sin periodo de expiración, pero solo con fines de desarrollo, y disponible en los sistemas siguientes: AIX, HP, Linux, Mac, Solaris y Windows. La versión Express propiamente dicha es la siguiente en la escala y ya es una versión de pago (pensada como un producto para Pequeña y Mediana Empresa). Los limites son de 1 Gb de Ram y un procesador, y un tamaño máximo de la base de datos de de 8 Gb. Podeís ver la comparativa de cada una de las versiones aquí.
• Adabas: es la base de datos Express menos generosa. En su Personal Edition Adabas 13, nos ofrecen 3 usuarios, limitados a una base de datos de 100Mb y 1 Cpu.

En todos los casos, si quereis pasar a las versiones completas, estan disponibles los correspondientes upgrades de pago. Antes de elegir una, podeís echar un vistazo a sus características en esta completa entrada de la wikipedia. Lo dicho, una opción interesante para formarnos, para validar productos o montar plataformas de desarrollo y prototipos. Y sin pasar por caja.

13.4.1. Tabla Hechos Venta. Particionado en MySql.

Antes de comenzar la implementación del proceso ETL para la carga de la tabla de Hechos de Ventas, vamos a realizar alguna consideración sobre el particionado de tablas.

Cuando estamos costruyendo un sistema de business intelligence con su correspondiente datawarehouse, uno de los objetivos (aparte de todas las ventajas de sistemas de este tipo: información homogenea, elaborada pensando en el analisis, dimensional, centralizada, estatica, historica, etc., etc.) es la velocidad a la hora de obtener información. Es decir, que las consultas se realicen con la suficiente rapidez y no tengamos los mismos problemas de rendimiento que suelen producirse en los sistemas operacionales (los informes incluso pueden tardar horas en elaborarse).

Para evitar este problema, hay diferentes técnicas que podemos aplicar a la hora de realizar el diseño fisico del DW. Una de las técnicas es el particionado.Pensar que estamos en un dw con millones de registros en una unica tabla y el gestor de la base de datos ha de mover toda la tabla. Ademas, seguramente habrá datos antiguos a los que ya no accederemos casi nunca (datos de varios años atras). Si somos capaces de tener la tabla «troceada» en segmentos mas pequeños seguramente aumentaremos el rendimiento y la velocidad del sistema.

El particionado nos permite distribuir porciones de una tabla individual en diferentes segmentos conforme a unas reglas establecidas por el usuario. Según quien realize la gestión del particionado, podemos distinguir dos tipos de particionado:

Manual: El particionado lo podriamos realizar nosotros en nuestra lógica de procesos de carga ETL (creando tablas para separar los datos, por ejemplo, tabla de ventas por año o por mes/año). Luego nuestro sistema de Business Intelligence tendrá que ser capaz de gestionar este particionado para saber de que tabla tiene que leer según los datos que le estemos pidiendo (tendra que tener un motor de generación de querys que sea capaz de construir las sentencias para leer de las diferentes tablas que incluyen los datos). Puede resultar complejo gestionar esto.

Automatico: Las diferentes porciones de la tabla podrán ser almacenadas en diferentes ubicaciones del sistema de forma automatica según nos permita el SGBDR que estemos utilizando.La gestión del particionado es automática y totalmente transparente para el usuario, que solo ve una tabla entera (la tabla «lógica» que estaria realmente partida en varias tablas «fisicas»). La gestión la realiza de forma automática el motor de base de datos tanto a la hora de insertar registros como a la hora de leerlos.

La partición de tablas se hace normalmente por razones de mantenimiento, rendimiento o gestión.

Lógica Particionado de tablas

Según la forma de realizar el particionado, podriamos distinguir:

Partición horizontal (por fila): consiste en repartir las filas de una tabla en diferentes particiones. Por ejemplo, los clientes de un pais estan incluidos en una determinada partición y el resto de clientes en otra. En cada partición se incluyen los registros completos de cada cliente.

Partición vertical( por columna): consiste en repartir determinadas columnas de un registro en una partición y otras columnas en otra (partimos la tabla verticalmente,). Por ejemplo, en una partición tenemos las columnas de datos de direcciones de los clientes, y en otra partición las columnas de datos bancarios.

Cada motor de base de datos implementa el particionado de forma diferente. Nosotros nos vamos a centrar en la forma de implementarlo utilizando Mysql, que es la base de datos que estamos utilizando para nuestro proyecto.

Particionado de tablas en MySql

MySql implementa el particionado horizontal. Basicamente, se pueden realizar cuatro tipos de particionado, que son:

• RANGE: la asignación de los registros de la tabla a las diferentes particiones se realiza según un rango de valores definido sobre una determinada columna de la tabla o expresión (ver manual online de MySql aquí ). Es decir, nosotros indicaremos el numero de particiones a crear, y para cada partición, el rango de valores que seran la condicion para insertar en ella, de forma que cuando un registro que se va a introducir en la base de datos tenga un valor del rango en la columna/expresion indicada, el registro se insertara en dicha partición.
• LIST: la asignación de los registros de la tabla a las diferentes particiones se realiza según una lista de valores definida sobre una determinada columna de la tabla o expresión (ver manual online de MySql aquí ). Es decir, nosotros indicaremos el numero de particiones a crear, y para cada partición, la lista de valores que seran la condicion para insertar en ella, de forma que cuando un registro que se va a introducir en la base de datos tenga un valor incluido en la lista de valores, el registro se insertara en dicha partición.
• HASH: este tipo de partición esta pensado para repartir de forma equitativa los registros de la tabla entre las diferentes particiones. Mientras en los dos particionados anteriores eramos nosotros los que teniamos que decidir, según los valores indicados, a que partición llevamos los registros, en la partición HASH es MySql quien hace ese trabajo. Para definir este tipo de particionado, deberemos de indicarle una columna del tipo integer o una función de usuario que devuelva un integer (ver manual online de MySql aquí ). En este caso, aplicamos una función sobre un determinado campo que devolvera un valor entero. Según el valor, MySql insertará el registro en una partición distinta.
• KEY: similar al HASH, pero la función para el particionado la proporciona MySql automáticamente (con la función MD5) (ver manual online de MySql aquí ). Se pueden indicar los campos para el particionado, pero siempre han de ser de la clave primaria de la tabla o de un indice único.
• SUBPARTITIONS: Mysql permite ademas realizar subparticionado. Permite la división de cada partición en multiples subparticiones. (ver manual online de MySql aquí).

Ademas, hemos de tener en cuenta que la definición de particiones no es estática. Es decir, MySql tiene herramientas para poder cambiar la configuración del particionado a posteriori, para añadir o suprimir particiones existentes, fusionar particiones en otras, dividir una particion en varias, etc. (ver aquí ).

El particionado tiene sus limitaciones y sus restricciones, pues no se puede realizar sobre cualquier tipo de columna o expresión (ver restricciones al particionado aquí), tenemos un limite de particiones a definir y habrá que tener en cuenta algunas cosas para mejorar el rendimiento de las consultas y evitar que estas se recorran todas las particiones de una tabla (ver el artículo MySql Partitions in Practice, donde se nos explica con un ejemplo trabajando sobre una base de datos muy grande, como realizar particionado y que cosas tener en cuenta para optimizar los accesos a las consultas). Para entender como funciona el particionado, hemos replicado los ejemplos definidos en este articulo con una tabla de pruebas de 1 millón de registros (llenada, por cierto,con datos de prueba generados con Talend y el componente tRowGenerator).

Ejemplo componente tRowGenerator para producir datos de test

En concreto, hemos creado dos tablas iguales (con la misma estructura). Una con particionado por año en un campo de la clave del tipo fecha, y la segunda con la misma estructura sin particionado. En ambas tablas tenemos un millon de registros repartidos entre los años 2008 y 2017. Una vez creadas las tablas, utilizamos la sentencia de MySql  explain y explain partitions para analizar como se ejecutaran las sentencias sql (analisis de indices). Ademas, comprobamos tiempos de ejecución con diferentes tipos de sentencia SQL. Los resultados son mas que obvios:

Analisis tiempos ejecucion

En las mayoria de los casos se obtiene un mejor tiempo de respuesta de la tabla particionada, y en los casos en los que no, el tiempo de ejecución es practicamente igual al de la tabla no particionada (diferencias de milesimas de segundo). Observar cuando indicamos condiciones fuera del indice (ultima sentencia SQL), como los tiempos de respuesta son aun mas relevantes. Y siempre conforme vamos leyendo de mas particiones (por incluir mas años en la condición), el tiempo de respuesta de la consulta entre una y otra tabla se va acercando.

Particionado de la tabla de hechos de Ventas en nuestro DW

Para nuestro DW, hemos decidir implementar un particionado del tipo LIST. Como os habreis podido dar cuenta, seguramente los particionados por RANGE o por LIST son los mas adecuados para un sistema de Business Intelligence pues nos van a permitir de una forma facil reducir el tamaño de las casi siempre monstruosas tablas de hechos, de una forma fácil y automática.

Vamos a crear 10 particiones y repartiremos los diferentes años en cada una de las particiones, empezando por 2005 –> Particion 1, 2006 –> Particion 2, 2007 –> Particion 3, …, 2013 –> Particion 9, 2014 –> Partición 10. A partir de 2015, volvemos a asignar cada año a las particiones y así hasta el año 2024 (tiempo de sobra para lo que seguramente será la vida de nuestro DW).

Como el campo año no lo tenemos en el diseño físico de la tabla de hechos, aplicaremos sobre la columna fecha la funcion YEAR para realizar el particionado. La sentencia para la creación de la tabla de hechos quedaría algo parecido a esto:

CREATE  TABLE IF NOT EXISTS `enobi`.`dwh_ventas` (
`fecha_id` DATE NOT NULL ,
`material_id` INT(11) NOT NULL ,
`cliente_id` INT(11) NOT NULL ,
`centro_id` INT(11) NOT NULL ,
`promocion_id` INT(11) NOT NULL ,
`pedido_id` INT(11) NOT NULL ,
`unidades` FLOAT NULL DEFAULT NULL COMMENT 'Unidades Vendidas' ,
`litros` FLOAT NULL DEFAULT NULL COMMENT 'Equivalencia en litros de las unidades vendidas' ,
`precio` FLOAT NULL DEFAULT NULL COMMENT 'Precio Unitario' ,
`coste_unit` FLOAT NULL DEFAULT NULL COMMENT 'Coste Unitario del Producto')

PARTITION BY LIST(YEAR(fecha_id)) (
    PARTITION p1 VALUES IN (2005,2015),
    PARTITION p2 VALUES IN (2006,2016),
    ..................................
    PARTITION p9 VALUES IN (2013,2023),
    PARTITION p10 VALUES IN (2014,2024)
);

Con este forma de definir el particionado estamos sacando ademas partido de la optimización de lo que en MySql llaman «Partitioning Pruning» ( ver aqui ). El concepto es relativamente simple y puede describirse como «No recorras las particiones donde no puede haber valores que coincidan con lo que estamos buscando». De esta forma, los procesos de lectura serán mucho mas rápidos.

A continuación, veremos en la siguiente entrada del Blog los ajustes de diseño de nuestro modelo físico en la tabla de hechos (teniendo en cuenta todo lo visto referente al particionado) y los procesos utilizando la ETL Talend para su llenado.

Bases de Datos OpenSource. ¿Porque elegimos Mysql para nuestro proyecto?.

Antes de continuar con la construccion de los procesos ETL para el resto de las dimensiones del proyecto, vamos a hacer una pausa para explicar el motivo de elegir MySql como gestor de base de datos para el proyecto ENOBI.

En primer lugar, el económico. Estamos realizando un proyecto utilizando productos OpenSource o productos licenciados libremente por los fabricantes (como Microstrategy Reporting Suite). Seguramente si estuviesemos en un gran proyecto, elegiriamos una opción de base de datos propietaria, como Oracle ( que es para muchos la mejor opción por estudios, prácticas o consenso). Podeis ver el estudio comparativo de bases de datos realizado por Forrester ( gracias a todobi.com ).

Estudio Forrester 2009 sobre Gestores de Base de Datos

En segundo lugar, estamos buscando productos con reconocido prestigio, fiabilidad, velocidad, rendimiento, facilidad de administración y conexión con otros productos, bien documentados, con una buena evolución y soporte. Productos de los que sea fácil obtener información, con buenas herramientas, y para los que incluso podamos recibir cursos de formación si fuese necesario. Productos que esten siendo utilizados en muchos entornos productivos y que nos den la suficiente confianza.

Ademas, los gestores de bases de datos OpenSource hace tiempo que dejaron de ser un experimiento y ya son una alternativa real para las empresas (incluso aparecen en los cuadrantes Gartner). Son productos cada vez mas evolucionados, con mas funcionalidades y las empresas que los desarrollan tienen también cada vez más volumen de negocio (cuestión importante para continuar la evolución de los productos).

Los productos OpenSource mas conocidos son:

MySQL, PostgreSQL, MaxDB, Firebird, Ingres , MonetDB, LuciDb.

Podeis echar un vistazo al directorio EOS de productos OpenSource para ver otras bases de datos Open, así como valoraciones de estas y casos reales de uso.

Existen multitud de comparativas sobre las bases de datos OpenSource, incluso estudios comparándolas con productos propietarios (ver comparativa Oracle/Mysql). La elección de una u otra dependerá del tipo de proyecto, el uso que vayamos a dar a la base de datos (Servidor Web, desarrollo aplicaciones, Dw), posibilidades de integración con otros productos, plataformas hardware o sistema operativo a utilizar, etc.

En nuestro caso, hemos decidido trabajar con MySql o PostgreSql, y vamos a centrarnos en analizar cual de los dos productos nos quedamos.

Existen multitud de comparativas, aunque algunas de las mas interesantes son:

Comparativa MySql vs PostGreSql.

Comparativa MySql vs PostGreSql: ¿cuando emplear cada una de ellas?

PostgreSQL Vs MySQL: Comparative Review: es una comparativa mas reciente donde se tiene en cuenta la evolución de los dos productos en los ultimos años.

Si analizamos las bases de datos centrandonos en el ámbito de los Datawarehouse, también existen estudios que analizan las diferentes opciones existentes ( otra vez gracias a todobi.com ). El estudio original, realizado por Jos van Dongen, lo tenemos aquí.

Comparativa BD-DW (estudio Jos van Dongen)

Incluso, tenemos en la Wikipedia un estudio comparativo de las bases de datos relacionales mas importantes, al que podemos acceder desde aquí.

Comparativa BD Wikipedia

Tenemos mucha información, pero es el momento de las conclusiones y de elegir el producto que utilizaremos en nuestro proyecto, teniendo en cuenta las siguientes consideraciones:

1) Vamos a construir un DataWarehouse, por lo que tendrá prioridad para nosotros la velocidad de acceso a los datos (habrá cargas de datos regulares cuando estemos alimentando el DW a partir de los sistemas operacionales, y la mayoria de accesos serán para consultar dichos datos).

2) Para mejorar el rendimiento de la base de datos, en la tabla de hechos (que recordemos es la que tendra millones de registros, pues en ella se guardan todas las transacciones de ventas), realizaremos particionado. Eso significa que para la misma tabla lógica, habrá diferentes tablas físicas y toda la gestión de dicho particionado recaera sobre el motor de base de datos, siendo el proceso totalmente transparente para el usuario (también habría cabido la opción de gestionarlo nosotros en los procesos de carga ETL separando los datos en tablas distintas según un criterio determinado (por ejemplo, el año), teniendo en cuenta que esto es soportado, por ejemplo, por las herramientas de Microstrategy, que son capaces de generar las sentencias SQL apropiadas para leer información de las diferentes tablas donde la información está repartida).

3) Es la primera vez que trabajamos con ambas bases de datos, y en ambos casos buscaremos la facilidad de trabajo, la existencia de herramientas gráficas y de administración, la documentación, etc.

Teniendo en cuenta estas consideraciones y los estudios y comparativas que hemos descrito anteriormente, decidimos trabajar con MySql por los siguiente motivos:

1)Velocidad: aunque en algunos estudios PostgreSql es mejor para entornos donde la integridad de datos es fundamental (como en el desarrollo de aplicaciones), a nivel de rendimiento MySql es mejor. En concreto, cuando utilizamos el tipo de motor MyIsam, el rendimiento de MySql es mucho mejor. Este será el tipo de Engine con el que definiremos todas nuestras tablas en el DW. El motor InnoDb es mas lento y esta orientado a aplicaciones donde la actualización e integridad es mas importante.

2)Particionado: aunque ambas plataformas permiten particionado, la gestión utilizando Mysql es mas sencilla.

3)Herramientas gráficas, documentación, plataformas: Mysql proporciona una amplia documentación (muy completa) y multitud de herramientas gráficas de gestión y de conectividad. PostgreSql también cumpliría los requisitos en el tema de documentación y plataformas soportadas. También es importante para nosotros poder disponer en Mysql de una herramienta de Diseño de Bases de datos, como es MySql Workbench, que nos permite definir nuestros modelos relacionales, generar las sentencias SQL y construir la base de datos, así como realizar ingenieria inversa (construir el módelo de datos a partir de una base de datos existente) o comparar el modelo definido en la herramienta con el existente en la base de datos.

Algunos de los usuarios mas destacados de Mysql son los siguientes:

• Amazon.com
• Cox Communications – La cuarta televisión por cable más importante de EEUU, tienen más de 3.600 tablas y aproximadamente dos millones de inserciones cada hora.
• Craigslist
• CNET Networks
• Digg – Sitio de noticias.
• flickr, usa MySQL para gestionar millones de fotos y usuarios.
• Google – Para el motor de búsqueda de la aplicación AdWords.
• Joomla!, con millones de usuarios.
• phpBB, Uno de los más famosos sitios de foros, con miles de instalaciones y con millones de usuarios.
• LiveJournal – Cerca de 300 millones de páginas servidas cada día.^[2]
• NASA
• NetQOS, usa MySQL para la gestión de algunas de las redes más grandes del mundo como las de Chevron, American Express y Boeing.
• Nokia, usa un cluster MySQL para mantener información en tiempo real sobre usuarios de redes de móviles.
• Omniture
• Sabre, y su sistema de reserva de viajes Travelocity
• Slashdot – con cerca de 50 millones de páginas servidas cada día.
• Wikipedia, sirve más de 200 millones de consultas y 1,2 millones de actualizaciones cada día, con picos de 11.000 consultas por segundo.
• WordPress, con cientos de blogs alojados en él.
• Yahoo! – para muchas aplicaciones críticas.

Si quereis ampliar la información sobre MySql, la entrada en la wikipedia es bastante completa.

Participa en la encuesta: