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_HOME
s. I don't know if this has something to do with this problem?
How to resolve this error on my Windows machine?
Best Answer
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.