Oracle's installed on one server, I'm running sqlplus
on a different (application) server. I've recently encountered a problem getting sqlplus
to do anything; it simply spins, consuming 99% CPU even if asked to print its own help or version information.
sqlplus
has run fine on the same box via cronjob
every day for the last a couple of years. I've also run it directly off the command-line on an ad-hoc basis when needed. The box sqlplus
runs on only contains the oracle client files, it's never had a full DB install.
Last week, the cron jobs stopped working and ps
output showed several instances of sqlplus
still running, but there was no DB activity. I tried running sqlplus
off the command-line, and it hung. I killed all old sqlplus
instances and tried sqlplus
again, and it still seems to hang. No error message, no output. It won't even complain if I give it completely made-up parameters, TNS entries or anything.
These are command I've tried, for which sqlplus
simply hangs and does not emit an error, failure code or any visible sign of life:
user@server:~ $ sqlplus
user@server:~ $ sqlplus -V
user@server:~ $ sqlplus -H
user@server:~ $ sqlplus /NOLOG
user@server:~ $ sqlplus -invalidoption
user@server:~ $ sqlplus -invalidoption /NOLOG
And, of course:
user@server:~ $ sqlplus -s dbuser/dbpassword@tnsid /some/script.sql
As you can see from the following, the oracle files haven't changed for a long time...
user@server:~ $ ls -ld $(which -a sqlplus)
-rwxr-xr-x 1 oracle oracle 12896 Apr 15 2008 /usr/oracle/product/10gClient/bin/sqlplus
user@server:~ $ sqlplus -H
user@server:~ $ env | grep ORACLE
ORACLE_BASE=/usr/oracle
ORACLE_HOME=/usr/oracle/product/10gClient
user@server:~ $ ls -ld $ORACLE_HOME/network/admin/*
drwxr-xr-x 2 oracle oracle 4096 Apr 15 2008 /usr/oracle/product/10gClient/network/admin/samples
-rwxr-xr-x 1 oracle oracle 172 Dec 26 2003 /usr/oracle/product/10gClient/network/admin/shrept.lst
-rwxr-xr-x 1 oracle oracle 289 Apr 16 2008 /usr/oracle/product/10gClient/network/admin/sqlnet.ora
-rw-rw-r-- 1 oracle oracle 187931 Apr 22 2009 /usr/oracle/product/10gClient/network/admin/tnsnames.ora
user@server:~ $
For comparison, this is the output I'd expect to see, using sqlplus
on another server:
user@server2:~ $ ls -ld $(which -a sqlplus)
-rwxr-xr-x 1 oracle oracle 12896 Apr 29 2008 /usr/oracle/product/10gClient/bin/sqlplus
user@server2:~ $ sqlplus -V
SQL*Plus: Release 10.2.0.1.0 - Production
user@server2:~ $
For what it's worth, tnsping
works fine. Additionally, we know other applications running on the same box are able to make database connections just fine. But as I've hopefully shown above, sqlplus isn't working even if I ask it to do something local without connecting to a db (i.e. if I prod sqlplus to output help-text or its own version info, it still consumes 99% CPU and does not exit.)
The one obvious thing not tried yet is to reboot the box. As the client server is used by several applications and teams in the company, I need to exhaust all available options before requesting a reboot.
Additional information requested in comments:
ps output while sqlplus is hanging:
user@server:~ $ ps aux | grep sqlplus
user 27319 99.2 0.0 20188 4628 pts/7 R+ 17:04 1:13 sqlplus -H
user 27427 0.0 0.0 3600 744 pts/5 S+ 17:05 0:00 grep sqlplus
And if I try running it as various users at once:
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 8089 88.7 0.0 20032 4628 pts/5 R+ 08:13 0:57 sqlplus /NOLOG
user1 8103 89.8 0.0 19824 4632 pts/3 R+ 08:13 0:53 sqlplus /NOLOG
user2 8125 91.0 0.0 19492 4628 pts/2 R+ 08:13 0:47 sqlplus /NOLOG
oracle 8286 74.8 0.0 20212 4632 pts/6 R+ 08:14 0:06 sqlplus /NOLOG
user3 8292 0.0 0.0 3444 748 pts/0 S+ 08:14 0:00 grep sqlplus
I don't have whence
, but which -a
output and the result of running with full path to the exe are shown:
user@server:~ $ which -a sqlplus
/usr/oracle/product/10gClient/bin/sqlplus
user@server:~ $ /usr/oracle/product/10gClient/bin/sqlplus -H
user@server:~ $ sqlplus -H
user@server:~ $
Any and all runs of sqlplus
require a Ctrl+C to abort (but never used to.)
strace -p<pid>
output of one of the hung processes:
....
times(NULL) = -2100628462
times(NULL) = -2100628462
times(NULL) = -2100628462
times(NULL) = -2100628462
times(NULL) = -2100628462
times(NULL) = -2100628462
times(NULL) = -2100628461
times(NULL) = -2100628461
times(NULL) = -2100628461
times(NULL) = -2100628461
.... ad infinitum ....
oracle
is hitting it, since you can run other commands asuser
. Can you log in asoracle
and run (any) processes, and is anything reported in the Oracle logs? (If that doesn't highlight anything, issqlplus
aliased, or a function; what doeswhence -v sqlplus
show? What doesps
show while it's hanging?)oracle
ID, on both boxes? Can you see theulimit
fornproc
?