Tired of trying to fix my daughter’s PC (that nightmare’s still ongoing) and dealing with the complex emotions of Oracle professionals (that’ll be me then), I thought I’d try to post something potentially useful.<\/p>\n
We use System Managed Undo (or Automatic Undo Management – your choice) on a large number of databases at our site, but have run into a few problems with it on one of our most active mission-critical databases. The last time this happened, we ended up with an undo tablespace that was offline but still had undo segments marked as active in the dictionary and delayed block cleanout would occasionally try to reference these offline\/online and corrupted segments and generate ORA-00600s. On top of that, SMON would unpredictably crash the instance.<\/p>\n
One particularly pleasant Saturday afternoon I ran a dictionary query to add some information to the TAR and the instance crashed. Trust me, a two-hour crisis call is not what Saturday afternoons were intended for!<\/p>\n
Anyway, when we finally got to deal with an Oracle BDE engineer who knew what he was doing, he suggested that the extensive onlining and offlining of the segments might be part of the problem, causing SMON to crash the instance. I’d wondered whether all of the offlining messages in the alert log might be significant because they were always there around any problem we had, but this instance is so active that the offline\/online activity is pretty constant anyway.<\/p>\n
To cut a long story short, he suggested that we set event 10511 which stops the offline\/online activity. It worked perfectly and the system has been running happily since. We still needed a direct dictionary update to tell Oracle to ignore undo segments in the offlined tablespace (under Oracle’s instruction of course) but we’re in a much better state now.<\/p>\n
But I reckon the real thing to watch for is this – the instance is on 9.2.0.2 and I suspect that if we’d been on a later release we wouldn’t have hit this problem. I know Oracle *always* tell you to upgrade, but they seemed pretty convinced that this area of functionality has been improved somewhat.<\/p>\n
So, should you run into occasional system crashes on a high throughput database and are using SMU, maybe you could ask Oracle Support about setting this event?<\/p>\n","protected":false},"excerpt":{"rendered":"