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.
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
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.
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"