Hiya Bruno, Arno, I wasn't as clear as I should have been... the database I'm trying to back up isn't the Bacula database, it's an Oracle database containing a lot (700GB so far, expected to grow to about ten times that) of business data. Also, FYI, the Oracle-recommended backup process is to back up one tablespace at a time, not one table; Oracle handles looking after consistency as long as you follow all the rules.
Our backup currently takes 7 or so hours to back up over NFS to SAN disk, followed by about the same again to dump that copy to tape (the tape drive isn't on the same server - refer my original email). So I'd expect the restore to consume the best part of a day with all the peripheral mucking about. Cheers, DJ Message: 2 Date: Tue, 25 Nov 2008 10:47:55 +0100 From: Arno Lehmann <[EMAIL PROTECTED]> Subject: Re: [Bacula-users] How to set up large database backup To: "bacula-users@lists.sourceforge.net" <bacula-users@lists.sourceforge.net> Message-ID: <[EMAIL PROTECTED]> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Hi, 25.11.2008 09:58, Bruno Friedmann wrote: > Hi David, just a little remark. > > In such situation, I would ask the question to how get a slave db server > running > and I would save this slave with whatever is the best for dumping the > database. Same idea here. Two more things to consider: - Backing up the tables Bacula uses separately is not a good idea as there are relations between the table's contents. Any ou seriuosly don't want those to break, so you'd have to lock all the tables, and thus are where your problem started... - The catalog backup is done by a relatively simple shell script which you can easily modify or replace. It's the script called as a "Run Before Job" action in the catalog job's definition. Arno > > It also improve general availability for the enterprise db > (If you consume 12hours for backups, how much for a complete restore ?) > > > > David Jurke wrote: >> Hiya, >> >> We have a classic enterprise-type backup setup - a number of "clients" >> backing up across the network to a tape library attached to a dedicated >> backup host. This is the easy bit, and works quite happily. >> >> The tape library is an IBM TS3310 30-tape, 2-drive unit, "attached" to the >> backup host via the SAN fabric. >> >> The problem I have is with our large (expected to grow to several terabytes) >> database server. I'm told by the DBAs that the size and amount of activity >> on this database is such that putting the whole database into hot backup >> mode for the several hours it takes to back it up is a Bad Idea, it >> generates far too many log(?) files. The method they recommend is to put a >> single tablespace into backup mode, back that up, put it back to normal, >> repeat for each tablespace. The backup takes the same time, but doesn't >> generate anything like the amount of log(?) files. >> >> Trouble is, I can't work out how to do that with Bacula without creating a >> backup job for every tablespace, which is a bit ugly because the tablespaces >> change frequently and without warning, and if we have to change the Bacula >> config every time it'd be too easy to miss one and thereby invalidate our >> backups. Not to mention the amount of work involved. >> >> One way I can think to do it is if I can dive into Bacula and get hold of >> the "back up this file" program, and write a little script to do the >> database hot-backup change, back up that file (across the network via >> Bacula, straight to tape), change it back, rinse and repeat. It would need >> to be the Bacula component, not something like dd or tar (did I mention >> these are all Linux boxes), because it's going to be sharing a tape, in fact >> interleaved with, all the other backups. Pointers, anyone? >> >> Another way would be to abandon the Bacula tape format and present one of >> the tape drives (they appear as separate WWNs on the SAN fabric) to the >> database server as a dedicated drive for that host, and write in whatever >> format we decide to use. The problem then is controlling the library - the >> changer arm is under Bacula control for all the other hosts, so presumably >> the database server would need to liaise with Bacula to nominate tapes and >> switch them over when they fill up and when the backup finishes. So I'm back >> to the same problem as above - digging particular functionality out of >> Bacula and scripting round it... and I haven't managed to spot how to do >> this. >> >> The best solution would be a combination of these two - present one of the >> tape drives to the database server and have Bacula do the >> one-tablespace-at-a-time routine straight to tape. This would avoid the data >> crossing the network, thereby presumably speeding it up no end. But it's >> boggling my mind trying to work out if it's even possible to make Bacula >> have a tape drive on one machine (the db server) fed by a changer arm on >> another machine (the backup server). >> >> It would be possible, I guess, to ditch the backup server and put all that >> functionality on the database server. But so far I seem to have had to keep >> mucking about with the backup server, rebooting it occasionally to sort out >> confused tape drives (or something; not sure what's going on, but a reboot >> seems to fix it), and doing that with our production database is sadly >> frowned upon. >> >> The method we're using for now is to back up the database by copying it to >> disk on the backup server (via NFS), and then back that up to tape. Trouble >> is, this is handling the data twice, and is currently taking well over >> twelve hours all up, which given the expected growth is going to become >> untenable fairly soon, so any suggestions gratefully received! Not to >> mention we're going to run out of disk space!! >> >> >> >> Help? >> >> David. >> > > -- Arno Lehmann IT-Service Lehmann Sandstr. 6, 49080 Osnabr?ck www.its-lehmann.de ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users