Oracle Database 12c – TNS Permission Denied on CentOS

centoslinuxoracle

(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?

Best Answer

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

Related Question