I recently had a situation where access to the database was completely blocked because of the infamous error message
ORA-00020: maximum number of processes (%s) exceededFacts:
At that time, the database had to become available to the users again ASAP.
When I have encountered these situations in the past, I have had to kill all the operating system processes and restart the instance. A brut-force method that is not particularly pretty, but sometimes necessary:
for a in $(ps -ef |grep $ORACLE_SID | grep -v grep | awk '{ print $2}'); do >kill -9 $a; >done
It normally does the job when you really have no other option.
This time however, after having killed all the processes, Oracle still rejected connections to the database using sqlplus:
sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:07:23 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. @ SQL> connect / ERROR: ORA-00020: maximum number of processes (1000) exceededI then found the page by tech.e2sn.com that showed how to use sqlplus with the "preliminary connection".
Simply by using
sqlplus -prelim "/as sysdba"
I was able to connect and shutdown the database with the abort option.sqlplus -prelim "/ as sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:09:15 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> shutdown abort ORACLE instance shut down. SQL> exit Disconnected from ORACLEAfter this point the database could once again be restarted:
sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Fri Jun 6 09:10:38 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2248080 bytes Variable Size 1258291824 bytes Database Buffers 855638016 bytes Redo Buffers 21708800 bytes Database mounted. Databasen opened.
The article referred to above is worth reading, but in short, the -prelim option will not try to create private session structures in the SGA. This allows you to connect to perform debugging or shutdown operations.
Thanks for the Information. It worked for me.
ReplyDeleteThanks for the Information. It worked for me.
ReplyDeleteGood Information.
ReplyDeleteThank you very much.It worked.
ReplyDeleteworked..Thank you
ReplyDelete