oracle: daily backup on windows

Here’s a small batch file I’ve written to make a daily backup of our Oracle database.

@echo off

if “%DUMP_PATH%”==”” set DUMP_PATH=d:temp
if “%DUMP_BASE_NAME%”==”” set DUMP_BASE_NAME=dump
if “%DUMP_EXTENSION%”==”” set DUMP_EXTENSION=dmp
if “%DUMP_CLEANUP_DAYS%”==”” set DUMP_CLEANUP_DAYS=7
if “%DUMP_OWNERS%”==”” set DUMP_OWNERS=user1,user2
if “%DUMP_ORA_USER%”==”” set DUMP_ORA_USER=user1
if “%DUMP_ORA_PWD%”==”” set DUMP_ORA_PWD=pwd1
if “%DUMP_ORA_SID%”==”” set DUMP_ORA_SID=SID1

set CURRENT_DATETIME=%date%_%time%
set CURRENT_DATETIME=%CURRENT_DATETIME: =_%
set CURRENT_DATETIME=%CURRENT_DATETIME::=%
set CURRENT_DATETIME=%CURRENT_DATETIME:/=_%
set CURRENT_DATETIME=%CURRENT_DATETIME:.=_%

exp.exe %DUMP_ORA_USER%/%DUMP_ORA_PWD%@%DUMP_ORA_SID% OWNER=%DUMP_OWNERS% FILE=%DUMP_PATH%%DUMP_BASE_NAME%_%CURRENT_DATETIME%.%DUMP_EXTENSION% CONSISTENT=Y

forfiles /P “%DUMP_PATH%” /M %DUMP_BASE_NAME%_*.%DUMP_EXTENSION% /D -%DUMP_CLEANUP_DAYS% -c “cmd /c del @path”
The variables DUMP_* can either be set as environment variables or in the batch file.

It creates exports the data belonging to the users defined by DUMP_OWNERS (comma-separated list of users). It then deletes all backup files older than DUMP_CLEANUP_DAYS days (so with the configuration above, it will keep the backups for a week).

This batch file can be run as a windows scheduled task everyday (in an Administrator command line):

schtasks /create /tn “OracleDumpDaily” /sc daily /st 03:00:00 /tr “d:tempdump_daily.bat” /RU SYSTEM 

This will start the script (assuming it’s in d:tempdump_daily.bat) daily at 3am.

Use this to check whether the scheduled task is installed and when it last run or will run next:

schtasks /query /fo LIST /tn OracleDumpDaily /v
To remove the scheduled task:

schtasks /delete /tn OracleDumpDaily
Now if something goes terribly wrong and you need to restore one of the backups:

imp.exe sdm/sdm FILE=dump_14_04_2012_030000.DMP FULL=Y

Leave a Reply

Your email address will not be published.