How to resolve ORA-01031: insufficient privileges error on Windows installation?

Question

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_DBA group. My account is also in the Administrator group. I don't have ORACLE_SID set 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 cmd as admin.

I have an sqlnet.ora file like this in the folder:

C:\app\myaccount\product\11.2.0\dbhome_1\NETWORK\ADMIN

sqlnet.ora

# 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

# 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

# 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 > ORACLE I can find two keys:

KEY_OraClient11g_home1
KEY_OraDb11g_home1

And under those keys I can find ORACLE_HOME variables. So it seem that the client installation generated also ORACLE_HOME so 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?

Answer 1

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:

1. 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).

2. Make sure that the path to the server's home is in your PATH. And no other Oracle home is in the path.

3. Make sure that your account is in the ORA_DBA group. Double check this, especially if you are using domains.

4. If all else fails, you could remove SQLNET.AUTHENTICATION_SERVICES=(NTS) from your sqlnet.ora as this will force you to use password authentication.

Answer 2

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:

1. startup db & listener;
2. make sure tnsname works by tnsping orcl; then
3. set ORACLE_SID=orcl
4. sqlplus sys/"passwd"@orcl as sysdba

Hope this helps.