Re: [Bacula-users] How to set up large database backup
2008/11/27 David Jurke [EMAIL PROTECTED] Whoa! Okay, I need to go talk to the DBAs about this lot, lots of it is too far on the DBA side for me to comment intelligently on it. It does sound promising, though - if we back up daily only the current month's data (the rest will be in static partitions), only the weekly(?) full backup will have space/time issues. But, after some thought... Basically, as I understand it, your first group of comments are about not backing up empty space, as per your example if there is only 10GB data in a 100GB data file. However, our database is growing rapidly, and our DBAs tend to allocate smaller tablespace files more frequently (rather than huge files seldom), Bad idea(tm) Oracle makes checkpoints ( something similar to a 'stamp of time' ) in each datafile header and other Oracle critical files and mem structures, to be able to restore the whole scenario to a known state if a crash occurs. This 'marks' are doing often, and of course they have a cost in CPU/IO time. Having more datafiles more time will be spent coordinating/executing the stamp ( it's an exclusive operation ). Again, could be very useful to know what release of Oracle RDBMS are you running ( 9iR2, 10gR2...? ) and what platform/architecture ( Linux x86_x86_64, itanium2...? ) but I think that you will have no problems to define bigger datafiles ( think in mind that Oracle defines 128GB datafile as the maximum for a 'small file' type ). We are using tablespaces of 360GB each one using groups 128GB databafiles. Another reason to use bigger datafiles, less time expended by the DBA creating datafiles ;) so at any time there's probably not more than 20 GB of unused space in the database files, which is less than 10% of our database currently, and the % will only decrease. So yes there would be a benefit, but not huge. Ok, rman goes to rescue again ;) . If you're in 10gR2, you can define a 'tracking file' where Oracle will store a little info about what blocks are changed. Then, you make a first full backup, but after that, you can use this tracking file with rman and it will only backup the data blocks changed. The tracking file could be 'reseted' at convenience ( after each backup, for example ). I did not say it before, but rman is able to do full/cumulative/differential backups. Your RMAN example still backs up the entire database to disk and then later to tape, which leaves us with the problems of disk space and backup duration. As mentioned above, these won't be mitigated very much by only backing up data and not empty space. rman have a lot of options, of course you can backup only a particular tablespace or a group of them, only a datafile even if the tablespace has more than one, only archive logs, etc... http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/toc.htm What I'd like to do is halve the backup time and remove the requirement for intermediate disk storage by backing up the tablespaces (RMAN or otherwise) straight to tape. Oracle provides a software api for the backup solution developers ( Legato, Tivoli, etc...) but is offered under $$$, don't know the prize or the conditions For which the only solution anyone's suggested which would actually work with Bacula is a variation of Kjetil's suggestion, running multiple backup tasks, one per tablespace. A little ugly in that there will be a LOT of backup jobs and hence a lot of emails in the morning, but it would work. do it with rman ( datafile per datafile or tablespace per tablespace ). rman will NOT block the datafile header and no extra redo info will generated. The DBAs are already talking about partitioning and making the older tablespaces read-only and only backing them up weekly or fortnightly or whatever, which solves the problem for the daily backups but still leaves us with a weekly/fortnightly backup which won't fit in the backup staging disk and won't complete before the next backup is due to kick in. It may be that we have to just accept that and not do daily backups over the weekend, say, working around the disk space issue somehow. disk is cheap today, think about it. ¿Do you need the latest and fastest disks? IMMO, no. For various reasons our hot backup site isn't ready yet. The business have agreed that in the interim an outage of several days is acceptable, while we restore from tape and start it all up. At this stage (it's a work in progress), an outage of this application doesn't affect external customers, only internally, and the pain is not great. Long term we will have a hot database replica at another site ready to step up to production status in the event of problems, but as I said this isn't ready yet. I don't know whether it will be Oracle Data Guard, but it'll be equivalent. We already do this for other applications/databases, the DBAs are well on top of this stuff. I don't know the details. The Data Guard option is the best, a
Re: [Bacula-users] How to set up large database backup
take a look at http://www.oracle.com/technology/deploy/availability/pdf/oracle-openworld-2007/S291487_1_Chien.pdf Backup and Recovery Best Practices for Very Large Databases (VLDBs) Regards D. - 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=100url=/___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
David Jurke wrote: The DBAs are already talking about partitioning and making the older tablespaces read-only and only backing them up weekly or fortnightly or whatever, which solves the problem for the daily backups but still leaves us with a weekly/fortnightly backup which won't fit in the backup staging disk and won't complete before the next backup is due to kick in. It may be that we have to just accept that and not do daily backups over the weekend, say, working around the disk space issue somehow. We use a similar partitioning scheme and make stuff read-only once it is older. For backups, what we do is split the read-only partitions into 8 groups, and once a week we back up one of these 8 groups on a rota. This means that each week we only backup one eighth of the RO data, and each RO file gets backed up once every 8 weeks. The tape retention is set so that we always have a couple of spare copies of each RO file in the archive before it is overwritten. This works pretty well for us. As an additional note, rather than creating multiple smallish files on a regular basis, I would suggest resizing files - you can even automate this by use of the AUTOINCREMENT clauses on datafiles. This keeps the number of files lower. This can have an impact during checkpointing (less files = faster checkpointing). Although there's no realistic limit on file sizes these days most of the time, I would suggest keeping files to say 50Gb or smaller, just because of recovery times when only a single file needs to be recovered. -- Mike Holden http://www.by-ang.com - the place to shop for all manner of hand crafted items, including Jewellery, Greetings Cards and Gifts - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
2008/11/25 David Jurke [EMAIL PROTECTED]
Hiya David,
I'm assured by our DBAs that this one-tablespace-at-a-time method works -
it's apparently what Oracle recommend, and they do restores and recovers
frequently for things like creating test databases, so it's a proven method.
It could be that my description of the process was rather lacking, there's
likely other details around it, but the basic theory is sound. Hot-backup
one tablespace at a time, followed by a log switch and back up all the logs.
Of course, it works, but again:
- if you have a tablespace with a datafile of 100GB and the tablespace is
empty at 90% ( only 10GB of data ) backing up the datafile as you do now,
you are spending a lot of time/resources to copy 90% of no data. Makes sense
to backup garbage?
- In normal operations, Oracle use the redo logfiles to store the changes
made to data blocks, in backup mode Oracle needs to store in the logfile ALL
block, even if the data changed is only one byte in this block.
Read http://www.speakeasy.org/~jwilton/oracle/hot-backup.html ( googled )
Then, you are spending a lot of time and resources to backup data that is
really not ecessary to backup. And you are worry about the time/disk/tape
need to backup your DB, aren't you?
Using RMAN is one of the options I'm looking at, but I haven't managed to
spot a good description of how to integrate it into Bacula to achieve a
backup straight to tape, either a directly attached drive or across the
network.
Every database, and how it is used, is different and needs a personalized
study, but you can give rman a try, using your development environment.
Backup your development database using your method, after that look the time
and volume data backed up ( take in mind the extra admin cost to mantain the
scripts / verification ) using your method
Now, let's give a try with rman:
on your development host ( the database must be in archive log mode ) ,
connected as oracle software owner and with the environment var pointing to
your desired ORACLE_HOME / INSTANCE, execute:
rman target / nocatalog
RMAN run {
ALLOCATE CHANNEL D1 DEVICE TYPE DISK FORMAT '/backups/%d_TEST_%u_%s_%p'; #
where /backups/ is a destination with space to store backup result, can be
nfs
#if your Oracle release is 8i :
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
#if 8i
BACKUP DATABASE PLUS ARCHIVELOGS;
#WE MAKE BACKUP OF CONTROL FILE, YOU CAN PUT HERE COMMANDS TO BACKUP THE
PARAMETER FILE, TOO
COPY CURRENT CONTROLFILE TO '/backups/TEST_CONTROLFILE.BCK';
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RELEASE CHANNEL D1;
}
after that, you could backup '/backups/' with bacula
As I said, we need more info ( database in archive log mode?, Oracle
release? How is the data stored? are you using partitioning? etc... ) but I
think that your backup time could be decreased a lot with a correct study of
your situation.
For example:
Your system seems to be highly transactional, but I think that a lot of
data, after inserted, is never touched again. You can make uise of
partitioning to move 'historical' data to definde 'historical tablespaces'
and backing up one time and putting them in readonly mode, focusing your
daily backup efforts in a subset of data ( if you need to restore the
entire database you will have available the 'historical tablespaces' from an
'historical backup' ) plus the last daily backup + archivelogs.
As Dan pointed ( and as I did in a previous mail, pointing about Oracle Data
Guard ), seems that you need urgently a site of contingence. If your host
crashes your service will be down until you get again a host with the
restored database uprunning. What time could be this? Can you ( your
customers ) wait for it?
What will happen if you are out of service for a while?
Configuring an Oracle Data Guard, you have guarantee of a remote site
replicating the changes of your production database, if a big crash occurs,
in a few minutes ( the time spent in putting your standby database in
'production mode' and may be pointing your dns entries to the standby host
), you will have your service uprunning again
Data Guard concepts:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96653/partpage1.htm#436380
D.
-
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=100url=/___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
Hemant Shah wrote: How about exporting/dumping one table at a time and then backing up the exported data using bacula. Unfortunately, export is not really suitable for a DR solution. The main reason is that each table is exported at a different time, and so the complex relationships between individual records cannot be maintained (unless you use the CONSISTENT flag on the export, but the timeframe involved here would blow your rollback resources, and you would have to do the entire export as a single operation). If you are using Oracle 10g or above, I would recommend RMAN together with BLOCK CHANGE TRACKING. These 2 combined allow you to only back up the data that has actually changed, thus reducing the size of your backup significantly in most situations. If you have enough spare disk space on the server (roughly the same as the database size plus some more - a bit vague I know but this varies significantly from one database to another), then look into setting up a FLASH RECOVERY AREA and use that as a staging area for your backups. -- Mike Holden http://www.by-ang.com - the place to shop for all manner of hand crafted items, including Jewellery, Greetings Cards and Gifts - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
I thought I'd get back to the original question :-)
David Jurke [EMAIL PROTECTED] writes:
The problem I have is with our large (expected to grow to several
terabytes) [Oracle] 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.
[...]
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?
I think you need to use a FIFO. actually, many FIFO's, one for each
tablespace.
essentially, you set up each FIFO ready to go like this in a
pre-script:
mkfifo /var/backup/tablespace1
cat /var/oracle/tablespace1.data /var/backup/tablespace1
cat will block, waiting for someone to open the FIFO and consume
bytes. so, when Bacula gets around to this file, cat will wake up and
transfer data.
you'll want to replace cat with a program which sets the correct locks
in Oracle, reads the data and dumps it into the FIFO, then releases
the locks.
the problem in your case is that you don't want to do the locking
until Bacula starts reading, and this calls for a little systems
programming. here's a rough outline: you'll need to dynamically
determine the list of tablespaces somehow, and you'll have to supply
the code to do the actual dumping :-)
#! /usr/bin/perl -w
use strict;
use POSIX;
my @tablespaces = (ts01, ts02, exp01, bal01, bal02);
my $dumpdir = /var/backup;
unless (-d $dumpdir) {
mkdir($dumpdir)
or die $dumpdir: $!;
}
my %children;
for my $ts (@tablespaces) {
my $pid = fork();
if ($pid == 0) {
# the child
mkfifo($dumpdir/$ts, 0600)
or die $dumpdir/$ts: $!;
open(my $fifo, , $dumpdir/$ts)
or die $dumpdir/$ts: $!;
# open will block until there's a reader
print STDERR Dumping $ts\n;
# ... do the work here. silly example just copies /etc/hosts
open(my $src, /etc/hosts);
while ($src) {
print $fifo $_;
}
close($src);
close($fifo)
or warn $ts: Closing FIFO failed: $!;
unlink($dumpdir/$ts)
or warn $dumpdir/$ts: $!;
exit(1);
} elsif ($pid 0) {
$children{$pid} = $ts;
} else {
warn $ts: fork failed: $!;
}
}
# Now we have started one process per tablespace in the background.
#
# You may want to skip this last bit, since the pre-script needs to
# exit before Bacula starts the backup. If you include it for better
# logging, you need to make sure the pre-script itself is started in
# the background.
my $pid;
do {
$pid = waitpid(-1, 0);
if ($pid 0 $? != 0) {
print STDERR $children{$pid} (pid $pid) exited with code , $?8,
\n;
}
} while ($pid 0);
--
hope this helps, | Redpill _
Kjetil T. Homme | Linpro (_)
-
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=100url=/
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
Thanks Kjetilho,
Sounds ingenious!
I think it'd need some tweaking to delay putting each tablespace into backup
mode until Bacula is ready to back it up - one of the problems I have is that
we can't put all the tablespaces into backup mode at the same time because of
the volume of logs produced.
Perhaps I could run a script before the backups start to query the database for
a list of tablespaces, and build a set of Bacula job configs, one per
tablespace, then tell Bacula to reload its config. Each job has a pre-backup
script which dumps the tablespace (either to disk or to a FIFO), and the
fileset specifies that disk file or FIFO. If I'm dumping to disk, I'd need to
schedule them all with the same priority to allow the second job's dump to disk
to run in parallel with the first job's backup to tape, and so on down the
chain. If I use a FIFO, then they should be scheduled sequentially.
In fact, Would I need to dump the database to anything? Wouldn't I just have a
pre-backup job to put the tablespace into backup mode, bacula that tablespace's
file straight to tape, then a post-backup to put the tablespace back to normal?
Apart from the inherent ugliness of building backup configs dynamically on the
fly, and having a backup job and hence report for every single tablespace, it's
an intriguingly simple idea - thanks for that! I'll have a chat to the DBAs
about it.
DJ
-Original Message-
From: Kjetil Torgrim Homme [mailto:[EMAIL PROTECTED]
Sent: Thursday, 27 November 2008 14:17
To: bacula-users@lists.sourceforge.net
Subject: Re: [Bacula-users] How to set up large database backup
I thought I'd get back to the original question :-)
David Jurke [EMAIL PROTECTED] writes:
The problem I have is with our large (expected to grow to several
terabytes) [Oracle] 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.
[...]
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?
I think you need to use a FIFO. actually, many FIFO's, one for each
tablespace.
essentially, you set up each FIFO ready to go like this in a
pre-script:
mkfifo /var/backup/tablespace1
cat /var/oracle/tablespace1.data /var/backup/tablespace1
cat will block, waiting for someone to open the FIFO and consume
bytes. so, when Bacula gets around to this file, cat will wake up and
transfer data.
you'll want to replace cat with a program which sets the correct locks
in Oracle, reads the data and dumps it into the FIFO, then releases
the locks.
the problem in your case is that you don't want to do the locking
until Bacula starts reading, and this calls for a little systems
programming. here's a rough outline: you'll need to dynamically
determine the list of tablespaces somehow, and you'll have to supply
the code to do the actual dumping :-)
#! /usr/bin/perl -w
use strict;
use POSIX;
my @tablespaces = (ts01, ts02, exp01, bal01, bal02);
my $dumpdir = /var/backup;
unless (-d $dumpdir) {
mkdir($dumpdir)
or die $dumpdir: $!;
}
my %children;
for my $ts (@tablespaces) {
my $pid = fork();
if ($pid == 0) {
# the child
mkfifo($dumpdir/$ts, 0600)
or die $dumpdir/$ts: $!;
open(my $fifo, , $dumpdir/$ts)
or die $dumpdir/$ts: $!;
# open will block until there's a reader
print STDERR Dumping $ts\n;
# ... do the work here. silly example just copies /etc/hosts
open(my $src, /etc/hosts);
while ($src) {
print $fifo $_;
}
close($src);
close($fifo)
or warn $ts: Closing FIFO failed: $!;
unlink($dumpdir/$ts)
or warn $dumpdir/$ts: $!;
exit(1);
} elsif ($pid 0) {
$children{$pid} = $ts;
} else {
warn $ts: fork failed: $!;
}
}
# Now we have started one process per tablespace in the background.
#
# You may want to skip this last bit, since the pre-script needs to
# exit before Bacula starts the backup. If you include it for better
# logging, you need to make sure the pre-script itself is started in
# the background.
my $pid;
do {
$pid = waitpid(-1, 0
Re: [Bacula-users] How to set up large database backup
Whoa! Okay, I need to go talk to the DBAs about this lot, lots of it is too far on the DBA side for me to comment intelligently on it. It does sound promising, though - if we back up daily only the current month's data (the rest will be in static partitions), only the weekly(?) full backup will have space/time issues. But, after some thought... Basically, as I understand it, your first group of comments are about not backing up empty space, as per your example if there is only 10GB data in a 100GB data file. However, our database is growing rapidly, and our DBAs tend to allocate smaller tablespace files more frequently (rather than huge files seldom), so at any time there's probably not more than 20 GB of unused space in the database files, which is less than 10% of our database currently, and the % will only decrease. So yes there would be a benefit, but not huge. Your RMAN example still backs up the entire database to disk and then later to tape, which leaves us with the problems of disk space and backup duration. As mentioned above, these won't be mitigated very much by only backing up data and not empty space. What I'd like to do is halve the backup time and remove the requirement for intermediate disk storage by backing up the tablespaces (RMAN or otherwise) straight to tape. For which the only solution anyone's suggested which would actually work with Bacula is a variation of Kjetil's suggestion, running multiple backup tasks, one per tablespace. A little ugly in that there will be a LOT of backup jobs and hence a lot of emails in the morning, but it would work. The DBAs are already talking about partitioning and making the older tablespaces read-only and only backing them up weekly or fortnightly or whatever, which solves the problem for the daily backups but still leaves us with a weekly/fortnightly backup which won't fit in the backup staging disk and won't complete before the next backup is due to kick in. It may be that we have to just accept that and not do daily backups over the weekend, say, working around the disk space issue somehow. For various reasons our hot backup site isn't ready yet. The business have agreed that in the interim an outage of several days is acceptable, while we restore from tape and start it all up. At this stage (it's a work in progress), an outage of this application doesn't affect external customers, only internally, and the pain is not great. Long term we will have a hot database replica at another site ready to step up to production status in the event of problems, but as I said this isn't ready yet. I don't know whether it will be Oracle Data Guard, but it'll be equivalent. We already do this for other applications/databases, the DBAs are well on top of this stuff. I don't know the details. Cheers, David. From: David Ballester [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2008 23:00 To: David Jurke Cc: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup 2008/11/25 David Jurke [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Hiya David, I'm assured by our DBAs that this one-tablespace-at-a-time method works - it's apparently what Oracle recommend, and they do restores and recovers frequently for things like creating test databases, so it's a proven method. It could be that my description of the process was rather lacking, there's likely other details around it, but the basic theory is sound. Hot-backup one tablespace at a time, followed by a log switch and back up all the logs. Of course, it works, but again: - if you have a tablespace with a datafile of 100GB and the tablespace is empty at 90% ( only 10GB of data ) backing up the datafile as you do now, you are spending a lot of time/resources to copy 90% of no data. Makes sense to backup garbage? - In normal operations, Oracle use the redo logfiles to store the changes made to data blocks, in backup mode Oracle needs to store in the logfile ALL block, even if the data changed is only one byte in this block. Read http://www.speakeasy.org/~jwilton/oracle/hot-backup.html ( googled ) Then, you are spending a lot of time and resources to backup data that is really not ecessary to backup. And you are worry about the time/disk/tape need to backup your DB, aren't you? Using RMAN is one of the options I'm looking at, but I haven't managed to spot a good description of how to integrate it into Bacula to achieve a backup straight to tape, either a directly attached drive or across the network. Every database, and how it is used, is different and needs a personalized study, but you can give rman a try, using your development environment. Backup your development database using your method, after that look the time and volume data backed up ( take in mind the extra admin cost to mantain the scripts / verification ) using your method Now, let's give a try with rman
Re: [Bacula-users] How to set up large database backup
David Jurke [EMAIL PROTECTED] writes: I think it'd need some tweaking to delay putting each tablespace into backup mode until Bacula is ready to back it up - one of the problems I have is that we can't put all the tablespaces into backup mode at the same time because of the volume of logs produced. the open of the FIFO will not return until Bacula is there wanting to read the data, so do the locking after the Dumping $ts line. Perhaps I could run a script before the backups start to query the database for a list of tablespaces, and build a set of Bacula job configs, one per tablespace, then tell Bacula to reload its config. you could do that, but I don't think you gain anything. the code I included is intended to be a pre-script, where the @tablesspaces initilisation at the top should be replaced by a database query. In fact, Would I need to dump the database to anything? Wouldn't I just have a pre-backup job to put the tablespace into backup mode, bacula that tablespace's file straight to tape, then a post-backup to put the tablespace back to normal? indeed. with my script, you don't actually have a post-backup script, though -- the tablespace twiddling is done in the FIFO handling process which exits as soon as Bacula is done reading. -- regards, | Redpill _ Kjetil T. Homme | Linpro (_) - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
Ah, yes, you're right, I'm with you now. You're kicking off a whole lot of parallel opens which all block, then get unblocked one at a time as Bacula starts reading them, at which point the tablespace is altered and the file copied to the FIFO and hence to tape. I hadn't clicked that the tablespace alter would only happen *after* Bacula started trying to read the file. So as long as there's no timeout on Bacula trying to read the FIFO before the database is altered and the copy starts writing to it (which should be quick) and also on the open of the FIFO before Bacula starts reading to it (which would be hours), all is happy. I'll have a bit of a play with this, see if I can make it work and, most importantly, make sure I can restore a working database from it! (Beware of filenames!) This may take some time... Cheers, David. -Original Message- From: Kjetil Torgrim Homme [mailto:[EMAIL PROTECTED] Sent: Thursday, 27 November 2008 16:56 To: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup David Jurke [EMAIL PROTECTED] writes: I think it'd need some tweaking to delay putting each tablespace into backup mode until Bacula is ready to back it up - one of the problems I have is that we can't put all the tablespaces into backup mode at the same time because of the volume of logs produced. the open of the FIFO will not return until Bacula is there wanting to read the data, so do the locking after the Dumping $ts line. Perhaps I could run a script before the backups start to query the database for a list of tablespaces, and build a set of Bacula job configs, one per tablespace, then tell Bacula to reload its config. you could do that, but I don't think you gain anything. the code I included is intended to be a pre-script, where the @tablesspaces initilisation at the top should be replaced by a database query. In fact, Would I need to dump the database to anything? Wouldn't I just have a pre-backup job to put the tablespace into backup mode, bacula that tablespace's file straight to tape, then a post-backup to put the tablespace back to normal? indeed. with my script, you don't actually have a post-backup script, though -- the tablespace twiddling is done in the FIFO handling process which exits as soon as Bacula is done reading. -- regards, | Redpill _ Kjetil T. Homme | Linpro (_) - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
David Jurke [EMAIL PROTECTED] writes: Basically, as I understand it, your first group of comments are about not backing up empty space, as per your example if there is only 10GB data in a 100GB data file. However, our database is growing rapidly, and our DBAs tend to allocate smaller tablespace files more frequently (rather than huge files seldom), so at any time there’s probably not more than 20 GB of unused space in the database files, which is less than 10% of our database currently, and the % will only decrease. So yes there would be a benefit, but not huge. presumably the empty space will also compress very well. -- regards, | Redpill _ Kjetil T. Homme | Linpro (_) - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
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. 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. -- Bruno Friedmann - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
David Jurke wrote: 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!! You don't mention what OS or DBMS the database is, given the context I'm assuming it's for some other business process than Bacula. Does the SAN support snapshots? You could always take a snapshot, back it up and then remove the snapshot afterwards. There may need to be some scripting to make sure the database is in a consistent state before you do this, but it'd be a lot quicker than copying the data. -- James Cort IT Manager U4EA Technologies Ltd. -- U4EA Technologies http://www.u4eatech.com - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
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
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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
Thanks James, Sorry, missed that bit... Oracle on Linux. The database is, as you say, for the business, hence the need to back it up! And sadly no, our SAN doesn't support snapshots, or for sure I'd have been doing that! Likewise I don't have the option of breaking RAID mirrors and doing it that way. Cheers, David. -Original Message- From: James Cort [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2008 00:02 To: David Jurke Cc: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup David Jurke wrote: 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!! You don't mention what OS or DBMS the database is, given the context I'm assuming it's for some other business process than Bacula. Does the SAN support snapshots? You could always take a snapshot, back it up and then remove the snapshot afterwards. There may need to be some scripting to make sure the database is in a consistent state before you do this, but it'd be a lot quicker than copying the data. -- James Cort IT Manager U4EA Technologies Ltd. -- U4EA Technologies http://www.u4eatech.com - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
Dan Langille wrote: There is no way to dump interactively? I'm a PostgreSQL fan and creating a backup doesn't add overhead. And using PITR, postgresql even allows you to fetch out the raw database files from underneath a running (and active with insert/updates) postgresql instance. This is just plain awfully cool. we backing up pg databases in 300GB+ sizes with this. .. but it is quite far from the orignial question :-) -- Jesper - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
El dc 26 de 11 del 2008 a les 07:34 +1300, en/na David Jurke va escriure: Thanks James, Sorry, missed that bit... Oracle on Linux. The database is, as you say, for the business, hence the need to back it up! And sadly no, our SAN doesn't support snapshots, or for sure I'd have been doing that! Likewise I don't have the option of breaking RAID mirrors and doing it that way. Cheers, David. [ Off-topic ] may be you could setup an Oracle Dataguard to protect this database and backup up the standby one. What Oracle release , 9i, 10i, 11g... ? Dou you use rman actually ? Regards D. - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
El dt 25 de 11 del 2008 a les 13:12 -0500, en/na Dan Langille va escriure: 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. Backing up only Oracle datafiles ( tablespaces ) making up 'backup mode' doesn't save you to backup an INCONSISTENT datafile. In backup mode, the datafile blocks are modified AS USUAL, but the datafile header is freezed, when turning on the tablespace 'bakup mode' off, the smon process executes a recover of this datafile using the redo/archive info. To get a recoverable database, you must put ALL tablespaces 'backup mode' on, copy the archives, put them again on backup mode off and force archiving of the current logfile, and copy all archives generated between the tablespace modification and 'now'. OF course after that you must backup a copy of control file, init parameters, etc... USE RMAN rman in 9i is able to know where is the tablespace highwatermark and only backup the data before it ( with your method, you copy all datafile, even it is empty of usable data ), it does compression on the backup result. rman in 10g is able to ( rman 9i + ) know the unused blocks under the tablespace highwatermark, backing up only the used blocks My 2 cents Feel free to give my mail to your dbas, I'm always open to help ( I'm usually stay on #oracle @ irc.freenode.net ) D. - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
Hiya David, I'm assured by our DBAs that this one-tablespace-at-a-time method works - it's apparently what Oracle recommend, and they do restores and recovers frequently for things like creating test databases, so it's a proven method. It could be that my description of the process was rather lacking, there's likely other details around it, but the basic theory is sound. Hot-backup one tablespace at a time, followed by a log switch and back up all the logs. Using RMAN is one of the options I'm looking at, but I haven't managed to spot a good description of how to integrate it into Bacula to achieve a backup straight to tape, either a directly attached drive or across the network. Ideally, as I think I mentioned previously, I'd like to configure our SAN fabric to give one of the drives (in our autochanger tape library) directly to the database server, and back up straight to tape without going across the network. But it's not at all clear to me how to do that with Bacula and still be able to use Bacula and the autochanger to switch tapes and keep track of volumes. Cheers, DJ. -Original Message- From: David Ballester [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2008 08:02 To: David Jurke; bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup El dt 25 de 11 del 2008 a les 13:12 -0500, en/na Dan Langille va escriure: 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. Backing up only Oracle datafiles ( tablespaces ) making up 'backup mode' doesn't save you to backup an INCONSISTENT datafile. In backup mode, the datafile blocks are modified AS USUAL, but the datafile header is freezed, when turning on the tablespace 'bakup mode' off, the smon process executes a recover of this datafile using the redo/archive info. To get a recoverable database, you must put ALL tablespaces 'backup mode' on, copy the archives, put them again on backup mode off and force archiving of the current logfile, and copy all archives generated between the tablespace modification and 'now'. OF course after that you must backup a copy of control file, init parameters, etc... USE RMAN rman in 9i is able to know where is the tablespace highwatermark and only backup the data before it ( with your method, you copy all datafile, even it is empty of usable data ), it does compression on the backup result. rman in 10g is able to ( rman 9i + ) know the unused blocks under the tablespace highwatermark, backing up only the used blocks My 2 cents Feel free to give my mail to your dbas, I'm always open to help ( I'm usually stay on #oracle @ irc.freenode.net ) D. - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
On Nov 25, 2008, at 2:03 PM, David Jurke wrote: 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* I think it would be much better if you started a new thread with a more descriptive topic (e.g. best practices for large Oracle backups). You want the Oracle people in on this thread. At the moment, you're probably not getting their attention. Also, Google for Oracle backups. I also think your DBA team needs to look at disaster recovery options: - the DBA server hardware has died - the disks are dead - etc If the time it would take to restore from a backup is insufficient, then they really need to look at replicas. -- 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
This is equivalent to what we're doing now, but we're sort of one step up in the hierarchy - backing up one tablespace at a time. You don't say whether you mean to back up each table to tape as it's produced, or back them all up at the end when they've all been copied. Either way, there's problems. The second option, copying/dumping them one at a time and then backing them all up to tape at the end, is what we're doing now, and it works... for now. The problem is, it currently takes about 12 hours or so to do this, with 700GB of data. We're expecting the database to grow to 5-7 terabytes, which will (assuming linear scaling) take about 120 hours to back up, which isn't particularly suitable for a daily backup :) Not to mention running out of intermediate disk space to keep it on for the duration of the operation. The first option, back up each table copy/export/dump as it's produced, has two problems. Firstly, it's effectively the same as the other option with respect to amount of data shuffled around and hence how long it'll take. (Unless we can be doing the tape backup of one file while we're doing the copy/export/dump of the next one, which would speed things up mightily.) The main problem with this is I have no idea how to do this with Bacula, without creating a separate Bacula job for every table(space), which as I think I mentioned in my original email opens up a whole lot of manual re-configuration on a frequent basis, and hence the likelihood of missing bits and thus invalidating the entire backup... and not noticing until it's too late. I know that Legato Networker plus RMAN allows you to give the database server the tape drive and tell RMAN to do the backup, with Legato still managing the tape changer and looking after the volumes (retention periods etc). I haven't found anything that'll tell me how to do this with Bacula, with or without RMAN. If I could find something that told me how, from a script, to tell Bacula to back up a given file/directory, now, to whatever tape is appropriate (find one and mount it if required), then I could fairly easily script up an iterative loop to go through all the tablespaces and bacula them straight across the network to tape. Basically integrate Bacula into the script I already have so it baculas the data to tape instead of copying it to disk. Even better, if I could get my head round how to configure Bacula to cope with a tape drive on one server being fed by an autochanger on a different machine, I could even eliminate the cross-network bit. Cheers, DJ -Original Message- From: Hemant Shah [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2008 10:44 To: James Cort; David Jurke Cc: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup --- On Tue, 11/25/08, David Jurke [EMAIL PROTECTED] wrote: From: David Jurke [EMAIL PROTECTED] Subject: Re: [Bacula-users] How to set up large database backup To: James Cort [EMAIL PROTECTED] Cc: bacula-users@lists.sourceforge.net bacula-users@lists.sourceforge.net Date: Tuesday, November 25, 2008, 12:34 PM Thanks James, Sorry, missed that bit... Oracle on Linux. The database is, as you say, for the business, hence the need to back it up! How about exporting/dumping one table at a time and then backing up the exported data using bacula. And sadly no, our SAN doesn't support snapshots, or for sure I'd have been doing that! Likewise I don't have the option of breaking RAID mirrors and doing it that way. Cheers, David. -Original Message- From: James Cort [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2008 00:02 To: David Jurke Cc: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup David Jurke wrote: 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!! You don't mention what OS or DBMS the database is, given the context I'm assuming it's for some other business process than Bacula. Does the SAN support snapshots? You could always take a snapshot, back it up and then remove the snapshot afterwards. There may need to be some scripting to make sure the database is in a consistent state before you do this, but it'd be a lot quicker than copying the data. -- James Cort IT Manager U4EA Technologies Ltd. -- U4EA Technologies http://www.u4eatech.com - This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux
Re: [Bacula-users] How to set up large database backup
--- On Tue, 11/25/08, David Jurke [EMAIL PROTECTED] wrote: From: David Jurke [EMAIL PROTECTED] Subject: Re: [Bacula-users] How to set up large database backup To: James Cort [EMAIL PROTECTED] Cc: bacula-users@lists.sourceforge.net bacula-users@lists.sourceforge.net Date: Tuesday, November 25, 2008, 12:34 PM Thanks James, Sorry, missed that bit... Oracle on Linux. The database is, as you say, for the business, hence the need to back it up! How about exporting/dumping one table at a time and then backing up the exported data using bacula. And sadly no, our SAN doesn't support snapshots, or for sure I'd have been doing that! Likewise I don't have the option of breaking RAID mirrors and doing it that way. Cheers, David. -Original Message- From: James Cort [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2008 00:02 To: David Jurke Cc: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup David Jurke wrote: 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!! You don't mention what OS or DBMS the database is, given the context I'm assuming it's for some other business process than Bacula. Does the SAN support snapshots? You could always take a snapshot, back it up and then remove the snapshot afterwards. There may need to be some scripting to make sure the database is in a consistent state before you do this, but it'd be a lot quicker than copying the data. -- James Cort IT Manager U4EA Technologies Ltd. -- U4EA Technologies http://www.u4eatech.com - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users Hemant Shah E-mail: [EMAIL PROTECTED] - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
This is equivalent to what we're doing now, but we're sort of one step up in the hierarchy - backing up one tablespace at a time. You don't say whether you mean to back up each table to tape as it's produced, or back them all up at the end when they've all been copied. Either way, there's problems. I'll chime in with another potential solution: volume snapshots. In my day job, not using Bacula, we back up multi-terabyte Oracle databases every day. The database is in backup mode for only a few minutes per day. Granted, the length of time is probably more due to the EMC BCV cloning process, but the entire database is consistent, and whole backups are possible. If you're hosting your databases on a hardware SAN, this might be another option. Jeff Kalchik - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] How to set up large database backup
Thanks Jeff, Now that would be really cool... if our SAN were smart enough to do snapshots. Sadly, this is not the case. It's large, but not very smart. Cheers, David. -Original Message- From: Jeff Kalchik [mailto:[EMAIL PROTECTED] Sent: Wednesday, 26 November 2008 15:47 To: bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] How to set up large database backup This is equivalent to what we're doing now, but we're sort of one step up in the hierarchy - backing up one tablespace at a time. You don't say whether you mean to back up each table to tape as it's produced, or back them all up at the end when they've all been copied. Either way, there's problems. I'll chime in with another potential solution: volume snapshots. In my day job, not using Bacula, we back up multi-terabyte Oracle databases every day. The database is in backup mode for only a few minutes per day. Granted, the length of time is probably more due to the EMC BCV cloning process, but the entire database is consistent, and whole backups are possible. If you're hosting your databases on a hardware SAN, this might be another option. Jeff Kalchik - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users - 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=100url=/ ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
