2

(This post has a similar title to another post but the underlying cause and solution are totally different.)

Problem:

I can't login to sqlplus as sysdba but the listener seems to start

[John@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 13-AUG-2015 18:19:03

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /home/John/app/John/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /home/John/app/John/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /home/John/app/John/diag/tnslsnr/localhost/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                13-AUG-2015 18:19:03
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /home/John/app/John/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /home/John/app/John/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

The listener seems to start but TNS listener rejects the sysdba login.

[John@localhost ~]$  sqlplus -prelim / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 13 18:25:49 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: ^C
[John@localhost ~]$

Possible causes:

I was able to login a few days before this happened. The only thing I can think of that could have caused the issue is that I changed the all files and folders that were owned by John:John to John:Users and then made Users the primary group of John. I don't know why that would matter though. If it does matter it certainly shouldn't.

How would I be get more detailed information as to why / as sysdba is suddenly rejected by the TNS listener? If it is clearly related to the group change then what specific changes need to be made to leave the group setup how it is while correcting the authentication error?

2

This syntax does not use the listener at all:

sqlplus -prelim / as sysdba

But you faced an authentication error, because the OS group based SYSDBA authentication failed. Check the below:

cat $ORACLE_HOME/rdbms/lib/config.c

There you should see a line like this:

#define SS_DBA_GRP "John"

Only those users can use / as sysdba that are members of the OS group defined above. If you installed the database software with the OSDBA group set to John, and changed your group to Users, then you will not be able to use / as sysdba.

If you want to change the OSDBA group to your new group, edit the above file, set the desired value, shutdown everything running from the ORACLE_HOME (database, listener), then run

$ORACLE_HOME/bin/relink all

  • config.c just defines the SS_DBA_GRP as "wheel" for some reason #define SS_DBA_GRP "wheel" #define SS_OPER_GRP "wheel" #define SS_ASM_GRP "" #define SS_BKP_GRP "wheel" #define SS_DGD_GRP "wheel" #define SS_KMT_GRP "wheel" – John Aug 14 '15 at 2:19
  • just a note: before this, I did grepped for any instances in /home/John/ of "=John" and replaced any instances found with "=Users". Some files containing "=John" were shell scripts that were run during install so those were re-run. – John Aug 14 '15 at 2:53
1

"The listener supports no services" says it all.

Is your database running?

sqlplus /nolog as sysdba 

This should tell you if you are connected to an idle instance or if the database is running.

Is the database is running? Are you using dynamic or static registration? Once the database has been registered with the listener you should be able to connect. And when you do:

lsnrctl status

It should list the services that the listener knows about.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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