Why I Don’t Like Role Separated Accounts – Part 1

One of the things that I’ve touched on before that tends to bother me – role separated grid infrastructure installations – gave me another reason to show my dislike a few weeks ago.  While working on a system that was being upgraded from 11.2.0.2 to 11.2.0.3, we ran into a strange issue.  After upgrading from 11.2.0.2 to 11.2.0.3, we could no longer connect to our databases.  When we would attempt to connect remotely, we would get:

[acolvin@homer ~]$ sqlplus system@odademo

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 20 11:58:23 2012

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

Enter password:
ERROR:
ORA-12537: TNS:connection closed

We could connect to the database without issue internally. There were no network issues to report, everything appeared to be working fine, except we couldn’t get in to the database.  The database we were connecting to (DEMO) was registered with the listener:

[grid@patty ~]$ lsnrctl stat LISTENER_SCAN1

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-MAY-2012 12:07:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                25-APR-2012 11:50:38
Uptime                    25 days 0 hr. 17 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0.3/grid/network/admin/listener.ora
Listener Log File         /u01/app/11.2.0.3/grid/log/diag/tnslsnr/patty/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=****)(PORT=1521)))
Services Summary...
Service "DEMO" has 2 instance(s).
  Instance "DEMO1", status READY, has 2 handler(s) for this service...
  Instance "DEMO2", status READY, has 2 handler(s) for this service...

[grid@patty ~]$ lsnrctl stat

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 20-MAY-2012 12:05:51

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                07-MAY-2012 15:21:43
Uptime                    12 days 20 hr. 44 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0.3/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/patty/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=****)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=****)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "DEMO" has 1 instance(s).
  Instance "DEMO1", status READY, has 2 handler(s) for this service...

After poking around for a little bit, I came across MOS note #1069517.1, “ORA-12537 if Listener (including SCAN Listener) and Database are Owned by Different OS User.” Hey, that looks familiar! Looking through the listener logs, we saw this error:

20-MAY-2012 12:10:48 * (CONNECT_DATA=(SID=DEMO1)(CID=(PROGRAM=perl@patty)(HOST=patty)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.219)(PORT=24042)) * establish * DEMO1 * 12518
TNS-12518: TNS:listener could not hand off client connection
 TNS-12547: TNS:lost contact
  TNS-12560: TNS:protocol adapter error
   TNS-00517: Lost contact
    Linux Error: 32: Broken pipe

Turns out this is an issue if the permissions on the oracle binary in the database $ORACLE_HOME/bin directory is missing the setuid bit.

[oracle@patty ~]$ ls -al /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwxr-x--x 1 oracle asmadmin 229009338 Jan 19 12:59 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

Once we reset the setuid bit, we were back in business:

[oracle@patty ~]$
chmod 6751 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

[oracle@patty ~]$ ls -al /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle
-rwsr-x--x 1 oracle asmadmin 229009338 Jan 19 12:59 /u01/app/oracle/product/11.2.0/dbhome_1/bin/oracle

While this isn’t something that comes up often, it’s still something that wouldn’t happen under an environment owned entirely by the Oracle account.