0

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.

Improve this question
4
  • For the simpliest case you can just do snapshot of datafiles, redo logs and controlfiles. Then in case of restore you will need database recovery which should happen automatically. To be on the safer side I'd do checkpoint (to reduce recovery time), ALTER DATABASE BEGIN BACKUP, snapshot, ALTER DATABASE END BACKUP.
    – Mindaugas Riauba
    Jun 30, 2015 at 20:24
  • 2
    without any warranty. some references: There is a note from Oracle Support"Supported Backup, Restore and Recovery Operations using Third Party Snapshot Technologies (Doc ID 604683.1)" about this topic. Here is a report from netapp concerning snapshot backups 'Using Crash-Consistent Snapshot Copies as Valid Oracle Backups, TR-3858'. IO can be suspended by the 'alter system suspend'But you should only implement such methods if you really understand what you are doing. Test you backup periodically.
    – miracle173
    Jul 1, 2015 at 6:57
  • Thanks @miracle173 , I will check and test about this command. A fast look at manual appear it's what I'm looking for... please , post as answer and if my tests work I will check as correct answer.
    – ceinmart
    Jul 1, 2015 at 11:22
  • 1
    Assuming your particular SAN infrastructure makes it possible, I would be much, much more concerned about building a relatively unique online backup process merely to avoid making documentation changes. It is far more likely that human error is going to bite you when you're doing something very unusual because it will violate one of the human's expectations. It is far more likely that you'll encounter an error because you're doing something slightly different from what anyone has tested.
    – Justin Cave
    Sep 25, 2016 at 3:27

2 Answers 2

Reset to default
1

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.

Improve this answer
0

I'm afraid there is no way how to do that, archivelogs are a must. Best approach is to use approach recomended by the vendor. Datafiles might be inconsistent anytime, Oracle writes changes into blocks even before the transaction is commited.

The only place where you can find an information needed to have data consistent is in (archived) redo-logs.

Oracle database can not be locked for writes, is designed for asynchonous backup.

Improve this answer
1
  • -1 I dont think that you need an archivelog if you have a crash consisten snapshot
    – miracle173
    Jul 1, 2015 at 6:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.