Hiya Dan, I'm not a DBA, so I don't know the internal details, but to do a hot backup with Oracle you have to put it into hot backup mode, take a copy of the data files, take it out of backup mode, switch logs, and back up all the log files. Then at restore time you restore all the data files and fire up Oracle. It will ask for all the log files and apply them, thus bringing the database back into a consistent state.
The problem is, while the hot backup is running it's got to write a copy of all the transactions to log files, to leave the data files in a semi-consistent state while you back them up. We're an international telco, the database is receiving call detail records for every phone call in or out of our network; I'm told the volume of records is about five times the total of all the banking transactions handled by all the banks in the country (NZ) combined. Over the several hours it takes to back up the database, this creates a lot of extra log files, hence the one-tablespace-at-a-time scheme to keep it down a bit. The DBAs have provided a script to do this, taking a copy of the data files to disk, over NFS. This is a proven method, and works well... for now. But the database is fairly new and hence small (700GB) and is expected to grow by roughly an order of magnitude, so we will a) run out of disk space to copy it to, and b) run out of time in the day to back it up. Part of the latter problem is that we're copying the data twice - once to disk, and then again from disk to tape - so the backup is taking twice as long as it could be. I really need to find a way to back it up straight to tape, integrated with (though not necessarily using) Bacula backing up the other servers in our environment. Hence my mention of scripting around bits of Bacula functionality, integrating Bacula into the DBAs' script. Or, if it's not possible, someone please tell me there's no way to do it and we'll go and spend some money on something like Legato Networker + Oracle RMAN. I like Bacula, it has a few quirks but seems good at what it does, but I suspect maybe Kern hasn't got a multi-terabyte SAN-based Oracle environment sitting around for him to play with, so handling some of the "larger" problems may be out of scope... *grin* Cheers, David. -----Original Message----- From: Dan Langille [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2008 07:12 To: David Jurke Cc: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup On Nov 25, 2008, at 2:10 AM, David Jurke wrote: > 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. There is no way to dump interactively? I'm a PostgreSQL fan and creating a backup doesn't add overhead. Are the DBAs sure that this won't create an inconsistent backup? That is, to restore, you are combining data from different times. I'm sure that transactions have occurred during that time... etc. What database are you backing up? > 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. Why can't the DBAs provide a script that will dump all the tablespaces, then you back them all up. -- Dan Langille http://langille.org/ ------------------------------------------------------------------------- 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
