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.
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
