Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01012: not logged on

user597936Oct 2 2007 — edited Mar 30 2012

[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

Maran Viswarayar

I think you need to start the database

dbtoo

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.

Jfcote

Is there anything in the alert file?

user597936

This is a Production Database. I cannot restart the DB

user597936

No errors in Alert Log

user597936

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

Maran Viswarayar

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

user597936

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

Maran Viswarayar

when you login after sometime did you set those environments

user597936

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

Maran Viswarayar

I think you are running short of memory

increase the SGA the PGA and see whether you still ahve the problem

dbtoo

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/

530453

Hi

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

Regards

247514

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.

jonjac

There might be another reason for this problem:

File permissions in ORACLE_HOME have been changed.

910699

-- > I agree with the concept of Permission changes to ORACLE_HOME.
The One good solution is to kill the pmon process and start the database instance once again.
It needs a downtime.

1 person found this helpful

907884

Try to connect the database through tns entry .. sqlplus sys/<password>@tnsentry as sysdba

927264

EXCEPTION DESCRIPTION: java.sql.SQLException: ORA-01012: not logged on

Can anyone please tell me what is the reason behind this error, and how can i Solve it.

EmaxG

924261 wrote:
EXCEPTION DESCRIPTION: java.sql.SQLException: ORA-01012: not logged on

Can anyone please tell me what is the reason behind this error, and how can i Solve it.

Can you please care to read the thread before asking questions? your issue has been mentioned earlier (Subject of the thread d'oh?)

Edited by: N K on 29-mar-2012 5:50

927264

It is a production Server and we cann't restart in weekdays, and for resolving this problem we are restarting the server in weekends and its is not the permanent solution.......

I hope any one can give me the permanent solution for this problem.. and one more thing, we don't have any authority for database.. we can do only through the entire application

is there any way to solve it.....please help me........

Nicolas Gasparotto

stopping hijacking 5 years old thread, thread locked

Nicolas.

1 - 21

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 27 2012

Added on Oct 2 2007

#general-database-discussions

21 comments

224,383 views

Oracle Database Discussions

ORA-01012: not logged on

Comments

Post Details