I was contacted by a DBA on a project I am on and they told me that a database they had executed a ‘shutdown immediate’ command on had been hanging for 20 minutes without any progress.

This problem is typical in an environment where a remote application maintains a pool of connections to the database.  It is these remote connection processes that are preventing the database from closing immediately.

I have not seen this issue in an Oracle-on-Windows environment, so I will just concentrate on resolving this issue in a Unix-type environment.  First, you need to see if you do indeed have remote connections:

$ >ps -ef | grep -v grep | grep LOCAL=NO
 oracle 29257 18960   0 05:31:03 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13255 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 29187 18960   0 05:31:01 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13277 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29283 18960   0 05:31:07 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13285 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 29191 18960   0 05:31:02 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29290 18960   0 05:31:18 ?     0:05 oracleDUMMY (LOCAL=NO)
 oracle 29182 18960   0 05:31:00 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13269 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13249 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 13251 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29288 18960   0 05:31:17 ?     0:17 oracleDUMMY (LOCAL=NO)
 oracle 29292 18960   0 05:31:18 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29281 18960   0 05:31:07 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13283 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 29285 18960   0 05:31:07 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 17496 18960   0 10:27:26 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 13267 18960   0   Sep 04 ?     0:01 oracleDUMMY (LOCAL=NO)
 oracle 13288 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 10164 18960   0 12:41:18 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle  3579 18960   0 09:00:08 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 12425 18960   0 12:56:18 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 13275 18960   0   Sep 04 ?     0:00 oracleDUMMY (LOCAL=NO)
 oracle 29185 18960   0 05:31:01 ?     0:00 oracleDUMMY (LOCAL=NO)

Now that we know there are remote connections, they need to be removed so the database can continue the shutdown process.  It would take some time to issue ‘kill -9’ commands individually for each of these processes, so I came up with method to kill them automatically.  You just need to isolate the OS process for each remote connection:

$ >ps -ef | grep -v grep | grep LOCAL=NO | awk '{print $2}'
 29257
 13255
 29187
 13277
 29283
 13285
 29191
 29290
 29182
 13269
 13249
 13251
 29288
 29292
 29281
 13283
 29285
 17496
 13267
 13288
 10164
 3579
 12425
 13275
 29185

Once we isolate the OS process ID’s, we can plug them into a command that will loop through and perform a ‘kill -9’ on each:

for i in $(`ps -ef | grep -v grep | grep LOCAL=NO | awk '{print $2}'`)
 do
 kill -9 $i
 done

The command will execute immediately after pressing <ENTER> after the word ‘done’.  I gave this process to the DBA who contacted me and it resolved their issue.  Try this the next time your database will not shutdown immediately.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s