CODEWITHSUNDEEP
oracle-databaseinstanceidle-processing
seanbseanbseanbseanb
seanbseanbseanbseanb

Reputation:

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

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
%

Upvotes: 17

Views: 117566

Answers (13)

J.Col
J.Col

Reputation: 41

just type : 

shutdown abort

then

startup

Upvotes: 0

Anwar Husain
Anwar Husain

Reputation: 21

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.

Upvotes: 2

GintsGints
GintsGints

Reputation: 855

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

Upvotes: 1

Talwinder Singh
Talwinder Singh

Reputation: 1

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.

Upvotes: 0

Eter Pani
Eter Pani

Reputation: 1

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.

Upvotes: 0

dbamanager
dbamanager

Reputation: 21

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.

Upvotes: 2

Edmael
Edmael

Reputation: 11

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

Upvotes: 1

pchov
pchov

Reputation: 41

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.

Upvotes: 4

Simon
Simon

Reputation:

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

Upvotes: 2

DCookie
DCookie

Reputation: 43523

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.

Upvotes: 2

Matthew Watson
Matthew Watson

Reputation: 14233

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.

Upvotes: 16

Leigh Riffel
Leigh Riffel

Reputation: 6641

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"

Upvotes: 3

Igor Zelaya
Igor Zelaya

Reputation: 4277

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

Upvotes: 8

Related Questions