OraFAQ Forum: Server Administration » connected to Idle Instance

Home » RDBMS Server » Server Administration » connected to Idle Instance (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

connected to Idle Instance [message #408932]

Thu, 18 June 2009 06:30

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...

Report message to a moderator

Re: connected to Idle Instance [message #408934 is a reply to message #408932]

Thu, 18 June 2009 06:37

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

Report message to a moderator

Re: connected to Idle Instance [message #408939 is a reply to message #408934]

Thu, 18 June 2009 07:08

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...

Report message to a moderator

Re: connected to Idle Instance [message #408941 is a reply to message #408939]

Thu, 18 June 2009 07:15

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

Report message to a moderator

Re: connected to Idle Instance [message #408943 is a reply to message #408939]

Thu, 18 June 2009 07:18

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: connected to Idle Instance [message #408965 is a reply to message #408943]

Thu, 18 June 2009 08:36

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??

Report message to a moderator

Re: connected to Idle Instance [message #408970 is a reply to message #408965]

Thu, 18 June 2009 09:01

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SGA is not allocated, background "processes" (actually threads in Windows) are not started.

Regards
Michel

Report message to a moderator

Re: connected to Idle Instance [message #409045 is a reply to message #408932]

Thu, 18 June 2009 23:49

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.

Report message to a moderator

Re: connected to Idle Instance [message #409048 is a reply to message #409045]

Fri, 19 June 2009 00:11

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: connected to Idle Instance [message #409076 is a reply to message #409048]

Fri, 19 June 2009 03:56

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?

Report message to a moderator

Re: connected to Idle Instance [message #409104 is a reply to message #409076]

Fri, 19 June 2009 07:18

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: connected to Idle Instance [message #409107 is a reply to message #408932]

Fri, 19 June 2009 07:27

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?

Report message to a moderator

Re: connected to Idle Instance [message #409110 is a reply to message #409107]

Fri, 19 June 2009 07:38

ThomasG
Messages: 3212
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'

Code: [Select all] [Show/ hide]

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>

Report message to a moderator

Re: connected to Idle Instance [message #409115 is a reply to message #409107]

Fri, 19 June 2009 08:11

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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...

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: connected to Idle Instance [message #409116 is a reply to message #409115]

Fri, 19 June 2009 08:28

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

Report message to a moderator

Re: connected to Idle Instance [message #409222 is a reply to message #409116]

Sat, 20 June 2009 13:17

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..

Report message to a moderator

Re: connected to Idle Instance [message #409226 is a reply to message #408932]

Sat, 20 June 2009 14:25

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: connected to Idle Instance [message #409227 is a reply to message #409222]

Sat, 20 June 2009 14:49

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

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..

Report message to a moderator

Re: connected to Idle Instance [message #409229 is a reply to message #408932]

Sat, 20 June 2009 16:34

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: connected to Idle Instance [message #409230 is a reply to message #409229]

Sat, 20 June 2009 16:59

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??

Report message to a moderator

Re: connected to Idle Instance [message #409231 is a reply to message #408932]

Sat, 20 June 2009 17:17

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: connected to Idle Instance [message #409239 is a reply to message #409230]

Sun, 21 June 2009 00:23

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: connected to Idle Instance [message #409384 is a reply to message #409239]

Mon, 22 June 2009 07:18

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?

Report message to a moderator

Re: connected to Idle Instance [message #409386 is a reply to message #409384]

Mon, 22 June 2009 07:26

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: connected to Idle Instance [message #409404 is a reply to message #409386]

Mon, 22 June 2009 07:56

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..

Report message to a moderator

Re: connected to Idle Instance [message #409410 is a reply to message #409404]

Mon, 22 June 2009 08:21

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Smile

Regards
Michel

Report message to a moderator

Re: connected to Idle Instance [message #409411 is a reply to message #409410]

Mon, 22 June 2009 08:24

Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku

Senior Member

He he he

Laughing

Report message to a moderator

Re: connected to Idle Instance [message #409516 is a reply to message #409410]

Tue, 23 June 2009 00:27

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

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..

Report message to a moderator

Re: connected to Idle Instance [message #409547 is a reply to message #409516]

Tue, 23 June 2009 01:36

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: connected to Idle Instance [message #409595 is a reply to message #409547]

Tue, 23 June 2009 04:20

satish_j
Messages: 35
Registered: June 2009
Location: Mumbai,India

Member

Michel Cadot wrote on Tue, 23 June 2009 01:36

Code: [Select all] [Show/ hide]

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?

Report message to a moderator

Re: connected to Idle Instance [message #409609 is a reply to message #409595]

Tue, 23 June 2009 05:28

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: connected to Idle Instance [message #409615 is a reply to message #409609]

Tue, 23 June 2009 05:41

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..

Report message to a moderator

Re: connected to Idle Instance [message #409617 is a reply to message #409615]

Tue, 23 June 2009 05:43

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

Report message to a moderator

Re: connected to Idle Instance [message #409624 is a reply to message #409617]

Tue, 23 June 2009 06:19

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..

Report message to a moderator

Re: connected to Idle Instance [message #409626 is a reply to message #409624]

Tue, 23 June 2009 06:20

Michel Cadot
Messages: 68758
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: connected to Idle Instance [message #409627 is a reply to message #409624]

Tue, 23 June 2009 06:25

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

Report message to a moderator

Re: connected to Idle Instance [message #409645 is a reply to message #409627]

Tue, 23 June 2009 07:09

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)

Report message to a moderator

Re: connected to Idle Instance [message #409649 is a reply to message #409627]

Tue, 23 June 2009 07:16

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?

Report message to a moderator

Re: connected to Idle Instance [message #409651 is a reply to message #409645]

Tue, 23 June 2009 07:18

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';

Report message to a moderator

Pages (2): [1 2 › »]

Previous Topic:	ora 01861
Next Topic:	create database service in linux by copy database files from windows

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 08 02:45:14 CDT 2025