Tuesday, August 10, 2010

expdp and impdp with ASM

ASM supports dump files created with expdp and impdp. Setup is similar to conventional expdp using a OS file systems, except dump file directory must be created inside the diskgroup if "root" diskgroup location is not used the location for the dumpfiles.
Create directory with asmcmd
. oraenv
ORACLE_SID = [clusdb1] ? +ASM1
+ASM1]$ asmcmd
ASMCMD> ls
CLUSTERDG/
DATA/
FLASH/        
mkdir data/dpump
ASMCMD> ls data
CLUSDB/
dpump/
Create a directory object and grant permission to user
SQL> create directory asmdumpdir as '+DATA/dpump';
Directory created.
grant read,write on directory asmdumpdir to asanga;
Grant succeeded. 
Log files created during expdp cannot be stored inside ASM, for log files a directory object that uses OS file system location must be given. If not following error will be thrown
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
Therefore another directory object must be created for logfile location (or nologfile option could be used).
Execute the expdp command as
expdp asanga/*** directory=asmdumpdir dumpfile=asanga.dmp schemas=asanga logfile=logdir:asa.log
Export: Release 11.2.0.1.0 - Production on Tue Aug 10 11:27:25 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=asmdumpdir dumpfile=asanga.dmp schemas=asanga logfile=logdir:asa.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 75.18 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE           
.
.
.

ASMCMD> ls data/dpump
asanga.dmp
View the created dump file in ASM using asmcmd. Similary import could also be done with
impdp asanga/*** directory=asmdumpdir dumpfile=asanga.dmp logfile=logdir:asmlog tables=city

Import: Release 11.2.0.1.0 - Production on Tue Aug 10 11:30:11 2010

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "ASANGA"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ASANGA"."SYS_IMPORT_TABLE_01":  asanga/******** directory=asmdumpdir dumpfile=asanga.dmp logfile=logdir:asmlog tables=city
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA  
.
.
To transfer a dumpfile created in ASM dbms_file_transfer.copy_file could be used. Code below copies the dump file created in asm to log file directory
exec dbms_file_transfer.copy_file('asmdumpdir','asanga.dmp','logdir','copydumpfile.dmp');
ASM-to-ASM transfer