FAQ
Anyone using snapshots to backup Oracle? Does it work? Anything I need to
know?

Search Discussions

  • Keith Moore at Jul 7, 2010 at 1:29 am
    I've used Veritas snapshots before and it worked great. The concept should
    be the same. Database files are only in backup mode for a couple of minutes
    while the snapshot is created.



    It was also great for using one physical development database as multiple
    'virtual' databases, one for each developer. That feature wasn't put to
    nearly as much use as I would have liked but the concept proved to be good.
    And if a developer mucks up his copy, just create a fresh snapshot.



    Keith



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Andrew Kerber
    Sent: Tuesday, July 06, 2010 11:19 AM
    To: post.ethan_at_gmail.com
    Cc: oracle-l
    Subject: Re: VMWARE Snapshots to backup Oracle



    I am not using it, but I understand that it works as long as you use the
    alter database begin backup command prior to taking the snapshot (and the
    alter database end backup command after taking it).

    On Tue, Jul 6, 2010 at 10:18 AM, Ethan Post wrote:

    Anyone using snapshots to backup Oracle? Does it work? Anything I need to
    know?

    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    No virus found in this incoming message.
    Checked by AVG - www.avg.com
    Version: 9.0.830 / Virus Database: 271.1.1/2983 - Release Date: 07/06/10
    01:36:00

    --
    http://www.freelists.org/webpage/oracle-l
  • David Roberts at Jul 7, 2010 at 1:36 am
    "Danger, Will Robinson, Danger"

    Why would enabling backup mode, mean that backups of online redo logs
    are not required?

    If you do a snapshot of the database while online, by implication the
    backup will only be useful after recovery, and the redo logs would be
    vital to guarantee the recovery?

    Perhaps, I've missed something, please correct me if I'm wrong.

    Dave
    On Tue, Jul 6, 2010 at 5:24 PM, Bobak, Mark wrote:
    Right.  If you do begin backup/end backup, you should *not* snapshot online
    redo, and you get a recoverable snapshot.  If you do *not* do begin/end
    backup, you *should* snapshot the online redo logs, and you get restartable
    snapshot.



    Hope that helps,



    -Mark



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Andrew Kerber
    Sent: Tuesday, July 06, 2010 12:19 PM
    To: post.ethan_at_gmail.com
    Cc: oracle-l
    Subject: Re: VMWARE Snapshots to backup Oracle



    I am not using it, but I understand that it works as long as you use the
    alter database begin backup command prior to taking the snapshot (and the
    alter database end backup command after taking it).

    On Tue, Jul 6, 2010 at 10:18 AM, Ethan Post wrote:

    Anyone using snapshots to backup Oracle? Does it work? Anything I need to
    know?


    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'
    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Jul 7, 2010 at 2:28 am
    Hi Dave,

    This is no different than any other hot backup. You put database into backup mode, and copy off the datafiles, exit backup mode. Then, you can use 'alter database backup controlfile....' to take a logical or physical backup of the controlfile. But, do *not* backup the online redo logs.

    Suppose you take a hot backup (snapshot or otherwise) with tablespaces all in backup mode, at 12:00pm. Suppose you *did* take a backup of the online redo as part of that backup/snapshot. Now, at 2:00pm, your database crashes. So, what happens? You restore all files from the snapshot, *including* the online redo. Now, remember, that online redo is from 12:00pm. You want to recover to 2:00pm, when the crash occurred. So, you have your archivelog backups. So, you put database into recovery mode (recover database), and start applying logs. Now, suppose the last archivelog you apply is from 1:55pm. Where are the last remaining transactions from 1:55pm till 2:00pm? They're in the online redo! But, you've overwritten it, with the data from 12:00p, which is useless, and, by the way, was already archived off, shortly after 12:00pm, and has been applied to the database. So, now you're stuck. You lost the data from the current online redo when you did the restore, and your
    online redo is from 12:00pm (backup time), which is useless to you. So, your only option is to do an incomplete recovery, open resetlogs, and explain to your boss why you lost all the transactions from 1:55pm-2:00pm.

    Now, you could argue that, in the above circumstance, you simply don't restore the online redo, and you'll be fine. That's true, (assuming that the snapshot technology you're using will allow for you to specify which files do and don't get restored), but, tell me, why backup the on-line redo in the first place? Show me a valid recovery scenario, from a hot backup, that requires a copy of the on-line redo to be in the backup set. I'm not aware of any such scenario. Can you think of one?

    Cold backups are a bit different. I always argue against backing up online redo with cold backup either. People always argue that the online redo is required if the database was not consistent (i.e. cleanly shutdown) when backup was done. And, I'd argue that if that's how you're doing cold backups, you're doing it wrong. (Though, I think I'd first wonder why the heck you're doing cold backups in the first place!)

    The only valid scenario that I can think of, where you *want* to include the online redo, is if you want a *restartable* snapshot, rather than a *recoverable* snapshot. Keep in mind, that restartable snapshot can only be used to restore the database to the point in time when the snapshot was taken. It's not recoverable, and cannot be rolled forward to a point in time.

    Hope that helps,

    -Mark

    From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of David Roberts [big.dave.roberts_at_googlemail.com]
    Sent: Tuesday, July 06, 2010 21:36
    To: oracle-l
    Subject: Re: VMWARE Snapshots to backup Oracle

    "Danger, Will Robinson, Danger"

    Why would enabling backup mode, mean that backups of online redo logs
    are not required?

    If you do a snapshot of the database while online, by implication the
    backup will only be useful after recovery, and the redo logs would be
    vital to guarantee the recovery?

    Perhaps, I've missed something, please correct me if I'm wrong.

    Dave
    On Tue, Jul 6, 2010 at 5:24 PM, Bobak, Mark wrote:
    Right. If you do begin backup/end backup, you should *not* snapshot online
    redo, and you get a recoverable snapshot. If you do *not* do begin/end
    backup, you *should* snapshot the online redo logs, and you get restartable
    snapshot.



    Hope that helps,



    -Mark



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Andrew Kerber
    Sent: Tuesday, July 06, 2010 12:19 PM
    To: post.ethan_at_gmail.com
    Cc: oracle-l
    Subject: Re: VMWARE Snapshots to backup Oracle



    I am not using it, but I understand that it works as long as you use the
    alter database begin backup command prior to taking the snapshot (and the
    alter database end backup command after taking it).

    On Tue, Jul 6, 2010 at 10:18 AM, Ethan Post wrote:

    Anyone using snapshots to backup Oracle? Does it work? Anything I need to
    know?


    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Jul 7, 2010 at 9:15 am
    First of all I'd like to say that I agree with Mark below for snapshots of
    databases. Or indeed hot backup full stop. I may be misunderstanding this,
    but I didn't think that VMWare snapshots were directly comparable to storage
    snapshots of filesystems, in particular since they also snapshot the memory
    state of the machine at the time - though this is optional. In this sense
    isn't a VMWare snapshot comparable to a noarchivelog mode backup (albeit
    probably of a running instance) - i.e you rewind the entire machine to 12:00
    pm in Mark's scenario below and lose the work from 12 - 2. It seems to me
    that most of this discussion seems to be around storage snapshots of the
    database mount points.

    Or am I just lala again.

    Niall
    On Wed, Jul 7, 2010 at 3:28 AM, Bobak, Mark wrote:

    Hi Dave,

    This is no different than any other hot backup. You put database into
    backup mode, and copy off the datafiles, exit backup mode. Then, you can
    use 'alter database backup controlfile....' to take a logical or physical
    backup of the controlfile. But, do *not* backup the online redo logs.

    Suppose you take a hot backup (snapshot or otherwise) with tablespaces all
    in backup mode, at 12:00pm. Suppose you *did* take a backup of the online
    redo as part of that backup/snapshot. Now, at 2:00pm, your database
    crashes. So, what happens? You restore all files from the snapshot,
    *including* the online redo. Now, remember, that online redo is from
    12:00pm. You want to recover to 2:00pm, when the crash occurred. So, you
    have your archivelog backups. So, you put database into recovery mode
    (recover database), and start applying logs. Now, suppose the last
    archivelog you apply is from 1:55pm. Where are the last remaining
    transactions from 1:55pm till 2:00pm? They're in the online redo! But,
    you've overwritten it, with the data from 12:00p, which is useless, and, by
    the way, was already archived off, shortly after 12:00pm, and has been
    applied to the database. So, now you're stuck. You lost the data from the
    current online redo when you did the restore, and your
    online redo is from 12:00pm (backup time), which is useless to you. So,
    your only option is to do an incomplete recovery, open resetlogs, and
    explain to your boss why you lost all the transactions from 1:55pm-2:00pm.

    Now, you could argue that, in the above circumstance, you simply don't
    restore the online redo, and you'll be fine. That's true, (assuming that
    the snapshot technology you're using will allow for you to specify which
    files do and don't get restored), but, tell me, why backup the on-line redo
    in the first place? Show me a valid recovery scenario, from a hot backup,
    that requires a copy of the on-line redo to be in the backup set. I'm not
    aware of any such scenario. Can you think of one?

    Cold backups are a bit different. I always argue against backing up online
    redo with cold backup either. People always argue that the online redo is
    required if the database was not consistent (i.e. cleanly shutdown) when
    backup was done. And, I'd argue that if that's how you're doing cold
    backups, you're doing it wrong. (Though, I think I'd first wonder why the
    heck you're doing cold backups in the first place!)

    The only valid scenario that I can think of, where you *want* to include
    the online redo, is if you want a *restartable* snapshot, rather than a
    *recoverable* snapshot. Keep in mind, that restartable snapshot can only be
    used to restore the database to the point in time when the snapshot was
    taken. It's not recoverable, and cannot be rolled forward to a point in
    time.

    Hope that helps,

    -Mark
    ________________________________________
    From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On
    Behalf Of David Roberts [big.dave.roberts_at_googlemail.com]
    Sent: Tuesday, July 06, 2010 21:36
    To: oracle-l
    Subject: Re: VMWARE Snapshots to backup Oracle

    "Danger, Will Robinson, Danger"

    Why would enabling backup mode, mean that backups of online redo logs
    are not required?

    If you do a snapshot of the database while online, by implication the
    backup will only be useful after recovery, and the redo logs would be
    vital to guarantee the recovery?

    Perhaps, I've missed something, please correct me if I'm wrong.


    Dave

    On Tue, Jul 6, 2010 at 5:24 PM, Bobak, Mark
    wrote:
    Right. If you do begin backup/end backup, you should *not* snapshot online
    redo, and you get a recoverable snapshot. If you do *not* do begin/end
    backup, you *should* snapshot the online redo logs, and you get
    restartable
    snapshot.



    Hope that helps,



    -Mark



    From: oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org]
    On Behalf Of Andrew Kerber
    Sent: Tuesday, July 06, 2010 12:19 PM
    To: post.ethan_at_gmail.com
    Cc: oracle-l
    Subject: Re: VMWARE Snapshots to backup Oracle



    I am not using it, but I understand that it works as long as you use the
    alter database begin backup command prior to taking the snapshot (and the
    alter database end backup command after taking it).

    On Tue, Jul 6, 2010 at 10:18 AM, Ethan Post wrote:
    Anyone using snapshots to backup Oracle? Does it work? Anything I need to
    know?


    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'
    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l

    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Jul 7, 2010 at 9:17 am
    Hi Freek,

    I was going to argue that it depends on how you intend to use the snapshots. As I alluded to in my previous mail, you can use snapshots for taking restartable snapshots. This is where snapshots really excel. If you want to "rollback" the entire database to point in time, put everything (datafiles, online redo, and controlfiles) in the snapshot group, and take a snapshot. Now, you can do what you need to do, be destructive, etc. When you want to get back to where you were when the snapshot was taken, restore the snapshot, startup the database, and all those changes disappear, and poof!, you're back where you started from.

    Then, I *was* going to argue, if you want to do "recoverable" snapshots, then you'd need to set up the snapshot group w/ only the datafiles, and backup the controlfiles and archived redo separately, and not backup the online redo, like you'd do with a conventional backup. That's all true, and in principle, should work. BUT, then I realized a flaw in my thinking. A snapshot is *not* a backup! It's a set of pointers to a point-in-time version of your datafiles. If you suffer from a media failure, there's no place to recover that data from. The data isn't being backed up. A snapshot is simply a set of pointers to some data and a copy-on-write mechanism to manage the blocks that have changed since the backup. Because the snapshot relies on the "live" set of datafiles, it cannot be relied upon, in the event of a media failure.

    So, if you want to restore your database to a point in time, using snapshots on the entire database (redo,data,control) will work well. But, if you want a recoverable set of datafiles, that can be relied upon in the event of a media failure, then you should *not* be doing snapshots that rely on the primary copy of the datafiles.

    So, I'm significantly changing my position on this, but mainly for different reason than what you pointed out. :-)

    If you want to be able to quickly restore your database to a specific point in time, you can snapshot the entire database (redo, control, data), and that will give you a way to get back to the snapshot point in time. But it does not allow for point in time recovery of any kind.

    If you want a full recoverable backup, that you can do an incomplete, point-in-time recovery, do *not* use snapshots! They are a bad idea! They do not do a full media backup, so, in the event of media failure (which is what backups are meant to protect you against), you don't have any way to recover.

    I stand corrected.

    Hope that fixes and/or clarifies my previous comments,

    -Mark

    From: D'Hooge Freek [Freek.DHooge_at_uptime.be]
    Sent: Wednesday, July 07, 2010 2:59
    To: Bobak, Mark; big.dave.roberts_at_googlemail.com; oracle-l
    Subject: RE: VMWARE Snapshots to backup Oracle

    Mark,

    Your explanation is true for conventional manual (non rman) backups.
    However, when using vmware snapshots to take a backup, you can always only recover to the point of the last backup as all later archived redo logs will be lost when putting back the snapshot.

    The problem with using vmware to create a consistent db backup is that you actually require 2 snapshots: one of the database files while in backup mode, and one of the archivelogs after the database has been put out of backup mode (and the current redolog has been archived).
    But you can't combine them into one image.

    The only thing I can think of is that you create 2 snapshots and when doing a restore, you clone the snapshots into a new guest. Then you need to copy over the files from the clone guest to the original. This way you would also be able to recover to a later point then the backup as the archived redo logs (and online redo logs) are not overwritten in the original guest.
    However in practice I would not recommend such a method as there is a high change for error during the restore.

    I just thought of another method, but I'm not sure it will work.
    I thought that you can create "independent" disks in vmware that are not affected by snapshots.
    If so, then you can create your controlfiles, online redologs and archived redo logs on such disks and during the rollback to the snapshot they will not be overwritten.
    However, you will not have a backup of your archived redologs, nor of your controlfiles.

    Regards,

    Freek D'Hooge
    Uptime
    Oracle Database Administrator
    email: freek.dhooge_at_uptime.be
    tel +32(0)3 451 23 82
    http://www.uptime.be
    disclaimer: www.uptime.be/disclaimer
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Bobak, Mark
    Sent: woensdag 7 juli 2010 4:28
    To: big.dave.roberts_at_googlemail.com; oracle-l
    Subject: RE: VMWARE Snapshots to backup Oracle

    Hi Dave,

    This is no different than any other hot backup. You put database into backup mode, and copy off the datafiles, exit backup mode. Then, you can use 'alter database backup controlfile....' to take a logical or physical backup of the controlfile. But, do *not* backup the online redo logs.

    Suppose you take a hot backup (snapshot or otherwise) with tablespaces all in backup mode, at 12:00pm. Suppose you *did* take a backup of the online redo as part of that backup/snapshot. Now, at 2:00pm, your database crashes. So, what happens? You restore all files from the snapshot, *including* the online redo. Now, remember, that online redo is from 12:00pm. You want to recover to 2:00pm, when the crash occurred. So, you have your archivelog backups. So, you put database into recovery mode (recover database), and start applying logs. Now, suppose the last archivelog you apply is from 1:55pm. Where are the last remaining transactions from 1:55pm till 2:00pm? They're in the online redo! But, you've overwritten it, with the data from 12:00p, which is useless, and, by the way, was already archived off, shortly after 12:00pm, and has been applied to the database. So, now you're stuck. You lost the data from the current online redo when you did the restore, and your
    online redo is from 12:00pm (backup time), which is useless to you. So, your only option is to do an incomplete recovery, open resetlogs, and explain to your boss why you lost all the transactions from 1:55pm-2:00pm.

    Now, you could argue that, in the above circumstance, you simply don't restore the online redo, and you'll be fine. That's true, (assuming that the snapshot technology you're using will allow for you to specify which files do and don't get restored), but, tell me, why backup the on-line redo in the first place? Show me a valid recovery scenario, from a hot backup, that requires a copy of the on-line redo to be in the backup set. I'm not aware of any such scenario. Can you think of one?

    Cold backups are a bit different. I always argue against backing up online redo with cold backup either. People always argue that the online redo is required if the database was not consistent (i.e. cleanly shutdown) when backup was done. And, I'd argue that if that's how you're doing cold backups, you're doing it wrong. (Though, I think I'd first wonder why the heck you're doing cold backups in the first place!)

    The only valid scenario that I can think of, where you *want* to include the online redo, is if you want a *restartable* snapshot, rather than a *recoverable* snapshot. Keep in mind, that restartable snapshot can only be used to restore the database to the point in time when the snapshot was taken. It's not recoverable, and cannot be rolled forward to a point in time.

    Hope that helps,

    -Mark

    From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of David Roberts [big.dave.roberts_at_googlemail.com]
    Sent: Tuesday, July 06, 2010 21:36
    To: oracle-l
    Subject: Re: VMWARE Snapshots to backup Oracle

    "Danger, Will Robinson, Danger"

    Why would enabling backup mode, mean that backups of online redo logs
    are not required?

    If you do a snapshot of the database while online, by implication the
    backup will only be useful after recovery, and the redo logs would be
    vital to guarantee the recovery?

    Perhaps, I've missed something, please correct me if I'm wrong.

    Dave
    On Tue, Jul 6, 2010 at 5:24 PM, Bobak, Mark wrote:
    Right. If you do begin backup/end backup, you should *not* snapshot online
    redo, and you get a recoverable snapshot. If you do *not* do begin/end
    backup, you *should* snapshot the online redo logs, and you get restartable
    snapshot.



    Hope that helps,



    -Mark



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Andrew Kerber
    Sent: Tuesday, July 06, 2010 12:19 PM
    To: post.ethan_at_gmail.com
    Cc: oracle-l
    Subject: Re: VMWARE Snapshots to backup Oracle



    I am not using it, but I understand that it works as long as you use the
    alter database begin backup command prior to taking the snapshot (and the
    alter database end backup command after taking it).

    On Tue, Jul 6, 2010 at 10:18 AM, Ethan Post wrote:

    Anyone using snapshots to backup Oracle? Does it work? Anything I need to
    know?


    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Scott Sibert at Jul 7, 2010 at 2:56 pm
    Instead of a VMware "snapshot" you could "clone" the VM which makes a
    complete and separate (not relying on the "live" original disk files) copy
    of everything. You'd have the same issues at the Oracle level as you would
    with a snapshot but this would be a complete separate copy that you could do
    whatever you wanted with. (To clone, VMware uses the snapshot mechanism to
    keep the original files static while it copies them, and when the copy is
    done it automatically applies the transactions to the original disk files,
    removing the snapshot files.)
    --Scott

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 6, '10 at 3:18p
activeJul 7, '10 at 2:56p
posts7
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase