Home » RDBMS Server » Server Administration » Unable to connect as Sys User
Unable to connect as Sys User [message #202807] Sun, 12 November 2006 22:36 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,

I am unable to connect as sys user to my test database
but connection is made perfectly by other users like scott,hr,NCL

I want to reset the password for sys by recreating the password file
Perhaps first i should shutdown the database
delete the password file and recreate the password file by using
orapwd utility

Can u please confirm should i follow these steps?
if u have better options then can u please send it to me?

thanx in advance

Report message to a moderator

Re: Unable to connect as Sys User [message #202815 is a reply to message #202807] Sun, 12 November 2006 23:34 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
hi
what exact error you get.

anyway try below procedure.

1. SQL> conn / as sysdba

but sure in sqlnet.ora ( SQLNET.AUTHENTICATION_SERVICES= (NTS)) values is "NTS". not "NONE".

if above method is successed then change ur sys password.

sql>alter user sys identified by pwd;


and if you not connect above method then post exact error you got.

HOPE THIS HELPS
MOHAMMAD TAJ.

[Updated on: Mon, 13 November 2006 00:08]

Report message to a moderator

Re: Unable to connect as Sys User [message #202819 is a reply to message #202815] Sun, 12 November 2006 23:46 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Exact error which i faced is as under
SQL>conn sys/gateway@testdb as sysdba

ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Report message to a moderator

Re: Unable to connect as Sys User [message #202822 is a reply to message #202819] Mon, 13 November 2006 00:02 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
hi
SQL> conn sys/admin@orcl1 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


are you sure ur provide right password.

SQL> conn sys/oracle@orcl1 as sysdba
Connected.
SQL>


and you'r try this one
1. cmd>set oracle_sid=db_name
2. cmd>sqlplus/nolog
3. SQL> conn / as sysdba


hope this helps
Mohammad Taj.

[Updated on: Mon, 13 November 2006 00:09]

Report message to a moderator

Re: Unable to connect as Sys User [message #202832 is a reply to message #202822] Mon, 13 November 2006 00:29 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
i have tried this
cmd>set oracle_sid=db_name
cmd>sqlplus/nolog
SQL> conn / as sysdba

and got the error
ERROR:
ORA-01031: insufficient privileges

My query was aboutu to reset the sysdba password by recreating the password file
and i have i asked you that what steps should i adopt to recreate the password file by orapwd utility

can u help me in this regard?
thanx again in advance

Report message to a moderator

Re: Unable to connect as Sys User [message #202843 is a reply to message #202832] Mon, 13 November 2006 01:02 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
hi shakeel

1.SQL>shutdown immediate;

first del ur password file and create new one.
2.C:\>del D:\oracle\product\10.1.0\db_2\database\pwdorcl.ora

3.C:\>orapwd file=D:\oracle\product\10.1.0\db_2\database\pwdorcl.ora password=orac
le entries=5 force=y

4.C:\>sqlplus/nolog
5.SQL>conn sys/pwd@orcl as sysdba


also go through
http://www.oracleutilities.com/OSUtil/orapwd.html

hope this helps.
Mohammad Taj.

Report message to a moderator

Re: Unable to connect as Sys User [message #202857 is a reply to message #202843] Mon, 13 November 2006 01:31 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
One more question
I have two other users like CYBER_NCL,BACKPROCESS granted the DBA Role
By recreating the password file will also reset the password for
above two users Cyber_NCL and Backprocess

I hope u won't mind it and guide me as early as possible
thanx in advance again

Report message to a moderator

Re: Unable to connect as Sys User [message #202861 is a reply to message #202857] Mon, 13 November 2006 01:47 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
hi shakeel

I have two other users like CYBER_NCL,BACKPROCESS granted the DBA Role
By recreating the password file will also reset the password for
above two users Cyber_NCL and Backprocess

No.

Just go through below point. it will help you understand about password file.

PASSWORD
This parameter sets the password for user SYS. If you issue the ALTER USER statement to change the password for SYS after connecting to the database, both the password stored in the data dictionary and the password stored in the password file are updated. This parameter is mandatory.


Note:

You cannot change the password for SYS if REMOTE_LOGIN_PASSWORDFILE is set to SHARED. An error message is issued if you attempt to do so.
*****************


ENTRIES
This parameter specifies the number of entries that you require the password file to accept. This number corresponds to the number of distinct users allowed to connect to the database as SYSDBA or SYSOPER. The actual number of allowable entries can be higher than the number of users, because the ORAPWD utility continues to assign password entries until an operating system block is filled. For example, if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

Entries can be reused as users are added to and removed from the password file. If you intend to specify REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE, and to allow the granting of SYSDBA and SYSOPER privileges to users, this parameter is required.

Caution:

When you exceed the allocated number of password entries, you must create a new password file. To avoid this necessity, allocate a number of entries that is larger than you think you will ever need.
************************

Adding Users to a Password File

When you grant SYSDBA or SYSOPER privileges to a user, that user's name and privilege information are added to the password file. If the server does not have an EXCLUSIVE password file (that is, if the initialization parameter REMOTE_LOGIN_PASSWORDFILE is NONE or SHARED, or the password file is missing), Oracle Database issues an error if you attempt to grant these privileges.

A user's name remains in the password file only as long as that user has at least one of these two privileges. If you revoke both of these privileges, Oracle Database removes the user from the password file.

****************

Setting REMOTE_LOGIN_ PASSWORDFILE
SHARED: A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified. This means that you cannot add users to a SHARED password file. Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error. All users needing SYSDBA or SYSOPER system privileges must be added to the password file when REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE. After all users are added, you can change REMOTE_LOGIN_PASSWORDFILE to SHARED, and then share the file.

This option is useful if you are administering multiple databases or a RAC database.

If REMOTE_LOGIN_PASSWORDFILE is set to EXCLUSIVE or SHARED and the password file is missing, this is equivalent to setting REMOTE_LOGIN_PASSWORDFILE to NONE.

hope this helps.
Mohammad Taj.

[Updated on: Mon, 13 November 2006 01:47]

Report message to a moderator

Re: Unable to connect as Sys User [message #203105 is a reply to message #202843] Mon, 13 November 2006 22:29 Go to previous messageGo to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Thanx for ur prompt response
i have a little confusion about the parameter force=y
i think it will forcfully create the password file if password file is still existing
am i right Sir?

Report message to a moderator

Re: Unable to connect as Sys User [message #203106 is a reply to message #203105] Mon, 13 November 2006 22:30 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
hi shakeel.

yes you are 100% right.

hope this helps.
Mohammad Taj.

Report message to a moderator

Re: Unable to connect as Sys User [message #203137 is a reply to message #202807] Tue, 14 November 2006 01:04 Go to previous message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Asalamu Alikum Mr Taj

>>>>>>>>>> if your operating system block size is 512 bytes, it holds four password entries. The number of password entries allocated is always a multiple of four.

I geo a bit confused with the above comments.
if you don't mind could you please elaborate it a bit...


Anticipating your response
thank you!

Report message to a moderator

Previous Topic: Users unable to connect to the database after rebooting the server
Next Topic: how to aletr an '_' parameter
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 06:26:10 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.