Home » RDBMS Server » Server Administration » connected to Idle Instance (Oracle 10g)
connected to Idle Instance [message #408932] Thu, 18 June 2009 06:30 Go to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
After installig oracle10g on XP and setting the services of oracle to start in 'manual' mode through services window,whenever i connect through sqlplus,i get message as "Connected to an Idle instance"
what does 'idle' instance mean.Does it mean that the various processes of oracle has been started but database is not mounted OR processes itself are not started.
I understand the sequence is:
startup
mount
open

Can these 3 steps be manually configured on WINDOWS..
Any help seriously appreciated...
Re: connected to Idle Instance [message #408934 is a reply to message #408932] Thu, 18 June 2009 06:37 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

If you want to know what does "idle instance" mean, firstly you should know what does "instance" mean. Instance consists of background processes and SGA memory. When you connect to sqlplus and issue conn / as sysdba, you connect to instance where you can start, mount or nomount

With nomount - you'll start the instance. Background processes will start and memory will be allocated

With mount - you'll open control files and verify destinactions of redo log files and data files

With open - you'll open your datafiles and redo log files
Re: connected to Idle Instance [message #408939 is a reply to message #408934] Thu, 18 June 2009 07:08 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Thanks for the reply,
i have made a change in registry as ORA_AUTOSTART=FALSE.
Does this change results in an idle instance??
From your explanation,i can understand that idle instance means oracle service has started various background processes and memory allocation,BUT has NOT mounted the database.

My other question is:After i get the message as"connected to idle instance",i fire 'startup nomount'.At this stage,i get message as 'Database mounted'.This should ideally give me message as 'Instance already started'.
pls clarify these startup points as i am really confused about basics..no ebook(i have read) gives proper explanation to it...
Re: connected to Idle Instance [message #408941 is a reply to message #408939] Thu, 18 June 2009 07:15 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Firstly, I advice you to refer to Oracle Concepts and Administration Guides documentations

Idle instance means that you connected to the instance which didn't started yet. By issuing STARTUP NOMOUNT you can start the instance

If you issue STARTUP MOUNT, it will open Control Files and look for datafiles

If you get "Database Mounted", it means that the database already mounted and you need just open it
Re: connected to Idle Instance [message #408943 is a reply to message #408939] Thu, 18 June 2009 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In Windows, "idle" means that the service is started but the instance is not there.
In Unix, it means there is nothing.

Regards
Michel
Re: connected to Idle Instance [message #408965 is a reply to message #408943] Thu, 18 June 2009 08:36 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Now,here you come what i have been searching for...
If 'idle' means service is started,but not instance,then
what is the difference between service and instance on windows??
Re: connected to Idle Instance [message #408970 is a reply to message #408965] Thu, 18 June 2009 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SGA is not allocated, background "processes" (actually threads in Windows) are not started.

Regards
Michel
Re: connected to Idle Instance [message #409045 is a reply to message #408932] Thu, 18 June 2009 23:49 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
In other words,that means the instance is not started.My question now is:What does service start along with it?
Also,ORA_AUTOSTART..what is the function of this registry key.
Re: connected to Idle Instance [message #409048 is a reply to message #409045] Fri, 19 June 2009 00:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What does service start along with it?

It is its implementation on Windows.

Quote:
ORA_AUTOSTART..what is the function of this registry key.

To automatically start or not the database when the service is started.

Regards
Michel
Re: connected to Idle Instance [message #409076 is a reply to message #409048] Fri, 19 June 2009 03:56 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Quote:
It is its implementation on Windows.


Implementation on Windows???
Can you be more specific?
Re: connected to Idle Instance [message #409104 is a reply to message #409076] Fri, 19 June 2009 07:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Windows needs a service to create a SGA.
It is the same question as why do you need Windows to execute a (windows) program.

Regards
Michel
Re: connected to Idle Instance [message #409107 is a reply to message #408932] Fri, 19 June 2009 07:27 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
So,does that mean that Linux does not use service??It directly starts the instance,mounts and opens the database.If so,then one can never come across such 'idle' instance on linux??Am i right?
Re: connected to Idle Instance [message #409110 is a reply to message #409107] Fri, 19 June 2009 07:38 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:
So,does that mean that Linux does not use service?


Yes.

Quote:
If so,then one can never come across such 'idle' instance on linux?


Or infinite ones. If you set ORACLE_SID to any random value and "connect /", then you are connected to an 'idle instance'

oracle ~ $ export ORACLE_SID=NOT_THERE
oracle ~ $ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fr Jun 19 14:37:33 2009

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

Connected to an idle instance.

SYS@NOT_THERE>
Re: connected to Idle Instance [message #409115 is a reply to message #409107] Fri, 19 June 2009 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition to Thomas answer.
There is one step more (and before) in Windows than in Unix/Linux: starting a service, this step is mandatory in Windows in order to get a starting point, this is Windows...

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jun 19 15:08:43 2009

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
C:\>net start OracleServiceMIKB
Le service OracleServiceMIKB démarre...........
Le service OracleServiceMIKB a démarré.

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Jun 19 15:10:22 2009

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

Connected to an idle instance.

Regards
Michel
Re: connected to Idle Instance [message #409116 is a reply to message #409115] Fri, 19 June 2009 08:28 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Thank you michel..you answered all my queries..i understood now the startup process on oracle
Thanks,
Satish
Re: connected to Idle Instance [message #409222 is a reply to message #409116] Sat, 20 June 2009 13:17 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Sorry to bother you again,but i have another query..
I have set oracle on my XP system to start only service at boot time.After system is booted and i enter 'sqlplus' through command prompt,iam asked for userid and pwd..
I enter 'sys as sysdba' as user and any random string as pwd and i always get the message 'connected to idle instance'..
I want to know why the sys account pwd is not getting validated??
is it because DB is in closed state??(pls correct me if wrong)
If this is the case,then the prompt for userid and pwd after sqlplus does not make any sense..
Re: connected to Idle Instance [message #409226 is a reply to message #408932] Sat, 20 June 2009 14:25 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
This is a FAQ plus normal & expected behaviour.
Oracle is using Operating System (OS) authentication.
You are logged onto the OS as the owner of Oracle s/w.
Therefore no password is required to access Oracle DB.
Re: connected to Idle Instance [message #409227 is a reply to message #409222] Sat, 20 June 2009 14:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read Database Concepts and Administrator's Guide, both contain sections on authentication in their first chapter.

Regards
Michel

[Updated on: Sat, 20 June 2009 14:49]

Report message to a moderator

Re: connected to Idle Instance [message #409228 is a reply to message #409226] Sat, 20 June 2009 15:27 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
BlackSwan wrote on Sat, 20 June 2009 14:25

You are logged onto the OS as the owner of Oracle s/w.


In that case,even the user id can be wrongly entered..

Quote:
Therefore no password is required to access Oracle DB

do u mean:no pwd to access oracle db server??
Also,if i try sqlplus /nolog,it directly gives the sql prompt without asking any username and pwd..
Re: connected to Idle Instance [message #409229 is a reply to message #408932] Sat, 20 June 2009 16:34 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Are you unwilling or incapable to Read The Fine Manuals directed to you by Michel Cadot?

Your answers are just a few clicks beyond the URL below.
http://tahiti.oracle.com
Let us know when you have found your answer.
Re: connected to Idle Instance [message #409230 is a reply to message #409229] Sat, 20 June 2009 16:59 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
BlackSwan wrote on Sat, 20 June 2009 16:34
Are you unwilling or incapable to Read The Fine Manuals directed to you by Michel Cadot?

Your answers are just a few clicks beyond the URL below.
http://tahiti.oracle.com
Let us know when you have found your answer.


Actually,the manuals suggested by Michel explains the entire oracle architecture.I want to learn only the installation/startup part of it..I will have to learn the most of the chapters to get answers to my queries..
So,i thought may be i can directly ask the experts..
can you give me the exact url/link from the manuals suggested,that can directly answer my queries??


Re: connected to Idle Instance [message #409231 is a reply to message #408932] Sat, 20 June 2009 17:17 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>can you give me the exact url/link from the manuals suggested,that can directly answer my queries??

Follow the links to the 2 manuals & look in the Index for the section which contains your answers
Re: connected to Idle Instance [message #409239 is a reply to message #409230] Sun, 21 June 2009 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
both contain sections on authentication in their first chapter.

Is this not sufficient for you to find the link from toc and then answer?

Regards
Michel
Re: connected to Idle Instance [message #409384 is a reply to message #409239] Mon, 22 June 2009 07:18 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Ok.
Sorry for frustating you with silly questions,but you all have really cleared most of my queries..
I have now understood that /nolog switch starts the sqlplus without connecting to any DB.However,if i do not specify this switch,then it tries to connect to DB.My question is:which DB is it trying to connect??
Is it the DB that is specified in ORACLE_SID env variable??
And,what if this env variable is not set?


Re: connected to Idle Instance [message #409386 is a reply to message #409384] Mon, 22 June 2009 07:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My question is:which DB is it trying to connect??

None, it just asks you for credentials.
In these ones you precise your target database or the one in ORACLE_SID if you don't specify anything.

Quote:
what if this env variable is not set?

Just try it.

Regards
Michel
Re: connected to Idle Instance [message #409404 is a reply to message #409386] Mon, 22 June 2009 07:56 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
I do not have any ORACLE_SID env variable set,not do i specify it at command prompt and I always get message as "Connected to an idle instance".
Don't you think it should throw error as "Instance not running" instead of above message?.. since the instance is not yet started,there should be no question of connecting to any DB..
Anyways,i will have to search now the significance of this ORACLE_SID env variable..
Re: connected to Idle Instance [message #409410 is a reply to message #409404] Mon, 22 June 2009 08:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"idle instance" and "Instance not running" are synonymous.
If you don't like the message, ask Oracle to change it. Smile

Regards
Michel
icon14.gif  Re: connected to Idle Instance [message #409411 is a reply to message #409410] Mon, 22 June 2009 08:24 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

He he he Laughing
Re: connected to Idle Instance [message #409516 is a reply to message #409410] Tue, 23 June 2009 00:27 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Michel Cadot wrote on Mon, 22 June 2009 08:21
"idle instance" and "Instance not running" are synonymous.
If you don't like the message, ask Oracle to change it. Smile

Regards
Michel


Smile
Ok,i got the point now.It is the message given by oracle that is confusing newbies like me.
BTW,i just learned that sqlplus utility can also be used by clients for logging into DB.And then,another query came to me that if the client enters sqlplus /nolog,then the sqlplus prompt will come directly.At this point,since still NOT logged on to any DB,client can issue 'Shutdown' command to stop the instance.
How does the orcle take care of this??
I know shutdown command can only be issued in sysdba role,but the client has not logged into any DB..
Re: connected to Idle Instance [message #409547 is a reply to message #409516] Tue, 23 June 2009 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 23 08:36:09 2009

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

SQL> shutdown
ORA-01012: not logged on
SQL>

Does this answer the question?

Regards
Michel
Re: connected to Idle Instance [message #409595 is a reply to message #409547] Tue, 23 June 2009 04:20 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Michel Cadot wrote on Tue, 23 June 2009 01:36
C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 23 08:36:09 2009

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

SQL> shutdown
ORA-01012: not logged on
SQL>

Does this answer the question?

Regards
Michel


Yes,it answered my ques..But,why do we not get the same message on using 'Startup' command?
Re: connected to Idle Instance [message #409609 is a reply to message #409595] Tue, 23 June 2009 05:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 23 12:27:35 2009

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

SQL> startup
ORA-01031: insufficient privileges
SQL>

Quote:
why do we not get the same message on using 'Startup' command?

Michel Cadot
If you don't like the message, ask Oracle to change it.

Regards
Michel
Re: connected to Idle Instance [message #409615 is a reply to message #409609] Tue, 23 June 2009 05:41 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
then,what is the significance of /Nolog switch??
If,either way,username and password has to be entered,then why use /nolog switch..I learned this switch is most desirable by DBAs..
Re: connected to Idle Instance [message #409617 is a reply to message #409615] Tue, 23 June 2009 05:43 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Because if you will connect sqlplus by giving password as a parameter, any user gettin history of your command, can get your username and password easily
By typing sqlplus /nolog you will jump into SQL> and by giving password in sqlplus prompt, no one can get your password by getting history of your commands from OS
Re: connected to Idle Instance [message #409624 is a reply to message #409617] Tue, 23 June 2009 06:19 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Kamran Agayev wrote on Tue, 23 June 2009 05:43
Because if you will connect sqlplus by giving password as a parameter, any user gettin history of your command, can get your username and password easily
By typing sqlplus /nolog you will jump into SQL> and by giving password in sqlplus prompt, no one can get your password by getting history of your commands from OS

Shocked Shocked
That was really very informative.However,i suppose it is only applicable for *nux systems and not for windows?
Besides,can you tell me the significance of ORACLE_SID env variable,i have searched a lot but did not got the answer.All i got was this variable must be same as DB_NAME parameter in Parameter file..
Re: connected to Idle Instance [message #409626 is a reply to message #409624] Tue, 23 June 2009 06:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Besides,can you tell me the significance of ORACLE_SID env variable,i have searched a lot but did not got the answer

Then you didn't read any of the 2 links I gave you.

Regards
Michel
Re: connected to Idle Instance [message #409627 is a reply to message #409624] Tue, 23 June 2009 06:25 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

You use ORACLE_SID to tell sqlplus and other database utilities that your database name is what you've specified in that variable

For example, if you've assigned any value to ORACLE_SID and issue startup command in sqlplus, Oracle firstly will look for ORACLE_SID environment variable and try to find the parameter file with that environment variable's name

Or if you have two databases and you want to start and run each time on of them, then by changing ORACLE_SID parameter, you can switch between two databases and use sqlplus to connect to both of them
Re: connected to Idle Instance [message #409645 is a reply to message #409627] Tue, 23 June 2009 07:09 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Kamran Agayev wrote on Tue, 23 June 2009 06:25

For example, if you've assigned any value to ORACLE_SID and issue startup command in sqlplus, Oracle firstly will look for ORACLE_SID environment variable and try to find the parameter file with that environment variable's name

What if this ORACLE_SID env variable is not there(or is not set)
Re: connected to Idle Instance [message #409649 is a reply to message #409627] Tue, 23 June 2009 07:16 Go to previous messageGo to next message
satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India
Member
Kamran Agayev wrote on Tue, 23 June 2009 06:25
You use ORACLE_SID to tell sqlplus and other database utilities that your database name is what you've specified in that variable

This means that this varaible is being used at the time of executing 'connect' command.If i type: CONNECT,i get a prompt for username and pwd,but not for database.The database name will then come from this variable??In other words,the value of this variable is the DEFAULT DATABASE??
right or wrong?
Re: connected to Idle Instance [message #409651 is a reply to message #409645] Tue, 23 June 2009 07:18 Go to previous messageGo to previous message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

You can try it by yourself. You'll not be able to connect to default database. You will have to startup database usint pfile= option

In other words, startup command will not work for you. You'll have to use
startup pfile='your_pfile.ora';
Previous Topic: ora 01861
Next Topic: create database service in linux by copy database files from windows
Goto Forum:
  


Current Time: Sun Dec 04 16:24:14 CST 2016

Total time taken to generate the page: 0.08242 seconds