"Hot-Backup" of Oracle DB using filesystem snapshot

[Adam Garsha]

Does anyone use VxFS filesystem snapshots to grab online backups of their Oracle databases on HP-UX? Any hang-ups on HP-UX?

Would this work?:


For each tablespace in my database {
put the tablespace in backup-mode
}

Create a snapshot of the entire "data" filesystem and mount it

For each tablespace in my database {
take the tablespace out of backup-mode
}

Copy all files from snapshot mount point to the "backup" mount point
Unmount and remove the snapshot


Do you have an example script that I could look at for particulars?:

Oracle says this: "Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode."

Still ok to use filesystem snapshots, correct? Since the Oracle recovery mechanism should handle the possibility of partially written blocks?

Thanks for any input.

[Adam Garsha]

Sorry for terminology error, I mean LVM-based snapshots.

[Steven E. Protter]

Shalom,

So long as writes are cached somewhere, this should work.

You have another option to do this, called Online JFS. It permits you to freeze writes on an entire logical volume while you do a cold oracle backup.

This is somewhat superior to your methodology.

Down database
freeze lv, create temporary one for writes
start database (elapsed time usually very short)
begin cold backup of frozen logical volume.
close temporary logical volume (puts writes into the previously frozen logical volume)

Of course Online JFS costs money.

SEP

[Bill Thorsteinson]

You do not need to freeze the lv to to
and Oracle Hot backup. You can
mark a tablespace as being backed up
with
alter tablespace tbs_name begin backup;
and end the backup with:
alter tablespace tbs_name end backup;

Roll forward will apply all changes from
the begining of the backup during recovery.

I don't use RMAN but I would expect that it
does it automatically.

I currently use a modified oracback.sh
script. We use hot backups exclusively
in developmnet with frequent rollbacks on
some databases, and occational clones.

[Adam Garsha]

Points will come after I get a few more posts. Thanks.

Question, we have OnlineJFS, but I am unfamiliar with the "freeze" idea. I had actually posted a question about the ability to freeze/thaw filesystems on HP-UX in the past and was told that it wasn't implemented for VxFS on HP-UX (yet).

Can you point me toward documentation that describes this freeze/thaw mechanism (sounds more breakable then simple copy-on-write snapshot)--unless you were just explaining "snapof" in a different way.

Also, I seek to not shut down Oracle... that is the tricky part. If I could shutdown (even for two minutes) I would just do the snaps and not have bothered with my post.

Said another way, I am sure that if I shutdown Oracle, the snap technique will work great. I was wondering how legitimate my backups will be if I leave Oracle running, but ALTER the tablespaces into backup mode.

[Adam Garsha]

Regarding the comment about freeze/thaw above. I am still unclear what was being talked about.

It looks to me that the VxFS freeze and thaw mechanism's are still only exposed as ioctl calls... no binary/scriptable utilities are included with the OS. I have example C code and could fortify it... but I wouldn't be able to get that past change control here.

Anyhow, previous posts had said it is all-good to do what I want; my concern was based on the oracle statement: "Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode"

[TwoProc · Accepted solution]

Adam,

That method should work just fine. I see no reason why that wouldn't work well, and you don't have to bring the database down to do it.

Essentially, instead of doing a single file at a time for backups, you'd essentially put the whole thing in a hotbackup state, snapshot, and then put it all back live. Good idea.

You're right about the fact that the data files still get written to, they do. Of course, from your snapshotted area you'd never see that type of activity, meaning that you'd have almost no data recovery (rolling forward archive logs) when copying or restoring that backup. The reason that it's OK that the data files get written to is that putting a tablespace in hotbackup mode primarily freezes that incrementation of the SCN number in each file until the file copies are finished and the backup mode is put back to normal. This means that a recovery would roll forward through all changes to the files until the scn number is caught up, even if the writes have taken place or not (it would just rewrite over the ones that have already taken place, plus anything else that hasn't).

Anyway, I like the idea, and think it could work great for you.