Environment
Oracle 12.1.0.2
SDE 10.4.1
Connection Methods
Direct connects using tnsnames.ora are used from desktop and servers.
Kill connections method
Construct an ALTER SYSTEM KILL SESSION (‘SID’,’SERIAL#’) dynamic sql statement from joining V$SESSION and V$PROCESS with SDE.PROCESS_INFORMATION. This is how we gather information, there is a next step of constructing and executing the ALTER SYSTEM that is not shown here.
WITH
sp AS (
SELECT s.audsid, s.LOGON_TIME, s.MACHINE, s.MODULE, s.OSUSER, s.PROCESS, s.PROGRAM, s.SERIAL
p.PID, p.PNAME, p.SPID
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND s.TYPE != 'BACKGROUND'
AND s.program IS NOT NULL
AND s.username NOT IN ('SYS', 'SYSTEM', 'DBSNMP') ),
pi AS (
SELECT p.audsid, p.sde_id, p.server_id, p.start_time, p.owner, p.nodename
FROM sde.process_information p )
select sp.LOGON_TIME, sp.MACHINE, sp.MODULE, sp.OSUSER, sp.PROCESS, sp.PROGRAM, sp.SERIAL
sp.PID, sp.PNAME, sp.SPID, pi.owner, pi.server_id
from pi, sp
where pi.owner = sp.username
and pi.server_id = SUBSTR (sp.process, 1, INSTR (sp.process, ':') - 1)
and pi.audsid = sp.audsid
order by sp.logon_time;
Scenario 1
We periodically need to update data during our maintenance window requiring all connections to be disconnected. After all sessions were disconnected, the V$SESSION and V$PROCESS tables showed no connections for SDE. But SDE.PROCESS_INFORMATION had 196 rows, and SDE.TABLE_LOCKS had over 3000 rows.
We bounced the database, but SDE.PROCESS_INFORMATION and SDE.TABLE_LOCKS returned the same.
Ultimately from SQL we deleted all rows in SDE.PROCESS_INFORMATION and SDE.TABLE_LOCKS, and update work progressed. After the maintenance window, desktop ArcMap, ARCSOC sessions came in, everything seems to be working fine.
Scenario 2
I believe that SDE.PROCESS_INFORMATION should always have an entry for an SDE connection. Right now if I pick a USERNAME/OWNER and compare V$SESSION has 9 rows, but there are 11 rows in SDE.PROCESS_INFORMATION. I’ve also seen cases where there are entries in SDE.PROCESS_INFORMATION that don’t exist in V$SESSION.
ISSUE
Prior to ArcGIS 10.5 sdemon was used to clear and rectify SDE process entries with Oracle sessions, even with direct connections. There isn’t an sdemon now, or a method (that I know of) to manage connections. There is something for locks, but not connections. This is why we resort to the brute force ALTER SYSTEM KILL SESSION method.
Something is not quite right, but I don’t know why. It’s hard to test because this is production.
Why do entries persist in SDE.PROCESS_INFORMATION?
Why do sessions show in V$SESSION that are not in SDE.PROCESS_INFORMATION?
It feels dangerous to manually monkey with SDE metadata tables. I see many posts about using the ALTER SYSTEM KILL method, so it seems widely accepted and recommended by ESRI.
At this point, things are working okay, but it’s uncomfortable not to have processes and connections matched up. And I can’t guarantee this won’t happen again during the next maintenance update.
Any insights or suggestions are welcome.
Sherrie
