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@mezon01 ~]$ sqlplus sys/System_2018@IIRDB

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:03:07 2018

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

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server

 

Enter user-name: sys
Enter password:
ERROR:
ORA-00020: maximum number of processes (1500) exceeded

 

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied

 

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[oracle@mezon01 ~]$ sqlplus -prelim “/as sysdba”

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:04:06 2018

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

SQL> show parameter sga_t;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

 

SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> show parameter sga_max;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

SQL> EXİT
SP2-0042: unknown command “EXİT” – rest of line ignored.
SQL> exit
Disconnected from ORACLE
[oracle@mezon01 ~]$ ps -ef | grep IIRDB
oracle 6096 1 0 08:05 ? 00:00:00 ora_pmon_IIRDB
oracle 6098 1 0 08:05 ? 00:00:00 ora_psp0_IIRDB
oracle 6136 1 1 08:05 ? 00:00:01 ora_vktm_IIRDB
oracle 6140 1 0 08:05 ? 00:00:00 ora_gen0_IIRDB
oracle 6142 1 0 08:05 ? 00:00:00 ora_diag_IIRDB
oracle 6144 1 0 08:05 ? 00:00:00 ora_dbrm_IIRDB
oracle 6146 1 0 08:05 ? 00:00:00 ora_ping_IIRDB
oracle 6148 1 0 08:05 ? 00:00:00 ora_acms_IIRDB
oracle 6150 1 0 08:05 ? 00:00:00 ora_dia0_IIRDB
oracle 6152 1 0 08:05 ? 00:00:00 ora_lmon_IIRDB
oracle 6162 1 0 08:05 ? 00:00:00 ora_lmd0_IIRDB
oracle 6164 1 0 08:05 ? 00:00:00 ora_rms0_IIRDB
oracle 6166 1 0 08:05 ? 00:00:00 ora_lmhb_IIRDB
oracle 6172 1 4 08:05 ? 00:00:04 ora_mman_IIRDB
oracle 6174 1 0 08:05 ? 00:00:00 ora_dbw0_IIRDB
oracle 6176 1 0 08:05 ? 00:00:00 ora_dbw1_IIRDB
oracle 6178 1 0 08:05 ? 00:00:00 ora_dbw2_IIRDB
oracle 6180 1 0 08:05 ? 00:00:00 ora_dbw3_IIRDB
oracle 6182 1 0 08:05 ? 00:00:00 ora_lgwr_IIRDB
oracle 6184 1 0 08:05 ? 00:00:00 ora_ckpt_IIRDB
oracle 6186 1 0 08:05 ? 00:00:00 ora_smon_IIRDB
oracle 6188 1 0 08:05 ? 00:00:00 ora_reco_IIRDB
oracle 6190 1 0 08:05 ? 00:00:00 ora_rbal_IIRDB
oracle 6192 1 0 08:05 ? 00:00:00 ora_asmb_IIRDB
oracle 6194 1 0 08:05 ? 00:00:00 ora_mmon_IIRDB
oracle 6198 1 0 08:05 ? 00:00:00 ora_mmnl_IIRDB
oracle 6200 1 0 08:05 ? 00:00:00 ora_d000_IIRDB
oracle 6202 1 0 08:05 ? 00:00:00 ora_s000_IIRDB
oracle 6204 1 0 08:05 ? 00:00:00 ora_mark_IIRDB
oracle 6206 1 0 08:05 ? 00:00:00 ora_ocf0_IIRDB
oracle 6325 1 0 08:05 ? 00:00:00 ora_o000_IIRDB
oracle 10089 28710 0 08:07 pts/1 00:00:00 grep IIRDB
oracle 20902 25965 0 May23 ? 00:04:20 /oracle/product/11.2.0.4/db/jdk/bin/java -server -Xmx384M -XX:MaxPermSize=400M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=40 -DORACLE_HOME=/oracle/product/11.2.0.4/db -Doracle.home=/oracle/product/11.2.0.4/db/oc4j -Doracle.oc4j.localhome=/oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman -DEMSTATE=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Doracle.j2ee.dont.use.memory.archive=true -Djava.protocol.handler.pkgs=HTTPClient -Doracle.security.jazn.config=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/jazn.xml -Djava.security.policy=/oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/java2.policy -Djavax.net.ssl.KeyStore=/oracle/product/11.2.0.4/db/sysman/config/OCMTrustedCerts.txt-Djava.security.properties=/oracle/product/11.2.0.4/db/oc4j/j2ee/home/config/jazn.security.props -DEMDROOT=/oracle/product/11.2.0.4/db/mezon01_IIRDB -Dsysman.md5password=true -Drepapi.oracle.home=/oracle/product/11.2.0.4/db -Ddisable.checkForUpdate=true -Doracle.sysman.ccr.ocmSDK.websvc.keystore=/oracle/product/11.2.0.4/db/jlib/emocmclnt.ks -Dice.pilots.html4.ignoreNonGenericFonts=true -Djava.awt.headless=true -jar /oracle/product/11.2.0.4/db/oc4j/j2ee/home/oc4j.jar -config /oracle/product/11.2.0.4/db/oc4j/j2ee/OC4J_DBConsole_mezon01_IIRDB/config/server.xml
oracle 25965 1 0 May23 ? 00:00:12 /oracle/product/11.2.0.4/db/perl/bin/perl /oracle/product/11.2.0.4/db/bin/emwd.pl dbconsole /oracle/product/11.2.0.4/db/mezon01_IIRDB/sysman/log/emdb.nohup

 

————————————————————————————————————————————-

[oracle@mezon01 ~]$ sysresv
IPC Resources for ORACLE_SID “IIRDB” :
Shared Memory:
ID KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64
Semaphores:
ID KEY
136445992 0xf1e96e54
136478764 0xf1e96e55
136511533 0xf1e96e56
136544302 0xf1e96e57
136577071 0xf1e96e58
136609840 0xf1e96e59
136642609 0xf1e96e5a
136675378 0xf1e96e5b
136708147 0xf1e96e5c
Oracle Instance alive for sid “IIRDB”
[oracle@mezon01 ~]$ ipcrm -m 100270098
[oracle@mezon01 ~]$ ipcrm -m 100302867
[oracle@mezon01 ~]$ ipcrm -m 100335643
[oracle@mezon01 ~]$
[oracle@mezon01 ~]$
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 “IIRDB” :

Shared Memory:
ID              KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64

Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid “IIRDB”

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

ipcrm -m <problem shared memory id>

ipcrm -m 100270098
ipcrm -m 100302867
ipcrm -m 100335643

$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.