Re: MYDUL avaialbe for recovery, another choise of DUL.

<yong321_at_yahoo.com> wrote in message
news:1127882329.912612.42450_at_z14g2000cwz.googlegroups.com...
>
>
> (remind people of
> the same trick that a database with a dropped column can be recovered
> by altering data dictionary, as long as it's subsequently exported and
> imported; credit to an Oracle expert well known in this newsgroup)
>

A (not very minor) correction to the 'trick'.

If you want to do this, then you export the entire database BEFORE you hack the dictionary.

THEN dump to file the table data that reappears. (Note - an export at this point may not be safe, as it may carry some definition error if your hacking of the data dictionary missed something). Whether you do an export or another form of extract, you need to check that the data extracted is actually correct, and that (for example) any triggers are associated with the correct columns.

Then you create an entire new database and import the entire export, drop the incorrect version of the table, and reload the table.

Alternatively:
You create a backup of the database (or a self-consistent set of tablespaces). Hack the backup, dump the table from the backup. Drop the table from the original and reload the copy. This is probably going to be quicker in most cases - but some people may have a problem finding somewhere to put the copy of the database.

Both strategies, of course, requires you to stop people using the database for the duration (to avoid changes to the critical table), and both require you to have a method for checking that the data from the dumped table is correct, and that all the triggers, indexes, RI etc. are correct after you've rebuilt the table.

Any other strategies leaves you with a data dictionary that may not be self-consistent, and therefore may crash, corrupt, or breach the security of your system at some future date. Either strategy is messy and long-winded - but you don't want a production database which has had the sys tables updated by hand.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle - Volume 1: Fundamentals
On-shelf date: Nov 2005

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Sept 2005