Sometimes when your running an Oracle database, it becomes necessary to automate some tasks like cleaning your archivelogs. Since in my setup we don’t have a tape drive to which we can backup the archivelogs and delete it, we keep a retention period of one week for the archive logs on disk. If the archive log destination gets full, your database will hang. And in a large setup with multiple databases. It becomes an overhead to keep checking the size of the archive log destination regularly, and this calls for automating log deletion.
1. UNIX
In UNIX flavour OS’es like Solaris it is fairly simple to delete files older than ‘x’ days by simply running:
find /u2/test -name “*.dbf” -mtime +7 -exec rm {} \;
You can automate this by putting an entry in the crontab for weekly cleaning
00 2 * * 5 find /u2/stest -name “*.dbf” -ctime +7 -exec rm {} \; >/dev/null 2>&1
This will delete .dbf files older than 7 days on every friday at 2:00 AM
______________________________________________________________________________
2. WINDOWS
For Windows this is a bit tricky. Though windows has a powerful scripting functionality via batch scripts. It doesn’t provide a good utility to find files older than x days. There is forfiles utility but it doesn’t come by default on all versions of windows.
Using forfiles to delete files older than 7 days.
FORFILES /p C:\myfolder /s /m *.dbf /d -7 /c “CMD /C del /Q /F @FILE
VBSCRIPT METHOD
The best thing to do is doing it via a small VB script and calling that vbscript via a batch file. Sounds complicated, but its very easy. Here’s a step-by-step way to delete files older than “x” days via a vbscript.
1. Create you vbscript. Simply copy the code below and provide the no. of days parameter you want and the location of your archive log folder.
# please note this will delete all files in that folder,older than 7days.
iDaysOld = 7
strPath = “F:\OraArchives”
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objFolder = objFSO.GetFolder(strPath)
Set colSubfolders = objFolder.Subfolders
Set colFiles = objFolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() – iDaysOld) Then
‘MsgBox “Dir: ” & objFolder.Name & vbCrLf & “File: ” & objFile.Name
objFile.Delete
End If
Next
For Each objSubfolder in colSubfolders
Set colFiles = objSubfolder.Files
For Each objFile in colFiles
If objFile.DateLastModified < (Date() – iDaysOld) Then
‘MsgBox “Dir: ” & objSubfolder.Name & vbCrLf & “File: ” & objFile.Name
objFile.Delete
End If
Next
Next
2. Save this script as DelArchive.vbs
3. Create a batch file by right clicking on desktop and creating a new text file and renaming this file with extension set to .bat and paste the below code.
call “C:\Documents and Settings\Desktop\DelArchive.vbs”
Now simply run the batch file and it will execute the vbscript and it will purge the files older than 7 days. You can put the batch file in task scheduler and run it any day of week according to your convenience.
