Kamran Agayev's Oracle Blog

Oracle Certified Master

Connected to an idle instance – while database is running

Posted by Kamran Agayev A. on July 11th, 2012

I strongly believe you already know it, but would like to share if you’ve forgot and can’t understand why you’re not able to connect to the instance which is already running

[php]-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jul 11 09:24:18 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> exit

-bash-3.2$ ps -ef | grep smon
oracle 21202 1 0 Nov 8 ? 118:15 ora_smon_MYDB

[/php]

 

The database is running, but I can’t login to the instance. Let’s check ORACLE_HOME environment variable:

[php]

/home/oracle/product/10g/

[/php]

 

Do you see the slash in the end of the line? That’s the reason! Let’s set it again without the slash at the end and try again

 

[php]

-bash-3.2$ export ORACLE_HOME=/home/oracle/product/10g
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Wed Jul 11 09:33:39 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

[/php]

 

Here it is …

 

10 Responses to “Connected to an idle instance – while database is running”

  1. Ulfet Says:

    Interesting! Thanks for share

  2. Mahir M. Quluzade Says:

    Hi Kamran!

    You are right, this is reason of connection idle instance.
    Thanks for share !

    Mistake ORACLE_SID is reason of connection idle instance, too.

    [oracle@oel62-x64 /]$ echo $ORACLE_SID
    admdb

    [oracle@oel62-x64 /]$
    [oracle@oel62-x64 /]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 12:55:47 2012
    Copyright (c) 1982, 2011, Oracle. All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options

    SQL>
    SQL> exit
    [oracle@oel62-x64 /]$ export ORACLE_SID=ADMDB
    [oracle@oel62-x64 /]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 11 12:56:01 2012
    Copyright (c) 1982, 2011, Oracle. All rights reserved.
    Connected to an idle instance.
    SQL>

    Regards
    Mahir M. Quluzade

  3. Kamran Agayev A. Says:

    That’s right Mahir. Actually in this case the first thing you check is ORACLE_SID. When you see it’s correctly set and the instance is running on the OS level, you need to check ORACLE_HOME variable and remove the last character which is slash “/”

  4. liveinoracle Says:

    Hi Kamran,

    I felt your blog is very interesting. it will be very helpful if we work on ORACLE Performance Tuning. We want to master it.do you have any idea how we can create a dummy application to learn oracle performance tuning. Thank you.With Regards..liveinoracle

  5. Connected to an idle instance y ORA-01034 | Mi Blog sobre Oracle Says:

    […] este ejemplo, escrito por Kamran Agayev, aquí y decidí traducirlo en español. Tenía una base de datos en un servidor […]

  6. javid Says:

    Hi,
    When i am trying to connect data base showing “connected to an idle instance”. it was connecting and working before, please provide solution

    Thanks
    Javid
    Javid.saber6@gmail.com

  7. Kamran Agayev A. Says:

    Hi Javid

    Make sure you’ve correctly specified ORACLE_SID

  8. abinash Says:

    [oracle@cfgdbxl45tenoi admin]$ sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 4 14:32:10 2014

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

    Connected to an idle instance.

    ************************************************

    please help…..

    [oracle@cfgdbxl45tenoi admin]$ echo $ORACLE_SID
    STGTSTD1
    ******************
    [oracle@cfgdbxl45tenoi ~]$ cat /etc/oratab
    #

    # This file is used by ORACLE utilities. It is created by root.sh
    # and updated by either Database Configuration Assistant while creating
    # a database or ASM Configuration Assistant while creating ASM instance.

    # A colon, ‘:’, is used as the field terminator. A new line terminates
    # the entry. Lines beginning with a pound sign, ‘#’, are comments.
    #
    # Entries are of the form:
    # $ORACLE_SID:$ORACLE_HOME::
    #
    # The first and second fields are the system identifier and home
    # directory of the database respectively. The third filed indicates
    # to the dbstart utility that the database should , “Y”, or should not,
    # “N”, be brought up at system boot time.
    #
    # Multiple entries with the same $ORACLE_SID are not allowed.
    #
    #
    STGTSTD1:/u01/app/oracle/product/11.2.0/dbhome_1:N
    TDSTSTO1:/u01/app/oracle/product/11.2.0/dbhome_1:N

    ***************************************************************
    ***************************************************************
    [oracle@cfgdbxl45tenoi admin]$ cat tnsnames.ora
    # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
    # Generated by Oracle configuration tools.
    TDSTSTO1 =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cfgdbxl45tenoi)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = TDSTSTO1)
    )
    )

    STGTSTD1=
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = cfgdbxl45tenoi)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = STGTSTD1)
    )
    )

    ******************************************************************

    Please help……..I am getting connecting to an idle instance

  9. Kamran Agayev A. Says:

    Check
    export ORACLE_SID=TDSTSTO1
    sqlplus / as sysdba
    startup

    or

    export ORACLE_SID=STGTSTD1
    sqlplus / as sysdba
    startup

    Make sure you’ve set environment variables correctly

  10. abinash Says:

    Hi Kamran,

    Please help me …in this situation……

    Thanks in advance….

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>