shutdown immediate taking ages (two days) to complete – or – ‘No, I will NOT enlarge the UNDO tablespace’ – or – patience …

Third party asked for a restart of one of our testing databases that was having issues. Although I’m always a bit skeptical on simple restart requests I think in this case it actually turned out to shed some light; one huge transaction of a ‘runaway process’ turned out to virtually block all other operations … …

The shutdown immediate took almost two days to complete. I was able to perfectly monitor the progress of the shutdown operation; in the alertlog there was a redo log file application every 50 minutes.
This way I could see that the shutdown immediate was not hanging – oracle was actually pretty busy in rolling back this large transaction … …

I ruled out the known issue that can sometimes arise with active client (‘LOCAL=no’) processes that sometimes block a shutdown immediate to progress. …

I’m glad I had advised the project team against simply enlarging the UNDO tablespace (larger than it’s production equivalent) prior to these issues in this UAT acceptance database … …

===
All, …

This morning (Saturday 22 September 2012) at 8:35 AM the shutdown immediate command for the ******** database ******** (********) completed. (see alertlog below). …

The shutdown took almost 48 hours to complete.

Thu Sep 20 12:33:24 2012
Shutting down instance: further logons disabled
...
Sat Sep 22 08:35:30 2012
Completed: ALTER DATABASE CLOSE NORMAL

I noticed some Logwriter tracefiles this morning at around the same time of the ‘close normal’ and also the database (instance) seemed in a unstable state – I was not able to spawn an internal sysdba connection …

Read more →