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

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

0

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 10.2.0.1.0 - Production on Mon Sep 24 15:22:47 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL>

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_BASE=/u01/app/oracle/
ORACLE_HOME=/u01/app/oracle/product/10.2/db_1/
ORACLE_SID=CDM05
[oracle@bdm05 db_1]$ env | fgrep ORA
ORACLE_SID=CDM05
ORACLE_BASE=/u01/app/oracle/
ORACLE_HOME=/u01/app/oracle/product/10.2/db_1/

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_SID=cdm05
ORACLE_BASE=/u01/app/oracle/
ORACLE_HOME=/u01/app/oracle/product/10.2/db_1
[oracle@server db_1]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - 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 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL>

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

*