Home » RDBMS Server » Server Administration » Backing up VLDB data warehouses (>1TB)
| Backing up VLDB data warehouses (>1TB) [message #50539] |
Wed, 20 March 2002 11:56  |
Don Gillespie
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
A recent experience with a recovery of an existing data mart <100GB in size made it very clear to me that our backup/recovery procedures are very backup-centric, and not very recovery friendly. I am looking for alternative techniques to backup up our new, and growing, enterprise data warehouse and data marts. It is anticipated that there will be 500GB to 1TB to back up within the year, and around 3TB in two to three years. Based on current backup performance benchmarks, we anticipate it taking 9 to 10 hours to perform an evening or weekend backup of a 1TB system.
Currently, there is no plan to use log archiving, although the Dodge/Gorman book makes a good argument for it.
Some of the current challenges are numerous multi-100GB systems (transactional and otherwise) vying for the same tape drive resources (6 tape drives) and the 1Gb line the data goes across to our backup site (where the tape drives reside). Without going into all the details, the aforementioned data mart recovery took about 40 hours.
I can think of a few options, but would like some feedback, on these suggestions as well as any you would like to make:
Use archive logging, with frequent compression of the archived logs until they are backed up to tape (possibly FTPing the compressed logs over to disk space at the back-up site on a regular (hourly or daily) basis and then backing up to tape). We could then perform hot backups during the day, taking advantage of tape drive availability and little, if any, load on the 1Gb line. This would have some performance impact for users once/week though. Also, loads would be slowed down by the archiving.
Variation of #1. Do the same, except break the backup into 5 backups, one running each day at lunch time to minimize user performance degradation.
Is it possible to set log_checkpoint_interval and log_checkpoint_timeout to 0, and then prior to backup, put the tablespaces into read-only mode, thus freezing updates to the header block info, such as the SCN? Then perform a "hot" backup, but without the archiving? I'm thinking there would still be a problem with changes going on in the system tablespace due to managing locks, sql caches and such. We were planning on having the tablespaces in read-only mode anyway, and having pre- and post-session Informatica scripts alter them out of and back into this status.
Do the following (I believe it would work, as long as there is logging from the start of the first tablespace backed up):
DB is in........Take DB out.....Perform.........Put DB back....Perform
archivelog......of archivelog...major...........in archivelog...weekly
mode............mode............weekly loads....mode............hot backup
----A---------------B----------------C----------------D-----------E/A-----
(Timeline)D would occur immediately after the last load from C, before the backup and any user access (but its very quick to do).
The problem I see with all of these options, given our multi-stream, multi-tape drive, multi-system backup procedures, is that recovery would still be very slow.
One last option comes to mind, although I don't know how popular it might be here, because it is in many regards not as sophisticated/complex as our current configuration. Buy a large capacity, hardware-compression-enabled, high speed backup device and attach it directly to our data warehouse server. Once a week have internal courier services pick up the tape(s) and take it to our backup site. No contention for tape drives for back-up, no contention for tape drives nor the needed tapes for recovery and no bandwidth constraints. It does become more of a manual process, suseptable to all the failings of manual processes.
Barring extremely expensive solutions like standby DBs (hardware), or backup systems using EMC2 disk subsystems (H/W again), etc., the above was all I could come up with. I would appreciate your thoughts on the above, and any other ideas. With all the big insurance companies and telcos down in the U.S., this topic must have come up numerous times.
Thanks for your assistance!
|
|
|
|
| Re: Backing up VLDB data warehouses (>1TB) [message #50557 is a reply to message #50539] |
Thu, 21 March 2002 03:59  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Have you considered RMAN ?
The db is ALWAYS online while all the backup is being done. Looking at the size of your db, backup the archive logs couple of times a day.
You send the backups right to the tapes and then the tapes go to offsite storage ....
|
|
|
|
Goto Forum:
Current Time: Tue Dec 24 22:49:49 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
