Google it ....

Wednesday, August 28, 2013

ORA-01012: not logged on

Hello,
Today we solved next problem:
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - 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 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Connected.
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
ERROR:
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) : Restart application or application server
Solution 3) : Shut down database server then start database server and database.

That's all.

2 comments:

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

    ReplyDelete
  2. […] link advises to find non-local background processes and kill them using following bash […]

    ReplyDelete