Re: sqlplus : 100% CPU usage while connecting to remote DB

Comments embedded.
On Jun 25, 8:30 am, Krivenok Dmitry <krivenok.dmi..._at_gmail.com> wrote:
> Hello!
> I am trying to connect to the remote DB using the following command:
>
> sqlplus dimas/pass_at_MTT
>
> Note, that MTT is accessible, I've tested it using tnsping.

All tnsping tests is the availability of the listener, it does nothing to test actual connectivity to the remote database.

>
> But sqlplus hangs and eats 100% of CPU.
> Look at top's output:
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 12767 voteuser 22 0 19752 4764 3364 R 100 0.2 0:03.34 sqlplus
>
> I'm just trying to connect to remote DB.
> What's wrong?

We know as much as you do at this point; you likely need to trace the listener process on the remote database server and see what may be preventing your connection from completing successfully. You may also need a client trace as well. In your listener.ora file on the remote server you'll need to add:

TRACE_LEVEL_LISTENER=16 and in your sqlnet.ora file you'll need to add:

TRACE_LEVEL_CLIENT=16 Once these additions have been made restart the listener on the remote server and initiate another connection to MTT and see what you get in the resulting trace files.

>
> I've traced sqlplus process via strace utility.
> It doesn't attempt to connect (doesn't call connect system call).
> Looks like an infinite loop:
>
> ...
> ...
> ...
> 13100 open("/usr/local/oracle/app/oracle/product/10.2.0/rdbms/mesg/
> ocius.msb", O_RDONLY) = 6
> 13100 fcntl64(6, F_SETFD, FD_CLOEXEC) = 0
> 13100 lseek(6, 0, SEEK_SET) = 0
> 13100 read(6, "\25\23\"\1\23\3\t\t
> \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 256) = 256
> 13100 lseek(6, 512, SEEK_SET) = 512
> 13100 read(6, "\337y
> \0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0"..., 512) = 512
> 13100 lseek(6, 1024, SEEK_SET) = 1024
> 13100 read(6, "\25\7\'\0072\7>\7j\7\276\17$\'\6K5S\24TfT\307T(VsV
> \222"..., 86) = 86
> 13100 brk(0x80af000) = 0x80af000
> 13100 times(NULL) = -2058704771
> 13100 times(NULL) = -2058704771
> 13100 times(NULL) = -2058704771
> 13100 times(NULL) = -2058704771
> 13100 times(NULL) = -2058704771
> 13100 times(NULL) = -2058704771
> ...
> ...
> ...
>
> That is sqlplus process calls only "times" system call in a loop (in
> fact, infinite loop).
>
> P.S.
> Remote connect to the MTT DB from another host is successful.

Connecting from another host doesn't solve this problem. You need to trace connection attempts from THIS client/host to the destination database and determine why THIS particular machine has problems.

David Fitzjarrell Received on Mon Jun 25 2007 - 08:45:31 CDT