Tuesday, July 17, 2012

ORA-01012 - not logged on

Problem Description

Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running ORACLE - shut it down first.

$sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed 16 04:30:16 2012
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

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

Root Cause Analysis (RCA):

Error 'ORA-01012' may occur under two conditions.
1. If no number of oracle sessions reached the number mentioned in PROCESSES parameter.
2. Improper database shutdown.

Solution:

For RCA: 1, clear older sessions and free space.Like,
1. Shutdown/restart application server.
2. Kill older oracle process which are holding the resources.

For RCA: 2, Shutdown the database.
1. Check background process and kill them if needed.
2. Bounce DB server if possible.

3 comments:

  1. Replies
    1. I keep having this same issue, the ''not logged on'' error, but i'm new with Oracle databases plus my mother tongue is not english so im having trouble understanding online how to fix it, can you please tell me step to step how to make it work? like which commands should i use to kill the sessions and processes and which ones should i kill, thanks

      Delete
    2. I'm having this same issue, the 'not logged on' error but i'm actually new about Oracle databases plus english is not my mother tongue, i was wondering if you could tell me step by step what can i do, like which commands to use and how and which processes/sessions to kill, thanks i'll really appreciate it

      Delete