Feature Request: Oracle SQL Support

[apronk]

It would be extremely handy to our organization if we could do restores of Oracle databases/schema's etc.
This means adding it at Application level support, like it has been done for Microsoft SQL and Exchange.

[Gostev]

Hi Arend, thanks for your feedback. This feature is already planned for the future releases. Thanks again for validating the need!
Can you please tell us a little more about your Oracle environment (OS, storage, database sizes) and how are you backing it up today?

[apronk]

Hi Gostev,

Currently we use Veeam to make a backup of the VM itself, and since we use Oracle 11g and 12 which support VSS, the backup succeeds.
However with one detail, the backup always fails the first time after an Oracle server has been rebooted, if we restart the VSS services after the reboot (eventhough they have been started) the Veeam backup works fine.
This exact same issue occurs with Veeam Endpoint Backup on our PC's and Laptops that have Oracle databases.
Also since Veeam doesn't support Application level recovery for Oracle yet, we make dumps every night of every database in the oracle instances.

Our Oracle Databases run on Windows Server 2008 R2 and Windows Server 2012 R2, and on the clients on Windows 7 x64).
Database sizes differ from 50GB to 400GB.

If you have any other question please let me know.

[apronk]

After the reboot of an Oracle server I run this batch file that I've made:

Code: Select all

net stop COMSysApp
net stop SENS
net stop iphlpsvc
net stop bits
net stop EventSystem
net stop swprv
net stop vss
net stop vmvss
net stop VMTools
net stop VMUpgradeHelper
net stop ReportServer
net stop UALSVC
net stop winmgmt
net stop apphostsvc
net start COMSysApp
net start SENS
net start apphostsvc
net start EventSystem
net start swprv
net start vss
net start vmvss
net start VMTools
net start VMUpgradeHelper
net start UALSVC
net start ReportServer
net start winmgmt
net start iphlpsvc
net start bits

It's a long list, I know, but they are needed because most of them are services that are dependencies of the vss and vmvss services.

Funny thing is, this issue does not happen with Microsoft SQL, only Oracle SQL.

[dellock6]

Arend,
I hope my paper on Oracle support for v8 will come out soon ,it's in editing stage. However, you can also try to use scripts to set database in "alter database backup" mode.
Also, if you want to have transaction level restore, even without a graphical interface like we have for MSSQL, you can enable archive logs and have point-in-time restores. The paper will have some example also on how to do that, but there are plenty of docs around on how to replay logs in Oracle.
Also, with alter backup the database is in backup mode when you backup the entire VM with Veeam, so when it starts is not running, but ready to be restored entirely or at a specified point in time.
If you go for scripts, just remember to disable at all the Oracle VSS service.

[apronk]

Hi Luca,

Unfortunately we did not buy Veeam to be using scripts, we bought it to get rid of all the scripts
Like I said, we currently use scripts to make a backup of all the databases and Veeam to backup the virtual machines, including the ones that have Oracle Databases.
And the whole idea is to get rid of those scripts as well.

[Gostev]

There's really nothing wrong with the scripts as long as you don't have to deploy and manage them on all of your Oracle servers, which is exactly what v8 allows you to do.

[apronk]

Actually there is no relationship between scripts & veeam.
The one can not control the other...

Besides that is not the point, we currently have a situation where the databases are backed up using scripts, which defaults the Veeam purpose.
The whole idea is that Veeam will do the whole thing, like it does for Exchange and Microsoft SQL.
Why is this pure logic so hard to understand?

[dellock6]

Hi Arend,
I completely understand the logic, and I agree. Simply, since we do not have the same level of support that we have for MSSQL or Exchange, I was giving you a workaround to concentrate the scripts in a central location in Veeam, and use them to backup all the Oracle database. I think a central location to manage all the scripts is already an improvement compared to configure them in any Oracle machine, and also the ability to use them as pre-job activities of a Veeam backup so that everything is cohordinated by Veeam itself.

[apronk]

Hi Luca,

Ah, there is some misunderstanding on my part then, my apologies.
I did not know that Veeam can have a repository for scripts and execute them.
Can you point me to documentation where I can read up about that?

[Gostev]

http://helpcenter.veeam.com/backup/80/v ... ts_vm.html

[apronk]

Thank you Gostev!

[apronk]

Arend,
I hope my paper on Oracle support for v8 will come out soon ,it's in editing stage. However, you can also try to use scripts to set database in "alter database backup" mode.
Also, if you want to have transaction level restore, even without a graphical interface like we have for MSSQL, you can enable archive logs and have point-in-time restores. The paper will have some example also on how to do that, but there are plenty of docs around on how to replay logs in Oracle.
Also, with alter backup the database is in backup mode when you backup the entire VM with Veeam, so when it starts is not running, but ready to be restored entirely or at a specified point in time.
If you go for scripts, just remember to disable at all the Oracle VSS service.

Luca,

We've (Veeam support & me) figured out that setting the ArchiveLog to enabled solves the issue with the backup.
However this generates a lot of .arc files, how can the deletion of these files be automated ?

[Gostev]

Most Oracle users schedule a script (for example, with a cron job) to prune oldest archives based on various parameters like age, total size, etc.

[apronk]

Gostev,

Yeah, I thought so.
And I figured since Luca was busy writing a paper on Oracle support for Veeam he'd have some code handy for me to use
Our Oracle servers run on Windows though.

[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