I am a SQL Server developer/DBA consultant who is helping out a customer who recently lost their Oracle DBA. I have done development on Oracle, but not much DBA work, and not in multi-homed environments like this.
The Problem: I can connect using "SYS As SYSDBA" from SQL Plus, but not from SQL Developer on the same system. However, I can connect using "system" with either SQL Plus or SQL Developer.
Details:
On this server, in my windows Admin account (also entered as server administrator in Oracle Admin Asst.), this DOS command works:
D:\Oracle\app\product\11.2.0\dbhome_1\BIN>sqlplus /@MyDb as sysdba
and, Select * from V$INSTANCE shows that it is in MyDb and that both tool and db are 11.2.0.3
So does this one:
D:\Oracle\app\product\11.2.0\dbhome_1\BIN>sqlplus sys/<pwd>@MyDb as sysdba
(ALSO: entering the wrong password works as well(!))
Using SQL-Plus start menu option from same (sever, account and home), these also work:
Enter user-name: /@MyDb as sysdba
Enter user-name: sys/<pwd>@MyDb as sysdba
Select * from V$INSTANCE shows that it is in MyDb and that both tool and db are 11.2.0.3
Attempt to access MyDb from same with SQL Developer, succeeds for "system" username, but fails for "sys" "as sysdba" with “ORA-01031:insufficient privileges”. However, this does work when connecting to other databases on other servers.
I have researched this, and most of the posted help for this seems to be aimed at cases where no connections at all can be made, which is not the case here. Just to head off some of these:
- The problem isn't that the network isn't setup, because SQL Developer works for "system" both locally and remotely.
- The problem isn't that the DB/Instance isn't started, because again, it works for "system"
- It's not that I am connecting to the wrong database, I checked it for "system"
- AFAIK, it's not that I am using the wrong syntax in SQL Developer to connect to a DB "As SYSDBA" because it works when I connect to other servers on other DBs.
My gut feel is that either
- there's some setting that says "don't allow SYSDBA through a network connection", or
- there's some configuration/authorization problem in the Oracle network Listener that is preventing it from checking my windows admin account, and additionally, the sys password is not what I was told it was, and finally Oracle is giving an obtuse error message for that.
Any help or guidance on this would be greatly appreciated.
OK, so using instructions from @BalasPapp, I was able to find more the following:
- This database is serviced by the second Oracle 11g home called OraDb11g_home3 pathed to
D:\Oracle\app\product\11.2.0\dbhome_1D:\Oracle\app\product\11.2.0\dbhome_1. - Windows services showed no startup parameters for it (except the DB name, of course).
- the command
show parameter remote_login_passwordfileshows a value of "EXCLUSIVE".
Contents of Listener.ora:
# listener.ora Network Configuration File: D:\Oracle\app\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 = D:\Oracle\app\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:D:\Oracle\app\product\11.2.0\dbhome_1\bin\oraclr11.dll")
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = <SERVERNAME>.<networkname>.local)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = D:\Oracle\app
Contents of sqlnet.ora:
# 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)
The output of lsnrctl status:
D:\Oracle\app\product\11.2.0\dbhome_1\BIN>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.3.0 - Production on 23-DEC-2015 12:03:55
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<SERVERNAME>.<networkname>.local)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.3.0 - Production
Start Date 02-DEC-2015 16:25:41
Uptime 20 days 19 hr. 38 min. 17 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File D:\Oracle\app\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File D:\Oracle\app\diag\tnslsnr\<SERVERNAME>\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<SERVERNAME>.<networkname>.local)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "<DBNAME>" has 1 instance(s).
Instance "<DBNAME>", status READY, has 1 handler(s) for this service...
The command completed successfully
D:\Oracle\app\product\11.2.0\dbhome_1\BIN>
Parameters when connecting from SQL Developer:
Username: system (or sys)
Password: <...>
Connection
Type: Basic
Role: Default (or SYSDBA)
Hostname: localhost
Port: 1521
SID (or Service name, both work/fail the same):
<dbname>
(Neither OS Authentication nor Kerberos is set)
Works for "system, Default", fails for "sys, SYSDBA" with "Insufficient privileges". I have tried many combinations of OS Authentication and Keberos settings as well, to no avail.
Finally, these are the only two .ora files in the /database directory:
SNCF<DBNAME>.ORA (18MB)
SPFILE<DBNAME>.ORA (4kb)
They both appear to be binary files in Notepad.
ALSO: select * from v$pwfile_users returns no rows.
%ORACLE_HOME%\network\admin\listener.oraandsqlnet.ora, the output oflsnrctl status, the value of the database parameterremote_login_passwordfile, the parameters you use when trying to connect from SQL Developer, and check if you have a file under%ORACLE_HOME%\databasecalled something likePWDmydb.ora. Usually this problem is caused by a misconfigured or missing password file, as remote SYSDBA logins are authenticated against a password file, not the database or OS membership. (However that would result in aORA-01017error, notORA-01031.)OracleServiceMYDB, and in its properties, you can find the executable and Oracle Home used for it. Similarly, for the listener, there should be a service calledOracleOraDB11gHome1TNSListeneror something like that. For the parameter: log in to the database, and executeshow parameter remote_login_passwordfile.