Automated Oracle Backup

Running an Oracle database for your customer comes along with the responsibility of maintaining regular backups of the database. Some of our clients do not have dedicated system personnel to take the backups for our database. In such cases, we have to deploy some automated plans that will ensure minimal downtime to resuscitate a database.

The automation is very simple. We just created a batch script to perform all the required operations and then use the Task Scheduler to run the backup every morning at 6 a.m when system is not being used.

The jobs performed in the batch script are as follows:

1. Run Oracle Export


2. Copy the dump and log files to backup area


3. Compress the backup to save space


4. Clear out dump files

Run Oracle Export
The database version we have deployed in most of our sites is Oracle 10gR2 (10.2.0.4). We use Oracle's Datapump facility for exports instead of the classic Export which is more time-consuming and generates large sized dump files. A batch script is created for every Database instance. A parameter file mentioned in the batch file would contain all parameters for the Export.
Batch file would be as follows:

[Oracle Home]\bin\EXPDP   [system/pwd@db-name]   PARFILE=[full URL of Parameter file]

The parameter file would look as follows:

DIRECTORY=[Oracle Directory Name]
DUMPFILE=[Export File Name].DMP
LOGFILE=[Oracle Directory]:[Filename].LOG
CONTENT=ALL
SCHEMAS=[All the Schemas that need to be exported]

Archiving the export
Steps 2,3 and 4 are done using VBScript. For performing Zip operations we use 7-Zip as it provides a command-line interface. (Download here)
The VBScript will do the following:

1. Create a folder for current day of week.


2. Copy the contents from Export area to current day's folder.


3. Compress the files in current day's folder.


4. Remove current day's folder.


5. Remove export dump files from Export directory.

The VBS file looks as follows:

'-----------------------------------------------------------
' wsh script to create directory by day MON,TUE,....
' copy the backup into the respective Folders
' They will be Zipped and stored in the respective Folders
'------------------------------------------------------------
dim filestr, objFSO, dir, objfolder
dim backup_ok
dim sFiles, s7zpath, sStatement, sFileName
' msgbox weekday(date)
select case weekday(date)
case 1 filestr = "SUN"
case 2 filestr = "MON"
case 3 filestr = "TUE"
case 4 filestr = "WED"
case 5 filestr = "THU"
case 6 filestr = "FRI"
case 7 filestr = "SAT"
case else msgbox "Not a valid day"
end select

backup_ok = "N"

dir = "C:\TEMP"
' ----------------------------------------------------------------------
' Ensure the target directory Exists
' ----------------------------------------------------------------------
src_dir = "C:\TEMP\TEST"
Set objFSO = CreateObject("Scripting.FileSystemObject")
If not objFSO.FolderExists(src_dir) Then
wscript.echo "TARGET directory does not exist, Hence backup Not Created"
End If
todaydir = dir&"\"&filestr

'-------------------------------------
' check whether folder exists
'-------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FolderExists(todaydir) Then
Set objFolder = objFSO.GetFolder(todaydir)
' Wscript.Echo "Folder existS."
objFSO.DeleteFolder(todaydir), TRUE
Else
' Wscript.Echo "Folder does not exist."
rem -------------------------------------
rem Create folder
rem -------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.CreateFolder(todaydir)
' Wscript.Echo "Folder Created"
End If

rem -------------------------------------
rem Copy all files in the folder
rem -------------------------------------
' wscript.echo todaydir

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.CopyFolder src_dir, todaydir , TRUE

'-----------------------------------------
' Zip the contents of folder using 7-zip
'-----------------------------------------
sFiles = todaydir & "\*.*"
s7zpath = """C:\Program Files\7-Zip\7z.exe"""
sFileName = dir & "\" & filestr & "Day.zip"
sStatement = s7zpath & " a -tzip -y " & sFileName & " " & sFiles
'wscript.echo sStatement

Set oShell = WScript.CreateObject("Wscript.Shell")
'----------------------------------------------------------
' 0 - Hides the window and activates another window.
' true - script execution halts until the program finishes
'----------------------------------------------------------
oShell.Run sStatement, 2, true


'-----------------------------------------
' Delete the contents of Temp Folder
'-----------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
if objFSO.FileExists(sFileName) then
'wscript.echo "Deleting " & todaydir
objFSO.DeleteFolder todaydir
'else
' wscript.echo "Cannot locate " & sFileName
end if

'-----------------------------------------------------------------------
' Delete DMP Files
'-----------------------------------------------------------------------
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each file In objfso.GetFolder(src_dir).Files
if objFSO.GetExtensionName(file) = "DMP" then
file.delete
end if
Next
'-----------------------------------------------------------------------
' End of backup Script
'=======================================================================