Feature Request: Oracle SQL Support

[dellock6]

You can use RMAN to manage archivelog retention, RMAN is available in any Oracle installation, both windows or linux. Even if you're not using RMAN itself to do backups but you are using just Veeam, you can run this command inside RMAN:

RMAN > delete archivelog all completed before 'sysdate -1';

This one will delete any log older than 1 day. By simply changing the 1 value at the end you can change the behaviour of the script.

Luca.

[tsightler]

Or if you want to stick to good old Windows commands you can do something like:

Code: Select all

forfiles -p "C:\<archive_log_dir>" -s -m *.* /D -<number of days> /C "cmd /c del @path"

The "forfiles" command should be available on any Windows server newer than 2003 although the syntax did change a little as I think earlier versions used "-" for parameters instead of "/". Remember that the number of days should be negative because you're deleting files older than that. You can just leave off the "/C" option if you just want to see what files it would be acting on.

[dellock6]

Tom,
just a check, sure it's ok to brutally remove archivelogs from the filesystem without having Oracle (or RMAN if in use) to know about it? I've read around to stick with Oracle operations for this, but maybe it was coming from Oracle people...

[tsightler]

If you're not using RMAN at all, then yes, it's completely OK. Once the Oracle database archives a log to the archive destination, it's done with that file and pretty much doesn't care about it anymore. If you perform a manual media recovery after a restore of the Oracle DB it will look in the archive destination for the required files (it uses the archive log naming and sequence numbers to know what files to look for even without a catalog of those files) and if the file it wants isn't there it will just prompt you and you can just restore the required files.

However, if you are using RMAN to backup the DB and/or archive logs, then you probably should use RMAN to delete the files, and I'm guessing this is why you sometimes see recommendations to use only Oracle tools. RMAN tracks what files are on disk so if you delete the files manually from the command line, and don't tell RMAN about it, it's catalog will no longer be in sync with the disk contents which can lead to confusion if you have to perform a recovery. However, even this is easy to recover from by running a couple of RMAN commands something like this:

Code: Select all

crosscheck archivelog all
delete noprompt expired archivelog all

This tells RMAN to check it's catalog against what's actually on disk and then delete any that have been manually removed from the filesystem.

The below command is pretty much the purge script we used on our Linux servers for years, before we started using RMAN (at the time we were doing Oracle hot backups using storage snapshots):

Code: Select all

find /path/to/archive* -mtime +5 -exec rm {} \;

[hawkeye80]

As being mainly a Oracle DBA I'm highly recommending to either delete the archivelogs via RMAN or when removing them at OS level to at least schedule a frequent crosscheck as tsightler suggested. If you miss the crosscheck and delete expired your database controlfiles will grow and grow and grow.
With a database generating some hunderts of archivelogs a day you will get a controlfile of some hunderts MB over the time and this will cause some performance troubles.

[apronk]

Wow all these reactions
I had already created a script that cleans up the logs, and for the benefit of others reading this thread I'll post my code.
This is written in the KiX scripting language purely for the reason that you can tokenize the the code so no one can get the credentials, and KiX is the fastest scripting language for Windows

Code: Select all

$=SetOption('Explicit','On')
Dim $objFSO, $objShell, $strFile, $objFile
$objFSO = CreateObject("Scripting.FileSystemObject")
$objShell = CreateObject("WScript.Shell")
$strFile = "%TEMP%\rman.txt"
$objFile = $objFSO.CreatetextFile($strFile, 1)
$objFile.Write("crosscheck archivelog all;" + @CrLf)
$objFile.Write("delete force noprompt archivelog all;" + @CrLf)
$objFile.Close
$=$objShell.Run("rman target <user>/<pass>@<DB_SID> CMDFILE " + $strFile, 0, 1)
$objFSO.DeleteFile($strFile)
$objFile = ""
$objFSO = ""
$objShell = ""

This script creates a cmdfile for RMAN in the %TEMP% dir, then runs rman using the cmdfile as arguement and then deletes the cmdfile again.
As explained by Hawkeye80, using RMAN makes sure the Oracle knows the logs are deleted.

[dellock6]

Nice Arend,
thanks for sharing. KiX brings my memory back of at least 15 years