Home » RDBMS Server » Server Administration » ORA-01031: insufficient privileges (11g, 11.2.0.1, window7)
ORA-01031: insufficient privileges [message #626975] Thu, 06 November 2014 01:13 Go to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

While logging through sqlplus / as sysdba, getting below error -

C:\Users\3130738>set oracle_sid=ashtest

C:\Users\3130738>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 6 12:39:03 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ashtest>  select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE

SYS@ashtest> show parameter remote_login

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SYS@ashtest>



SQLNET.AUTHENTICATION_SERVICES=(NONE). Please assist me to resolve this problem.

Regards,
Ashish
Re: ORA-01031: insufficient privileges [message #626978 is a reply to message #626975] Thu, 06 November 2014 01:30 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
This, SQLNET.AUTHENTICATION_SERVICES=(NONE) disables operating system authentication. So you can't login without a password. It isn't an error, this is by design.

[Updated on: Thu, 06 November 2014 01:31]

Report message to a moderator

Re: ORA-01031: insufficient privileges [message #626980 is a reply to message #626978] Thu, 06 November 2014 02:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... So the solution is to set it to:
SQLNET.AUTHENTICATION_SERVICES=(NT)

Re: ORA-01031: insufficient privileges [message #626981 is a reply to message #626980] Thu, 06 November 2014 02:54 Go to previous messageGo to next message
gazzag
Messages: 985
Registered: November 2010
Location: Bristol, UK
Senior Member
I would use:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

HTH
-g
Re: ORA-01031: insufficient privileges [message #626983 is a reply to message #626981] Thu, 06 November 2014 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, thanks to correct my typo.

Re: ORA-01031: insufficient privileges [message #626993 is a reply to message #626983] Thu, 06 November 2014 06:13 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

I tried it with SQLNET.AUTHENTICATION_SERVICES = (NTS) but getting same error.
Re: ORA-01031: insufficient privileges [message #626995 is a reply to message #626993] Thu, 06 November 2014 06:30 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It works for me.
As you don't show what you do and get we can't accurately help.
Most likely you modified a sqlnet.ora you don't use or you make a typo or the line is commented or...

Re: ORA-01031: insufficient privileges [message #626996 is a reply to message #626993] Thu, 06 November 2014 06:32 Go to previous messageGo to next message
gazzag
Messages: 985
Registered: November 2010
Location: Bristol, UK
Senior Member
Post your sqlnet.ora file please.
Re: ORA-01031: insufficient privileges [message #627060 is a reply to message #626996] Thu, 06 November 2014 23:12 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

Please find the sqlnet.ora file.
  • Attachment: sqlnet.ora
    (Size: 0.26KB, Downloaded 129 times)
Re: ORA-01031: insufficient privileges [message #627061 is a reply to message #627060] Thu, 06 November 2014 23:15 Go to previous messageGo to next message
BlackSwan
Messages: 26193
Registered: January 2009
Location: SoCal
Senior Member
is COPY & PASTE broken for you?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/
Re: ORA-01031: insufficient privileges [message #627062 is a reply to message #627061] Thu, 06 November 2014 23:33 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear BlackSwan,

Attachment facility is also provided in orafaq. That's why i used it.

Thank you very much for highlighting such things instead of assisting me.
Please find the copy and paste answer --

# This file is actually generated by netca. But if customers choose to 
# install "Software Only", this file wont exist and without the native 
# authentication, they will not be able to connect to the database on NT.

 SQLNET.AUTHENTICATION_SERVICES = (NTS)


Re: ORA-01031: insufficient privileges [message #627063 is a reply to message #627062] Fri, 07 November 2014 00:19 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear All,

My listener is throwing below error-

C:\Users\3130738>lsnrctl

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-NOV-2014 11:29:40

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.39.21)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.39.21)(PORT=1521)))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   64-bit Windows Error: 61: Unknown error
LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is E:\app\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to e:\app\diag\tnslsnr\ER02DSK40615002\listener\alert\log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.39.21)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
 TNS-00583: Valid node checking: unable to parse configuration parameters


Listener failed to start. See the error message(s) above...



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

e:\app\diag\tnslsnr\ER02DSK40615002\listener\alert>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 7 11:38:17 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@ashtest> sho parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string
log_archive_local_first              boolean     TRUE
parallel_force_local                 boolean     FALSE
SYS@ashtest> alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.39.21)(PORT = 1521))'
 scope=both;

System altered.

SYS@ashtest> alter system register;

System altered.

SYS@ashtest> conn system/oracle
Connected.
SYSTEM@ashtest> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

e:\app\diag\tnslsnr\ER02DSK40615002\listener\alert>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 7 11:39:39 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:


Below are the messages in listener log -

Toggle Spoiler


Please assist me to find out the root cause of it.

Regards,
Ashish

[Updated on: Fri, 07 November 2014 00:51] by Moderator

Report message to a moderator

Re: ORA-01031: insufficient privileges [message #627066 is a reply to message #627063] Fri, 07 November 2014 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ You don't need any listener to connect a local database
2/ Your user is not in the ORA_DBA OS group

Re: ORA-01031: insufficient privileges [message #627067 is a reply to message #627066] Fri, 07 November 2014 01:12 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

1) You are correct. Listener doesn't require as I am connecting database directly.
2) my user 3130738 is in ora_dba group (computer management>> local Users and Groups >>Groups >> ora_dba).

Please let me know where to check more?

Regards,
Ashish
Re: ORA-01031: insufficient privileges [message #627069 is a reply to message #627067] Fri, 07 November 2014 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post in a DOS box:
set
net user %USERNAME%
sqlplus / as sysdba

Re: ORA-01031: insufficient privileges [message #627077 is a reply to message #627069] Fri, 07 November 2014 02:50 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

Please find the details -

e:\app\diag\tnslsnr\ER02DSK40615002\listener\alert>set
ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\3130738\AppData\Roaming
CommonProgramFiles=C:\Program Files\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
CommonProgramW6432=C:\Program Files\Common Files
COMPUTERNAME=ER02DSK40615002
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\3130738
LOCALAPPDATA=C:\Users\3130738\AppData\Local
LOGONSERVER=\\ER01SRVADCAMAC
NUMBER_OF_PROCESSORS=4
oracle_sid=ashtest
OS=Windows_NT
Path=E:\app\product\11.2.0\dbhome_1\bin;C:\Program Files (x86)\Common Files\NetSarang;C:\Program Files\Common
Files\Microsoft Shared\Windows Live;C:\Program Files (x86)\Common Files\Microsoft Shared\Windows Live;D:\oracl
e\product\10.2.0\client_2\bin;C:\Program Files (x86)\Intel\iCLS Client\;C:\Program Files\Intel\iCLS Client\;C:
\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Program F
iles\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files\Intel\Intel(R) Management Engine Compone
nts\IPT;C:\Program Files (x86)\Intel\Intel(R) Management Engine Components\DAL;C:\Program Files (x86)\Intel\In
tel(R) Management Engine Components\IPT;C:\Program Files (x86)\Intel\OpenCL SDK\3.0\bin\x86;C:\Program Files (
x86)\Intel\OpenCL SDK\3.0\bin\x64;C:\Program Files (x86)\Windows Live\Shared
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 58 Stepping 9, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=3a09
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files
ProgramFiles(x86)=C:\Program Files (x86)
ProgramW6432=C:\Program Files
PROMPT=$P$G
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
SESSIONNAME=Console
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\3130738\AppData\Local\Temp
TMP=C:\Users\3130738\AppData\Local\Temp
USERDNSDOMAIN=CMCINDIA.COM
USERDOMAIN=CMCINDIA
USERNAME=3130738
USERPROFILE=C:\Users\3130738
windir=C:\Windows
windows_tracing_flags=3
windows_tracing_logfile=C:\BVTBin\Tests\installpackage\csilogfile.log

e:\app\diag\tnslsnr\ER02DSK40615002\listener\alert>net user %USERNAME%
The user name could not be found.

More help is available by typing NET HELPMSG 2221.


e:\app\diag\tnslsnr\ER02DSK40615002\listener\alert>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 7 14:17:16 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:



Regards,
Ashish
Re: ORA-01031: insufficient privileges [message #627078 is a reply to message #627077] Fri, 07 November 2014 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
e:\app\diag\tnslsnr\ER02DSK40615002\listener\alert>net user %USERNAME%
The user name could not be found.


This is suspicious, is your user a local or a domain user?

Re: ORA-01031: insufficient privileges [message #627083 is a reply to message #627078] Fri, 07 November 2014 03:05 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear Michel,

It's domain user only. No local user. Do I need to add user(3130738) on the ora_dba group?
Re: ORA-01031: insufficient privileges [message #627086 is a reply to message #627083] Fri, 07 November 2014 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know if it will work; I think you already know how to add it Wink
Quote:
computer management>> local Users and Groups >>Groups >> ora_dba

Then click on "Add"

Re: ORA-01031: insufficient privileges [message #627087 is a reply to message #627083] Fri, 07 November 2014 03:11 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
I do not think it is possible to use OS authentication if you are a domain user and the instance is running under a local user: a technology limitation. You can try, of course.
Re: ORA-01031: insufficient privileges [message #627100 is a reply to message #627087] Fri, 07 November 2014 03:47 Go to previous messageGo to next message
ashishkumarmahanta80
Messages: 231
Registered: October 2006
Location: kolkatta
Senior Member
Dear John,

You are correct. It's not allowed.

Even when i tried to delete the database though dbca,it gives the same error - ORA-01031: insufficient privileges. When pressed on OK button, another message pop-up

connection to the database failed, verify the user name and password and try again.


Waiting for your valuable inputs -

regards,
Ashish
Re: ORA-01031: insufficient privileges [message #627126 is a reply to message #627100] Fri, 07 November 2014 09:55 Go to previous message
Michel Cadot
Messages: 65844
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As we said, you have to create a local account.

Previous Topic: license of oracle 11g
Next Topic: Release storage back to Unix OS from Oracle 11g Tablespace
Goto Forum:
  


Current Time: Wed Oct 17 22:25:05 CDT 2018