Is possible do an online snapshot backup , without archive logs at Oracle Database


Using Oracle Database Standard Edition 11r2 .

I'm looking a way to backup the database with zero downtime , using a snapshot of datafiles (LVM snapshots) and without need backup de archive logs.

All options found so far , we will need save fews archives logs.

My reason for this requirement is today our databases are put offline to copy our datafiles. There are procedures and documentation about backup and the restore . I want to minimize any change here at backup and restore process , all looking for minimize point of failures , human error which could be invalidate all backup.
So , I need to exclude the archive logs from the equation , do not want they involved.

I think something like :

  • flush all data from database to disk (checkpoint)
  • lock any write at the database datafiles (how?)
  • create the snapshot
  • release the database
  • mount the snapshot
  • copy all data of the snapshot
  • destroy the snapshot.

And if need to restore , just restore de data files and open the database.

Best Answer

Yes, you can do an online backup without archivelogs if you have a storage system or backup tool that can take a point in time snapshot that will be consistent across all your datafiles and control files. It sounds like you have such a tool and are using it for cold backups. If that is the case, your documentation will still need to change. If there is a problem with the system and you need to restore the backup, starting up the restored database will be as though the power plug was pulled a the time of the backup, therefore crash recovery will likely be necessary.

Given that your documentation needs to change anyway and that you want zero downtime backups, you should probably re-think your resistance to archive logs. It limits your recovery possibilities greatly. For example, if due to a disk error you get a bad block in one of your datafiles, and you have older consistent copies of that datafile, you can recover it using archive logs. With snapshots taken online you would probably end up doing lengthy extracts from multiple snapshots of the system (assuming you can tolerate the downtime) and would probably still loose data.