which user process on SG node A causes Oracle process on SG node b?

[John Kittel]

We have a 2 node active-active ServiceGuard cluster. The application runs on node A, but it uses Oracle on node B. Hardly any resource utilization on node A, lots on B. I frequently find oracle processes on node B consuming lots of CPU, disk activity, etc., and want to find out which user process on node A is responible for it.

Any suggestions on how to go about it?

- John Kittel

[John Kittel]

I guess I should add:

the application process on node A runs under the user's account name. For example, user tr18132 runs a process that looks like this ( output of ps -fx):

tr18132 12617 3217 0 06:59:27 ? 0:05 gem_thin_server IREN57PROD tcp,10.2.12.1,6068


The oracle processes on node B look like this:

oracle 29939 1 0 09:00:58 ? 0:01 oracleprod1 (LOCAL=NO)

- John

[Patrick Wallek · Accepted solution]

That would be a sqlnet connection from node A to node B. Whichever process on node A that needs DB access to node B could initiate the connection.

If you have lsof installed on the machines, you could use it to determine any connections from node A to node B.

[John Kittel]

I will get lsof installed and give it a try. Thanks.

[John Kittel]

OK, thats working!

But I'd like to refine my approach if I can get a little more help with lsof.

On the Oracle node, I can easily find just the sqlnet sockets for a particular process. But then on the application node, how can I find the corresponding process without having to list all the ipv4 sockets and grep for the socket numer?

For example, on the Oracle side I see this:
# /tmp/lsof -p 29996 -i 4 -a
COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 29996 oracle 8u IPv4 0x5033d640 0t0 TCP *:* (IDLE)
oracle 29996 oracle 13u IPv4 0x67751240 0t74899 TCP erpsqlnet.mydomain.co
m:1521->mynode.mydomain.com:52235 (ESTABLISHED)

On the application node I can't seem to figure out how to specify the lsof command to select only the file(s) I want. I've tried all different combinations of the lsof (file) "name" parameter, but it just keeps telling me I'm not specifying a valid name.

I probably won't get back to this until Monday morning... will read replies, assign points, etc. then.

Thanks for any advice.

- John

[Sandman!]

John,

Process IDs on the application node making a call to the Oracle server on node B can be found as follows...

# lsof -iTCP@:

for example if IP of oracle server is 10.11.12.13 and the oracle listener process is listening on port 1521...then run the follwing on your application node:

# lsof -iTCP@10.11.12.13:1521

cheers!

[Volker Borowski]

Hi,

well this is pretty normal and depends on the db-requests the application sends.

A select with a small result set might need low application resources and can require big db-resources i.e. if a full tablescan is needed to get the result.

Your problem might not be lack of CPU resource on node B but more related to poor application design.

I think you will need to do some tuning.
Install the Oracle statspack and get some snapshots and print the reports.
Calculate DB-statistics frequently.
This will quickly show some hints where to go on.

Good hunting
Volker

[John Kittel]

Thanks everyone. lsof is providing the information I asked for. Thanks "sandman" for the proper syntax.

- John