Re: Detecting Orphan SQLnet Shadow Processes

Lee,

The problem you describe is fairly common

In article <Cnr33I.AAM_at_world.std.com>, lparsons_at_world.std.com (Lee E Parsons) writes:
>
> Given a large Database Server with many Shadow Tasks (100+) attaching
> from PC clients, how do you identify and kill idle processes associated
> with rebooted/down PC's?
>
> A user could lock a number of important files then shutdown his system.
> The shadow task would not go away but when asked the user would not
> think he was still accessing the system.
>
> Certainly this problem could be tracked down via the lock monitors
> but that seems very manual and timeconsuming for a large shop.
>
> How do people routinely avoid/clean up this type of problem?
>
> Additionally, we find this senario is a problem when shutting down
> the system. ie) Are the remaining processes orphan shadow tasks
> or just users who have not gotten the word to log off?
> --
> Regards,
>
> Lee E. Parsons
> Systems Oracle DBA lparsons_at_world.std.com

There are a couple Oracle things you can do. Manually or automatically.

1. Manually: Using netstat & ofiles

   ---

The following situation I am about to describe is for UNIX boxes. do.

1. tcpctl stat Find out if there are any active connections? If there are, log them out or kill the unix server connection process . How do you find them?
2. grep oracle /etc/services Get the socket number. This is normally 1525.
3. netstat -A |grep 1525 Get all processes associated with the Oracle socket,1525

PCB      Proto Recv-Q Send-Q  Local Address      Foreign Address (state)
fface20c tcp        0      0  redbaron.1714      biggles.1525    ESTABLISHED
ffa9d60c tcp        0      0  redbaron.1525      biggles.4847    ESTABLISHED
ffa7ae8c tcp        0      0  redbaron.1052      biggles.1525    ESTABLISHED

4. ofiles -n PCB_number

    This is the difficult part. You now need to find out the UNIX     process that is attached to the socket. There is a public domain     program called, ofiles that can do this. Basically it looks at the     protocol control block (PCB) and maps that to a UNIX process. You     will get a PID from ofiles.

5. kill PID

    Once you have the PID, you may kill it.

6. Try using tcpctl again

The problem is normally associated with PC-Client processes that switch off their PC or reboot them while connected a server database.

2. Automatically: Setting KEEPALIVE

Have a look at TCP's keepalive time interval. It defaults to 7200 seconds or 2hours. This means you shadow process (server end) will be terminated 2 hours after the client hangs up or reboot. You are able to reduce this down and I have it set to 5 minutes. Dont set it too low as it may affect other daemon programs.

I dont have the details on the actual parameter and file that it is set in. It has been done on site against SUN OS 4.XXX and Pyramid OS/X and they work. Speak to your UNIX system administrator and he should be able to help you.

Hope this helps. Please letme know if it resolves your problem or not or did you user another approach.

ta
tony

 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  _        /_ __ /       |Colonial Mutual Invest Mgmt,Aust (ACN004021809)
 /(_)/ )(_/ \_/(///(/_)/_(   |EMAIL: TJambu_at_cmutual.com.au
 \_______/                   |PHONE: +61-3-2831639       FAX: +61-3-2831090
-- 
 _____       ________ / ____ |Tony Jambu, Database Administrator
  /_  _        /_ __ /       |Colonial Mutual Invest Mgmt,Aust (ACN004021809)
 /(_)/ )(_/ \_/(///(/_)/_(   |EMAIL: TJambu_at_cmutual.com.au
 \_______/                   |PHONE: +61-3-2831639       FAX: +61-3-2831090