Saturday, May 3, 2008

Simple Oracle Export script

Here is a simple script to export a Oracle schema or full database. Of course there are better ways to backup Oracle database these days. But some time we just need to KISS (keep it simple silly).

Set mm=%DATE:~4,2%
Set dd=%DATE:~7,2%
Set yyyy=%DATE:~10,4%
exp system/password file=DMP\sysadm-%yyyy%-%mm%-%dd%.dmp compress=y owner=schema1 consistent=y log=logs\sysadm-%yyyy%-%mm%-%dd%.log

This puts the dump file in a folder called DMP and a log file in the logs folder under the current folder where the script runs with a timestamp in the file name.

Now I know you will ask me how to delete older backups. This link: gives you a script to delete old files based on your needs. Just in case the link doesn't work, here is the script again (Thanks Benjamin):

Delete Old Files script

This sample script (WHICH IS PROVIDED AS IS) may help purging files older than n days in a folder

Just copy the following code to a VBS file (DeleteOldFiles.vbs).

Sample call:

cscript DeleteOldFiles.vbs C:\Windows\Temp 90

The code is:

option explicit

Call DoTheJob()
WScript.Echo "--- end of script execution ---"

Sub DoTheJob
dim limitDate
dim formattedLimitDate
dim folder
dim strComputer
dim objWMIService
dim colFileList
dim objFile
dim nbFiles
dim totalFiles
dim nbErrors
dim result
dim nbDays

if WScript.Arguments.Count <> 2 then
WScript.Echo "usage : DeleteOldFiles.vbs "
WScript.Echo "sample: DeleteOldFiles.vbs C:\Windows\temp 90"
Exit Sub
end if

folder = WScript.Arguments(0)
nbDays = WScript.Arguments(1)

'calculate and format limit date
limitDate = DateAdd("d", -1 * nbDays , Date)

formattedLimitDate = DatePart("yyyy", limitDate)

if DatePart("m", limitDate) < formattedlimitdate =" formattedLimitDate" formattedlimitdate =" formattedLimitDate">

if DatePart("d", limitDate) < formattedlimitdate =" formattedLimitDate" formattedlimitdate =" formattedLimitDate">

'show what will be done
WScript.Echo "Will remove files from " & folder & " with a date older than " & formattedLimitDate & " (" & nbDays & " days ago)"

'Get the files and delete the old ones
strComputer = "."

Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

Set colFileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='" & folder & "'} Where " _
& "ResultClass = CIM_DataFile")

nbFiles = 0
totalFiles = 0
nbErrors = 0

For Each objFile In colFileList
totalFiles = totalFiles + 1
if objFile.CreationDate < result =" objFile.Delete()" result =" 0" nbfiles =" nbFiles" nberrors =" nbErrors">

'Show the result
Wscript.Echo "Total files in folder: " & totalFiles
WScript.Echo "Deleted files: " & nbFiles
WScript.echo "Errors: " & nbErrors
End Sub

No comments: