What’s going on? Connected to an idle instance but database is running

Connected to an idle instance but the database is running

We have seen this a few times over the past few months, cases where a database is running but when you try to initiate a local connection to it, you receive the “connected to an idle instance” message. This can be a little bit confusing, especially if your database is working fine otherwise with connections via the listener working perfectly. In this post let's take a closer look at this scenario. In the first examples below there is an Oracle Linux 5.8 running Oracle 11g.

We ran a similar test on 19c and interestingly the message is now different, it is “ORA-12547: TNS:lost contact”.  But before we get to this, let's follow the example on 11g then have a look at 19c at the end of the post.

So here is a quick example of what happens; first, we see that the database is running, but when trying to connect the “Connected to an idle instance” message appears.

oracle@dbvlin505[/home/oracle]: ps -ef|grep pmon
oracle 24134 1 0 15:48 ? 00:00:00 ora_pmon_testdb
oracle@dbvlin505[/home/oracle]: sqlplus /nolog SQL*Plus: Release 11.2.0.3.0 Production on Mon May 27 15:48:00 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect / as sysdba Connected to an idle instance.

What Is Happening Here?  

Further investigation revealed that the database was originally started with the ORACLE_HOME environment variable including an extra “/” at the end. But since that time the ORACLE_HOME environment variable had been updated to the correct path - which is without the additional slash “/” at the end. 

For example, you might be using the /etc/oratab file together with “. oraenv” to set your environment and the /etc/oratab file makes use of the correct path without the slash – but more on the use of /etc/oratab and oraenv later in the post.

To simulate this let me show you an example:

First I set the ORACLE_HOME to include the extra "/" at the end, and start the database:

oracle@dbvlin505[/home/oracle]: exportORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1/
oracle@dbvlin505[/home/oracle]: sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 27 15:48:00 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 310379864 bytes
Database Buffers 306184192 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Now we can start the database, but how can we tell what the ORACLE_HOME environment variable was when the database was started?  

There is a nice option in Linux to get to this by making use of the /proc filesystem. First, let’s get the process id of the PMON background process, then once we have this review the contents of the /proc/<pmon_process_id>/eniron file. For example:

oracle@dbvlin505[/home/oracle]: ps -ef|grep pmon
oracle 24134 1 0 15:48 ? 00:00:00 ora_pmon_testdb
oracle 24209 22062 0 15:48 pts/1 00:00:00 grep pmon

At this stage you can just use “cat /proc/24134/environ” but this might not be a good way to see all the variables in a nice, clear way. Using xargs -0 –n1 helps with formatting the output.

oracle@dbvlin505[/home/oracle]: cat /proc/24134/environ | xargs -0 -n1 |grep ORACLE_HOME
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1/

As you can see from the above, the ORACLE_HOME shows the extra slash at the end of the path. So if the environment is set to use ORACLE_HOME without the slash and an attempt to connect to the database happens, we will get the “connected to an idle instance” message:

oracle@dbvlin505[/home/oracle]: export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
oracle@dbvlin505[/home/oracle]: sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 27 15:48:54 2013

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

SQL> connect / as sysdba
Connected to an idle instance.
SQL>

To resolve the issue the database needs to be started with the correct ORACLE_HOME without the extra slash. For example:

Step 1:

First set the path to include an extra slash, then connect via sqlplus:

oracle@dbvlin505[/home/oracle]: export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1/
oracle@dbvlin505[/home/oracle]: sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 27 15:56:38 2013

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

SQL> connect / as sysdba
Connected.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

Step 2: 
Now reset the path to the correct one without a slash:
oracle@dbvlin505[/home/oracle]: export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
oracle@dbvlin505[/home/oracle]: sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 27 15:57:00 2013

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

SQL> connect / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 626327552 bytes
Fixed Size 2230952 bytes
Variable Size 310379864 bytes
Database Buffers 306184192 bytes
Redo Buffers 7532544 bytes
Database mounted.
Database opened.
SQL>

Once the steps above are complete, you should be able to connect without any issue using the correct ORACLE_HOME path, without the additional slash at the end. We recommend you make use of the /etc/oratab and the “oraenv” script to set your environment. First, make sure that your /etc/oratab file has the database and its correct path listed (without the slash), and then execute “. oraenv” to set your environment. Here is an example:

My /etc/oratab contains the following:
#
# 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:<N|Y>:
#
# 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.
#
#
testdb:/u01/app/oracle/product/11.2.0/dbhome_1:N

Now we can just run ".oraenv" to set the correct environment for the testdb database:

oracle@dbvlin101[/home/oracle]: . oraenv
ORACLE_SID = [testdb] ? testdb
The Oracle base remains unchanged with value /u01/app/oracle

oracle@dbvlin101[/home/oracle]: env |grep ORACLE
ORACLE_SID=testdb

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

We hope you find this information useful in helping you troubleshoot why you are getting “connected to an idle instance” when attempting a local connection with a database that is already running.

Update on Oracle 19c

While testing a similar scenario on Oracle 19c, we noticed that the message is now different. Instead of getting the “connected to an idle instance”, you get: “ORA-12547: TNS:lost contact”.

For example, we start the Oracle 19c database with ORACLE_HOME set to include an extra slash at the end:

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1/

Then start the database instance and exit your connection.

Following that, variable is set to be without the slash:

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

And now trying a sqlplus connection, we get this:

[oracle@dbvx ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
[oracle@dbvx ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Mon May 4 14:43:18 2020
Version 19.6.0.0.0

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

SQL> connect / as sysdba
ERROR:
ORA-12547: TNS:lost contact

SQL>

So be aware there seem to be slight differences here in how this issue is being reported in earlier versions of Oracle compared to the later versions.  

In summary, if you are getting “connected to an idle instance” or “TNS: lost contact”, have a quick look at your ORACLE_HOME variable used during database startup compared to your current setting, and make sure you do not use the extra slash at the end of the path.