online backup

[Tuval]

it is well known that most of DB's nowadays are supporting online backup, which basically means that the application that are sitting on top of those DB's can support 24x7. the question is how the DB's are doing so and how is online backup actually performs? Does the backup s/w lock the DB from access? for how long the access is blocked? lets have a test case of 50GB data that needs to be online backup - what is the time required by the backup s/w for backing it up online.
thanks

[paul courry]

In online backups changes ares stored off to the side and applied after the backup is finished. This does not affect access to the changed data.

The backup performs with normal speed in my experience, tape being so much slower than disk IO.

[Michael Tully]

We use Informix as our database and we do backups like this:

Daily backup of the whole database/logs to tape using OmniBack.
We also have a procedure where we have a number of transactions logs each 10 MB. Once one fills it goes straight to tape and providing the backup is successful, the log is removed. This gives us the ability to restore an entire backup and roll forward or back.

During the next year we will be utilising a different method whereby we will use a disk split between two different sites using an EMC symmetrix.
Live data to disk which is mirrored. This is then copied via SRDF to another EMC symmetrix to another site where the BCV copy will be split off and a backup to tape performed.
I agree tape is slow, but this will also depend on the type of data and volume usage.

[Brian M Rawlings]

As Paul points out, the database gets put into 'backup mode'. In essence, the database goes into read-only mode, so there are no changes made to it. Any writes go to a "change space", which Oracle uses if any data is accessed which has changed since the backup started.

One point is that it is necessary to 'flush' the system, so that all oracle data kept in system buffers (etc) get written to disk, but this only takes a few seconds (often sub-second).

The disk image/split technique that Michael mentions has several variations. Lots of arrays these days can take a 'snapshot' of the data, using a technique quite similar to that described above for Oracle's on-line backups. The "snap" image is then available to be mounted to a backup server, and backed up without impacting the database.

Using this technique, you quiess Oracle, and put it in backup mode. Then you take a snapshot of the volumes used by Oracle (which takes about a second), then you take Oracle out of backup mode. This all takes, like, 5-10 seconds, users are up the whole time, and backups happen at your liesure.

Obviously, you have to coordinate backups of log files as well, it isn't as simple as backing up a file system.

One of the other advantages of doing the snapshot backup thing is that, even after the backup is done, the 'snap' copy, for a certain point in time, remains on the array. Since you can keep several/many snaps of one disk volume, you keep a rotating set of snapshots (like, a week's worth). Then, recovery is a snap. Bring down Oracle, restore the snap (takes a second or so), bring Oracle back up and use logs to sync everything up, and you're back in a good state (from data corruption or a mistake or whatever).

Plus, you can use snapshots to do dev/testing on live data, or do batch/reports/whatever from live data, but without impact to production. Maybe some impact, since you are hitting the same spindles, but you can throttle processes as you need to, the point is they are hitting seperate 'instances', giving you control over much more than you used to have.

Sorry, this is a big topic, I'll shut up now.

Later... --bmr

[Chuck Ciesinski]

Tuval,

You didn't specify which type of database or back-up tool your using so we are all speakin in generic terms. In doing an on-line back-up, as everyone has said, the database iis in a read only mode and the amount of transaction processing you do impacts have fast the back-up will get done. Another factor is your back-up tool and how it interfaces with your database. Bottom line is yes, you can access data and continue processing, however, the caveat, IMHO, is keeping logical consistency of you data for disaster recovery if you have more than one system and they are have data that is 'shared' between them.

My $.04 for inflation,

Chuck Ciesinski

[Tuval]

Hi All,
Thanks for the answers. to clarify, we haven't chosen a tool for the backup and may consider any tool from Veritas, HP, Tivoli, Legato etc. the database can be Oracle, SQL Server, DB2.