2014 Oracle - Esri Geodatabase Tidbits

Blog Post created by Dale_Brooks-esristaff Employee on Jan 30, 2015

Wanted to pass on a few thoughts from 2014 Oracle experiences


Oracle SEPS

Oracle Secure External Password Store is a nice way to avoid updating stale Oracle connection credentials in many MXD files.  One secure file, one set of credentials to update on a regular basis.  Syntax for Oracle connection leaves out the user/password, and relies simply on a unique TNS_ALIAS assigned for each user/database.  At 10.1 (or 10.2?) this stopped working for ArcGIS Server data source configuration, and in service definition files, and I want to test in 10.3 to see if this has been addressed.


Don't Drop that SDE Schema Yet!

Dropping the SDE user when there are still ST_Geometry table dependencies is still an important danger that we need to make sure customer Oracle DBAs are aware of.  Happened twice this year.  Most Oracle DBAs don't encounter configurations with abstract data types (ADTs), and Oracle doesn't warn them of dependencies on the type when the SDE user is being dropped.  The result, of course, is the ST_Geometry configuration in the database gets corrupted, without a possible fix.  The only recourse is to restore the complete database (including the SYSTEM tablespace) from a backup.  There is no re-installing our ST_Geometry technology in a corrupted database.  Please let your DBAs know -- all dependencies in other user schemas must be removed before dropping the SDE user.  And an SDE Schema export from another database needs to be imported into a new database *first* before importing all the user data schemas with ST_Geometry dependencies.


Oracle Standby Database Options.

One Oracle Architect wants to have a read only standby database.  I mentioned the fact that GDB user connections typically write to SDE_LOGFILES and KEYSET Tables, and write transactions occur in the SDE schema, such as in the PROCESS_INFORMATION table.  I recommended consideration of the other standby options that allow writes in the database.


Oracle Instant Client

Oracle Instant Client has been a lifesaver at a customer site where Oracle client is not in the workstation baseline, and the client configuration is very locked down.  Copying the instant on a share, and dynamically updating the PATH when starting ArcGIS via a .bat file does the trick for direct connections to the database.  Thanks to Bill Major.