Backup question and "Incremental" tar in a way
 

hello,

We currently have several RHEL servers hosting oracle databases

the way we currently back these up is simply a nightly tar of the entire server to tape
(except for /sys /dev /proc /mnt /tmp )

Note that we do not stop the oracle database and I realise this is not the best way to backup an oracle database. That will be changed later this year. Note also that we have restored successfully from the tarball.

My question is not about that, however.

We would like to back up the server:
- to a NAS
- copy that tarball across a WAN to a remote site
thus we have a local copy and a remote copy, doing away with tapes

However, the tarballs range in size from 10 GB to about 200 GB and copying those across the WAN is much too slow.


?
Are there any tools that will allow us to
- backup to tar
- copy only the changes within the tarball (i.e. maybe just block level changes or compare two tarballs) between sites
or
- not use tar but still only backup the changed blocks rather than whole files?
?


note that all oracle files will change daily and so copyign the entire database files is not really an option.

We have looked at commercial options but these can be expensive.

When we have the ability to do incremental oracle backups later this year we can use RMAN for hot backups.


cheers,
A

I'm surprised that you can recover a running Oracle DB using just tar when the DB was open...

Anyway, you could/should be compressing the tarball with -z (gzip) or -j (bzip2).

Also, if its backing up the same files all the time, use rsync to txmit/update the changes only http://linux.die.net/man/1/rsync.
Try the following flags to begin with '-avz'.
Set up the receiving acct to accept ssh-keys to avoid passwd interaction.

You can perform incremental backups using tar. See the section in the tar info manual about "incremental dumps". However if most of data backed up amounts to the database's files, they will be backed up every time, and you may not see much difference in size.

How large would a diff between two consecutive sql dumps be?

thanks for the reply, chrism01.

1. tar of oracle
yes, i was surprised, as well, but it did work but i am not confident it would work again... it is not a long term solution- we just have to plan for enabling the ARCHIVELOG.
also, we sync this with an old-style database CISAM, which can be copied via tar so even if the oracle db fails then it can be reconstructed from the CISAM DB. This is a transitional arrangement.

2. we compress the tarball, which still leads to a large file

3. regarding rsync, the link states:
"It is famous for its delta-transfer algorithm, which reduces the amount of data sent over the network by sending only the differences between the source files and the existing files in the destination.
"

so, as an example, does this mean that if one entry is changed in a 20 GB database file (say a dollar value changes ina financial table), which therefore would only change the file by a small amount, then that change only is copied rather than the entire 20 GB, i.e. maybe about 1 kB?

on the other hand, if we use tar to create a tar.gz file, and teh majority of that file does not change, then we could use rsync to only send changes, which may only be a small amount, too, without sending a multi-gigabyte file?



cheers,
A

'delta' exactly :)

BTW, I know you said 'Oracle DB", but C ISAM DB tells me you actually mean MySQL.
I've used real Oracle DB since v5 and I've never heard of it (Oracle DB) using that format, hence my extreme surprise at recovery.
If you are using MySQL, you should be using mysqldump, which will guarantee a consistent DB extract, even if you use InnoDb format.
Please confirm exactly what DB+version you are using.

hi,

Re: rsync
great. that will help as nightly changes of the tar.gz would be small so deltas will be small and we can rsync across the WAN

re: oracle
we use Oracle.
the developers have written a "syncing" program to sync changes between the oracle db and the old flat file C ISAM DB. it is specific for us, i guess, and was done to migrate been a terminal-based usage 9which uses the CISAM DB) and a windows GUI and improved program (that uses Oracle).

we have only had to recover once and it worked but i am not banking on it working again. hoepfully we get the ARCHIVELOG stuff working sooner than later.

thanks!

Well, you should setup archive_log mode in init.ora eg http://ss64.com/ora/syntax-initora.html and you can enable it on the fly or spfile with restart here http://cuddletech.com/articles/oracle/node58.html or maybe http://docs.oracle.com/cd/B19306_01/...57/ch12007.htm

hi, unfortunately, the servers are in production so we have to plan this change carefully.

Thanks for the link to the documents. I had seen yhe oracle doc but no tthe others.

that will be a big help.

cheers,
A

You're welcome :)