DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Wednesday, March 29, 2017

Startup Fails With 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 Production on Sat Mar 14 07:53:11 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release - 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 on

SQL> desc v$instance
ORA-01012: not logged on

SQL> startup
ORA-01012: not logged on

Oracle Database has been shutdown , after applying GST Patch.

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 "UAT" :
Shared Memory:
ID              KEY
4194312         0xffffffff
4194313         0xffffffff
4194314         0x060ca1c4
Oracle Instance not alive for sid "UAT"

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

ipcrm -m <problem shared memory id>
$ ipcrm -m 4194312
$ ipcrm -m 4194313
$ ipcrm -m 4194314

$sqlplus '/as sysdba'

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2225776 bytes
Variable Size            1962936720 bytes
Database Buffers         1224736768 bytes
Redo Buffers               16936960 bytes
Database mounted.

Oracle Database Opened.

No comments: