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:dan AT langille DOT org] 
Sent: Wednesday, 26 November 2008 07:12
To: David Jurke
Cc: bacula-users AT lists.sourceforge DOT 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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users