Forum Stats

  • 3,760,437 Users
  • 2,251,706 Discussions
  • 7,871,120 Comments

Discussions

Connected to an idle instance issue

Helios-GunesEROL
Helios-GunesEROL Member Posts: 18,272
edited Feb 3, 2011 6:31AM in General Database Discussions
Hi friend;

I have strange issue. I have 11.2.0.1 db on AIX6. ORACLE_HOME and ORACLE_SID already set.

When i try to run below command:

sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on 2011

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

Connected to an idle instance.

But Oracle process is running

When i try

sqlplus sys/[email protected] as sysdba << i can connect SQL and can run query


I checked my listener.ora file and it seems okey. No any error message in alert.log,i can also login database from toad wihtout any issue.

No luck also in metalink notes, anyone face similar issue before?

PS: Memory size: 32 G - usage : 32 G

Regard
Helios

Answers

  • G777
    G777 Member Posts: 480
    hi

    make sure ORACLE_SID is exported correctly and your run sqlplus from the right directory (binaries) in case you have more oracle installations in the box.
    to check , try
    echo $ORACLE_SID
  • Philippe Florent
    Philippe Florent Member Posts: 667 Silver Badge
    edited Feb 3, 2011 5:31AM
    Hi,
    You must know that when Oracle connects to a local instance under Unix it is using ORACLE_SID and ORACLE_HOME env.var to compute shared memory key used by instance (and displayed by ipcs -mob):

    So if SQL*Plus says "idle instance", it means the computed key is wrong because something has changed between

    - the time instance has been started
    - the time you try to connect

    You should really try to find what has changed for ORACLE_SID and ORACLE_HOME.

    Are you also sure that "testuser" is connected on the same host as "oracle" ? If not local connections cannot work and you should use Oracle Net connection strings.

    A possible explanation by P.Forstmann ?
    1124640

    Best regards
    Phil

    Edited by: Philippe Florent on Feb 3, 2011 11:30 AM
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    Consider to double-check your ORACLE_HOME :
    [email protected]:/appl/oracle/product/11.2.0.2/dbs(o11)# export ORACLE_SID=hdmo912
    [email protected]:/appl/oracle/product/11.2.0.2/dbs(o11)# export ORACLE_HOME=//appl/oracle/product/11.2.0.2 <--extra slash upfront
    [email protected]:/appl/oracle/product/11.2.0.2/dbs(o11)# sqlplus sysadm/SYSADM <-- cannot connect without db alias
    
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 3 11:26:28 2011
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    ERROR:
    ORA-01034: ORACLE not available
    ORA-27101: shared memory realm does not exist
    IBM AIX RISC System/6000 Error: 2: No such file or directory
    Process ID: 0
    Session ID: 0 Serial number: 0
    
    
    Enter user-name:
    [email protected]:/appl/oracle/product/11.2.0.2/dbs(o11)# sqlplus sysadm/[email protected] <-- can connect with db alias
    
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 3 11:26:37 2011
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL> quit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    [email protected]:/appl/oracle/product/11.2.0.2/dbs(o11)# export ORACLE_HOME=/appl/oracle/product/11.2.0.2 <-- removed the extra slash
    [email protected]:/appl/oracle/product/11.2.0.2/dbs(o11)# sqlplus sysadm/SYSADM <-- now can connect without db alias
    
    SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 3 11:27:18 2011
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
    SQL>
    Nicolas.
    Nicolas Gasparotto
  • Chinar
    Chinar Member Posts: 5,542
    1) Are there how many ORACLE_HOME s?
    2) Post there ls $ORACLE_HOME/dbs
  • Helios-GunesEROL
    Helios-GunesEROL Member Posts: 18,272
    edited Feb 3, 2011 6:31AM
    Hi Friends;

    Thanks for all valuable input. For all suggestion already done before i post issue here(Checking env, Setting ORALE_HOME,ORACLE_SID etc).

    I could connect sqlplus when i reebot database,(Memory usage became less than real memory size.) Now i could connect.

    For this issue who can face i suggest chech below steps.

    1.Be sure you set ORACLE_HOME and ORACLE_SID
    2. Be sure have valid entery in listener.ora
    3. Check memory usage
    4. Be sure not exceed session number
    5. If non-is help than i sugest reboot database

    Regard
    Helios
This discussion has been closed.