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.
Very nice. This helped me!!!!
You saved my day
thanks…glad to hear it. was a very strange issue when we ran into it.