sqlplus -prelim

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

18 thoughts on “sqlplus -prelim

  1. Anonymous

    >>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

  2. Anonymous

    >>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)

  3. Laurent Schneider Post author

    @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 😉

  4. Anonymous

    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?

  5. Anonymous1

    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.

  6. Laurent Schneider Post author

    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 sysdba

    SQL*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 sysdba

    SQL*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?

  7. Anonymous II

    @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.

  8. Laurent Schneider Post author

    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 !

  9. Anonymous1

    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

  10. pk

    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.

  11. Pingback: bypass ora-20 | Laurent Schneider

Comments are closed.