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 …
July 11th, 2012 at 7:33 am
Interesting! Thanks for share
July 11th, 2012 at 7:56 am
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
July 11th, 2012 at 8:39 am
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 “/”
July 11th, 2012 at 7:26 pm
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
December 13th, 2012 at 11:33 pm
[…] este ejemplo, escrito por Kamran Agayev, aquí y decidí traducirlo en español. Tenía una base de datos en un servidor […]
February 4th, 2014 at 4:05 am
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
June 17th, 2014 at 3:59 am
Hi Javid
Make sure you’ve correctly specified ORACLE_SID
June 4th, 2014 at 8:37 am
[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
June 11th, 2014 at 8:24 am
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
June 4th, 2014 at 9:38 am
Hi Kamran,
Please help me …in this situation……
Thanks in advance….