"Live" Backups

[Mike Fisher_5]

Hi all

BACKGROUND:

A client has asked me to specify a new system
It's to serve a 24-hour call centre

They gave me a spec based on one of their other call centres...
"On a daily basis the server must be capable of
i) Performing all associated backups of oracle database(s)/logs etc.
& a full system backup
(excluding the oracle specific backups).
ii) Backup solution must be capable of carrying out the backup of the database(s) with the application & data still available to users.

I asked them how their current call centre achieved the above
[I assumed they'd mention say...
OnLine JFS, Veritas NetBackup or A.N.Other]
but they claim they just use commands within the OS [HP-UX 11.0]

MY QUERY:
Is this possible with HP-UX straight out of the box?
[frankly I thought it not possible without some hefty scripting]
Is it advisable?
Is there a better approach I could recommend to them?

All views are welcome Makarov

Mike (rabbit warren) Fisher

[Mark Greene_1 · Accepted solution]

We have an new system that went live last month. It runs Oracle, and to back it up we run the Oracle dump to disk, and then backup the dump files to tape using fbackup. The backup runs at 1:30am when the system load is the lightest, but the users still have access to the system.

HTH
mark

[Pete Randall]

Mike,

We use OmniBack to do this with an Informix database, but we also do it in the middle of the night when there is next to nothing for activity. I wouldn't think it would be very reliable otherwise.


Pete

[Jarle Bjorgeengen]

Mark,

What about database consistency when dumping ? Do you put the db in backup mode or something ?

Rgds Jarle

[A. Clay Stephenson]

Oh sure, it's possible to backup a running Oracle instance even with good ole tar without bothering with any of that pesky scripting. If the specifications simply call for a backup then you are home free.

Now did they really mean a useful backup? i.e. one that could reasonably be expected to restore an Oracle instance to life? In that case, things are a lot more complicated.

If they have RMAN then that can make the database scripting part much easier but otherwise you will need to write scripts to enable Oracle hotbackups. They are not hard but they are also not "out of the box".

The other thing to consider is the possibility that metadata that describes the Oracle data may also need to be kept "in sync" - that can really throw a monkey wrench into the works. The point is that you (and they) need to do a lot more digging before settling upon a solution.

[Pete Randall]

Mike,

I suppose you could also look into MirrorDisk/UX. Mirror all the DB disks, break the mirror and do the backup on the copy, then re-sync.


Pete

[Duncan Edmonstone]

Sure its possible, and not that complex... many sites have some very simple scripts which put oracle datafiles in backup mode, backup using tar or fbackup or cpio, and then put everything back in normal mode. A very simple inflexible script to do this wouldn't be much more than 50 lines of code.

but of course this isn't necessarily the best way to back these things up, as you generally end up with a bespoke solution that only the local admins know/understand, and when it comes to actually doing a recovery - well oracle can't help that much if they don't have a full handle on how you have backed up...

a better approach then is to use 'industry best practice', and that best practice for oracle is currently RMAN with some sore of media manager such as omniback or netbackup.

if performance is an issue you might look at split mirror technology to offload the IO to another host and reduce the amount of time the database is in hot backup mode - that said, for call centres although availability is an issue, performance rarely is late at night when there are 5 reps and maybe 10 calls an hour!

HTH

Duncan

[Steven E. Protter]

One thing I can tell you is that it is possible to do it the way they said. If you use the rman utility to back up Oracle, you can cut hot backups to disk and use fbackup to get the backups on to tape.

There are actually certain advantages to this approach because rman creates backups that are easy to roll forward from in the oracle recovery process.

Omniback's utility for oracle does the same thing, uses rman.

Veritas also uses rman for backups both hot and cold.

Having used all three, I'd say if you have the money Veritas is the best answer.

Depending on the severity of data loss, I'd be cutting hot backups every 6 hours or so.

It is possible with mirror/ux to break mirrors and get some kind of a cold backup but Oracle doesn't support this methodology.

SEP

[Mike Fisher_5]

Further info [Forgot to mention]
The plan is to use MirrorDisk

[Pete Randall]

I just knew it! I must be a genious!


Pete

[Mike Fisher_5]

Pete

By coincidence the current
Guinness ad campaigne is based on a Genius theme

Was your mother's milkman Irish by any chance?

[Pete Randall]

I do seem to have a touch of Irish in me, courtesy of the milkman or not.

I was curious about the Makarov reference. Are you referring to our dear friend Lubomir (now know as LM) or the original Walther?


Pete

[Ian Dennison_1]

Mike,

If you want to really impress, and have a DR Plan with a second host in place for this database, here's what you can do,...

Setup a standby database on the DR Server, applying Redo Logs.

When you want to take a backup, stop the database on the DR machine, backup it up, then bring it up and catch up again.

Complete offline and recoverable backup of the Standby Database, can then be restored as production if needed and further redo logs applied to bring it up to date.

What maintenance downtime have you negotiated into this System? or have the Users seen that 24x7 is "possible" and therefore assumed it is "practical"?

Share and Enjoy! Ian

[Hai Nguyen_1]

Mike,

You can use the Oracle RMAN feature + OmniBack to hot backup a database. I have a RMAN script to run as a pre-exec in OmniBack filesystem backup.

Hai

[Mike Fisher_5]

Pete

I was referring to
Luby/Makarov/Kalasknikov

I genuinely wanted him to reply
I'm just a little sorry for a guy who can't just play from the hand he's dealt
[It HAS to be a guy - right?]

But the Walther PP is better than the Makarov 9mm

[Pete Randall]

Admit it, Mike - you just wanted to give him a 0, didn't you?

;^)

[Stuart Abramson_2]

The normal "modern" solution, as mentioned by a few people above is:
1. Put the tablespaces in hot backup mode.
2. Backup the running databases.
3. Take the database tablespaces out of hotbackup mode.
4. Reapply logs that accumulated while in hot backup mode.

Many products, such as Veritas NetBackup, have "agents" that allow you to do this. You purchase them. I think that they interface with RMAN.

The split-mirror solution, with HP-UX MirrorDisk or EMC Timefinder/BCV, allows you take the bakcup from another server, removing the backup I/O load from your production server and reducing the amount of time your production database has to stay in Hot Backup mode:

1. Put the DB tablespaces in hot backup mode.
2. Split the mirror off.
3. Put the DB tablespaces back in normal mode.
4. Reapply logs.

5. Present/mount mirrors to backup server.
6. Take backup.

[Mike Fisher_5]

Pete

I'm so transparent
Yes...
I was gonna do a "Null Point" where it don't shine

[Chris Vail]

If your client has A LOT OF MONEY: look into EMC's Symmetrix as a solution. Its what we use here. They have what they call a BCV or Business Continuance Volume. This concept involves mirroring disks (with their hardware/software), but with the curve ball that you have a 3rd disk synchronize with the first two. Once the synchronization is complete, this mirror is broken, and the disk is attached to another host. You make your backup to tape from this second host. Recovering is a cinch: just mount the BCV back to the original host, or you can even bring Oracle up on the second host. We've done this: its a nice way to build a report server against semi-live data.
It costs a lot of money, and there is a lot of training involved, but if you want REAL disaster recovery abilities, this is the industries' best. I can put you in touch with the right people if you're interested.

Chris

[Lars-Olof Fermvall]

I have been using Online JFS for backing up Oracle AND restoring it. The trick is that you have to have all the relevant files (control, data files..) in the same file system, which you then 'snap' mount. Then you can use either fbackup or vxdump to take the actual dump. We have a rather small Oracle application, part of an Information Manager type product sitting on top of process control systems, so that was not a problem.
When I mentioned what I was doing to an Oracle lecturer here in Melbourne he was very surprised that it worked, but then he checked with some colleagues and they said it should work. So far I have never had a problem restoring such a backup and get Oracle up and running again. This method is suitable if you have it all relevant files in the same place (file system), of course, and could fail on systems where files are spread over many file systems.

[Mike Fisher_5]

This is one way for me to gain an education
Any more for any more?

[Massimo Bianchi]

Hi,
speaking about oracle: it's quite easy, using the "begin backup" functionality.


It require a little knoledge of oracle and of its restore tecnique, but it's feasyble.
I also tested the restore and recovery.
??
I did it for a customer who was not willing to buy extra software.

Massimo

[Victor BERRIDGE]

Hi,

Well evreythings is possible, its generally not at the backup you know you did the good choice its when it comes to restoration...

With just os commands all has been said in the previous mails (tar works fine - I tested against fbackup [problems with open files] etc...) the question is data integrity, now I doubt you being able to backup while open you oracle data, I believe they are fooling you when saying "they just use commands within the OS [HP-UX 11.0] "
Im sure when they are playing with words...
And in fact they do an oracle-export of tha database and yes in this case oracle is still running...
but in order to be sure to use it, at the instant T you decide to do a full export (with the good options for using online the DB) you would need also make a copy on the same backup of the exportfile of the controlfiles and redologs and maybe more...

All the best
Victor

[Ian Hillier]

All of those solutions sound great - and complicated! I use Omniback to back up a linux/oracle 9i box and it created the following script for the 'Whole Online/Delete' option. Granted, this script only works with the omniback provided lib, but it's just sql and could probably be modified to work for you.

in rman...
run {
sql 'alter system archive log current';
allocate channel 'dev_0' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=prdweb,OB2BARLIST=prdweb1)';
allocate channel 'dev_1' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=prdweb,OB2BARLIST=prdweb1)';
allocate channel 'dev_2' type 'sbt_tape'
parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=prdweb,OB2BARLIST=prdweb1)';
backup incremental level 0 filesperset 1
format 'prdweb.dbf'
database
include current controlfile
archivelog all delete input
;
}

We also use omniback to do online backups of our SAP systems AND have successfully recovered them with very little pain and heartache. As mentioned above, you need to get all the control files and online/offline redo logs.

Regards,

John

[Dave La Mar]

Mike -
We have an Oracle db of about 1.5tb in size. We had been using RMAN, incrementals, colds, business copies.
When one day faced with a restore
we timed the restore from the backups using RMAN and found we would be down over 8 hrs.
A full cold backup, 4+ hours.
A restore from the business copy was one hour but it did require reentry of a lot of data since the last resync.
We now nightly, resync, bring down the the db, split the business copy, bring up the db.
The down/up of the database costs us 15 minutes/day down time. We then back up the split business copy volumes.
With business copy you can sync and split at intervals during the day and recover from any of these to any give point in time.
Still not fool proof by any means, just what our shop is doing. At one time we actually had two business copy versions for different critical points of recovery.

When the day comes, please let us know what the ultimate decision was.

Best regards,

dl