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.
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 184.108.40.206
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 220.127.116.11
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
- 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
D:\Oracle\app\product\11.2.0\dbhome_1\BIN>lsnrctl status LSNRCTL for 64-bit Windows: Version 18.104.22.168.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 22.214.171.124.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
SNCF<DBNAME>.ORA (18MB) SPFILE<DBNAME>.ORA (4kb)
They both appear to be binary files in Notepad.
select * from v$pwfile_users returns no rows.