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:

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 up&running. 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 up&running 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=100&url=/
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Reply via email to