Trying to connect internally to Oracle, getting an idle instance?

Question

So I have an Oracle instance, and I know it's running on this system, I've su'd to the oracle user, and I'm trying to connect using "/ as sysdba". However, when I do connect, it says the instance is idle. I know the database is up and opened, because my application's talking to it. My paths (ORACLE_HOME, etc.) might be incorrect: any idea which incorrect setting might result in this?

% sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Dec 8 09:23:22 2008

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

Connected to an idle instance.

09:23:22 SQL> Disconnected

% ps -ef | grep smon
 oracle  6961     1   0   Nov 05 ?           1:24 ora_smon_ORA003
%

Andy

Answer 1

make sure you have your ORACLE_HOME setup exactly the same as when the server was started, I've seen this problem with oracle 9.2.0.5.0 on solaris,

ORACLE_HOME=/opt/oracle
ORACLE_HOME=/opt/oracle/

is two different things, and will result in issues connecting locally.

Valentin

Answer 2

that means that the database instance is not mounted nor open. Execute the startup command and see if any errors appear.

Break

Answer 3

thanks. it indeed was that extra front slash at the end in ORACLE_HOME variable.

In my case, see the wierdness - I logged in to the server and tried connecting but got the above error. I knew that instance was up and DB was opened. So checked the ORACLE_HOME because I was sort of aware about this possibility. What I saw was that ORACLE_HOME was fine (i.e. no extra front slash at the end). Then after trying a lot when I read this thread, it struck me. The DB was started with ORACLE_HOME set with that extra front slash. So DB was started with ORACLE_HOME=/u01/app/oracle/product/10.2.0.3/ and all the while, I was trying with ORACLE_HOME=/u01/app/oracle/product/10.2.0.3 :(

Thanks again.

Stranger

Answer 4

The database cannot be idle and in use by your application at the same time. It seems like the sqlplus session must be connected to a different instance than the application. Try specifying the connect identifier in the connect statement as follows:

sqlplus "/@ConnectIdentifier as sysdba"

Authors

Answer 5

try from the machine console

export ORACLE_SID=your sid here
sqlplus /nolog
startup

I know on windows there is a command to create a service to start up an instance for you, oradmin -new -sid %ORACLE_SID% -intpwd %oracle_pwd% -startmode A

Susurration

Answer 6

Case is significant on *nix systems, so make sure your ORACLE_SID exactly matches the instance name. In this case, ORA003 is NOT the same as ora003.

Bost

Answer 7

I had the same problem. By removing the extra "/" at the end of ORACLE_HOME solved it.

Thanks for sharing - it would have been really difficult to diagnose and resolve this problem in the absence of this blog.

Thorley

Gosh! It has solved my problem after 5 hours trying!!! Thanks. Obs: RHEL 7.2 – Truitt

Answer 8

Try this Login with sqlplus sys/sys as sysdba

SQL> startup

OUTPUT SHOULD BE LIKE:\n

Total System Global Area  467652608 bytes
Fixed Size          2214416 bytes
Variable Size         352323056 bytes
Database Buffers      104857600 bytes
Redo Buffers            8257536 bytes
Database mounted.
Database opened.

Heterogenous

Answer 9

Ok here's what i've Found out about instance idle it has to do with the spfile.. sometimes your init.ora is located somewhere else

try to search for init.ora, once you found the location

try that code startup spfile="C:\location";

it will say that instance started.

well that one worked for me

Daven

Answer 10

I had same issue while specify wrong SID (xe instead of XE). If you install database with rpm, check all enviroment at /etc/inid.d/oracle

Mess

Answer 11

The problem can be if the session could not be opened due to extremally busy database. in this case connection using any user even dummy one

sqlplus dummy/dummy

would give you actual problem but not idle instance.

Faubert

Answer 12

I tried sqlplus dummy/dummy and it gave me the actual issue (out of memory). I made memory available and I was able to login without any issue.

Cohabit

Answer 13

just type :

shutdown abort

then

startup

Coralloid