Home » RDBMS Server » Server Administration » Unable to connect to an idle instance
Unable to connect to an idle instance [message #200331] Mon, 30 October 2006 06:48 Go to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
I have two databses PRACTICE AND TRIAL running on my windows machine and i wanted to connect to an idle instance.

I have the oracle service up for both PRACTICE AND TRIaL database. So I stopped the service, then I edited the SQLNET.ora file to make sqlnet.authentication_services=(NTS). Since I have the databases on my own windows machine, I am a user of the ora_dba group.

Now when I connect to oracle as sysdba and do not provide a password ( since I belong to ora_dba group) , I get an error

SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error

I assume when we want to connect to an idle instance we need not give a service name right!!!

Then why does this error occur . In fact this should actually get me to an IDLE instance right!!! I am not able to understand the reason for such a message being displayed.

Please let me know if iam wrong in my approach.
Re: Unable to connect to an idle instance [message #200335 is a reply to message #200331] Mon, 30 October 2006 06:56 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

i wanted to connect to an idle instance.

For no need to stop oracle services.

You have two database ok.

then first shudtown normal. ur database.

and try this way to connect to database.

cmd>set oracle_sid=<db_name>
cmd>sqlplus/nolog
cmd>conn sys as sysdba


ORA-12560: TNS:protocol adapter error
for avoid above error set oracle_sid variable before connect.

sqlnet.authentication_services=(NTS)
If you set "NTS" then no need to give "sys" password try to connect like

cmd> sqlplus "sys / as sysdba"
or
cmd>sqlplus/nolog
cmd>conn / as sysdba


Note : idle instance means ur database is right now close , nomount stage.

here three step between "idle instance to open"

cmd>sqlplus/nolog
cmd>conn / as sysdba
connected to idle instance
sql>startup nomout;
....
sql>alter database mount;
......
sql>alter database open;

or
sql>startup

...for directory open.

refer doc for more info.
thanx.
Re: Unable to connect to an idle instance [message #200342 is a reply to message #200335] Mon, 30 October 2006 07:18 Go to previous messageGo to next message
asherisfine
Messages: 63
Registered: June 2006
Location: India
Member
Thanks for the reply

I followed the steps you suggested.

1) Restarted my oracle services. Then i shutdown my database PRACTICE.

then i set oracle_sid='Practice'

SQL> conn sys@practice as sys
Enter password:
Connected.
SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.


Again i logged in as sys


SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> conn sys@practice as sysdba
Enter password:
Connected to an idle instance.


but when i try

SQL> disconnect
Disconnected
[B]SQL> conn sys as sysdba[/B]
Enter password:
ERROR:
ORA-12154: TNS:could not resolve service name




same error occurs when i give
SQL> conn sys/ as sysdba
Enter password:
ERROR:
ORA-12154: TNS:could not resolve service name


that is whenever i try to login without servicename i get this error.However if i have specified a service name, it doesn't matter if i don't give password.

[Updated on: Mon, 30 October 2006 07:21]

Report message to a moderator

Re: Unable to connect to an idle instance [message #200344 is a reply to message #200342] Mon, 30 October 2006 07:23 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

SQL> disconnect
Disconnected
SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-12154: TNS:could not resolve service name
Try this

sql>conn sys@practice as sysdba
Enter password:******


or 
first set oracle_sid variable
cmd>set oracle_sid=<practice or trial> whatever.
cmd>sqlplus/nolog
sql>conn / as sysdba



If You still getting some error then post tnsnames.ora file.

found in "oraclehome-->network-->admin" folder.

hope this clear.
Previous Topic: Oracle 9i version for Windows 2003 Server
Next Topic: Table Space
Goto Forum:
  


Current Time: Thu Dec 08 12:48:39 CST 2016

Total time taken to generate the page: 0.07768 seconds