Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

ORA-01031: insufficient privileges While trying / as sysdba

534640Apr 9 2008 — edited Aug 13 2013

Hi All,
We have oracle database 10.2.0.3 on Linux x86 32 bit Server.

We are trying to connect our database with sysdba and getting [ORA-01031: insufficient privileges ] error.

I have recreated the password file:
orapwd file=orapwdPRODT password=mypassword

But still we are unable to login
-------------------------------------------------------------------------
sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 9 12:22:27 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter password:
ERROR:
ORA-01031: insufficient privileges
-----------------------------------------------------------------------
[oracle@dev1 PRODT_dev1]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Apr 9 12:33:30 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges
-----------------------------------------------------------------------
Please note that we are able to login if we supply exact password which is in password file. But / as sysdba command is giving above errors.

My sqlnet.ora file is having entries:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

I have also tried by removing this line but same error exist.

MY initPRODT.ora file is having this entry:

remote_login_passwordfile='EXCLUSIVE'

I have searched through metalink and google and normally this kind of errors resolved by recreating the password file. I have tried these solutions but unable to resolve this issue.

I have also followed Note:435947.1 and tried to relink the config files but again same problem exist.

Can any one help me in this regard???

Maran Viswarayar

Are you a member of DBA group in Linux

Insufficient Privs

534640

Well about OS details:

[oracle@dev1]$ id oracle
uid=500(oracle) gid=500(dba) groups=500(dba),501(oinstall)

[oracle@dev1]$ grep oracle /etc/passwd
oracle:x:500:500::/home/oracle:/bin/bash

[oracle@dev1]$ grep dba /etc/group
dba:x:500:

Maran Viswarayar

Set the ORACLE_SID and ORACLE_HOME values first before using OS Authentication

Are you having multiplie DBs /Multiple Oracle Homes?

534640

We have two different homes. one for current Oracle database 10gR2 and other one is old 9i database from which we have upgraded the database. (9i to 10g).

We have properly set the environment for ORACLE_SID and ORACLE_HOME in bash_profile. even I have tried .oraenv before trying to connect.

Note: Sysdba logins were possible after the upgrade and we have applied the patch (5985992-upgraded version of autoconfig) day before and this problem started occuring.
Thats why we relink the oracle and its libraries according to metalink notes.

Right now env command is also showing the correct path of current database.

Maran Viswarayar

echo $ORACLE_SID
echo $ORACLE_HOME

534640

Maran As I mentioned in previous thread I have confirmed these parameters:

[oracle@dev1 or10g]$ echo $ORACLE_SID
PRODT
[oracle@dev1 or10g]$ echo $ORACLE_HOME
/dev1_vis/or10g

You can see that SID and HOME both are properly set.
Even when we are trying sqlplus utility its showing version 10.2.0.3 which is confirmation that its using New oracle 10g Home.

Maran Viswarayar

Last Option

Try TNSPING DBNAME

check which sqlnet.ora it uses...

534640

[oracle@dev1 ~]$ tnsping PRODT

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 09-APR-2008 14:44:19

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
/dev1_vis/or10g/network/admin/PRODT_dev1/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=DEV1.ubl.com.pk)(PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=PRODT) (INSTANCE_NAME=PRODT)))
OK (10 msec)

628871

Hi Khalid,

I hope the problem is with the password file,
Please create the file in the following format.

orapwd file=orapwPRODT password=mypassword entries=5.

just check it out and let me know. Correct me if am wrong.

Maran Viswarayar

One more thing where is the password file created?

/dbs or database it should be under /dbs

534640

Friends,
Since we are on linux so the password file is created at default /dbs location.

As I had mentioned above we have tried recreating the password file again and again but unable to login. We have tried all possible cases like password file,sqlnet.ora file changes,relink oracle libraries, rechecking of os user and groups etc.

Any other idea folks..............

628871

Hi khalid,

I faced the same problem.

just give a try with the password file.

be specific with file name.

orapwd file=orapwPRODT password=mypassword entries=5.

make sure your file name is orapw(dbname).. hope it will work. try it and let me know if am wrong.

Message was edited by:
user625868

Sabdar Syed

>> I have recreated the password file:
orapwd file=orapwdPRODT password=mypassword

Yes, as already told.

Create password file again with as follow, because in your command there was a d in file=orapwdPRODT

orapwd file=orapwPRODT password=mypassword

Regards,
Sabdar Syed.

As I just tested on my test server,
With orapwd<SID> file, then I'm getting error.

ORA-01990: error opening password file '/oracle/ora92/dbs/orapw'
ORA-27037: unable to obtain file status

When I try with, orapw<SID>.
It's connecting fine.

Message was edited by:
Sabdar Syed

534640

Syed,
Sorry for the typing mistake its orapwSID file
-----------------------------------------------------------------------
[oracle@dev1 dbs]$ ls -ltr orapw*
-rw-r----- 1 oracle dba 1536 Apr 9 10:56 orapwPRODT

Password file is correct I think because we can connect with sys user when we supply the password.
I have tried one more thing that (after login as sysdba after supplying passwrd) I changed the password of sys and then disconnected and relogin with new password and it was working. This shows that we have valid password files.

537688

Is there any solution given to Sabdar's problem ? ?

I am facing similar issue as Sabdar, and tried all the possible ways, but conn / as sysdba still gives ORA-01031

730296

Try to see this line, SQLNET.AUTHENTICATION_SERVICES = (NTS), in sqlnet.ora file if exists. If yes, comment that and try to connect using / as sysdba. Post here if it´s work.

1 person found this helpful

user4108388

I was in the same boat and commenting out the SQLNET.AUTHENTICATION_SERVICES= (NTS) line in the sqlnet.ora file allow me to log into sqlplus with

SQL> connect / as sysdba
Connected.
SQL>

Matt - novatnym@ctc.com

745928

Hello,

export ORACLE_SID=duprdb

sqlplus "/ as sysdba"

alter system register;

i am sure you will be able now to conn as sqlplus sys/***@duprdb as sysdba

Please respond if successful even if it is not, i would try to help, I already simulated it and was successful with it !!

Lalit Verma
http://sites.google.com/site/racinsights1

1 person found this helpful

745928

Hello,

This is for Windows, but in unix you do not have NTS authentication

Lalit Verma
http://sites.google.com/site/racinsights1

KeshavM

could you check by which user oracle installations are done?

Anil Malkai

if you are the owner of the oracle software and you are in DBA group then

try creating your password file this way.it should work.

orapwd file=orapw password=mypassword

dont give SID or any extention to your password file.

Anil Malkai

1 person found this helpful

marcusafs

Fkhalid is right in telling you to include the entries=5 option in the create password file.

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 multiple of four.

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

Edited by: Marcus AFS on Feb 23, 2010 9:36 AM

J_K

syed

you are genius.

i had same problem.

i tried everything but didnt get through

select * from v$pwfile_users. didnt show me any rows

then i created password file according your suggested way.

and it works

thanks lot dear

875642

If password file is ok and the group is correct for the oracle account, you might also want to check file $ORACLE_HOME/rdbms/lib/config.c
It should contain the oracle database group.
Running ./runInstaller on the copied oracle home can update this file and possibly remove this entry.
If so, edit config.c, add the oracle database group, eg oiinstall , and run make on oracle or relink all. Then sqlplus / as sysdba should work.

Edited by: George Rose on Jul 14, 2011 12:41 PM

1 person found this helpful

1032816

look at this article

http://aprakash.wordpress.com/2010/06/01/sys-as-sysdba-insufficient-privileges/#comment-1240

regards

sb92075

look at start date of thread & date of last response before resurrecting ancient zombie thread which should remain dead & undisturbed.

1 person found this helpful

1 - 26

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 10 2013

Added on Apr 9 2008

#database, #dba, #general-database-discussions, #installation

26 comments

265,421 views

Oracle Database Discussions

ORA-01031: insufficient privileges While trying / as sysdba

Comments

Post Details