connect as sysdba

[a128]

If I have two databases on the same server, how do I connect / as sysdba in SQL*Plus?
----------
C:\>set ORACLE_SID=SIL

C:\>SQLPLUS /NOLOG

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Apr 14 20:34:24 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL> CONNECT / AS SYSDBA
ERROR:
ORA-12560: TNSrotocol adapter error
----------

I know I can do CONNECT INTERNAL@SIL just fine but what's the equivalent in CONNECT / AS SYSDBA?

Thanks,

[adewri]

Make sure your OracleSerciveSil is running before connecting...

[a128]

I am sure the service is running because I can do CONNECT INTERNAL@SIL in SQL*Plus.

[akhadar]

If the user is created with external identification then he can't be granted with sysdba privileges.But if the user is created with password then he can connect as sysdba and can use '/'.

SQL> create user ops$abc identified by abc;

User created.

SVRMGR> grant sysdba to ops$abc;
Statement processed.
SVRMGR> connect / as sysdba;
Connected.
SVRMGR> connect /
ORA-01045: user OPS$abc lacks CREATE SESSION privilege; logon denied
SVRMGR> connect internal
Connected.
SVRMGR> grant create session to ops$abc;
Statement processed.
SVRMGR> connect /
Connected.
SVRMGR> connect / as sysdba;
Connected.

one more point is take care that the parameter
SQLNET.AUTHENTICATION_SERVICES= (NTS) in SQLNET.ORA is not commented out or 'NTS' is not replaced with NONE.

[abhaysk]

Originally posted by akhadar
one more point is take care that the parameter
SQLNET.AUTHENTICATION_SERVICES= (NTS) in SQLNET.ORA is not commented out or 'NTS' is not replaced with NONE.

it would have errored with Insufficent Privilages and not TNS Protocol Adapter error.

If I have two databases on the same server, how do I connect / as sysdba in SQL*Plus?
----------
C:\>set ORACLE_SID=SIL

C:\>SQLPLUS /NOLOG

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Apr 14 20:34:24 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL> CONNECT / AS SYSDBA
ERROR:
ORA-12560: TNSrotocol adapter error
----------

Post this...coz some times windows sucks....and will not set ur SID that u wished to.

C:\>set oracle_sid=oemrep

C:\>echo %oracle_sid%
oemrep

[akhadar]

[QUOTE]Originally posted by abhaysk
[B]it would have errored with Insufficent Privilages and not TNS Protocol Adapter error.

My point in SQLNET.AUTHENTICATION_SERVICES= (NTS) is if he comment it out he can't connect with OS authentication as SYSDBA but not what error he is going to encounter.

yes this is a problem with windooooz.Unless he will not use the connecting string of particular database he can't connect to it with sysdba.

[thomasp]

Have you tried doing a
C:\set oracle_sid=TEST

C:\>echo %oracle_sid%
TEST

C:\>sqlplus /nolog

SQL> conn sys/pwd as sysdba
Connected.

See if this works,
then surely conn / as sysdba should work.

And just out of curiosity,
which version of oracle are you using???

[akhadar]

This will work when there is single instance.But will give error when u are trying to set one instance and connecting with OS authentication.IF u want to avoid the error u need to use particular connecting_string.
Try it--

[abhaysk]

Originally posted by akhadar
But will give error when u are trying to set one instance and connecting with OS authentication.Try it--

Not True.

PS

C:\>set oracle_sid=test

C:\>echo %oracle_sid%
test

C:\>sqlplus

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Apr 15 17:10:41 2003

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------------------------------------------
TEST.DOMAIN.NET

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\>sqlplus

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Apr 15 17:11:12 2003

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------------------------------------------------------------
TEST.DOMAIN.NET

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\>set oracle_sid=oemrep

C:\>echo %oracle_sid%
oemrep

C:\>sqlplus

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Apr 15 17:12:53 2003

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> select * from global_name;
select * from global_name
*
ERROR at line 1:
ORA-01034: ORACLE not available -- DB(Oemrep) is shutdown


SQL> exit
Disconnected

C:\>sqlplus

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Apr 15 17:13:17 2003

(c) Copyright 2001 Oracle Corporation.  All rights reserved.

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> select * from global_name;
select * from global_name
*
ERROR at line 1:
ORA-01034: ORACLE not available -- DB(Oemrep) is shutdown


SQL> exit
Disconnected

I used NT (OS) Authentication and also i had 2 instances and no connect string.

Thanks
Abhay.

[akhadar]

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

c:\set oracle_sid = ricd

SQL> conn / as sysdba
ERROR:
ORA-12560: TNSrotocol adapter error


Warning: You are no longer connected to ORACLE.
SQL> conn /@ricd as sysdba
Connected.