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/

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *