This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Mar 30, 2012 4:08 AM by Nicolas.Gasparotto RSS

ORA-01012: not logged on

600939 Newbie
Currently Being Moderated
[b]At times I am not able to execute any Select statements after logging in. Does this has to do with SGA size? How to troubleshoot this issue

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Oct 1 10:30:32 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected.
SQL> select instance_name from v$instance;
select instance_name from v$instance
*
ERROR at line 1:
ORA-01012: not logged on

=======================================

This is the o/p taken from already connected session

Name Bytes % Used Var. Size
-------------------------- ------------ ------- ------------
free memory 25,165,824 40.00 62,914,560
free memory 8,189,984 13.02 62,914,560
library cache 5,911,848 9.40 62,914,560
miscellaneous 5,895,664 9.37 62,914,560
dictionary cache 3,221,760 5.12 62,914,560
event statistics per sess 2,287,800 3.64 62,914,560
kgl simulator 2,145,024 3.41 62,914,560
1M buffer 2,098,176 3.33 62,914,560
sql area 1,324,880 2.11 62,914,560
KSXR receive buffers 1,034,000 1.64 62,914,560
message pool freequeue 940,944 1.50 62,914,560
KSXR pending messages que 853,952 1.36 62,914,560
sessions 608,400 .97 62,914,560
transaction 399,696 .64 62,914,560
FileOpenBlock 393,232 .63 62,914,560
enqueue 378,016 .60 62,914,560
character set object 274,528 .44 62,914,560
processes 257,600 .41 62,914,560
krvxrr 253,056 .40 62,914,560
KQR L PO 250,240 .40 62,914,560
db_handles 232,000 .37 62,914,560
KGLS heap 231,680 .37 62,914,560
KSXR large reply queue 166,824 .27 62,914,560
KQR M PO 150,624 .24 62,914,560
PX subheap 20,872 .03 62,914,560
KQR S SO 6,672 .01 62,914,560
joxs heap init 4,240 .01 62,914,560
KGK heap 2,200 .00 62,914,560
PLS non-lib hp 2,088 .00 62,914,560
table definiti 992 .00 62,914,560
fixed allocation callback 544 .00 62,914,560

============================================================

Message was edited by:
user597936
  • 1. Re: ORA-01012: not logged on
    Maran Viswarayar Pro
    Currently Being Moderated
    I think you need to start the database
  • 2. Re: ORA-01012: not logged on
    dbtoo Explorer
    Currently Being Moderated
    Your oracle_sid is not set or your sid is not a valid sid for the oracle_home. Ensure that your oracle_home and oracle_sid are set correctly.
  • 3. Re: ORA-01012: not logged on
    519713 Newbie
    Currently Being Moderated
    Is there anything in the alert file?
  • 4. Re: ORA-01012: not logged on
    600939 Newbie
    Currently Being Moderated
    This is a Production Database. I cannot restart the DB
  • 5. Re: ORA-01012: not logged on
    600939 Newbie
    Currently Being Moderated
    No errors in Alert Log
  • 6. Re: ORA-01012: not logged on
    600939 Newbie
    Currently Being Moderated
    All Environmental variables are set. I am able to query the database once I am logged in . But say after 10 minutes if I try to open another sql plus session and try to execute DML statements is says not logged in

    Following are the o/p when connected and not connected

    Not Connected
    =========================================
    $ sqlplus "/ as sysdba"

    SQL*Plus: Release 9.2.0.1.0 - Production on Mon Oct 1 10:30:32 2007

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Connected.
    SQL> select instance_name from v$instance;
    select instance_name from v$instance
    *
    ERROR at line 1:
    ORA-01012: not logged on
    =========================================
    Connected
    =========================================
    $ sqlplus "/ as sysdba"

    SQL*Plus: Release 9.2.0.1.0 - Production on Mon Sep 24 08:32:40 2007

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.1.0 - Production

    SQL> SELECT instance_name FROM v$instance;

    INSTANCE_NAME
    ----------------
    zzaz
  • 7. Re: ORA-01012: not logged on
    Maran Viswarayar Pro
    Currently Being Moderated
    Do not restart

    As earlier post mentioned i assume that your SID is not set

    Do you have more than database in the server ?MOre than one version of DB?

    If so set your Oracle_home and Oracle_SID=<yourDB>

    then try
  • 8. Re: ORA-01012: not logged on
    600939 Newbie
    Currently Being Moderated
    Everything is set. I am not getting this error frequently. These are the steps I do

    1. set oracle_sid and Oracle_home
    2. sqlplus "/ as sysdba"
    3. will be able to connect to the instance and perform DML

    4. Exit
    5. After sometime sqlplus "/ as sysdba"
    6. It says connected, but I am not able to any DMLs it shows not logged in after the SQL statement
  • 9. Re: ORA-01012: not logged on
    Maran Viswarayar Pro
    Currently Being Moderated
    when you login after sometime did you set those environments
  • 10. Re: ORA-01012: not logged on
    600939 Newbie
    Currently Being Moderated
    Yes, all the environmental variables are set each time I login.

    When I am getting the error ORA-01012, Application users are getting ora-04031
  • 11. Re: ORA-01012: not logged on
    Maran Viswarayar Pro
    Currently Being Moderated
    I think you are running short of memory

    increase the SGA the PGA and see whether you still ahve the problem
  • 12. Re: ORA-01012: not logged on
    dbtoo Explorer
    Currently Being Moderated
    So what does ora-04031 say? You should have mentioned that earlier.
    oerr ora 4031
    04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
    // *Cause:  More shared memory is needed than was allocated in the shared
    // pool.
    // *Action: If the shared pool is out of memory, either use the
    // dbms_shared_pool package to pin large packages,
    // reduce your use of shared memory, or increase the amount of
    // available shared memory by increasing the value of the
    // INIT.ORA parameters "shared_pool_reserved_size" and
    // "shared_pool_size".
    // If the large pool is out of memory, increase the INIT.ORA
    // parameter "large_pool_size".


    http://ora-04031.ora-code.com/
  • 13. Re: ORA-01012: not logged on
    530453 Newbie
    Currently Being Moderated
    Hi

    If this is Productin, then you should start the SR with Oracle ASAP.

    Regards
  • 14. Re: ORA-01012: not logged on
    247514 Expert
    Currently Being Moderated
    I had the same problem happen to one of my production server. When I do my research found this thread. Thought it would be better to add a note in case someone run into this again.

    Oracle 10.2.0.3 Linux RHEL4

    The symptom is same as OP had,

    sqlplus / as sysdba

    show 'connected' without Server Banner

    any query will result
    ORA-01012: not logged on
    There's no errors in alert log file and listener.log what so ever.

    The problem is limit of parameter processes hit.
    There's no much you can do, except drop some session so that you can connect as sysdba and increase processes. Need re-bounce.

    Also check your APP server so why there's sudden increase of connections.
1 2 Previous Next