If you cannot login to the database, for instance due to ORA-00020 maximum number of processes exceeded, then chance exists that you could use the -prelim option.
Documented in note 121779.1 for sqlplus version 10.1 and later :
In some cases, no connections are allowed on the instance (in some ORA-20 situations for example).
As of 10.1.x, there is a new option with SQL*Plus to allow access to an instance to
generate traces.
sqlplus -prelim / as sysdba
Only sysdba connection is possible.
sqlplus -prelim system/manager
SQL*Plus: Release 11.2.0.2.0 Production on Mon Jul 4 10:38:36 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-24300: bad value for mode
And very little access is granted
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0
You can shutdown abort and then restart your database, instead of rebooting your server where other instances may be running.
This is the ultimate chance before reboot. Before this, consider disconnecting / killing some user session to get a regular sqlplus / as sysdba
Hello,
After you connect with “-prelim” mode, it’s also possible to use oradebug commands to examine the situation:
http://tech.e2sn.com/oracle/troubleshooting/hang/how-to-log-on-even-when-sysdba-can-t-do-so
Regards
Gokhan
@Gokhan Atil Thanks for the comment. shutdown abort first, think next is probably a bit brute force I admit 😉
thank You for sharing -)
>>You can shutdown abort and then restart your database, instead of rebooting your server where other instances may be running.
Kill LogWriter or DBWriter and so on and enjoy Oracle rebooting w/o OS rebooting
>>You can shutdown abort and then restart your database, instead of rebooting your server where other instances may be running.
Or you can stop Windows service (if Oracle was installed on this OS)
@Anonymous
+1. Or kill SMON, or PMON.
Author of the post, are you sure you are OCM?
Sincerely yours, just a 10g/11g OCP.
@Anonymous
Thats pretty the wrong approach. If you have reached max process and kill -9 all processes, chances exist you will no longer be able to login in the database, even if no process is running.
The sqlplus -prelim is probably the best you can do then !
As I wrote in this article, you should first consider killing some user session oracleSID (LOCAL=NO) and surely not killing critical background sessions like ora_smon_SID and ora_pmon_SID
This is of course the voice of a real life dba more than an OCM dba 😉
@Laurent Schneider
Yeah, man, you’re the only one real Oracle hero.
You wrote:
“You can shutdown abort and then restart your database, instead of rebooting your server where other instances may be running. ”
and then
“Thats pretty the wrong approach. If you have reached max process and kill -9 all processes, chances exist you will no longer be able to login in the database, even if no process is running. ”
How it can be understand?
@Anonymous
Dear Anonymous,
please stop using my stage name, i.e. Anonymous. It offends me.
Dear Anynymouses, don’t quarrel. Lets assign numbers to ourselves. Please pick those from a sequence =)
Dear Laurent, I do not really understand, if we decided to do “shutdown abort” anyway (as you wrote), why do we need such as a hemorrhoid, i.e. to run sqlplus with some switches to shutdown abort the database. It is much easier to kill SMON, and you have “Instance terminated by PMON”. That’s it. We are NOT talking about the cases when we want the db instance to survive.
If you can login as sqlplus without switch, do not use the switch. I believe shutdown abort is cleaner than kill SMON.
Let’s take another test case where you cannot kill SMON neither !
SQL> alter system set processes=22 scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 513585152 bytes
Fixed Size 2221128 bytes
Variable Size 444599224 bytes
Database Buffers 58720256 bytes
Redo Buffers 8044544 bytes
Database mounted.
Database opened.
SQL> quit
$ sqlplus -s -L / as sysdba
ERROR:
ORA-00020: maximum number of processes (22) exceeded
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
Let’s simulate some serious process crash
$ ps -ef | grep $ORACLE_SID
oracle 147460 1 0 15:42:53 - 0:00 ora_smco_DB01
oracle 512080 1 0 15:37:44 - 0:00 ora_mmnl_DB01
oracle 712842 1 0 15:37:44 - 0:00 ora_smon_DB01
oracle 925888 1 0 15:37:41 - 0:00 ora_pmon_DB01
oracle 1188058 1 0 15:37:43 - 0:00 ora_ckpt_DB01
oracle 1306828 1 0 15:37:43 - 0:00 ora_lgwr_DB01
oracle 1650812 1 0 15:37:43 - 0:00 ora_mman_DB01
oracle 1675368 286794 0 15:43:21 pts/0 0:00 grep DB01
oracle 1720456 1 0 15:42:54 - 0:00 ora_q001_DB01
oracle 1732776 1 0 15:37:43 - 0:00 ora_dbrm_DB01
oracle 1740984 1 0 15:42:56 - 0:00 ora_w000_DB01
oracle 1757272 1 0 15:37:52 - 3:22 ora_qmnc_DB01
oracle 1765558 1 0 15:38:23 - 0:00 ora_q000_DB01
oracle 1937428 1 0 15:37:42 - 0:00 ora_diag_DB01
oracle 2089144 1 0 15:37:42 - 0:00 ora_gen0_DB01
oracle 2392190 1 0 15:37:44 - 0:00 ora_mmon_DB01
oracle 2420802 1 0 15:37:41 - 0:00 ora_psp0_DB01
oracle 2461842 1 0 15:37:42 - 0:00 ora_vktm_DB01
oracle 2633842 1 0 15:37:43 - 0:00 ora_dbw0_DB01
oracle 2920672 1 0 15:37:43 - 0:00 ora_dia0_DB01
oracle 3059912 1 0 15:37:44 - 0:00 ora_reco_DB01
$ kill -9 147460 512080 712842 925888 1188058 1306828 1650812 1720456 1732776 1740984 1757272 1765558 1937428 2089144 2392190 2420802 2461842 2633842 2920672 3059912
$ ps -ef | grep $ORACLE_SID
oracle 2633868 286794 0 15:47:20 pts/0 0:00 grep DB01
Ok, what are you going to kill ?
Try to startup now …
$ sqlplus -L / as sysdbaSQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 6 15:48:31 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (22) exceeded
SP2-0751: Unable to connect to Oracle. Exiting SQL*Plus
As you see, you cannot log anymore, and you cannot kill anything
Ok, let’s do the prelim 🙂
$ sqlplus -prelim -L / as sysdbaSQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 6 15:49:27 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> shutdown abort
ORACLE instance shut down.
SQL> quit
Disconnected from ORACLE
$ sqlplus -L / as sysdba
SQL> startup
ORACLE instance started.
Total System Global Area 513585152 bytes
Fixed Size 2221128 bytes
Variable Size 444599224 bytes
Database Buffers 58720256 bytes
Redo Buffers 8044544 bytes
Database mounted.
Database opened.
SQL>
Capito?
@Laurent Schneider
>>Ok, what are you going to kill ?
Just imagine a lot of things still can be existing and relate to Oracle instance even ps reports about nothing
# ipcs -a | grep oracle
`coz mega-killing of all Oracle processes is the WRONG way. You was proposed to destroy THE ONLY ONE important background process.
I did ipcs, but having a dozen of database on the same server, I could not identify to which database the segment was allocated.
> `coz mega-killing of all Oracle processes is the WRONG way
indeed !
Hi Laurent,
So, did you have a chance to try my way? One more time:
1. ps -ef | grep smon
2. kill it (JUST ONE SMON PROCESS, DO NOT KILL OTHERS)
3. PMON realizes that SMON is missing and aborts the database (with killing all the processes and freeing up shared memory segments and other resources). That happens immediately
4. Database is down
5. You can reconnect /as sysdba to idle instance successfully.
4. ?????
5. PROFIT
> It is much easier to kill SMON
It is up to you anon1. Your method surely works in most cases
It is easier to kill smon and i did the same in staging environment yesterday.. But DB dint startup later cause of various reasons like prev resources still in use….
so using -prelim is good option to use and i do prefer that.
Pingback: bypass ora-20 | Laurent Schneider