How to connect as sysdba
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to connect as sysdba

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    I created a user anurag and assigned him dba, resource and connect roles.

    Now I want to be able to shutdown the database using this user but am unable to login as sysdba.
    what steps do I need to take so that I should be able to connect as sysdba.

    Infact I am unable to shutdown the database using internal also from the sql plus prompt but am able to do so using internal from the enterprise manager.

    SQL> connect anurag/anurag@orcl8i
    Connected.
    SQL> shutdown immediate;
    ORA-01031: insufficient privileges
    SQL> connect anurag/anurag@orcl8i as sysdba
    ERROR:
    ORA-01031: insufficient privileges


    Warning: You are no longer connected to ORACLE.

    Thanks
    Anurag

    [Edited by anuragmin on 06-12-2001 at 10:19 AM]
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  2. #2
    Join Date
    Apr 2001
    Posts
    107
    Hi,

    You will need to create a password file, then grant sysdba to your user when connected as 'internal'...

    Fabien

  3. #3
    Join Date
    Sep 2000
    Posts
    362
    Originally posted by shadow21
    Hi,

    You will need to create a password file, then grant sysdba to your user when connected as 'internal'...

    Fabien
    How do I create a password file.

    Also I read that internal user is being desupported and is being replaced by sysdba privelege. But again why do we need to use internal to grant priveleges.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  4. #4
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    I logged into sqlplus using the internal user and was able to grant sysdba privelege to the user anurag.

    Now I am a little confused.

    -I never created a password file (I am very sure about this) but still it existed under the oracle_home.

    -why do one has to connect as internal to grant the priveleges if it is desupported.

    -why was internal user desupported and what are the advantages if using sysdba/sysoper over internal user.

    -what should happen if the password file is deleted somehow.

    -Can you have multiple password files or rename the existing ones.

    Please suggest.

    Thanks
    Anurag



    [Edited by anuragmin on 06-12-2001 at 10:56 AM]
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  5. #5
    Join Date
    Sep 2000
    Posts
    362

    Still waiting for a response

    .
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  6. #6
    Join Date
    Apr 2001
    Posts
    107
    Hi,

    I unfortunately can't answer to all your questions, but a few ones:
    I ignored that 'internal' use was to be desupported... are you sure of this ?

    - A password file is created automatically when you use 816 database config assistant, otherwise, if you create your database manually, and want a password file, you must use the command "orapwd" (type orapwd /? under a dos prompt for an explanation on how to use it...)

    - If a password file is deleted, you must create another one, and make the grants again...
    - There should be only one pwd per server, which will be used for any database of this server...

    - You can't just rename the passord file, but if you wish to move it another place, or rename it anyway, you have 2 solutions:
    1- delete the file, create another one (orapwd) and grant your privileged users again.
    2- make a copy of your pwd file, delete it, then create another one with orapwd (new name, new place), and then rename the copy of your 'old' pwd file to the new one.
    WARNING though: under UNIX OS, I was always unable to create a pwd file with name other than the default, and was unable too to locate it another place. I don't know under NT, as we don't use pwd files anyway...

    Hope it helps! (and forgive my late answer..)

    Cheers

    Fabien

  7. #7
    Join Date
    Jun 2001
    Posts
    33
    u can connect internal by using sqlplus /NOLOG


  8. #8
    Join Date
    Mar 2001
    Posts
    635
    Hi Anurag,

    This is how you create a password file on unix or windows NT from prompt

    orapwd file=passwordfile password=urpassword entries=5

    The entries parameter is optional the password parameter is what will be ur internal password.Internal user is not desupported still in Oracle 8i.Whenever you assign sysdba or sysoper privilege to any of the user it is also added to the password file on the server and whenever you change password of a user who has been assigned sysdba or sysoper privilege his password is changed in the password file as well as in the database.But you will not be add more then 5 users if the entries = 5 then you will have to delete the password file and recreate it increasing the no of entries so it is better to plan before you create the password file.I will put the step below how to create a password file and assigne a user sysdba privilege

    Make sure you have deleted the password file before creating a new one.

    1)

    c:\> orapwd file=passwordfile password=urpassword entries=5

    2) Login as sys into the database

    grant sysdba to username;

    Now the above user has sysdba privilege he will have permission to shutdown,startup,recover the database.

    How to connect as sysdba

    1) From prompt

    sqlplus /nolog

    connect username as sysdba;

    Hope this calirifies your doubt.

    If you still have any doubt please let us know

    Regards
    Santosh

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