November 11, 2004 at 7:33 am
My company supports both Oracle & SQL Server RDBMS for our software. The oracle DBA and I were wondering why literally the exact same 5 GB DB (ported from SS to OR) takes 10 minutes to restore from .bak in SS and 4 hours to restore from .dmp in OR.
Thought I'd throw it out to the community!
Thanks
November 11, 2004 at 9:21 am
Ok, my Oracle architecture is a bit old, so someone correct me if new features have corrected my statements here.
First the physical architectures for the storage of data is completely different. I think that this is causing the problem.
SQL server stores things on the database level using a DEVICE or File to store ALL tables. This makes backups easy as you backup the whole database just like a sequential physical read. Restoring is by file, and quick. Our benchmark times showed that a dump that takes 25 minutes could sometimes be restored in 24 minutes....I cannot explain it.
Oracle uses a LOGICAL grouping of Tablespaces to define a database. Each table was stored on a single tablespace. The problem as I remember it was that the restore has to be by table, sequentaly to preserve the RI. Our benchmark was able to get Oracle to backup at the very fast speed of 540 GIG per hour. The problem was that restores took 25 GIG per hour. Maybe the new releases have improved on this or a vendor like BMC has a faster solution, but it was ALOT slower to do the restore.
My 2 cents.
Eric
November 12, 2004 at 11:17 am
An Oracle dmp file is not same type of backup as a SQL Server .bak file.
An Oracle dmp file is the result of an export utilitiy. An export utility writes out the DDL and insert statements to recreate and re-insert the data. An Oracle backup would the right choice to backup a file for recovery.
A backup, backups up the file, and the restore - puts the file back - this is obviously much quicker than recreating and re-inserting the data.
Sounds like you all might want to read up on Oracle backup recovery. Exports are handy - and we use them a lot, but datafile backups should be the core of your backup process.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply