miércoles, 27 de mayo de 2015

Consejos y trucos: Crear pequeñas copias de una base de datos de producción


Consejos y trucos: Crear pequeñas copias de una base de datos de producción

Una de las áreas más comunes de un Administrador de Base de Datos es crear una copia de una base de producción para un ambiente de desarrollo o propósitos de pruebas, pero el problema es que algunas veces tu no tienes los recursos de espacio suficientes para crear una copia completa de la base de datos de producción, entonces algunas personas deciden organizar los datos en términos de meses y años de modo que ellos puedan crear el nuevo ambiente con solamente aquellos tablespaces de los últimos meses pero ¿qué sucede si se necesitara datos de otros meses en el nuevo ambiente? Algunas veces ciertamente no necesitamos solo unos cuantos tablespaces desde producción sino lo que necesitamos es un porcentaje de todos los datos de la base de datos completa de producción. Una vez nosotros conozcamos el porcentaje que necesitamos la pregunta sería con qué tamaño debería de crear los datafiles en el nuevo ambiente ya que usualmente nosotros no tenemos mucho espacio para ambientes secundarios. ¿Que sucede si creamos un tablespace con un tamaño grande cuyos datos en él serán pocos y a la vez creamos un tablespace pequeño que realmente necesita mucho espacio? Tomando en cuenta que disponemos de espacio limitado, esto sería un problema. Luego podríamos reducir el tamaño de todos los datafiles que se crearon sobredimensionados, pero esto es más trabajo ¿no?. Existen muchas otras preguntas que vienen bajo este contexto y la tarea empieza a tornarse más difícil. Bien, te vamos a mostrar que hay una manera muy fácil de realizar esta tarea que estamos planteando, cómo hacerlo es lo que te enseñaremos a lo largo de nuestro articulo. Al final de este articulo serás capaz de crear ambientes de pruebas o desarrollo donde lo único que necesitas es saber qué porcentaje de la base de datos de producción quieres recrear.
La tarea consta de dos partes:
  • Extraer el porcentaje de datos desde la base de datos de producción.
  • Importar los datos pero creando todos los datafiles y sus  extensiones con un porcentaje de producción.
La primer parte puede ser fácilmente resuelta usando el parámetro SAMPLE de el utilitario expdp de datapump y la segunda parte puede también ser fácilmente resuelta  usando PCTSPACE en la herramienta impdp. Vamos a darte una pequeña definición de ambos parámetros:
SAMPLE:X – Extrae un porcentaje X de dato. Dicho porcentaje extraído aplica a cada objeto dentro de la base de datos fuente. Esta es una opción de la herramienta expdp de data pump. El parámetro SAMPLE te permite exportar subconjuntos de datos especificando únicamente un número, el porcentaje. El porcentaje únicamente es una probabilidad de que un bloque de datos sea seleccionado como parte del archivo resultante de la operación de exportación. Tu puedes indicar el porcentaje que tu quieras pero no puedes especificar el valor 0, solamente puedes elegir entre (0,100] dicho numero debe estar precediendo el esquema y la tabla. Si se especifica un esquema, también debe especificarse la tabla. Si solo especificas el nombre de la tabla y no el nombre del esquema, data pump asumirá que la tabla es parte del esquema del usuario que está realizando la operación de exportación. Si ninguna tabla es especificada, entonces dicho porcentaje aplica a todos los objetos que se estén extrayendo.
PCTSPACE:X – Reduce todas las extensiones hasta un determinado porcentaje X. Mientras la importación de los datos esté creando todas las extensiones, automáticamente data pump reducirá cada extensión hasta el porcentaje especificado. La opción PCTSPACE es usada con el parámetro TRANSFORM para reducir el tablespaces y también sus datafiles de manera física cuando muchas filas han sido removidas y se quiere tomar ventaja de ese espacio liberado. Todos las operaciones de redimensionado son hechas por la herramienta impdp automáticamente, tu solo necesitas especificar un valor, un número, el cuál es el porcentaje.
Ahora te mostraremos cómo es que funciona todo esto, crearemos un ambiente de pruebas basándonos en un porcentaje del ambiente de producción.
Creando un ambiente de pruebas con el 10% de los datos del ambiente de producción
Ambiente de Producción
En nuestro ambiente de producción tenemos 2 tablas, T1 y T2 con 96MB y casi 900,000 filas en el esquema OraWorld.
Revisando los tamaños de las tablas:
SQL> select segment_name, 
            segment_type, 
            tablespace_name, 
            bytes/1024/1024 MB_Size 
     from dba_segments 
     where owner='ORAWORLD';
 
SEGMENT_NA SEGMENT_TYPE       TABLESPACE    MB_SIZE
---------- ------------------ ---------- ----------
T1         TABLE              DATA1              96
T2         TABLE              DATA2              96
IND_T1     INDEX              INDEX1             15
IND_T2     INDEX              INDEX2             15
 

Revisando el numero de filas:
SQL> select table_name, 
            num_rows 
      from dba_tables 
      where owner='ORAWORLD';
 
TABLE_NAME                         NUM_ROWS
------------------------------ ------------
T2                                   881000
T1                                   881000
 

Revisando los datafiles y sus tamaños:
SQL> select file_name, 
            bytes/1024/1024 MB_Size 
     from dba_data_files 
     where tablespace_name in ('DATA1','DATA2','INDEX1','INDEX2');
 
FILE_NAME                                   MB_SIZE
---------------------------------------- ----------
+DATA/orcl/datafile/data1.342.860024365         150
+DATA/orcl/datafile/data2.265.860024375         150
+DATA/orcl/datafile/index1.353.860024387        100
+DATA/orcl/datafile/index2.354.860024397        100

Revisando el spfile a copiar hacia el ambiente de pruebas:
SQL> select name from v$database;
 
NAME
---------
ORCL
 
SQL> show parameter spfile
 
NAME      TYPE        VALUE
-------   ----------- ----------------------------------------------------
spfile    string      /u01/app/oracle/product/11.2/db_1/dbs/spfileorcl.ora
 

Copiando el spfile hacia el ambiente de pruebas:
[oracle@prod ~]$ scp /u01/app/oracle/product/11.2/db_1/dbs/spfileorcl.ora 
oracle@dev.oraworld.com:/u01/app/oracle/product/11.2/db_1/dbs/
 
oracle@dev.oraworld.com's password: 
 
spfileorcl.ora                             100% 3584     3.5KB/s   00:00:01    
 

Ambiente de pruebas
El spfile fue transferido correctamente:
[oracle@dev dbs]$ pwd
/u01/app/oracle/product/11.2/db_1/dbs
 
[oracle@dev dbs]$ ls -ltr spfileorcl.ora 
-rw-r----- 1 oracle oinstall 3584 Oct 31 22:55 spfileorcl.ora
 
[oracle@dev dbs]$ echo $ORACLE_SID
orcl
 

Creando el ambiente de pruebas:
[oracle@dev dbs]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 31 22:57:20 2014
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
Connected to an idle instance.
 
SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area  1068937216 bytes
Fixed Size                   2260088 bytes
Variable Size              578814856 bytes
Database Buffers           482344960 bytes
Redo Buffers                 5517312 bytes
 
SQL> 
 

Revisando los parámetros:
SQL> show parameters db_create
 
NAME                                     TYPE     VALUE
------------------------------------  ----------  ------------
db_create_file_dest                     string    +DATA
db_create_online_log_dest_1             string    +DATA
db_create_online_log_dest_2             string
db_create_online_log_dest_3             string
db_create_online_log_dest_4             string
db_create_online_log_dest_5             string
 

Ajustando el parámetro controlfile:
SQL> alter system set control_files='+DATA' scope=spfile;
System altered.
 
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
 
SQL> startup nomount;
ORACLE instance started.
 
Total System Global Area  1068937216 bytes
Fixed Size                   2260088 bytes
Variable Size              587203464 bytes
Database Buffers           473956352 bytes
Redo Buffers                 5517312 bytes
SQL> 
 

Creando la base de datos de pruebas:
SQL> CREATE DATABASE orcl
USER SYS IDENTIFIED BY manager1
USER SYSTEM IDENTIFIED BY manager1
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE temp
UNDO TABLESPACE undotbs1
DEFAULT TABLESPACE users;
 
Database created.

Ejecutando los scripts post-creación de base de datos:
SQL> show user
USER is "SYS"
 
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
 
SQL> conn system/manager1
Connected.
 
SQL> show user
USER is "SYSTEM"
 
SQL> @?/sqlplus/admin/pupbld.sql
 
SQL> create directory oracle as '/home/oracle';
Directory created.
 
SQL> create user oraworld identified by oraworld;
User created.
 
SQL> grant imp_full_database to oraworld;
Grant succeeded.
 
SQL> grant connect, resource to oraworld;
Grant succeeded.
 
SQL> grant read,write on directory oracle to oraworld;
Grant succeeded.
 
SQL> alter user oraworld quota unlimited on users;
User altered.
 

Especificando un porcentaje de los datos para ser extraídos usando la herramienta expdp del utilitario data pump.
Como puedes ver, 8,180 filas es el 10% de las filas en la tabla T1, recuerda que la tabla tiene actualmente 881,000 filas.
[oracle@prod ~]$ expdp oraworld/oraworld sample=10 full=y directory=oracle 
dumpfile=expdp_10pct.dmp log=expdp_10pct.log
 
Processing object type DATABASE_EXPORT/AUDIT
. . exported "ORAWORLD"."T1"                             8.180 MB   87693 rows 
. . exported "ORAWORLD"."T2"                             8.242 MB   88344 rows 
 
 
[oracle@prod ~]$ scp expdp_10pct.dmp oracle@dev.oraworld.com:/home/oracle
oracle@dev.oraworld.com's password: 
 
expdp_10pct.dmp                                          100%   20MB  20.0MB/s   00:01    
 

Importando los datos desde el ambiente de producción hacia nuestro ambiente de pruebas indicando el porcentaje con que se crearán todas las extensiones y los datafiles físicos. En este ejemplo estamos usando el 10%.

[oracle@dev ~]$  impdp oraworld/oraworld transform=pctspace:10 directory=oracle 
dumpfile=expdp_10pct.dmp log=impdp_10pct.log TABLE_EXISTS_ACTION=SKIP
 
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "ORAWORLD"."T1"                             8.180 MB   87693 rows 
. . imported "ORAWORLD"."T2"                             8.242 MB   88344 rows 
 

El parámetro SAMPLE no especifica una cantidad exacta de datos, como habíamos indicado es una probabilidad por lo que nuestro resultado si bien es muy acertado podría variar en pequeñas proporciones.
Actualizando las estadísticas del esquema OraWorld:
SQL> exec dbms_stats.gather_schema_stats('ORAWORLD');
PL/SQL procedure successfully completed.
 

Verificando el 10% de los datos y el 10% en el tamaño de los datafiles:
SQL> select segment_name, 
            segment_type, 
            tablespace_name, 
            bytes/1024/1024 MB_Size 
     from dba_segments 
     where owner='ORAWORLD';
 
SEGMENT_NAME SEGMENT_TYPE       TABLESPACE      MB_SIZE
------------ ------------------ ---------- ------------
T1           TABLE                DATA1              10 
T2           TABLE                DATA2              10 
IND_T1       INDEX                INDEX1              2 
IND_T2       INDEX                INDEX2              2 
 
 
SQL> select table_name, num_rows from dba_tables where owner='ORAWORLD';
 
TABLE_NAME                        NUM_ROWS
------------------------------   ----------
T2                                    88344 
T1                                    87693
 

Cómo puedes ver también las estructuras físicas fueron creadas con el 10% del tamaño con que fueron creadas en el ambiente de producción:
SQL> select file_name, 
            bytes/1024/1024 MB_Size 
     from dba_data_files 
     where tablespace_name in ('DATA1','DATA2','INDEX1','INDEX2');
 
FILE_NAME                                   MB_SIZE
---------------------------------------- ----------
+DATA/orcl/datafile/data1.281.862448977          15 
+DATA/orcl/datafile/data2.282.862448977          15 
+DATA/orcl/datafile/index1.279.862448979         10 
+DATA/orcl/datafile/index2.280.862448979         10
 

Un error muy frecuente
Para evitar cualquier problema mientras estés creando tu ambiente de pruebas queremos que estés consiente de un error muy común cuando se trabaja con opciones como SAMPLE y PCTSPACE, algunas veces puedes llegar a ver que tus datafiles no se crearon con el porcentaje que indicaste en la operación de importación, esto pasa cuando tu realizaste varias operaciones “alter database datafile … resize” sobre los datafiles en el ambiente de producción, dichos operaciones de redimensionado no son replicadas en el archivo resultante de la exportación por lo que los datafiles y sus extensiones serán creadas respetando solamente el tamaño original y el valor MAXSIZE. En este caso debes modificar el valor “maxsize” de los datafiles o también puedes crear todos los tablespaces con los tamaños que tu prefieras antes de realizar la importación. Te mostraremos mas detalles sobre esto con un ejemplo:
Ambiente de producción:
SQL> select file_name, bytes/1024/1024 MB_Size 
     from dba_data_files 
     where tablespace_name in ('DATA1','DATA2','INDEX1','INDEX2');
 
FILE_NAME                                             MB_SIZE
-------------------------------------------------- ----------
+DATA/orcl/datafile/data1.342.860024365                   150 
+DATA/orcl/datafile/data2.265.860024375                   150
+DATA/orcl/datafile/index1.353.860024387                  100
+DATA/orcl/datafile/index2.354.860024397                  100
 
SQL> alter database datafile '+DATA/orcl/datafile/data1.342.860024365' resize 200M;
 
Database altered.
 
[oracle@prod ~]$ expdp oraworld/oraworld sample=10 full=y directory=oracle 
dumpfile=expdp_10pct.dmp log=expdp_10pct.log
Processing object type DATABASE_EXPORT/AUDIT
. . exported "ORAWORLD"."T1"                             8.162 MB   87515 rows
. . exported "ORAWORLD"."T2"                             8.183 MB   87745 rows
 
[oracle@prod ~]$  scp expdp_10pct.dmp oracle@dev.oraworld.com:/home/oracle
oracle@dev.oraworld.com's password: 
expdp_10pct.dmp                                          100%   20MB  20.0MB/s   00:01    
 

Ambiente de desarrollo:
[oracle@dev ~]$  impdp oraworld/oraworld transform=pctspace:10 directory=oracle 
dumpfile=expdp_10pct.dmp log=impdp_10pct.log sqlfile=sqlfile.txt
 
[oracle@dev ~]$ cat sqlfile.txt |grep -A 5 DATA1
 
CREATE TABLESPACE "DATA1" DATAFILE 
SIZE 15728640 
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT 
NOCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO;
 
 ¿Por qué 15728640 bytes (15M)? Nuestra base de datos de producción tiene 200MB actualmente porque nosotros redimensionamos dicho datafile, pero como vemos el tamaño actual no fue efectuado. El valor de 10% debería de ser 20M.
Desafortunadamente la tarea de exportación no incluye las sentencias “ALTER DATABASE DATAFILE … RESIZE” que se realizaron después de la creación de los tablespaces. La tarea de exportación únicamente respeta el valor MAXSIZE de cada datafile. Pero algunas veces nosotros no especificamos dicho valor MAXSIZE, en lugar a nosotros nos gusta redimensionar cada datafile cada vez que se necesite (on demand).
Si nosotros miramos dentro del DDL del tablespace ‘DATA1’ nosotros veremos lo siguiente:
SQL> select dbms_metadata.get_ddl('TABLESPACE','DATA1') from dual;
 
DBMS_METADATA.GET_DDL('TABLESPACE','DATA1')
-----------------------------------------------------------------------------
 
CREATE TABLESPACE "DATA1" DATAFILE
SIZE 157286400
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
OCOMPRESS  SEGMENT SPACE MANAGEMENT AUTO 
ALTER DATABASE DATAFILE
'+DATA/orcl/datafile/data1.342.860024365' RESIZE 209715200 

La parte de  "ALTER DATABASE DATAFILE .. RESIZE" es la parte que no es incluida en el archivo resultante de la operación de exportación pero cono puedes ver el valor MAXSIZE no fue especificado cuando nosotros creamos el tablespace:
SQL> select file_name, maxbytes 
     from dba_data_files 
     where tablespace_name in ('DATA1','DATA2','INDEX1','INDEX2');
 
FILE_NAME                                  MAXBYTES
---------------------------------------- ----------
+DATA/orcl/datafile/data1.342.860024365           0
+DATA/orcl/datafile/data2.265.860024375           0
+DATA/orcl/datafile/index1.353.860024387          0
+DATA/orcl/datafile/index2.354.860024397          0

Esta es la razón de porque nosotros vimos que el tamaño “15MB” como el 10% del tablespace DATA1 cuando el valor correcto debería ser 20M porque el tamaño actual del datafile en el ambiente de producción es de 200MB. Este comportamiento es el mismo para todos los datafiles que nosotros hemos redimensionado a lo largo de la vida de la base de datos.
¿Cómo solucionarlo?
Opción 1:
  • Pre-crear los tablespaces en la base de datos de pruebas y ejecutar otra vez la tarea de importación. Puedes usar la siguiente sentencia para extraer los DDL’s de los tablespaces:    
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)  
from dba_tablespaces;
 
Opción 2:
  • En la base de datos fuente, incrementar el valor de MAXSIZE de la clausula AUTOEXTEND hasta el tamaño actual de dicho datafile. Puedes usar la siguiente sentencia para esto:    
select 'ALTER DATABASE DATAFILE '''||file_name||''' AUTOEXTEND ON MAXSIZE '|| bytes||';' 
from dba_data_files where maxbytes < bytes;
 
  • Realice una exportación completa de los datos usando data pump
  • Recree la base de datos de pruebas usando el archivo de exportación que se realizó en el paso 2. Esto debe hacerse con impdp del utilitario data pump.

Referencias:
  • How To Export Only a Percentage Of Data In Tables Using The Datapump SAMPLE Parameter (Doc ID 1422064.1)
  • How To Specify A Percentage Of Data To Be Exported Using DataPump Export (Doc ID 1385364.1)
  • "ORA-02494: invalid or missing maximum file size in MAXSIZE clause" during IMPDP (Doc ID 1670695.1)
  • Oracle Database 12c Backup and Recovery Survival Guide - Franciscy Muñoz Alvarez