No other user can connect to database. Whenever user with sysdba privilege try to login to database it shows connected but it does not allow to happen any query to database instead it fails with ORA-01012: not logged on as below.
oracle:/home/oracle CIDMP> $sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Wed Oct 28 00:12:16 2009 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected. SQL> select open_mode from v$database; select open_mode from v$database * 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
Cause of the Problem
Note that this problem is not same as SP2-0640: Not connected which is sql*plus message and it raised whenever you try to run query without log in to database. Just like below.
SQL> conn arju/a Connected. SQL> conn arju/e ERROR: ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. SQL> select instance from v$thread; SP2-0640: Not connected
"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 in turn defined by PROCESSES parameter) and database is flooded with concurrent load then 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.
Solution of the problem
The solution is free up the sessions. You can do it in whatever ways you want. Like,
1)Shut down application server sessions: You can shut down application server and thus will release the sessions.
2)Shut down database server: Shuttting down database server will shutdown database and all sessions will be release.
3)Kill oracle process: You can kill oracle process and then you need to start the oracle database again which will also work.