Batch Script in Windows to take export EXPDP of Oracle Database

To automate the process of running Oracle Data Pump (expdp) on a daily basis, and including the date in the filenames for both dump and log files, you can write a Windows batch script. Below is an example script for automating the expdp process with date-based file names.

Batch Script for Oracle expdp Backup with Date in Dump and Log Files

@echo off
SET ORACLE_HOME=C:\app\oracle\product\12.1.0\dbhome_1
SET ORACLE_SID=ORCL

REM Directory where dumps and logs will be stored
SET BACKUP_DIR=C:\backup\oracle\expdp

REM Get the current date in YYYYMMDD format
for /f "tokens=2-4 delims=/ " %%a in ('date /t') do (
set CURR_DATE=%%c%%a%%b
)

REM Create the backup directory if it does not exist
if not exist %BACKUP_DIR% mkdir %BACKUP_DIR%

REM Export the database using expdp with a date-stamped filename
%ORACLE_HOME%\bin\expdp system/password@ORCL schemas=SCHEMA_NAME directory=DATA_PUMP_DIR dumpfile=expdp_%CURR_DATE%.dmp logfile=expdp_%CURR_DATE%.log

REM Print a completion message
echo Data pump export completed for %CURR_DATE%. Check the log and dump files at %BACKUP_DIR%.

Explanation of Key Parts:

ORACLE_HOME and ORACLE_SID: You must replace C:\app\oracle\product\12.1.0\dbhome_1 and ORCL with your actual Oracle home directory and SID.

BACKUP_DIR: This is the directory where the export dump files (.dmp) and logs (.log) will be saved. Customize the path according to your setup.

Date Extraction: The script extracts the current date in YYYYMMDD format and uses it in both the dump file and log file names.

expdp Command: The expdp command exports the specified schema (SCHEMA_NAME). Replace system/password with your Oracle username and password, and SCHEMA_NAME with the schema you're exporting.

Scheduling the Script:

Once the script is set up, you can schedule it using Windows Task Scheduler to run daily:

Open Task Scheduler.

Click on Create Basic Task.
Set the trigger to run daily.
In the action, choose Start a Program and browse for the batch script.
This will ensure your expdp process runs automatically every day, generating files with unique timestamps.