How to resolve ORA-01031: insufficient privileges error on Windows installation?
I installed Oracle 11G on my Windows 2008 R2 Server. I also installed Oracle Client Libraries using separate installation media. After the client installation, when I try to log into the database using:
C:\>sqlplus / as sysdba
I get a following error:
ORA-01031: insufficient privileges
This worked before the client installation. My account is on the
ORA_DBAgroup. My account is also in the Administrator group. I don't have
ORACLE_SIDset in my environment variables list. Does it have to be? This worked before the client installation, and I did not have that variable at that time either.
UAC is on at the lowest level, and I always run
I have an
sqlnet.orafile like this in the folder:
# sqlnet.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\sqlnet.ora # Generated by Oracle configuration tools. # 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) NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
# tnsnames.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\tnsnames.ora # Generated by Oracle configuration tools. LISTENER_ORCL = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.mydb.com) ) )
# listener.ora Network Configuration File: C:\app\myaccount\product\11.2.0\dbhome_1\network\admin\listener.ora # Generated by Oracle configuration tools. SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = CLRExtProc) (ORACLE_HOME = C:\app\myaccount\product\11.2.0\dbhome_1) (PROGRAM = extproc) (ENVS = "EXTPROC_DLLS=ONLY:C:\app\myaccount\product\11.2.0\dbhome_1\bin\oraclr11.dll") ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) ) ADR_BASE_LISTENER = C:\app\myaccount
From the windows registry (using regedit), from the path
Computer > HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ORACLEI can find two keys:
And under those keys I can find
ORACLE_HOMEvariables. So it seem that the client installation generated also
ORACLE_HOMEso that I have now two
ORACLE_HOMEs. I don't know if this has something to do with this problem?
How to resolve this error on my Windows machine?
The problem is that your Oracle Client installation is in the system PATH before your Oracle Server installation (in fact, you even mention that it worked before you installed the client).
Proceed as follows:
Remove the Oracle Client as it's only confusing things. Use the uninstall option but afterwards check that the directory and the Oracle Client registry key is gone and remove by hand if necessary.
(It's possible to get this working by having the Oracle client home in the path after the server home, but it's much simpler to just have the single install. An Oracle server installation already contains a complete Oracle client anyway).
Make sure that the path to the server's home is in your PATH. And no other Oracle home is in the path.
Make sure that your account is in the ORA_DBA group. Double check this, especially if you are using domains.
If all else fails, you could remove
SQLNET.AUTHENTICATION_SERVICES=(NTS)from your sqlnet.ora as this will force you to use password authentication.
I have both 10g and 11g installed on my laptop, plus OBIEE client most recently. I used to be able to login as sysdb to 11g instance, but got the following this morning:
SQL> conn / as sysdba ERROR: ORA-01031: insufficient privileges
I resolved the problem by doing the following:
- startup db & listener;
- make sure tnsname works by
tnsping orcl; then
sqlplus sys/"passwd"@orcl as sysdba
Hope this helps.