Andrew,
All good stuff, and we do need to improve in this area. However, as far as I know it wouldn't have helped with yesterday's problem.
What happened was that the system dropped like a rock while I was doing an import. The initial problem was that the control file was newer (had a higher SCN) than the system dbf file. The solution to this problem is to either move forward using log files, or roll back to a point in time where all data is available.
I had all the pieces I needed. The problem was that the very last change wasn't written to the redo log, because of the unexpectedness of the system going down. I should have been able to get Oracle to cleanly roll back to a previous point in time, but for unknown reasons it just wouldn't do it. So I finally resorted to that undocumented flag, which overlooks the fact that the redo logs are junky and reconciles things as best it can.
With the backup strategies you're talking about, all of which are better than nightly dumps, I could have restored from backups without losing a day's worth of entries, and that's a good thing. I've been meaning to look into better strategies and haven't had time, so I'll definitely look at your notes. But at the moment I'm more interested in understanding recovery strategies. As I understand it, even if I had archive logs I would have had the same chance of them being out of sync, and I would have been in the same situation I ended up in anyway.
In any case, I got past that problem only to create a new one. I installed the 8.1.7.4 patch on this system, which is apparently incompatible with RH 7.2. Completely hosed the Oracle installation. :( At first I wasn't sure if the problems I was having were due to database problems (Oracle did warn about subtle corruption after using that flag) or what, so I did a fresh Oracle install, added the patch, and boom, Oracle no workie once again. So now I'm moving the site to a temporary location so we can upgrade that system to 7.3, which I know does work with the patch.
This is *not* how I wanted to spend this weekend! :)
