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

  1. there's some setting that says "don't allow SYSDBA through a network connection", or
  2. 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_passwordfile shows 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.

  • 1
    Post the contents of %ORACLE_HOME%\network\admin\listener.ora and sqlnet.ora, the output of lsnrctl status, the value of the database parameter remote_login_passwordfile, the parameters you use when trying to connect from SQL Developer, and check if you have a file under %ORACLE_HOME%\database called something like PWDmydb.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 a ORA-01017 error, not ORA-01031.) – Balazs Papp Dec 20 '15 at 22:31
  • Yeah, one of the problems here is that this server has 3 Oracle Homes, two of them Oracle 11G (i have no idea why), and I am not sure which one is being used for this database (i wouldn't mind help figuring out directions to figure that out either). In any event i can't just post the config files for security reasons, I will have to clean them first, and of course that will take some time ... – RBarryYoung Dec 21 '15 at 4:37
  • Oh, and how do I find that database parameter? – RBarryYoung Dec 21 '15 at 4:41
  • Go to Windows Services, you should see there 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 called OracleOraDB11gHome1TNSListener or something like that. For the parameter: log in to the database, and execute show parameter remote_login_passwordfile. – Balazs Papp Dec 21 '15 at 13:20
  • @BalazsPapp OK, thanks. I've been out sick the last two days, but I am back now, and I have some of this information. – RBarryYoung Dec 23 '15 at 16:38
up vote 3 down vote accepted

To connect using 'as sysdba' from a client over sql*net you need to have the

  1. remote_login_passwordfile parameter to have 'exclusive'
  2. password file (orapw${ORACLE_SID}) present for the instance where you try to connect to
  3. have the user granted sysdba in the database (sys has this default and uses the passwd file password.

The default location for the password file is $ORACLE_HOME/dbs but since Oracle v12 it can also be stored in ASM. In that case you find it using srvctl

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.