How to backup Oracle

[cicero]

Hi,

does anybody have experiences about snapshoting a
Oracle-Server for backing him up?

- if I just take a snapshot, the backedup-oracle is only
crash-consistent, but maybe i can reliable recover it afterwards when nesesary?

- or does somebody use prefreeze/postthaw scripts, putting oracle in
backup mode for a snapshot and set it back online afterwards?
I think applications depending on the database will crash without their DB
in these minutes between the two scripts...
But maybe the oracle backup mode is transparent to applications
since it uses the archivelogs?

- I'm currently using Orache 10g on a Linux RHEL4, so VSS may today
be not an option for me, but may it be helpful if i move
to Orace 11g on Windows 2008 in this behavior?

Thanks, David

[Gostev]

David, Oracle for Windows does have VSS writer component for sure.
I would also like to hear feedback from customers running Oracle on Windows and using Veeam Backup VSS integration.

Thank you!

[alexr]

I do not believe oracle before 11g has VSS.
We use

ALTER SYSTEM SUSPEND;
and
ALTER SYSTEM RESUME;

on freeze/thaw which will essentially hold IO while snapshot is done. Not perfect but better than nothing perhaps

[cicero]

Hi Alexr,

sounds to be ok for replication the Oracle VM to a save place,
since we additional take a RMAN Full Backup of the data too.
Did you already test te recovery successful?

What is your freeze/thaw skript like?
Maybe you could post it?

Thank you, David

[alexr]

freeze:
set ORACLE_SID=ora1
sqlplus /nolog @D:\oracle\stop.sql > D:\oracle\stop.log

where stop.sql is

connect system/password as sysdba;
ALTER SYSTEM SUSPEND;
exit;

thaw is the opposite.

I have not tested restore yet but it is definetely better than nothing and better than VMware driver (forgot its name) to syspend IO on OS level which used to crash oracle all the time and cause all sorts of problems

[Gostev]

It's called "sync driver" and yes it does corrupt MSSQL and Oracle databases. Just as FYI for new users, Veeam Backup disables sync driver automatically when Veeam VSS is used, and if it is not used but you still want it disabled, there is a check box in advanced job settings for this.

[stevenfoo]

We are also using Oracle 10g on Windows with 2 VM instance.

I would like to know where to put the script SUSPEND (before snapshot) and RESUME (after snapshot)?

Any documentation and steps?

Thanks
Steven

[Gostev]

Steven,

Check out this blog post:
http://blog.ntgconsult.dk/2009/05/vmwar ... cript.html

You can Google on the following keywords for more information (using these scripts on other operating systems etc.)
vmware pre-freeze post-thaw

Thanks!

[digitaud]

Yes sirs... Oracle 11g has a coponent named "Oracle VSS Writer" that installs when selecting Windows OCI Components on the Oracle 11g database install wizard. It does support Oracle 10g also starting from patchset 10.2.0.3 (See MetaLink for download).

We use it with a W2K3 R2 and 10g 10.2.0.3. Veeam starts windows VSS agent and VSS starts Oracle VSS Writer, does the full consistent hot-backup of the database and then I use a script to clean archive logs and traces manually.

This is working like a charm for us and the problem of backing up Oracle is over.

You can see more information to: http://download-uk.oracle.com/docs/cd/B ... 10/vss.htm

Thanks!

[Gostev]

Thanks Adam - this is great piece of information!

[athome]

[merged]

Hi!

I have a customer that would like the ability to run scripts before Veeam starts the job, (ie. a script that shuts down an oracle database inside a VM)

Does Veeam have plans to implement this feature?

A

[Vitaliy S.]

No plans, as this functionality is already available with VMware Tools pre-freeze and post-thaw scripts.

[rileybloke]

Hello,

I am currently using Veeam 5.0.1 - replicating an Oracle 11g server every hour. We have the oracle VSS installed.
We have found that some oracle sessions are orphaned intermittantly when the snapshot is removed.
How can we tell if Veeam is using the Oracle VSS writers, or has anyone else encountered issues like this before?

Thanks

[Gostev]

Hi Phil, in the Windows event log you should see events from Oracle VSS writer at the same time when VM snapshot is created. Please check to see if you can see them. Another thing to check is run vssadmin list writers in the command prompt, and make sure Oracle VSS writers are listed and do not report any errors. Thanks!

[NetWise]

Is anyone doing Veeam backups like this on a 'big' (big to me anyway) Oracle server? We have 10g running on 2008 R2 and I did some monkeying around to do a test that stopped the import/replay of logs from our primary to our standby, so I could do backups of the standby. This was needed because without stopping it, the 2.5TB VM was getting 200MB/1-2 minutes of Archive Logs to replay, and of course the snapshot just took forever to deal with. Even doing it this way, attached to a 10GbE SAN, it took 8 hours to do a full backup, and 5 hours to do a diff backup. Then the associated catchup after for the Archive Log replay. Now I know this isn't a Veeam problem - if we made a snapshot on that VM for an hour or so just to do patches and didn't stop the Archive Log Replay, the same thing happens, so this is simply an issue with VMware snapshots on such a large VM. But there must be other people out there doing backups/replication of a large VM that is this busy as well at the disk level? As an example, my 2.45TB became a 640GB VBK, but the next day the Diff created a 120GB VBR (reverse-incremental mode), which tells me that about 20-25% of my blocks/data are changing per day.....

[Gostev]

This is perfectly normal for servers with high rate of disk changes (Exchange, SQL, Oracle). You can find other topics talking about the same issue here. Basically, this is something you just have to live with. Backup process has to pickup the actual state of the disk, and if it changed a lot since the previous run, then this will result in large incremental backup files.

You can reduce the size of your rollbacks in about half by changing storage optimization to WAN target (which will make Veeam use smaller block size), however this will reduce the backup speed because of processing overhead.