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.

1 comment: