Simon Krenger

On a few test databases, test managers often need to preserve certain states in the database. This is why we use daily datapump scripts to create exports for archival.

It is important to note that such scripts are never a replacement for a proper RMAN backup, but an easy way to preserve multiple states of a database and reuse data where applicable.

This batch file uses the expdp tool provided by Oracle and the 7-zip archiver to compress the exports for archival. The export tool itself creates a full export of the whole database (full=y). Also, the flashback_time parameter is specified to get a consistent export.

@echo off
SET ORACLE_SID=kdb01
SET HNAME=hostname_here
SET BASE_FOLDER="D:\oracle\app\product\10.2.0\admin\%ORACLE_SID%\datapump"
SET DUMPFILE_FOLDER="D:\oracle\app\product\10.2.0\admin\%ORACLE_SID%\datapump"
SET ARCHIVE_PROGRAM="C:\Program Files\7-Zip\7z.exe"

SET DD=%date:~0,2%
SET MM=%date:~3,2%
SET YYYY=%date:~6,4%
SET T=%TIME: =0%
SET EXPORTDATE=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2% %T:~0,2%%T:~3,2%00

SET BASE_NAME=%YYYY%%MM%%DD%_%HNAME%_%ORACLE_SID%_fullexp
SET DUMPFILE_NAME=%BASE_NAME%.dmp
SET LOGFILE_NAME=%BASE_NAME%.log
SET BACKUP_FOLDER=%BASE_FOLDER%
SET BACKUP_FILENAME=%BASE_NAME%.zip

REM Script begin
REM expdp, full backup to "dpdir", flashback_time=systimestamp for consistency
expdp '/ as sysdba' full=y directory=dpdir dumpfile=%DUMPFILE_NAME% logfile=%LOGFILE_NAME% flashback_time=SYSTIMESTAMP
IF %ERRORLEVEL% NEQ 0 GOTO ERROR


%ARCHIVE_PROGRAM% a -tzip %BACKUP_FOLDER%\%BACKUP_FILENAME% %DUMPFILE_FOLDER%\%DUMPFILE_NAME% %DUMPFILE_FOLDER%\%LOGFILE_NAME%
IF %ERRORLEVEL% NEQ 0 GOTO ERROR

DEL %DUMPFILE_FOLDER%\%DUMPFILE_NAME%
DEL %DUMPFILE_FOLDER%\%LOGFILE_NAME%

EXIT 0

:ERROR
EXIT 1

The script itself basically does the following things:

• Set some environment variables
• Execute expdp with ‘full=y’
• Compress the exported files with 7zip
• If everything went well, delete the uncompressed files

You will need to run this script as the user that owns the Oracle installation or a user that is in the “dba” group of the Operating System, so the user can log in without a password (and just uses '/ as sysdba').

Also, make sure you set DUMPFILE_FOLDER and the directory= parameter to correct values. You might have to create a new datapump directory for your daily batch file.

Because the error level is checked after expdp as well as 7zip, Windows Scheduled Task should properly report the status of the batch script. With Nagios, you could then check the status of all scheduled tasks and be notified if anything goes wrong.