Re: schema recovery

On Sat, 19 Nov 2005, Onkar N Tiwary wrote:

> Today I had been asked one question : Scenario is : My DB is in
> noarchive log mode, no backup available, one of the datafile got
> corrupted say users01.dbf containing one schema TEST. Now can I
> recover the objects of the schema from that corrupted dbf file? If
> yes then how???? Note : Partial/full data loss is acceptable as its
> a development db but all the objects are required!!!

It depends on what you mean by "corrupted". Usually corruption on a datafile consists of just one or two corrupted blocks. You then have to use your own skills and knowledge to determine the best path to repair.

If the block is on an index, for instance, then you can just rebuild the index. If the block is in an unused portion of a segment, then you can just move the segment. If the block is in a unused portion of datafile, then you can write over the block with a new segment then drop the segment. Then there is the fancy stuff like recreating the table as create table...as select * from corrupted_table where rowid not in (blah), using dbms_rowid to generate a range of rowids that may occupy the corrupted block. These are justa few ideas, and by no means an exhaustive list of recovery paths.

These are all things that you could do without a backup, and that you would consider long before hauling out DUL (as was suggested) or trying to repair the blocks by hand (or emailing them to Matthew Parker :-) ).

Of course if by "corrupted" you mean "lost or detroyed", the it is a different story. With no backup at all you cannot get the data back.

Since you mention that "partial or full data loss is acceptable," then unless the corrupted segment is in the data dictionary,, you could easily extract the DDL for the objects using export or another tool that reads the data dictionary. Or better yet, just keep a copy of the DDL around somewhere.

So why no backup, even a baseline backup with just the empty objects? Your question seems academic, since you could easily just have a backup or export of the empty database on hand.

--
Jeremiah Wilton
ORA-600 Consulting
Emergencies - Seminars - Hiring
http://www.ora-600.net
--
http://www.freelists.org/webpage/oracle-l