SHUTDOWN IMMEDIATE
Shutdown immediate may not go smoothly as you thought, it could seem to be stuck in an infinite waiting. If you wait and hope the process monitor to terminate those dead, unresponsive or unstoppable user sessions in a short time, then you need more luck, because something is blocking the way.
In hanging situation, it's probably taking too long time to complete the shutdown. If you can't wait, you can try to cancel it and issue a SHUTDOWN ABORT or kill System Monitor (SMON) or Process Monitor (PMON) instead of SHUTDOWN IMMEDIATE.
Although SHUTDOWN ABORT is safer than killing SMON or PMON, they both have some side effects. In this post, I introduce a way to make stuck shutdowns go more smoothly, which is, killing dead user processes at OS-level in one command.
Identify Dead User Processes
Either LOCAL is YES or NO, they are potential candidates to be killed. Additionally, all their process name contain oracle$ORACLE_SID.
[oracle@primary02 ~]$ ps -ef | grep LOCAL | grep oracle$ORACLE_SID
oracle 1369 1 33 15:40 ? 03:03:25 oraclePRIMDB2 (LOCAL=NO)
oracle 2998 1 9 15:44 ? 03:00:36 oraclePRIMDB2 (LOCAL=NO)
oracle 3000 1 11 15:44 ? 03:00:43 oraclePRIMDB2 (LOCAL=NO)
oracle 3153 1 3 15:44 ? 03:00:12 oraclePRIMDB2 (LOCAL=NO)
oracle 3155 1 5 15:44 ? 03:00:18 oraclePRIMDB2 (LOCAL=NO)
oracle 3158 1 5 15:44 ? 03:00:21 oraclePRIMDB2 (LOCAL=NO)
oracle 3160 1 5 15:44 ? 03:00:18 oraclePRIMDB2 (LOCAL=NO)
oracle 3175 1 1 15:44 ? 03:00:07 oraclePRIMDB2 (LOCAL=NO)
oracle 3178 1 3 15:45 ? 03:00:12 oraclePRIMDB2 (LOCAL=NO)
oracle 3188 1 3 15:45 ? 03:00:12 oraclePRIMDB2 (LOCAL=NO)
...
In my case, there're about 64 dead processes to be terminated in node 2 of a cluster database, which prevent SHUTDOWN IMMEDIATE.
List Dead User Process ID
I used awk to cut all the process id for later use.
[oracle@primary02 ~]$ ps -ef | grep LOCAL | grep oracle$ORACLE_SID | awk '{print $2}'
1369
2998
3000
3153
3155
3158
3160
3175
3178
3188
...
Kill Dead User Processes
Now, it's time to kill them all in one command.
[oracle@primary02 ~]$ kill -9 $(ps -ef | grep LOCAL | grep oracle$ORACLE_SID | awk '{print $2}')
[oracle@primary02 ~]$ echo $?
0
In the above, we treated all process id as arguments for kill command to terminate. Now we can check the shutdown again.
After an immediate medication, we should go back to the basic question, what would be the possible causes of shutdown stuck? Let's think about it.
In some rare incidents, the instance may decide to shutdown itself in case of further damage to the whole database. We should be aware of that.
It worked beautifully. Very good!
In my case I needed to execute shutdown abort after the procedure, before startup.
I’m glad that it works.