Export / Import de una base de Datos Oracle

Una de las cosas que más me gustan de la programación es el manejo de la base de datos, así como la programación en lenguaje PL/SQL y un jugar un poco a hacer el DBA. El otro día tuve que hacer un «dump» o la exportación de una base de datos completa (schema, sus objetos -tablas, sinónimos, etc, y sus datos) para importarla en otra máquina donde otra instancia de Oracle se ejecuta.

Para los que conocen un poco – o incluso no – esta operación aunque delicada, es simple. Dos comandos nos permiten exportar y recuperar lo que necesitamos : expdp e impdp. Es lo que se nos enseña de base. Pero por ejemplo no se nos dice que antes de ejecutar estas operaciones sería bueno saber el tamaño que ocupa los objetos que vamos a exportar, y si tenemos el espacio necesario en la base de datos de destinación. Pues voilà el problema que me ocurrió que días. En el mundo real las BDD pueden tener tamaños enormes, (31 Gb en mi caso). El no conocer el espacio libre del tablespace destinación puede presentar problemas.

Efectivamente, cuando no se analiza primero el espacio disponible y se lanza el proceso de importación, el mensaje que Oracle muestra es el siguiente:

ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment NEW_SCHEMA.SYS_LOB0000747924C00002$$ by 8192 in tablespace CMS_REFONTE
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment NEW_SCHEMA.SYS_LOB0000747924C00002$$ by 8192 in tablespace CMS_REFONTE
ORA-39171: Job is experiencing a resumable wait.
ORA-01691: unable to extend lob segment NEW_SCHEMA.SYS_LOB0000747924C00002$$ by 8192 in tablespace CMS_REFONTE
ORA-39171: Job is experiencing a resumable wait.

Y el problema es que el mensaje se muestra durante un buen tiempo, pero el proceso no se aborta. Para más información sobre este problema, pueden ver acá y acá.

Para evitar esto, lo primero es lanzar el proceso de exportación en modo simulación. Esto nos permitirá calcular el tamaño que necesitaremos. El comando expdp, al que debemos indicarle :

1. schema a exportar : atributo SCHEMAS
2. los elementos a exportar (o a ignorar en este caso) : atributo EXCLUDE
3. directorio donde el archivo .dmp (resultado de la exportación) : atributo DIRECTORY
4. Y EL ATRIBUTO PARA SIMULAR EL CALCULO -> ESTIMATE_ONLY=y


12:17 joan@jomaora:~% expdp scott/tiger@sid DIRECTORY=EXPORT schemas=SCHEMA_OLD ESTIMATE_ONLY=y EXCLUDE=synonym,statistics

Export: Release 11.2.0.3.0 - Production on Wed Oct 24 12:18:06 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": scott/********@sid DIRECTORY=EXPORT schemas=SCHEMA_OLD ESTIMATE_ONLY=y EXCLUDE=synonym,statistics
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. estimated "SCHEMA_OLD"."TITULOS"    2.796 GB
. estimated "SCHEMA_OLD"."AUTORES"    1.125 GB
. estimated "SCHEMA_OLD"."AMTERIAS"      35 MB
. estimated "SCHEMA_OLD"."USUARIOS"      21 MB
. estimated "SCHEMA_OLD"."PRESTAMOS"     19 MB
. estimated "SCHEMA_OLD"."MULTAS"        11 MB
......
Total estimation using BLOCKS method: 4.079 GB
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:18:11

Al final obtenemos el espacio que necesitaremos. Luego sólo debemos ir a ver en la base de datos de destinación, si hay el suficiente espacio en el tablespace donde vamos a crear el nuevo schema. Para esto, un query en modo DBA es suficiente:

SQL> SELECT
________a.TABLESPACE_NAME,
________a.BYTES / 1024 / 1024 MBused, b.BYTES / 1024 / 1024 MBfree,
________100 - round(((a.BYTES)/(a.BYTES + b.BYTES))*100,0) percent_free
____FROM
________(select TABLESPACE_NAME, sum(BYTES) BYTES from dba_data_files group by TABLESPACE_NAME) a,
________(select TABLESPACE_NAME, sum(BYTES) BYTES , max(BYTES) largest from dba_free_space group by TABLESPACE_NAME) b
____WHERE a.TABLESPACE_NAME=b.TABLESPACE_NAME;

TABLESPACE_NAME_________MBUSED______MBFREE______PERCENT_FREE
----------------------- ----------- ----------- ------------
TABLESP1________________71680_______2395.125_____3
SYSAUX___________________1440_______89.75________6
SYSTEM___________________1610_______507.5625____24
TABLESP2_______________171.25_______8.375________5
TABLESP3________________40960_______29283.5_____42

Teniendo ya esta información, y analizando los espacios disponibles, nos queda seleccionar el tablespace correcto para crear nuestro nuevo schema, y luego hacer el import. En este caso, el tablespace TABLESP3 es el que mas espacio tiene (29 Gb).  Efectuemos el export primero:

joan@jomaora:~% expdp scott/tiger@sid DIRECTORY=EXPORT schemas=SCHEMA_OLD ESTIMATE_ONLY=y EXCLUDE=synonym,statistics LOGFILE=SCHEMA_OLD_exp.log DUMPFILE=SCHEMA_OLD_exp.dmp

Para crear el schema hacemos:

create user SCHEMA_NEW identified by SCHEMA_NEW default tablespace TABLESP3 temporary tablespace TEMP QUOTA UNLIMITED ON TABLESP3;
grant connect to SCHEMA_NEW;
grant resource to SCHEMA_NEW;

Y ya con el schema creado, hacemos:

joan@jomaora:~% impdp admin/123@sid2 directory=EXPORT dumpfile=SCHEMA_OLD_exp.dmp logfile=SCHEMA_NEW_imp.dmp remap_schema=SCHEMA_OLD:SCHEMA_NEW

Los atributos de este comando son:

1. DIRECTORY: directorio donde esta el archivo a dmp importar.
2. DUMPFILE: Nombre del archivo a importar (el mismo que se da en el comando expdp)
3. LOGFILE: Archivo que permite guardar los logs de la operación.
4. REMAP_SCHEMA: Debe indicarse el nombre del schema de origen : nombre del schema destinación.