[Oracle] Troubleshoot “Connected to an idle instance.”

Posted by cikul | Posted in Linux, Oracle, SQL, Windows | Posted on 24-09-2012-05-2008


Problem : You have an Oracle instance, and you know it’s running on the system, when you’re trying to connect using “/ as sysdba” it says the instance is idle. You know the database is up and opened, because your application’s talking to it.

[oracle@server db_1]$ sqlplus / as sysdba
SQL*Plus: Release - Production on Mon Sep 24 15:22:47 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.

Connected to an idle instance‘ means that the client (sqlplus) did not find the shared memory segment. The key for the segment is based on ORACLE_SID and ORACLE_HOME, so we can say it’s an environment variables setting issue.

to check your environtment variables, check with this command :

[oracle@bdm05 db_1]$ set | grep ORA
[oracle@bdm05 db_1]$ env | fgrep ORA

Are you sure that your ORACLE_HOME and ORACLE_SID are the right one ?

For example, if the ORACLE_HOME was started with ‘/u01/app/oracle/product/10.2/db_1/’ then it is different from ‘/u01/app/oracle/product/10.2/db_1/’  and ORACLE_SID ‘CDM05’ different with ‘cdm05’ (in unix base is case sensitive)

If your ORACLE_HOME variable have extra slash, you must remove the slash, and for ORACLE_SID the variable is case sensitive, you must sure the SID is correct.

To set the correct value, you can do this command :

[oracle@server db_1]$ export ORACLE_SID=cdm05
[oracle@server db_1]$ export ORACLE_HOME=/u01/app/oracle/product/10.2/db_1
[oracle@server db_1]$ env | fgrep ORA
[oracle@server db_1]$ sqlplus / as sysdba

SQL*Plus: Release - Production on Mon Sep 24 15:48:34 2012

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

Connected to:
Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP and Data Mining options


And how to know the correct ORACLE_SID parameter? ORACLE_SID MAY BE or MAY NOT BE the same as the database name. Here is a way to find it:

[oracle@server db_1]$ ps afxu | grep pmon
oracle    7035  0.0  0.0 51120  672 pts/2    S+   15:50   0:00                  \_ grep pmon
oracle    7641  0.0  0.3 4333512 24808 ?     Ss    2011   9:54 ora_pmon_cdm05

Or in SunOS

<pre>root@SUNSVR # ps -efy | grep pmon
oracle  7183     1   0 14:17:58 ?           0:00 ora_pmon_cdm05
root  7994  6415   0 15:02:08 pts/6       0:00 grep pmon

see the string ora_pmon_cdm05, from the result we can see that active SID : cdm05.

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

Share and Enjoy

Write a comment