It is tvery common to export dump file to the standard file system(Mount points). However, we can export the dump files to ASM diskgroup also.
In this article, we will show the steps for this .
DEMO
1.Create a directory pointing to ASM diskgroup( for dump files)
SQL> create directory SOURCE_DUMP as '+NEWTST/TESTDB2/TEMPFILE'; Directory created. SQL> grant read,write on directory SOURCE_DUMP to public; Grant succeeded.
2.Create a directory pointing to normal filesystem ( for logfiles)
The logfile will be created in normal file system only.
SQL> create directory EXPLOG as '/export/home/oracle'; Directory created.
3. Run expdp command:
SYNTAX:
expdp dumpfile=dumpfile.dmp logfile={LOGFILE_DIRECTORY}:logfile.log directory={DUMP_DIRECTORY} tables=TABLE_NAME
expdp dumpfile=test.dmp logfile=EXPLOG:test.log directory=SOURCE_DUMP tables=dbatest.EMPTAB exclude=statistics
expdp dumpfile=test.dmp logfile=EXPLOG:test.log directory=SOURCE_DUMP tables=dbatest.EMPTAB exclude=statistics Export: Release 12.1.0.2.0 - Production on Wed Feb 8 10:07:54 2017 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA dumpfile=test.dmp logfile=EXPLOG:test.log directory=SOURCE_DUMP tables=dbatest.EMPTAB exclude=statistics Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 11 MB Processing object type TABLE_EXPORT/TABLE/TABLE . . exported "DBATEST"."EMPTAB" 8.691 MB 81753 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: +NEWTST/TESTDB2/tempfile/test.dmp ---- >>> Dump file created in ASM DISKGROUP. Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Feb 8 10:08:13 2017 elapsed 0 00:00:15
NOTE – > Failing to mention the LOG FILE DIRECTORY, will result in below error.
expdp dumpfile=test.dmp logfile=test.log directory=SOURCE_DUMP tables=dbatest.EMPTAB exclude=statistics
Export: Release 12.1.0.2.0 – Production on Wed Feb 8 10:06:12 2017
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
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