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
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 ....