topic Re: SDE.PROCESS_INFORMATION entries persist in Data Management Questions

SDE.PROCESS_INFORMATION entries persist

SherrieKubis — Sat, 11 Dec 2021 09:53:18 GMT

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#, s.SID, s.TERMINAL, s.USERNAME,
         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.SID, sp.TERMINAL, sp.USERNAME,
        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

Re: SDE.PROCESS_INFORMATION entries persist

Asrujit_SenGupta — Sun, 09 Dec 2018 04:35:29 GMT

Have you checked these:

Manage connections in Oracle—Help | ArcGIS Desktop

To disconnect users from a geodatabase in Oracle, the geodatabase administrator must either be added to the DBA role or granted ALTER SYSTEM and SELECT_CATALOG_ROLE privileges.

DisconnectUser

Re: SDE.PROCESS_INFORMATION entries persist

SherrieKubis — Mon, 10 Dec 2018 20:18:51 GMT

Yes. The user doing the ALTER SYSTEM KILL SESSION has DBA privileges.

The issue isn't that the sid,serial# can't be killed, it's that it doesn't exist, it's already been disconnected. When the session in V$SESSION and v$PROCESS has been killed, the corresponding entry in SDE.PROCESS_INFORMATION should be deleted but it's not. It persists so it appears that there are SDE connections when there aren't.

At the time of this issue, there were NO sessions other then DBSNMP and Oracle background processes connected. However, SDE.PROCESS_INFORMATION table had 196 rows.

Sherrie

Re: SDE.PROCESS_INFORMATION entries persist

Asrujit_SenGupta — Tue, 11 Dec 2018 11:11:02 GMT

No, I wasn't pointing towards the permissions for the disconnecting user.

What I meant was, did you trying using the Script suggested in those links for disconnecting the connected users. Does that make any difference?

Re: SDE.PROCESS_INFORMATION entries persist

SherrieKubis — Tue, 11 Dec 2018 19:33:21 GMT

Gotcha. No, we have always used alter system kill session How To: Kill direct connect processes to the ArcSDE databaseBut we've been doing it for so long I don't remember exactly where it came from, although I'm sure it was from ESRI. We'll give Arcpy a look and see what it does. At this point, I'm way past the production issue but will keep this in mind.

Re: SDE.PROCESS_INFORMATION entries persist

FrankBailey — Sun, 10 Jan 2021 18:05:35 GMT

We are running ArcGIS 10.6.1 and Oracle 19c (12.2.0.3) and we're seeing some similar behavior in our database which is what brought me to this topic. When viewing the Process_Information table in SQL Developer we see a large number of what appear to be stale connections. Hence my interest in your scripts above.

Interestingly enough, when I connected to the database w/ArcCatalog and ran the Administer Geodatabase tool referenced by Asrujit and viewed the connections, it only shows the active connections, none of the stale entries show. After doing so, I went back into SQL Developer and refreshed my query of the Process_Information table. At that point all of the stale connection entries had been cleared out! So it seems ESRI has built some sort of check/maintenance function into the tool. I'm wondering if it can be scripted in Python and run as a scheduled task to keep the stale connection entries and their associated locks to a minimum.

Just thought I'd share this in case anyone else runs into similar issues on their systems.

Re: SDE.PROCESS_INFORMATION entries persist

PhilippeWaecker — Fri, 01 Sep 2023 07:42:48 GMT

We are running ArcGIS 11.1 with PostgreSQL 12 and have exactly the same issue. we have to regularly cleanup running arcpy.DisconnectUser() function.

Has there been any evolution on this topic ?

Regards

Philippe