"ORA-01075: you are currently logged on" with ORA-4031

Version: 

Oracle 11g EE 11.2.0.3.0 - 64bit Production


Issue:

ORA-01075: you are currently logged on after getting ORA-4031

Cannot login to the SQLPlus as sysdba.


60-oracle@DBServer1=> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 01:04:36 2014

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

ERROR:
ORA-01075: you are currently logged on


Enter user-name: sys as sysdba
Enter password: 
ERROR:
ORA-01075: you are currently logged on


Verification:

The alert log file had so many errors related to ORA-4031

ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select file#, block#, ts# fr...","Typecheck","kgghtInit")

I was able to login as system user. Tried to flush the shared_pool, but ended up getting memory error

SQL> alter system flush shared_pool;
alter system flush shared_pool
*
ERROR at line 1:
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 3896 bytes of shared memory ("shared
pool","select t.ts#,t.file#,t.block...","sga heap(1,0)","kglsim object batch")


Solution:


We need to solve the ORA-4031 issue first. Once ORA-4031 issue is resolved, the ORA-1075 will be resolved by itself.

ORA-1075 is because of the after effect of ORA-4031. The only work around is to connect as system user and flush the shared_pool. If that is also not working, then kill the background process, restrat the DB and increase shared pool. The steps are mentioned below

Step 1. Identify and kill the DB background process

65-oracle@DBServer1=> ps -ef|grep pmon|grep orcl
oracle   22255     1  0 Mar18 ?        00:00:07 ora_pmon_orcl

kill -9 22255

Step 2. Connect to DB and start the instance

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 24 01:25:05 2014

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

Connected to an idle instance.

SQL> 
SQL> 
SQL> startup

Step 3. Increase the shared pool size

Increase sga_target size or shared_pool_size parameter.