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.
Related Documents
http://arjudba.blogspot.com/2008/07/ora-00018-ora-00020-maximum-number-of.html
http://arjudba.blogspot.com/2009/01/ora-00054-resource-busy-and-acquire.html
http://arjudba.blogspot.com/2008/07/process-and-runtime-limits.html
http://arjudba.blogspot.com/2008/04/user-resource-limits-in-oracle.html
Mohammad,
ReplyDeleteYou will also see similar behaviour on Unix platforms, if your instance crashed and teh shared memory segments are not released. To find out the shared memory segments allocated currently, you can run the command "ipcs -b" from an Unix prompt. This will show all the shared memory segments that are currently used. If you see any segments allocated to the Oracle user, and if the database is not running (if the background processes are not running), then you can use the "ipcrm -m" command to remove the shared memory segments. Once this is done, you should be able to restart the database. Please use caution while using ipcrm, as it will remove the shared memory segment without any warning. So, if you have more than one database running on the same server with the same oracle user, you will have to be careful to find the appropriate shared memory segment id before running ipcrm.
Hope this helps.
Thanks,
Dhilip Ranganathan.