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

Batch: Get parameters from command line or user input

When you write a batch file where you either want the caller to provide a parameter or ask the user to type it in if not provided, you can use the following piece of code:

set MyParameter=%1
if “%MyParameter%”==”” (
:input
set INPUT=
set /P INPUT=Enter a value for the parameter: %=%
)
if “%MyParameter%”==”” (
if “%INPUT%”==”” goto input
)
if “%MyParameter%”==”” (
set MyParameter=%INPUT%
)

It first gets the first command line argument. If it’s not empty you can just skip every (so either do it the way I did it or just use a goto.

As long as the user hasn’t entered anything, the script will keep asking. Instead of just checking for an empty string, you could also have a more elaborate validation of the input.

The multiple if blocks are required because if you put it all in one block the %INPUT% variable will only be evaluated when the script is started and not again after user input.

Batch: convert to uppercase

The following batch file can be used to convert a string to uppercase:

uppercase.bat:

@ECHO OFF
SET STRING=%1
IF [%STRING%]==[] GOTO:EOF
SET STRING=%STRING:a=A%
SET STRING=%STRING:b=B%
SET STRING=%STRING:c=C%
SET STRING=%STRING:d=D%
SET STRING=%STRING:e=E%
SET STRING=%STRING:f=F%
SET STRING=%STRING:g=G%
SET STRING=%STRING:h=H%
SET STRING=%STRING:i=I%
SET STRING=%STRING:j=J%
SET STRING=%STRING:k=K%
SET STRING=%STRING:l=L%
SET STRING=%STRING:m=M%
SET STRING=%STRING:n=N%
SET STRING=%STRING:o=O%
SET STRING=%STRING:p=P%
SET STRING=%STRING:q=Q%
SET STRING=%STRING:r=R%
SET STRING=%STRING:s=S%
SET STRING=%STRING:t=T%
SET STRING=%STRING:u=U%
SET STRING=%STRING:v=V%
SET STRING=%STRING:w=W%
SET STRING=%STRING:x=X%
SET STRING=%STRING:y=Y%
SET STRING=%STRING:z=Z%
ECHO %STRING%

This can be used this way:

> uppercase.bat “I want to be an uppercase string”
“I WANT TO BE AN UPPERCASE STRING”