How to connect as sysdba

[anuragmin]

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]

[shadow21]

Hi,

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

Fabien

[anuragmin]

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

[anuragmin]

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]

[anuragmin]

.

[shadow21]

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

[armel]

u can connect internal by using sqlplus /NOLOG

[santoshym]

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