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 11.2.0.3.0 Production on Sat Oct 12 07:53:11 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

ERROR at line 1:

ORA-01012: not logged onSQL> desc v$instance

ERROR:

ORA-01012: not logged on

SQL> startup

ORA-01012: not logged on

Changes

Oracle has been forcefully shutdown at OS level or crashed.

CAUSE

An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command

ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle 'sysresv' utility shows a shared memory segment for a non-existing instance, e.g.

$ sysresv

IPC Resources for ORACLE_SID "TEST" :

Shared Memory:

ID KEY

5963794 0x00000000

5996563 0x00000000

6029332 0xb2e3c9ac

Semaphores:

ID KEY

No semaphore resources used

Oracle Instance not alive for sid "TEST"

Solution

On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>

$ ipcrm -m 5963794

$ ipcrm -m 5996563

$ ipcrm -m 6029332

$sqlplus '/as sysdba'

Connected to an idle instance.

SQL> startup Oracle instance started

Total System Global Area 10689474560 bytes

Fixed Size 2237776 bytes

Variable Size 6375344816 bytes

Database Buffers 4294967296 bytes

Redo Buffers 16924672 bytes

Oracle Instance Started.

Oracle Database Opened.