Creando y restaurando DUMPs (backups lógicos) en Oracle Database 11g (exp e imp)

Durante esta publicación, demostraré cómo crear y restaurar copias de seguridad en la base de datos Oracle utilizando las herramientas exp e imp, que le permiten generar copias de seguridad completas de un propietario y restaurarlas en otro servidor.

Configuración de variables de entorno en Windows

SET ORACLE_SID=ORCLTESTE
SET ORACLE_HOME=C:\oracle\product\11.2.0\dbhome_1
SET NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Definición de variables de entorno en Linux

EXPORT ORACLE_SID=ORCLTESTE
EXPORT ORACLE_HOME=/oracle/product/11.1.0/db_1/
EXPORT NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Parámetros de exportación

Aquí demostraré cómo exportar los datos de la base de datos a un archivo de volcado, que puede usarse para restaurar los datos y replicar la base de datos en otra instancia/servidor.

Aquí hay algunos parámetros:

• archivo = file.dmp: Define el nombre del archivo de volcado que se generará. Si no se informa, el nombre del archivo será “EXPDAT.DMP”
• registro = file.log: Define el nombre del archivo de registro que se generará. Si no se informa, no se generará ningún registro.
• dueño = ,: Define el nombre del propietario que se exportará
• lleno = y: Indica si se debe exportar toda la base de datos, con usuarios, espacios de tablas, subvenciones, etc.
• subvenciones = y: Define si se exportarán los privilegios otorgados por el propietario
• índices =y: define si los objetos de índice se exportarán
• comprimir =y: Define si el archivo de volcado se comprimirá para reducir el tamaño del archivo final.
• coherente = y: define si Oracle evitará que los datos modificados durante la exportación se exporten en el archivo de volcado. Esta operación utilizará el área de deshacer para almacenar estos cambios y aplicarlos a la base de datos después de la exportación.
• restricciones = y: Define si las restricciones PK y FK se exportarán en el archivo de volcado.
• directo = y: Define si los datos exportados omitirán la capa de análisis de Oracle (capa de procesamiento de comandos SQL - búfer de evaluación), que valida la sintaxis de los comandos y acelera la exportación (aproximadamente un 10% generalmente).
• comentario = 0: Define cuántos registros la utilidad exp mostrará el progreso en la pantalla. 0 está deshabilitado.
• tamaño de archivo = 10 MB: establece el límite de tamaño por archivo. Si se especifican 2 MB, por ejemplo, exp dividirá el archivo de volcado en archivos de 2 MB, que deben ingresarse en el parámetro ARCHIVO. Si no se informa, el archivo de volcado constará de solo 1 archivo.
• flashback_scn: Permite generar la exportación con los datos a través del SCN, donde obtienes el valor actual de la base de datos y garantizas que los datos no cambiarán después de la exportación. Para conocer el valor actual del SCN, simplemente ejecute la consulta select current_scn from v$database;
• flashback_time: Permite generar la exportación con datos de una fecha específica.
• mesas = regiones: Permite definir las tablas que se exportarán
• consulta = “condición”: Le permite definir una condición para exportar los datos. Se debe informar el parámetro TABLES y la condición debe ser aplicable a todas estas tablas.
• parfile = “ruta del archivo”: Le permite definir un archivo de parámetros para exportar, donde cada parámetro debe estar en una línea y la utilidad leerá este archivo y ejecutará el comando exp usando estos parámetros.

  Ejemplo de archivo Parfile:
  
userid=usuario/senha
file=exp_HR_20150319.dmp
log=exp_HR_20150319.log
owner=hr
full=n
buffer=409600000
rows=n
statistics=none
direct=y
consistent=y


• estadística = none: Permite definir si se deben exportar o no las estadísticas que genera la base de datos para el rendimiento. Las opciones son ninguna, calcule y estime.
• desencadenantes = y: define si los activadores de tabla deben exportarse
• espacios de tabla = tablespace1,tablespace2: si se informa, este parámetro hace que exp genere datos de todas las tablas que están asignadas en los espacios de tablas informados.
• buffer = 4096: Define el tamaño (en bytes) del buffer utilizado por la exportación para recuperar las líneas (Solo se aplica cuando direct=n)
• ayuda =y: Información de ayuda y documentación

Exportar los datos – Generar el archivo de respaldo

# Gerando backup de um owner, sem dados, apenas tabelas (rows=n)
exp usuario/senha file=exp_HR_20150319.dmp log=exp_HR_20150319.log owner=hr buffer=409600000 rows=n

# Gerando backup dos owners HR e SYS, com dados (rows=y)
exp usuario/senha file=exp_HR_20150319.dmp owner=hr,sys full=n rows=y statistics=none direct=y consistent=y

# Gerando backup de todos os objetos de todos os owners da instância (full=y)
exp usuario/senha file=exp_HR_20150319.dmp full=y rows=y statistics=none direct=y consistent=y

# Gerando backup de todos os objetos de um owner, em 3 arquivos de no máximo 10 MB
exp usuario/senha file=exp_HR_20150319_1.dmp,HR_20150319_2.dmp,HR_20150319_3.dmp filesize=10MB

# Gerando backup dos dados a partir de uma query
exp usuario/senha file=exp_HR_20150319.dmp tables=hr.jobs query=\"where max_salary <= 10000\"

# Gerando backup dos dados usando um arquivo de configuração (PARFILE)
exp parfile="C:\parfile.dat"

# Como eu costumo utilizar - Crio um arquivo parfile.dat com o userid
exp parfile="C:\parfile.dat" file=exp_<owner>_<instancia>.dmp log=exp_<owner>_<instancia>.log buffer=409600000 statistics=none direct=y consistent=y

Importar parámetros

Aquí demostraré cómo importar datos de una base de datos desde un archivo de volcado.

Aquí hay algunos parámetros, además de los mostrados arriba para exportar, que también son aplicables para importar:

• cometer = n: Si commit=y, imp realizará una confirmación después de cada bloque de inserción de datos. De lo contrario, la confirmación se realizará para cada tabla importada.
• compilar = y: Define si imp compilará los procedimientos, funciones y disparadores después de su creación.
• de usuario = usuario: Identifica el(los) propietario(s) de origen de los datos que se exportaron
• usuario = usuario: Identifica el(los) propietario(s) de destino donde se importarán los datos. Los propietarios deben existir en la instancia de destino antes de ejecutar la importación.
• lleno =y: Importa el archivo de volcado completo, sin filtros de objetos
• ignorar = y: Define si imp ignorará los errores al crear objetos o detendrá y abortará la operación en caso de error.
• espectáculo =y: define si imp simplemente enumerará el contenido del volcado en lugar de importarlo.
• estadística = valor: Define la forma en que el diablillo importará las estadísticas recopiladas del banco a Export. Las opciones posibles son: Siempre (Importa siempre estadísticas), Ninguno (No importa ni recalcula estadísticas), Seguro (Importa estadísticas si no son cuestionables. En caso contrario, recalcula) o Recalcular (No importa estadísticas, pero las recalcula)

Importación de datos al nuevo propietario: preparación del entorno

Antes de realizar la importación, necesitaremos crear los usuarios y los espacios de tabla utilizados por ese usuario en la instancia de destino.

Tenga en cuenta que el tamaño de los espacios de tabla y los archivos de datos debe calcularse para que haya suficiente espacio para recibir los datos de origen. Puede obtener estos datos replicando los mismos valores de los espacios de tabla de origen o activando la opción de extensión automática del archivo de datos, lo que hace que aumente de tamaño según sea necesario. Además, establecí la cuota del usuario en el espacio de tabla creado como ilimitada, para que pueda asignar todo el espacio de tabla si es necesario.

Esta no es una buena práctica, ya que usted, como DBA, debe evaluar la asignación de espacio en el proceso de exportación/importación, pero para realizar pruebas es ideal.

Comandos utilizados:

create tablespace TS_novo_hr datafile 'C:\ORACLE\ORADATA\ORCLTESTE\novo_hr.dbf' size 10M;
create user novo_hr identified by teste default tablespace TS_novo_hr;
ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\ORCLTESTE\NOVO_HR.DBF' autoextend on;
alter user novo_hr quota unlimited on TS_novo_hr;

Importando datos al nuevo propietario

# Exemplo simples, importando do owner HR para NOVO_HR e gerando arquivo de log
imp dirceu/dirceu file=exp_HR_20150319.dmp log=imp_HR_20150319.log fromuser=hr touser=novo_hr

# Realizando o import sem trazer estruturas adicionais
imp dirceu/dirceu file=exp_HR_20150319.dmp log=imp_HR_20150319.log fromuser=hr touser=novo_hr grants=n commit=n ignore=y analyze=n constraints=n indexes=n

# Como eu costumo utilizar - Crio um arquivo parfile.dat com o userid
imp parfile="C:\parfile.dat" file=exp_<owner>_<instancia>.dmp buffer=409600000 log=imp_<owner>_<instancia>.dmp fromuser=<owner> touser=<owner> grants=y commit=n ignore=y analyze=n constraints=y

Importación de estructura y datos para el propietario existente

Una operación ampliamente utilizada para la replicación o creación de nuevos entornos, importar estructura y datos para un propietario existente consiste en eliminar todos los objetos debajo del propietario del entorno de destino y realizar la importación.

Eliminar toda la estructura de un propietario puede resultar complicado en ocasiones, por lo que muchos administradores de bases de datos prefieren utilizar el comando drop user <usuario> cascade; para eliminar el usuario y sus objetos de forma rápida y sencilla.

Particularmente no me gusta esta solución, ya que no tengo control sobre lo que se eliminará, y mucho menos un registro de esta operación. Para hacer esto, creé un script que realiza esta tarea y crea un nuevo script con comandos para eliminar todos los objetos del propietario y registra un registro con cada operación realizada (script adjunto al pie de página de la publicación):

Después de ejecutar el script generado, eliminando todos los objetos del propietario, puede importar normalmente, lo que recreará todos los objetos del propietario de acuerdo con lo que existe en el archivo de volcado. (Recuerde verificar el tamaño de los espacios de tablas y archivos de datos, como se mencionó anteriormente)

# Como eu costumo utilizar - Crio um arquivo parfile.dat com o userid
imp parfile="C:\parfile.dat" file=exp_<owner>_<instancia>.dmp buffer=409600000 log=imp_<owner>_<instancia>.dmp fromuser=<owner> touser=<owner> grants=n commit=n ignore=n analyze=n constraints=y

Importar solo datos para el propietario existente

Esta es una operación más compleja que el volcado de estructura y datos y tiene como objetivo actualizar los datos en un entorno, sin cambiar su estructura en el entorno de destino. Para lograr esto, necesitaremos eliminar todos los datos de las tablas, deshabilitar restricciones y activadores, eliminar secuencias (script adjunto al pie de página de la publicación).

Como sabe, si hay nuevos objetos en el entorno de origen en el que se generó la exportación de datos, estos objetos se crearán en la importación. Por lo tanto, necesitaremos crear un script para eliminar estos nuevos objetos que se pueden crear si se produce esta situación.

La forma más sencilla de hacerlo es utilizar el software. Sapo para Oráculo, que permite la comparación visual y la generación de guiones para adaptarlos a los entornos. Este script debe ser validado por usted para que solo maneje la eliminación de nuevos objetos creados (si los hay). Aquí no existe una receta fácil, ya que cada entorno y propietario requiere guiones personalizados para cada situación.

Si hay una diferencia entre los objetos, la importación de datos fallará. Usted, como DBA, debe alertar al solicitante que la importación no será posible o aplicar el script Toad para ecualizar los entornos, y después de la importación, aplicar el script Toad para devolver los cambios y mantener la estructura del entorno como estaba antes.

# Como eu costumo utilizar - Crio um arquivo parfile.dat com o userid
imp parfile="C:\parfile.dat" file=exp_<owner>_<instancia>.dmp buffer=409600000 log=imp_<owner>_<instancia>.dmp fromuser=<owner> touser=<owner> grants=n commit=n ignore=y analyze=n constraints=n

Algunos consejos para importar

• mostrar archivo de parámetros: Verifica la ubicación donde se almacenan los registros de transacciones (archivelog), para que pueda verificar si hay espacio disponible para almacenar los registros que generará la Importación. De lo contrario, fracasará.
• Verifique el tamaño del espacio de tabla UNDO para calcular si admitirá las operaciones UNDO que generará Importar
• Comprueba el tamaño del propietario y su objeto más grande. No debe ser mayor que el espacio de tabla de deshacer.
• Compruebe si hay restricciones deshabilitadas antes de Importar. Después de la importación, las restricciones pueden dejar de ser válidas o presentar errores de inconsistencia en los datos. Debe asegurarse de que estos errores ya existieran en la base de datos antes de la importación.
• Verifique y compare el tamaño de los espacios de tabla y archivos de datos de origen y de destino para asegurarse de que los datos no desborden su espacio de tabla o archivo de datos en la instancia de destino.
• Los guiones con este paso a paso. están disponibles aquí.

select sum(bytes)/1024/1024 || ' mb' from dba_segments where owner = 'HR';
select owner, sum(bytes)/1024/1024 || ' mb' from dba_segments where owner = 'HR' group by owner;
select distinct tablespace_name from dba_segments where owner = 'HR' order by tablespace_name;


@tablespace_tamanho
HR


@info_tablespace
undo


select owner, segment_name, segment_type, bytes/1024/1024 || ' MB' 
from dba_segments 
where bytes = (
	select max(bytes) 
	from dba_segments 
	where owner = 'HR'
	and segment_type not like '%LOB%'
) and owner = 'HR';

select owner, segment_name, segment_type, bytes/1024/1024 || ' MB' 
from dba_segments 
where bytes = (
	select max(bytes) 
	from dba_segments 
	where owner = 'HR'
	and segment_type like '%LOB%'
) and owner = 'HR';

show parameter archive

@free_asm

select * from dba_constraints where status = 'DISABLED' and owner = 'HR';
select * from v$instance;
select * from gv$instance;

Scripts utilizados para importar estructura y datos y solo datos.

Para obtener información técnica más detallada, consulte la Sitio web oficial de Oracle: exportación e importación.