ORA-01012: not logged on

Today we solved next problem:

sqlplus / as sysdba
SQL*Plus: Release - Production on Tue Aug 27 21:36:06 2013
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select status from v$instance;
select status from v$instance;
ERROR at line 1:
ORA-01012: not logged on

Sometimes connecting as sysdba shows database is in idle instance but whenever you issue startup it says ORA-01081: cannot start already-running ORACLE – shut it down first.

SQL> conn / as sysdba
Connected to an idle instance.

SQL> desc v$instance
ORA-01012: not logged on

SQL> conn / as sysdba
Connected to an idle instance.

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

“The ORA-01012: not logged on” error occurred due to heavy load in the database. If there is maximum number of sessions connected to the database, Which is defined by PROCESSES parameter and database does not allow sysdba privileged user as well as other users to be connected to the database. If sysdba privileged user try to connect to database then above error ORA-01012 comes.
Also check /etc/hosts file if there is incorrect host name or ip correct it and try start database.

Solution 1) :
without shutdown anything just kill that database processes which are not local processes. local means background processes.

ps -ef | grep $ORACLE_SID | grep -v grep | grep "LOCAL=NO" | awk '{print $2}' | xargs kill -9

Solution 2) : Shut down application server sessions
Solution 3) : Shut down database server then start database server and database.

That’s all.


2 thoughts on “ORA-01012: not logged on

  1. www.lolzparadise.com

    I truly wanted to send a small comment in order to appreciate you for all the precious items you
    are giving at this site. My extended internet search has now been recognized with beneficial insight to share with my visitors.
    I would believe that most of us readers actually are truly endowed to live
    in a fantastic website with so many wonderful individuals with great solutions.
    I feel pretty blessed to have discovered the weblog and look forward to
    so many more fun minutes reading here. Thanks once again for
    all the details.

  2. Pingback: Oracle: How to find non-local (background) processes in Windows Machine | Question and Answer

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