The fastest way to solve the hanging is to do a SHUTDOWN ABORT or even kill SMON process, but they do not remove the root causes. Beside, they are all categorized as instance failures which have several side effects after all.
The potential causes that block the database from shutdown could be various and sometimes unknown, but most of them have one common feature: The database is waiting for something to be finished, they could be:
- Long running queries with slow disk IO
- Incomplete User Actions
- Redoing (Roll Forward)
- Undoing (Roll Back)
- Log Switching
- Archives Transportation
- Scheduler Jobs
- MV Freshing
- Power Connections: It may be from OEM.
Theoretically, any user connections or transactions will be blocked or terminated after shutdown immediate was issued, but occasionally, you may see some users still be able to work on the database, especially when the database seemed to be stuck.
Here I list some actions that you may take:
- Stop Listener: Chances are, you didn’t stop the listener before shutting down the database. The listener could be unaware of the shutting down, and the service handler is still serviceable! This is not what we want, so please stop the listener first.
- Kill blocking sessions: You should identify the busy sessions first in order to kill them thereafter. ALTER SYSTEM KILL SESSION is usually useless in this moment, so please kill them on OS-level.
- Stop all jobs: The materialized views could be the suspects, some of which were doing a complete refresh from a big master table and no one knows, it can make your system busy to be hang. So please try to kill the job processes before next interval.
- Shutdown the instances one by one: Since the data guard connects both ends via network which goes across many external environments, the behaviors of the broker may become unpredictable. If the broker failed to shutdown primary or standby database, please log in each instance and use the plain-old SQL command to shutdown.
- ARCn is busy, then wait: You must ensure all the user transactions are terminated, and wait for the action of ARCn to complete. You can see the disk IO was considerable high, this is because ARCn was mostly dumping rollback logs for uncommitted transactions.
Further reading: How to Resolve Shutdown Immediate Hangs