topic Re: Help! - ArcSDE is locking our Oracle schemas in Data Management Questions

Help! - ArcSDE is locking our Oracle schemas

NathanielWingfield — Mon, 22 Oct 2012 13:10:55 GMT

We changed a password on an Oracle schema, but apparently we have some rogue SDE clients - probably old ArcGIS map services on a dev machine somewhere - attempting to connect with the old password. SDE is causing Oracle to lock out the schema because of the rapidfire attempts to connect with the bad password.

Is there any way to detect where the bad login attempts are coming from? I have turned on the ArcSDE intercept log, but I don't see anything related to connect attempts there.

We're using SDE 9.3.1.

Re: Help! - ArcSDE is locking our Oracle schemas

VinceAngelo — Mon, 22 Oct 2012 14:22:19 GMT

The intercept log is the conversation between the server and the database. If connection
isn't made, there won't be much to talk about.

Are you using application server or Direct Connect connections? Application servers will
at least log in one location (and would be more forthcoming with "set SDEVERBOSE=TRUE"
in the dbinit.sde; Direct Connect will log on individual clients, but you'd probably be more
interested in the SQL*Net logs.

A more accurate title for this thread would have been "ArcGIS Server SOC(s) with old
password locking Oracle user accounts". ArcSDE is quite innocent of all charges.

- V

Re: Help! - ArcSDE is locking our Oracle schemas

NathanielWingfield — Sun, 12 Dec 2021 00:55:05 GMT

I turned on verbosity, then made a test connection from ArcCatalog with a bad password. Looking at the giomgr_x.log file, this is all I see. Should I look elsewhere? Is the client hostname or IP address not logged somewhere?

Mon Oct 22 12:13:24 2012 - Error (-9):Couldn't Start Server Task.

Mon Oct 22 12:13:24 2012 - Process 15444, no shared information block established

Mon Oct 22 12:13:24 2012 - SDE Server 15444 exit'd with status 3

Re: Help! - ArcSDE is locking our Oracle schemas

VinceAngelo — Mon, 22 Oct 2012 15:42:14 GMT

Try enabling a firewall on the ArcSDE server, set to permit but log connection requests
on the application server port.

After that you'll need to scan the AGS logs on all your development clients, looking for
a persistent-but-misguided SOC.

- V

Re: Help! - ArcSDE is locking our Oracle schemas

NathanielWingfield — Mon, 22 Oct 2012 19:07:09 GMT

How do you suggest I then correlate the inbound connections to SDE with the corresponding outbound connections to Oracle?

Consider this a formal request for better logging capabilities. I think your customer base would appreciate more visibility into SDE's behavior.

Mapping inbound spatial queries to outbound SQL (e.g., for performance tuning) is another deficiency that needs attention, perhaps even more so.

Re: Help! - ArcSDE is locking our Oracle schemas

VinceAngelo — Mon, 22 Oct 2012 19:27:34 GMT

Remote application servers are not recommended, but that is unreleated to this
problem -- you just need to know which clients are attempting connect on the
application server port (to identify the rogue actors), so it doesn't really matter
how connections map back out again.

All formal requests should go through formal processes (like the ideas.esri.com site).

- V