-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Feature Request: Oracle SQL Support
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.
This means adding it at Application level support, like it has been done for Microsoft SQL and Exchange.
-
- Chief Product Officer
- Posts: 31804
- Liked: 7298 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: Feature Request: Oracle SQL Support
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?
Can you please tell us a little more about your Oracle environment (OS, storage, database sizes) and how are you backing it up today?
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
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.
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.
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
After the reboot of an Oracle server I run this batch file that I've made:
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.
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
Funny thing is, this issue does not happen with Microsoft SQL, only Oracle SQL.
-
- VeeaMVP
- Posts: 6165
- Liked: 1971 times
- Joined: Jul 26, 2009 3:39 pm
- Full Name: Luca Dell'Oca
- Location: Varese, Italy
- Contact:
Re: Feature Request: Oracle SQL Support
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.
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 Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
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.
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.
-
- Chief Product Officer
- Posts: 31804
- Liked: 7298 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: Feature Request: Oracle SQL Support
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.
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
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?
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?
-
- VeeaMVP
- Posts: 6165
- Liked: 1971 times
- Joined: Jul 26, 2009 3:39 pm
- Full Name: Luca Dell'Oca
- Location: Varese, Italy
- Contact:
Re: Feature Request: Oracle SQL Support
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.
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.
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
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?
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?
-
- Chief Product Officer
- Posts: 31804
- Liked: 7298 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
Thank you Gostev!
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
Luca,dellock6 wrote: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.
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 ?
-
- Chief Product Officer
- Posts: 31804
- Liked: 7298 times
- Joined: Jan 01, 2006 1:01 am
- Location: Baar, Switzerland
- Contact:
Re: Feature Request: Oracle SQL Support
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.
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
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.
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.
-
- VeeaMVP
- Posts: 6165
- Liked: 1971 times
- Joined: Jul 26, 2009 3:39 pm
- Full Name: Luca Dell'Oca
- Location: Varese, Italy
- Contact:
Re: Feature Request: Oracle SQL Support
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.
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.
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
-
- VP, Product Management
- Posts: 6035
- Liked: 2860 times
- Joined: Jun 05, 2009 12:57 pm
- Full Name: Tom Sightler
- Contact:
Re: Feature Request: Oracle SQL Support
Or if you want to stick to good old Windows commands you can do something like:
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.
Code: Select all
forfiles -p "C:\<archive_log_dir>" -s -m *.* /D -<number of days> /C "cmd /c del @path"
-
- VeeaMVP
- Posts: 6165
- Liked: 1971 times
- Joined: Jul 26, 2009 3:39 pm
- Full Name: Luca Dell'Oca
- Location: Varese, Italy
- Contact:
Re: Feature Request: Oracle SQL Support
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...
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...
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
-
- VP, Product Management
- Posts: 6035
- Liked: 2860 times
- Joined: Jun 05, 2009 12:57 pm
- Full Name: Tom Sightler
- Contact:
Re: Feature Request: Oracle SQL Support
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:
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):
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
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 {} \;
-
- Novice
- Posts: 7
- Liked: 2 times
- Joined: Mar 18, 2015 8:12 am
- Contact:
Re: Feature Request: Oracle SQL Support
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.
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.
-
- Enthusiast
- Posts: 76
- Liked: 9 times
- Joined: Mar 23, 2015 2:47 pm
- Full Name: Arend Pronk
- Contact:
Re: Feature Request: Oracle SQL Support
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
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.
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 = ""
As explained by Hawkeye80, using RMAN makes sure the Oracle knows the logs are deleted.
-
- VeeaMVP
- Posts: 6165
- Liked: 1971 times
- Joined: Jul 26, 2009 3:39 pm
- Full Name: Luca Dell'Oca
- Location: Varese, Italy
- Contact:
Re: Feature Request: Oracle SQL Support
Nice Arend,
thanks for sharing. KiX brings my memory back of at least 15 years
thanks for sharing. KiX brings my memory back of at least 15 years
Luca Dell'Oca
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
Principal EMEA Cloud Architect @ Veeam Software
@dellock6
https://www.virtualtothecore.com/
vExpert 2011 -> 2022
Veeam VMCE #1
Who is online
Users browsing this forum: No registered users and 105 guests