Skip to Main Content
  • Questions

  • Restoring a file stored in the database

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Aps.

Asked: February 21, 2007 - 1:25 pm UTC

Last updated: February 22, 2007 - 8:52 am UTC

Version: 10gR2

Viewed 1000+ times

You Asked

Hi Tom -

I'm having some trouble convincing some SAP guys that they should be storing their content file inside the Oracle database rather than storing metadata only in the database and files on a file server.

Their 2 biggest arguments against it are 1) the database will grow to several TB in size, which makes the database slow and difficult to manage; and 2) how do they go about restoring a single file if it is accidentally deleted.

For point #1 I think I can fend them off and ease their concerns, but I can see their point about #2. If a file is stored on a file system, and someone deletes it on Monday then 3 months from now we just go to the FS backup on tape and find the file. If the file is stored inside the database, all we have is an RMAN backup or an Export and have to then restore the table from export or tablespace from RMAN, filter through all the restored information, find the row that contains the LOB and restore it.

Reading through your comments on all the advantages of storing files in the database, can you comment on whether or not this is a disadvantage of storing files in the database and/or how you would overcome this challenge.

Thanks for your opinions.

Aps

and Tom said...

1) hahahahaha, ouch. sorry I just fell out of my chair.

Hmm, the bigger the database, the slower it is. Interesting.

But completely and utterly wrong.

I'd much rather manage a single database then try to figure out how to manage thousands of files in the terabyte range in a FILE SYSTEM!! Now that - that would be painful.

2) well, there are flashback technologies - they might not even have to go to backups. And then - well - there are backups.

And - it is so very very very hard to "accidentally" delete a file in the database, whereas it is simply trivial to do so in the file system (and what sort of auditing do you have in the file system for these things - you know, when you discover 6 months from now that an important document was removed???)


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Let's be fair...

Michael Friedman, February 21, 2007 - 11:23 pm UTC

If you have a terabyte sized DB (or even a 10 terabyte DB!) recovering the entire DB from backup to restore a single file is a pretty appalling use of resources.

I am not aware of any way to use Oracle's standard tools to recover a single LOB from a backup without doing a complete restore.

That said, the database is still the way to go... you just need supplemental code.

We're about to implement this for a customer. Key points:

1. Deleted files will be moved into a logical "trashcan"

2. Trashcan will be periodically archived and purged. DB will retain metadata so you can always know which archive contains a particular deleted file.

3. If necessary you can restore an archive back to the trashcan and then back to the appropriate logical system location.
Tom Kyte
February 22, 2007 - 8:39 am UTC

there is partitioning, which one likely thinks "you would be using"

Also, as I said - "accidental deletion"??? Interesting concept - I can see it easily in a file system - I do not see it in a database.


Aps Reine, February 22, 2007 - 2:05 am UTC

well, I never said they were "intelligent" SAP guys . . . so convincing them not to be scared of a +3TB database will take some nice powerpoint presentations and graphs filled with some advice directly from this site.

However, I'm not sure how I'd go about demonstrating how to easily restore a single file that is deleted (accidentally = user error = they delete it and then later say "oops, I want that back"). I've had success using Flashback query if the delete is caught within the retention period. And dpexp/dpimp are nice if I have the resources to restore the table (which including the LOBs could be 1TB).

We do have a 'recycle bin' thingy in the application, but we're trying to demonstrate that Oracle can restore a file stored in a database 30 days after it's deleted and do it just as fast/just as easy as you can restore a file from a file system backup.

All humor aside, can you restore a single file (LOB) stored in the database without restoring the entire table from exp/imp, or restoring the entire tablespace from RMAN? Maybe LogMiner, but I'm not sure if LogMiner can mine through RMAN backups on a tape?

Thanks

Tom Kyte
February 22, 2007 - 8:46 am UTC

you would use partitioning to divide and conquer.

you would restore a bit of the table, the partition in question.

Just bite the bullet

Mike Friedman, February 22, 2007 - 4:45 am UTC

If you go with Logminer make sure you performance test it - as I understand it, the log files are not indexed - you need to scan them.

I think you should bite the bullet and write archive / purge / restore routines.

This is a 'requirement'

Mike, February 22, 2007 - 7:16 am UTC

It sounds like there is a 'requirement' to 'delete' and 'un-delete'. If so, design the application to meet this requirement. If you think of it that way, you probably won't have anyone saying 'we can restore from a backup', regardless of whether you are talking about filesystem or database.

Too often, people will throw stones at a design for reasons that are not relevant to the requirements. And they don't want to add to the requiremetns because they don't have the authority to do so, or because they can't sneak changes in under the radar.
Tom Kyte
February 22, 2007 - 8:52 am UTC

I agree, partitioning, flag delete - with a retention policy, etc - all make this 'accidental delete' "not a real thing"
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.