EXPDP With Multiple Directories

EXPDP with multiple dump file on multiple directories

Sometimes we have to take a logical backup of our database using EXPDP utility. Consider database size to be 400GB. We cannot take backup of 400 gb database to a single FS , we have to take it in multiple FS as one FS donot have that much space. 

In such Situation we use the multiple directories feature of EXPDP utility. Using this feature we can create dumpfile in multiple directories and distribute our logical backup to different directories.

Example :-

Consider two directories named DATAPUMP and TESTING in Oracle Database :-

SQL > create or replace directory DATAPUMP as '/amit/datapump';

Directory created.

SQL > create or replace directory TESTING as '/amit/testing';

Directory created.

SQL >  select * from dba_directories;

OWNER                     DIRECTORY_NAME                      DIRECTORY_PATH
------------------------- ----------------------------------- ----------------------------------------
SYS                               TESTING                                  /amit/testing
SYS                               DATAPUMP                            /amit/datapump

Now take logical backup using multiple directories.

$ expdp amit/amit dumpfile=datapump:expdp_datapump_%U.dmp,TESTING:expdp_testing_%U.dmp compression=all parallel=2 &

We can check that multiple dumpfiles will be created.

bash-3.2$ pwd

/amit/datapump

bash-3.2$ ls -ltr|tail

-rw-r--r--    1 oracle   oinstall      89785 Aug 30 19:45 import.log

-rw-r-----    1 oracle   oinstall    7680000 Aug 31 02:34 expdp_datapump_01.dmp

bash-3.2$ cd -

/amit/testing

bash-3.2$ ls -ltr|tail

total 731952

-rw-r--r--    1 oracle   oinstall     185540 May 31 17:40 export.log

-rw-r-----    1 oracle   oinstall  229363712 Aug 31 02:34 expdp_testing_01.dmp

 

Parallel parameter must be used while using multiple directories, otherwise expdp will not fail but it will write to only first directory. Number of directories used must be equal to parallel parameter then only all directories will be used for writing.

%U is used to create multiple files in same directory. Like expdp_datapump_%U.dmp will create expdp_datapump_01.dmp, expdp_datapump_02.dmp. If we want using %U expdp write to multiple files in parallel mode then we have to use parallel parameter. 

We can also set FILESIZE parameter while using EXPDP, it will not create file more than the filesize allocated.

$expdp amit/amit dumpfile=datapump:expdp_datapump_%U.dmp,TESTING:expdp_testing_%U.dmp compression=all parallel=2 filesize=2g &

Above example will create dumpfiles like expdp_datapump_01.dmp, expdp_testing_01.dmp, expdp_datapump_02.dmp, expdp_testing_02.dmp. All dumpfiles have filesize of 2g.