Oracle Apps DBA

My photo
More than 5+ Years of IT Experience in administering Enterprise wide Multi Vendor UNIX Servers, Oracle Databases (8i to 11gR2), Middle-tiers, Applications and Clusters. I am a Sun Certified System Administrator (SCSA) for Solaris 10, oracle 10g database certified associate(OCA), Oracle 10g Database certified professional (OCP) and Oracle E-Business suite R12 certified professionl(OCP)

Oracle Database and Applications

Saturday, 18 June 2011

Oracle Shutdown immediate waiting on active process.

Hi,

Usually whenever we try to shutdown an oracle database with immediate option it will wait for some active process to get terminate. This may take long time and it also depends on the number of processes.

use below command to find out the active sessions on database:



bash-3.00$ ps -ef | grep LOCAL=NO
ora11g 17862 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17882 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17854 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17890 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17906 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17848 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17978 1 0 Jun 13 ? 0:03 oracletest (LOCAL=NO)
ora11g 17908 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17918 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17910 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17852 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 18235 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 252 1 13 Jun 14 ? 51:48 oracletest (LOCAL=NO)
ora11g 15268 15152 0 11:34:20 pts/2 0:00 grep LOCAL=NO
ora11g 17922 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17982 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17970 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17866 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17930 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17914 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17874 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17932 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17986 1 0 Jun 13 ? 0:03 oracletest (LOCAL=NO)
ora11g 17952 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17936 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17836 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17944 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17974 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17948 1 0 Jun 13 ? 0:05 oracletest (LOCAL=NO)
ora11g 17898 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17878 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17876 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17900 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17924 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17902 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17892 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17894 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17956 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17966 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17940 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17834 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17960 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17934 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17884 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17916 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17860 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17868 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)
ora11g 17856 1 0 Jun 13 ? 0:00 oracletest (LOCAL=NO)



we need to kill the process only with option LOCAL=NO. The below is the handy UNIX command which can be used for killing these idle active session on database which are avoiding the database to shutdown with immediate option.




ps -ef|grep 'oracletest (LOCAL=NO)'|grep -v grep|awk '{print$2}'|xargs -i kill -9 {}



Now verify the processes again with ps -ef | grep LOCAL=NO it should not return any list.

If these processes killed then the database will shutdown.

Happy reading :)

No comments: