In Oracle 8 doing a web-based backup with BLOBs within the database is very slow. By slow, I am talking about over an hour or so to backup a database with 100MB of BLOB data. Oracle acknowledged it had been slow, but wouldn't repair the problem (a lot for having to pay for support.) Does anybody determine if Oracle has fixed this issue with subsequent releases? Also, how quickly do online backup copies use BLOBs operate in SQL Server and MySQL?

I have had this problem previously, and also the only decent workarounds we found would make certain the LOBs were in their own individual tablespace, and employ another backup strategy together, in order to change to while using BFILE type. Whether you are able to manage with BFILE will rely on how you are while using LOBs.

Some usage information on BFILE:

Observe that BFILEs survive the filesystem outdoors of Oracle, so you'd have to back them up inside a process outdoors of the normal Oracle backup. On a single project we simply were built with a scheduled rsync to off-site backup. Also worth noting is you cannot create/update BFILEs via JDBC, but read them.

To reply to your question concerning the speed of online backup copies of BLOBs in SQL Server, it is the same speed as copying regular data for SQL 2000/2005/2008 - it's typically restricted to the rate of the storage. It's my job to overcome 100mb/sec on my small database backup copies with BLOBs.

Be skeptical of utilizing backup compression tools with individuals, though - when the BLOB is binary-style data that's heavily random, then you will waste CPU cycles attempting to compress the information, and compression could make the backup reduced rather than faster.

I personally use SQL Backup from Redgate for SQL Server -- it's absurdly fast, despite my BLOB data.

I have a copy of each and every file which i do EDI with, so when they aren't huge, they're numerous and BLOBs. I am more than 100Megs of just these text files.

You need to observe that Redgate's SQL Backup is simply a front-finish towards the standard SQL offers a superior additional management features, essentially, but nonetheless utilizes the SQL Server backup engine.

With respect to the size the BLOBs, make certain you are storing them in-line / from line properly.


Are you able to place the export file you are creating and also the Oracle tablespaces on different disks? You I/O throughput might be the constraining factor...?

exp on 8i was slow, but less than you describe. I've backed-up gb of blobs within a few minutes in 10g..(to disk - using expdp)