How to Export ( EXPDP ) Oracle Database with Crontab Job and Place a Date in a Datapump Export File Name

Hi,

I will explain How to Export ( EXPDP ) Oracle Database with Crontab Job and Place a Date in a Datapump Export File Name in this article.

 

If you don’t know what is the Oracle Datapump, read the following articles before this.

https://ittutorial.org/oracle-data-pump-export-import-4/

 

Datapump Export ( expdp ) is an alternative backup method of Oracle database beside RMAN Backup.

Companies need both Export backup and RMAN Backup for their Critical databases. Because you can restore specific tables and objects from Export backup.

So If you want to take export backup of database daily, you need to place a Date in a Datapump Export File. Otherwise the new backup job will overwrite the old backup file.

 

You can place a Date in a Datapump Export File in the dumpfile parameter as follows.

dumpfile=FILE_NAME_`date +%b_%d_%y_%H_%M_%S.`dmp

 

or You can place a Date in a Datapump Export File on Unix systems ( IBM AIX )

DATE=$(date +"%Y%m%d")

For example; You can export of full database ( compressed ) with a Date in a Datapump Export File as follows on IBM AIX.

expdp \"/ as sysdba\" full=y directory=DUMP dumpfile=$DATE_FULL_DATABASE%U.dmp logfile=$DATE-FULL_DATABASE.log compression=all parallel=8 cluster=n

 

For example; You can export of full database ( compressed ) with a Date in a Datapump Export File as follows on Linux and etc..

expdp \"/ as sysdba\" full=y directory=PUMP dumpfile=FULL_DATABASE_`date +%b_%d_%y_%H_%M_%S.`dmp logfile=FULL_DATABASE_`date +%b_%d_%y_%H_%M_%S.`log compression=all cluster=n

 

Create a Crontab job for this task, this cron job runs every day.

[oracle@msddbadm01 export]$ crontab -e
0 */12 * * * sh /home/oracle/scripts/export_full.sh > /home/oracle/scripts/export_full.log

 

 

 

List the crontab job as follows.

[oracle@msddbadm01 export]$ crontab -l
0 */12 * * * sh /home/oracle/scripts/export_full.sh > /home/oracle/scripts/export_full.log

 

The contenf of /home/oracle/scripts/export_full.sh will be as follows. The job will run every day at 12:00 and export the database , and also it will delete the backups which are older than 7 days.

[oracle@msddbadm01 export]$  cat /home/oracle/scripts/export_full.sh

export PATH
export TZ=Europe/Istanbul
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export ORACLE_SID=msd1
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9

find /u02/export -name "*.dmp" -mtime +7 -delete

expdp \"/ as sysdba\" full=y directory=PUMP dumpfile=FULL_DATABASE_`date +%b_%d_%y_%H_%M_%S.`dmp logfile=FULL_DATABASE_`date +%b_%d_%y_%H_%M_%S.`log compression=all cluster=n

 

 

This Crontab job will generate the following backups everyday.

[oracle@msddbadm01 export]$ ls -ltr
total 245337552
-rw-r----- 1 oracle dba 13569581056 Mar 20 13:12 FULL_DATABASE_Mar_20_20_12_00_01.dmp
-rw-r----- 1 oracle dba 13563244544 Mar 21 13:10 FULL_DATABASE_Mar_21_20_12_00_01.dmp
-rw-r----- 1 oracle dba 13562368000 Mar 22 13:08 FULL_DATABASE_Mar_22_20_12_00_01.dmp
-rw-r----- 1 oracle dba 13573545984 Mar 23 13:07 FULL_DATABASE_Mar_23_20_12_00_01.dmp
-rw-r----- 1 oracle dba 13572603904 Mar 24 13:08 FULL_DATABASE_Mar_24_20_12_00_01.dmp
-rw-r----- 1 oracle dba 13574406144 Mar 25 13:11 FULL_DATABASE_Mar_25_20_12_00_02.dmp
-rw-r----- 1 oracle dba 13577162752 Mar 26 13:10 FULL_DATABASE_Mar_26_20_12_00_01.dmp
-rw-r----- 1 oracle dba 13578702848 Mar 27 13:11 FULL_DATABASE_Mar_27_20_12_00_01.dmp
-rw-r----- 1 oracle dba 13572825088 Mar 28 13:09 FULL_DATABASE_Mar_28_20_12_00_01.dmp
[oracle@msddbadm01 export]$

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/