connect as sysdba
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: connect as sysdba

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    257

    connect as sysdba

    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,

  2. #2
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Make sure your OracleSerciveSil is running before connecting...
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  3. #3
    Join Date
    Apr 2001
    Posts
    257
    I am sure the service is running because I can do CONNECT INTERNAL@SIL in SQL*Plus.

  4. #4
    Join Date
    May 2001
    Posts
    736
    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.
    Last edited by akhadar; 04-15-2003 at 01:02 AM.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    Code:
    C:\>set oracle_sid=oemrep
    
    C:\>echo %oracle_sid%
    oemrep
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    May 2001
    Posts
    736
    [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.
    Last edited by akhadar; 04-15-2003 at 04:51 AM.

  7. #7
    Join Date
    Feb 2003
    Location
    London
    Posts
    170
    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???

  8. #8
    Join Date
    May 2001
    Posts
    736
    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--

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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

    Code:
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    May 2001
    Posts
    736
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width