Data Pump in 11g ; New parameters

Data Pump in 11g
This article is an overview of some of the new Data pump parameters in 11g Oracle Database.

ENCRYPTION

Oracle Database 11g presents Data Pump Encryption parameters. This is a mechanism to encrypt the metadata only or the Data only or the columns only or all (metadata & data & Columns).
To use the encryption you should specify either the ENCRYPTION or the ENCRYPTION_PASSWORD parameter.
Data pump encryption is specified by the encryption parameter, the algorithm of the encryption and the mode of the encryption.

ENCRYPTION Parameter:
Following is an example:
C:\Documents and Settings\welkhlifi>expdp full=yes userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:encrypt.db.dmp ENCRYPTION=metadata_only ENCRYPTION_PASSWORD=WISSEM

ENCRYPTION_ALGORITHM Parameter:
This parameter is to specify the cryptographic algorithm: {AES128 | AES192 | AES256}

C:\Documents and Settings\welkhlifi>expdp full=yes userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:encrypt.db.dmp ENCRYPTION=metadata_only ENCRYPTION_PASSWORD=WISSEM ENCRYPTION_ALGORITHM=AES256

ENCRYPTION_MODE Parameter:
It Specifies the type of security to use when encryption and decryption are performed.
ENCRYPTION_MODE = {DUAL | PASSWORD | TRANSPARENT}

C:\Documents and Settings\welkhlifi>expdp full=yes userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:encrypt.db.dmp ENCRYPTION=metadata_only ENCRYPTION_PASSWORD=WISSEM ENCRYPTION_ALGORITHM=AES256 ENCRYPTION_MODE=dual

COMPRESSION

Oracle Database 11g presents Data Pump compression. This is a mechanism to compress both metadata and Data.

Following is how to use it:
C:\Documents and Settings\welkhlifi>expdp full=yes userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:full.db.dmp compression=ALL

Export: Release 11.1.0.7.0 – Production on Viernes, 20 Agosto, 2010 14:48:56
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando “SYS”.”SYS_EXPORT_FULL_01″: full=yes userid=”/******** AS SYSDBA” dumpfile=DATA_PUMP_DIR:full.db1.dmp compression=ALL
Estimaci¾n en curso mediante el mÚtodo BLOCKS…
Procesando el tipo de objeto DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Estimaci¾n total mediante el mÚtodo BLOCKS: 152.2 MB
Procesando el tipo de objeto DATABASE_EXPORT/TABLESPACE
Procesando el tipo de objeto DATABASE_EXPORT/PROFILE
……
Procesando el tipo de objeto DATABASE_EXPORT/SYS_USER/USER
La tabla maestra “SYS”.”SYS_EXPORT_FULL_01″ se ha cargado/descargado correctamente
******************************************************************************
El juego de archivos de volcado para SYS.SYS_EXPORT_FULL_01 es:
F:\APP\ORAWISS\ADMIN\ORAWISS\DPDUMP\FULL.DB.DMP
El trabajo “SYS”.”SYS_EXPORT_FULL_01″ ha terminado correctamente en 14:46:43

REUSE DUMP FILE

When the export attempts to write to a dump file that already exists. You will have following error:
C:\Documents and Settings\welkhlifi>expdp full=yes userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:full.db.dmp compression=ALL

Export: Release 11.1.0.7.0 – Production on Viernes, 20 Agosto, 2010 14:47:47
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: valor de argumento no vßlido
ORA-39000: especificaci¾n de archivo de volcado err¾nea
ORA-31641: no se ha podido crear el archivo de volcado “F:\app\orawiss\admin\orawiss\dpdump\full.db.dmp”
ORA-27038: el archivo creado ya existe
OSD-04010: opci┐CREATE> especificada; el archivo ya existe
To fix this you should use the option REUSE_DUMPFILES=y; this is a new parameter that allow the overwrite of the existing dump file.

C:\Documents and Settings\welkhlifi>expdp full=yes userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:full.db.dmp compression=ALL REUSE_DUMPFILES=y

Export: Release 11.1.0.7.0 – Production on Viernes, 20 Agosto, 2010 14:51:36
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando “SYS”.”SYS_EXPORT_FULL_02″: full=yes userid=”/******** AS SYSDBA” dumpfile=DATA_PUMP_DIR:full.db.dmp compression=ALL REUSE_DUMPFILES=y
Estimaci¾n en curso mediante el mÚtodo BLOCKS…
Procesando el tipo de objeto DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
…..
******************************************************************************
El juego de archivos de volcado para SYS.SYS_EXPORT_FULL_02 es:
F:\APP\ORAWISS\ADMIN\ORAWISS\DPDUMP\FULL.DB.DMP
El trabajo “SYS”.”SYS_EXPORT_FULL_02″ ha terminado correctamente en 15:02:01
C:\Documents and Settings\welkhlifi>

REMAPPING DATA

The REMAP_DATA parameter allows you to remap a column value as a parameter and return a modified version of the data using a packaged function.

Following is how to use it:
— Function used by the remap created under sys user for example:
Create or replace package Translare_package
as
function modify_salary ( p_salary NUMBER) return NUMBER;
end;
/
Create or replace package body Translare_package as
function modify_salary (p_salary NUMBER) return NUMBER
as
v_return NUMBER;
begin
v_return:= p_salary * 2;
return v_return;
end;
end;
/

C:\Documents and Settings\welkhlifi>expdp userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:hr.salary.dmp compression=ALL REUSE_DUMPFILES=y TABLES=hr.employees REMAP_DATA=hr.employees.salary:SYS.Translare_package.modify_salary

Export: Release 11.1.0.7.0 – Production on Viernes, 20 Agosto, 2010 15:18:27
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Iniciando “SYS”.”SYS_EXPORT_TABLE_01″: userid=”/******** AS SYSDBA” dumpfile=DA
TA_PUMP_DIR:hr.salary.dmp compression=ALL REUSE_DUMPFILES=y TABLES=hr.employees
REMAP_DATA=hr.employees.salary:SYS.Translare_package.modify_salary
Estimaci¾n en curso mediante el mÚtodo BLOCKS…
Procesando el tipo de objeto TABLE_EXPORT/TABLE/TABLE_DATA
Estimaci¾n total mediante el mÚtodo BLOCKS: 64 KB
Procesando el tipo de objeto TABLE_EXPORT/TABLE/TABLE
Procesando el tipo de objeto TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Procesando el tipo de objeto TABLE_EXPORT/TABLE/INDEX/INDEX
Procesando el tipo de objeto TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Procesando el tipo de objeto TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Procesando el tipo de objeto TABLE_EXPORT/TABLE/COMMENT
Procesando el tipo de objeto TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Procesando el tipo de objeto TABLE_EXPORT/TABLE/TRIGGER
Procesando el tipo de objeto TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . “HR”.”EMPLOYEES” 8.765 KB 107 filas exportadas
La tabla maestra “SYS”.”SYS_EXPORT_TABLE_01″ se ha cargado/descargado correctame
nte
******************************************************************************
El juego de archivos de volcado para SYS.SYS_EXPORT_TABLE_01 es:
F:\APP\ORAWISS\ADMIN\ORAWISS\DPDUMP\HR.SALARY.DMP
El trabajo “SYS”.”SYS_EXPORT_TABLE_01″ ha terminado correctamente en 15:18:44
C:\Documents and Settings\welkhlifi>

RENAMING TABLES DURING EXPORT OR IMPORT

You can rename the table during the import process.
Following is how to use it:

C:\Documents and Settings\welkhlifi>impdp userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:full.db.dmp REMAP_TABLE=hr.employees:employees2
Import: Release 11.1.0.7.0 – Production on Viernes, 20 Agosto, 2010 15:23:18
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Conectado a: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
….

IGNORE NO DEFERRED CONSTRAINTS

Set the DATA options parameter to SKIP_CONSTRAINT_ERRORS will cause the import program to skip errors generated by the no deferred database constraints.
Following is how to use it:

C:\Documents and Settings\welkhlifi>impdp userid=”‘/ as sysdba'” dumpfile=DATA_PUMP_DIR:full.db.dmp tables=HR.JOBS data_options=SKIP_CONSTRAINT_ERRORS

Nice Reading
Wissem