How to take export backup or create multiple dump file on multiple file system

Oracle's Export utility (expdp) is a useful part of an overall backup strategy.  Consider 300GB as database size , if we do not have FS of 300 GB , we can not take whole dump on single FS , So what to do , no worries oracle is smart enough , we can create multiple dump files at different location as per your convenient dump file size.

First of all you have to create multiple directories as per space available on FS.

SQL > create directory DATAPUMP as 'data01/export';

Directory created.

SQL > create directory DATA_DUMP_1 as '/data02/export;

Directory created.

SQL > create directory DATA_DUMP_2 as '/data03/export;

Directory created.

13:36:31 SQL> select * from dba_directories;

OWNER      DIRECTORY_NAME       DIRECTORY_PATH

---------- -------------------- -------------------

SYS        DATA_DUMP             /data01/export

SYS        DATA_DUMP_1           /data02/export

SYS        DATA_DUMP_2           /data03/export

Now fire below command

Without parallel :

nohup expdp \"/ AS SYSDBA \" dumpfile=DATA_DUMP:expdp_DATA_DUMP_%U.dmp,DATA_DUMP_1:expdp_DATA_DUMP_1_%U.dmp,DATA_DUMP_2:expdp_DATA_DUMP_2_%U.dmp, logfile=expdp_log_30Aug2017.log parallel=3 filesize=21474836480 schemas=SCOTT  compression=all &

With Parallel :

nohup expdp \"/ AS SYSDBA \" dumpfile=DATA_DUMP:expdp_DATA_DUMP_%U.dmp,DATA_DUMP_1:expdp_DATA_DUMP_1_%U.dmp,DATA_DUMP_2:expdp_DATA_DUMP_2_%U.dmp, logfile=expdp_log_30Aug2017.log parallel=3 filesize=21474836480 schemas=SCOTT compression=all parallel=2 &

Note :- Compression key word will not work for oracle 10g or less

For oracle 10g (10.2.0.3.0)  :-

Compression have  below parameter.

• metadata_only
• none

For oracle 11g & 12c 

Compression have  below parameter.

• all
• data_only
• metadata_only
• none

Look at the export command carefully we have not mentioned directory parameter.

We have provided directory name in dumpfile only like 

DATA_DUMP:expdp_DATA_DUMP_%U.dmp

DATA_DUMP_1:expdp_DATA_DUMP_1_%U.dmp

DATA_DUMP_2:expdp_DATA_DUMP_2_%U.dmp   

separated by comma's 

%U is used to create multiple files in same directory

eg :- 

expdp_DATA_DUMP_01.dmp

expdp_DATA_DUMP_02.dmp

expdp_DATA_DUMP_1_01.dmp

expdp_DATA_DUMP_1_02.dmp

expdp_DATA_DUMP_2_01.dmp 

expdp_DATA_DUMP_2_02.dmp  and so on

We can also provide filesize parameter depending on server File System. In my case I have given 20GB. Which means maximum size of any dumpfile will be 20 GB.

OUTPUT:-

Here in my case I have manually zipped dumpfiles thats why size is showing less than 20GB .

Thats it :)