6

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 ....
7
  • Do you have a lot of idle/stuck processes - you could get this effect if you're hitting the nproc limit; or more likely if oracle is hitting it, since you can run other commands as user. Can you log in as oracle and run (any) processes, and is anything reported in the Oracle logs? (If that doesn't highlight anything, is sqlplus aliased, or a function; what does whence -v sqlplus show? What does ps show while it's hanging?)
    – Alex Poole
    Jun 25, 2013 at 15:54
  • So it's spinning rather than hanging, from the 99.2% CPU. How many processes are running under the oracle ID, on both boxes? Can you see the ulimit for nproc?
    – Alex Poole
    Jun 25, 2013 at 16:12
  • I can su to oracle, create shell script and run them. limits.conf has nproc limit set to 16,384. The oracle user doesn't have any processes runnig (bar the one's I'm executing to research this: 'ps', 'grep' etc.) ulimit shows 'unlimited'. As for logs, where should I begin looking? This box only has the client, not the DB.
    – searlea
    Jun 25, 2013 at 16:15
  • OK, I'm way off base then. Kind of missed that it was client only, and it's happening too early to be at the server end. Sorry, and good luck...
    – Alex Poole
    Jun 25, 2013 at 16:31
  • Fair comment, I'll update the question to make it more obvious this is a client-only box.
    – searlea
    Jun 25, 2013 at 16:34

1 Answer 1

4

The Oracle client version, strace output and system uptime details combined indicate this could be Oracle bug 4612267 for which a patch is available. The problem is supposed to be resolvable by either rebooting or applying the patch.

user@server:~ $ uptime
  9:54am  up 254 days  0:26,  5 users,  load average: 1.39, 1.56, 1.75
user@server:~ $ genezi -v
Client Shared Library 32-bit - 10.02.00.01.00

I had requested the patch be applied to our server to confirm if the patch fixed the issue. Unfortunately someone randomly rebooted the server before the patch could be applied - end result is that sqlplus is functioning again, which is good for me, but means I can't confirm if the patch would have fixed the issue without a reboot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.