Can't connect to Ora 10g w/ 3-tier conn from AGS Svr 10 SP1

[ChristinaKepler]

This thread was posted to the 'AGS General' forum, but thought I'd re-post here since not getting a solid answer...

We have AGS 10 SP1 running on Windows Server 2003, and Oracle 10g (10.2.0.4) on a remote server (also Server 2003).  We can connect from AGS using a direct connection (have the Oracle client s/w installed, etc.)  But when attempting to connect with a service-based 3-tier connection, we get a "Error 1053: The service did not respond to the start or control request in a timely fashion" error.

The Windows Event Viewer has the following two simultaneous entries:
1. Timeout (30000 milliseconds) waiting for the ArcSde Service(esri_sde_ora2) service to connect.

2. The ArcSde Service(esri_sde_ora2) service failed to start due to the following error:
The service did not respond to the start or control request in a timely fashion.

Oddly, my sde_esri_sde_ora2.log has not been logged to since Jan. 24th of this year, even though I just tried to start the service and the errors were logged to the Windows event log.

It's also worth noting that the errors are reported immediately after trying to start the service, so a 30 second timeout error seems strange.

The executable that the service is attempting to start is 'C:\ESRI\ArcGIS\ArcSDE\ora10gexe\\bin\giomgr.exe'. (Apparently the double backslash '\\' has no adverse affects and the system can correctly resolve the path to the exe). If I run that from the command line, it throws the 'OCI.dll not found' error.

I've verified that my ORACLE_HOME env var is set to the correct location and that ORACLE_HOME\bin is in my Path env var.  Also the LOCAL var is set correctly in dbinit.sde, etc.

I've followed the instructions for Installing Oracle and ArcSDE on separate servers and Creating an ArcSDE service from a remote Windows server, but when I try to start the remote service using the instructions at Starting a remote ArcSDE service on Windows, I immediately get the error:

Error: Access denied when starting esri_sde_ora2 instance on ora2.
Operation Failed, Unable to start iomgr.

My questions are:
1. What is the <DB_Admin_password> noted in the sdemon commands? Is that the password for the sde user on the Oracle server? Or the 'sys' Oracle user? Or something else?

2. When running the command to create a service on the remote Oracle server according to the instructions at Creating an ArcSDE service from a remote Windows server, it creates a service locally on the SDE server. Is it also creating a service on the remote Oracle server??

3. What should I have our Oracle admin look for on the Oracle server to ensure that the service was created successfully on that server?  Are there any tests I can try outside of a service connection to verify that the installation steps were successful?

4. When creating the remote service on the Oracke server, there is no parameter to define which port to use. If I already have a SDE service running on port 5151 on the local machine, I'd like to use a different port. How is it possible to define the port on the remote machine? The 'sdeservice -o create' command does not seem to have a parameter for port. The instructions at Starting a remote ArcSDE service on Windows indicate that the port number must be entered into the Windows services file on BOTH the local and remote servers.  How do I keep these in sync between the local and remote servers?

[VinceAngelo]

ArcGIS (both desktop and server) is [presently] a 32-bit application. *All* operating systems
supported by ArcSDE require homogeneous architectures for dynamic libraries (32-bit app,
32-bit DLL; 64-bit app, 64-bit DLL), so "DC works but AS doesn't" would be expected with
32-bit client, 32-bit AGS, and 64-bit SDE.

"ORA2 SDEHOME" was my shorthand for "the SDEHOME to operate with the ORA2 instance."
The fact that no log was written could have been because the first instance already on the
host was also Oracle ("ORA2" vice "ORA1"?), and the 'sdeservice' command was using the
other SDEHOME\etc.
 
It may take a reboot to clear the deleted Windows service.

Vince's First Law of Compatibility: For optimal stability, the application software should
have been released after the server software, which should have been released after
the operating system (tOS < tRDBMS < tGIS). [I made sure I knew my answer before
posting the "second" law]

Check your PATH variable for alternate SG.DLL locations -- Did you copy the 64-bit DLLs
into Oracle\bin for ST_GEOMETRY support?

- V

[VinceAngelo]

Doh. I just re-read your post, and yes the 64-bit ArcSDE for SQL-Server libraries are conflicting
with the 32-bit ArcSDE for Oracle (that's what the SG.DLL entrypoint error was about).

When it comes down to it, Windows just doesn't have the same level of flexibility as Unix to
handle 32-bit and 64-bit applications at the same time.  Trying to do too many things on a
single Windows box often causes trouble.

You might be able to work around the 32/64 DLL conflicts by using a different  login for
the ArcGIS service, so it can have a PATH which is different from the 64-bit ArcSDEs for
both Oracle and SQL-Server, or you can install a 32-bit ArcSDE for SQL-Server and run
all 32-bit apps (except for the 64-bit SQL-Server itself), or you can use *only* Direct
Connect for accessing the Oracle ArcSDE database (it's not always necessary to run an
application server), but at this point, running 32-bit AGS, 32-bit Oracle Client, 64-bit
Oracle Client, 64-bit SQL-Server, 64-bit ArcSDE for Oracle, *and* 64-bit ArcSDE for
SQL-Server on a single host may just be a bridge too far.

- V

[ChristinaKepler]

Thanks again Vince.

Some updates today, but first, w/r/t "... or you can use *only* Direct Connect for accessing the Oracle ArcSDE database (it's not always necessary to run an application server)": 
Is it possible to:
   - register spatial views with a direct connection?
   - run the equivalent of 'sdemon' commands (and other CLI cmds) w/o an AS?

If so, I'll happily avoid configuring an AS.

That said, here's what I've done since our last correspondence:

- uninstalled ORA SDE 64 and installed ORA SDE 32
- applied Oracle 10.2.0.4 patch set to 10.2.0.3 client (now matches Oracle server version)
- copied sg.dll from %SDEHOME%\bin to %ORACLE_HOME%\bin (since Oracle is 1st in PATH)
- Successfully ran SDE post-installer (did authorization only -- didn't create service or repo)
- Created SDE service acc. to instructs
- Ran command to start service acc. to instructs:

     Error: Access denied when starting esri_sde_ora2 instance on ora2.
     Operation Failed, Unable to start iomgr.

If I tried to start the service w/ the Windows service mgr, I get:

The ArcSde Service(esri_sde_ora2) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Log and Alerts service.

Windows Event Viewer didn't have any meaningful info.

Then saw your most recent note, so:
- uninstalled SQL SDE 32  (have not yet reinstalled)
- applied v10 SP1 to ORA SDE 32
- ran post-install to authorize
- moved SDEHOME(ORA)\bin to front of PATH but just after ORACLE_HOME\bin, which is first

Still can't start service, getting:
esri_sde_ora2 service failed during initialization.
Please check event log or error log files.
Error starting esri_sde_ora2 service(0)
Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and dbinit.sde.

sde_esri_sde_ora2.log:
Error opening Software\ESRI\ArcInfo\ArcSDE\ArcSDE for Oracle\esri_sde_ora2 key (131)

ST_Geometry Schema Owner: (SDE) Type Release: 1007
Instance initialized for ((sde)) . . .

Connected to instance . . .
ERROR in checking system st_geometry type (1).
Error checking the existence of system tables. Run
the Upgrade Geodatabase tool to install/upgrade system tables (-450).
Refer ArcSDE installation guide for instructions.

Tried to upgrade the geodatabase (from ArcCatalog):
Executing: UpgradeGDB "Database Connections\ORA2_sde_dc.sde" PREREQUISITE_CHECK NO_UPGRADE
Start Time: Sun Mar 13 15:42:32 2011
Cannot upgrade because there are other active connections (SDE).
Cannot upgrade because there are other active connections (SDE).
User has privileges required to upgrade.
Connected RDBMS instance is not setup for ST_GEOMETRY configuration. [Unable to determine current version of ST_SHAPELIB
The latest ST_GEOMETRY and dependent libraries need
to be copied to the correct software location.
Please consult ArcSDE for Oracle Installation Guide
for further details.
]
Running Pre-Requisite check (C:\Documents and Settings\cbeaudette\Application Data\ESRI\Desktop10.0\ArcCatalog\ORA2_sde_dc.sde).
Instance supports XML type.
Failed to execute (UpgradeGDB).
Failed at Sun Mar 13 15:42:34 2011 (Elapsed Time: 2.00 seconds)

GDBUpgrade.log:
[3/13/2011 3:26:21 PM] Error: Could not pause the database.
[3/13/2011 3:26:21 PM] Error: Cannot upgrade because there are other active connections (SDE).
[3/13/2011 3:26:21 PM] Error: Cannot upgrade because there are other active connections (SDE).
[3/13/2011 3:26:21 PM] User has privileges required to upgrade.
[3/13/2011 3:26:21 PM] Warning: Connected RDBMS instance is not setup for ST_GEOMETRY configuration. [Unable to determine current version of ST_SHAPELIB
The latest ST_GEOMETRY and dependent libraries need
to be copied to the correct software location.
Please consult ArcSDE for Oracle Installation Guide
for further details.
]
[3/13/2011 3:26:21 PM] Running Pre-Requisite check (C:\Documents and Settings\cbeaudette\Application Data\ESRI\Desktop10.0\ArcCatalog\ORA2_sde_dc.sde).
[3/13/2011 3:26:21 PM] Instance supports XML type.


sdedc_Oracle.log:
ST_Geometry Schema Owner: (SDE) Type Release: 1007
Instance initialized for ((sde)) . . .

[Sun Mar 13 15:21:48 2011] [129] [GIS4] db_array_fetch_attrs OCI Fetch Error (28575)
[Sun Mar 13 15:21:48 2011] [129] [GIS4] load_buffer error -51 on GDB_ITEMS, rowid -1
[Sun Mar 13 15:22:04 2011] [129] [GIS4] db_array_fetch_attrs OCI Fetch Error (28575)
[Sun Mar 13 15:22:04 2011] [129] [GIS4] load_buffer error -51 on GDB_ITEMS, rowid -1
[Sun Mar 13 15:26:21 2011] [129] [GIS4] db_array_fetch_attrs OCI Fetch Error (28575)
[Sun Mar 13 15:26:21 2011] [129] [GIS4] load_buffer error -51 on GDB_ITEMS, rowid -1

Can't kill connections w/ 'sdemon -o kill' since I don't have AS, so rebooted, tried again to update geodb, same errors.

Per this thread, ran:

select object_name, object_type, created, last_ddl_time, status from user_objects where object_type in ('OPERATOR','PACKAGE','PACKAGE BODY','INDEXTYPE','FUNCTION','TYPE','TYPE BODY');

The indextype ST_SPATIAL_INDEX is *NOT* missing from my database schema.   (Attaching file 'ora_objects.txt').

Through all this, direct connections are still working to Oracle db.  However geodb is one version behind (not yet SP1) from SDE.

[ChristinaKepler]

Quick follow-up:  I just noticed that the st_shapelib.dll (and libst_raster_ora.dll) that were originally copied to the Oracle server long ago were the v10 versions, so we have to copy the v10 SP1 versions to Oracle.  Though the message reported during the geodb upgrade was a warning only and I don't think of itself is the root cause of my connectivity issues.

Would love to know how to kill a geodb lock w/ an AS!  And/or why there are locks at all after a reboot.

[ChristinaKepler]

Previous post should read:  "Would love to know how to kill a geodb lock *WITHOUT* an AS! And/or why there are locks at all after a reboot."

Have since had st_shapelib.dll and libst_raster_ora.dll copied to Oracle server, still getting same erorrs when attempting to start the service.

[ChristinaKepler]

Success! 

The last step required was to upgrade the geodb to SP1 (can't do so w/ the dialog in ArcCatalog, since you have to connect to the db to upgrade but you can't upgrade if there are connections -- holy catch 22!  -- so got around it by using a python script to do the upgrade, as described here).

Next step is to see if I can get SQL SDE 32 running on same server.  Will heed Vince's recommendation to have a separate SDEHOME.  Fingers crossed...

There were so many changes that it's hard to tell what the magic combination was, but in the end these were the material changes:
- uninstall all ArcSDE instances besides Oracle (using 10g in this case, v10.2.0.4)
- uninstall 64-bit version of ArcSDE for Oracle
- install 32-bit version of ArcSDE for Oracle
- make sure Oracle client version matches Oracle server version exactly (v10.2.0.4 in this case)
- upgrade geodb to match exact ArcSDE version (v10 SP1)
- follow instructions for Installing Oracle and ArcSDE on separate servers and Creating an ArcSDE service from a remote Windows server
- copy correct versions of required shared libs (st_shapelib.dll, libst_raster_ora.dll) from SDEHOME\bin on SDE server to ORACLE_HOME\bin on Oracle server
- copy correct versions of required shared libs (st_shapelib.dll, libst_raster_ora.dll, sg.dll) from SDEHOME\bin to ORACLE_HOME\bin on SDE server (may not be required but took this step nonetheless)
- If SDE was upgraded, run post-intall to authorize it
- moved SDEHOME(ORA)\bin to front of PATH but just after ORACLE_HOME\bin, which is first
- make sure the following are set in dbinit.sde:
     set LOCAL=<ORACLE NET SERVICE NAME FROM TNSNAMES.ORA>
     set ORACLE_HOME=<ORACLE CLIENT HOME>
     set SDEHOME=<SDEHOME FOR ORACLE>
- make sure ORACLE_SID is NOT set in dbinit.sde

Thanks a ton to Vince A. for all his help.

[VinceAngelo]

To close this up, Direct Connect replaces the 'gsrvr' daemon with a DLL running the equivalent
instructions in a local thread of the client process.  You can use Direct Connect for all command-
line utilities *except* 'sdemon', since this utility manages interaction with the application server
(giomgr process) and never creates a connection.

- V

[jamiehollingsworth]

hi guys,

hoping you are still watching this thread

after two full days i am not able to start sdeservice or even update the database.  i was able to make a direct connection but not able to load any data.

operating system windows 2003 r2
arcsde v10 sp1
oracle 11.1.0.7
all on same machine

feel like i have tried lots of things in the forums but seem to get no where.

not even sure how to explain at this point

thanks in advance
jamie

[BertrandLAURANCIN]

ArcSDE service doesn't start after upgrade ArcSDE 9.3.1 to ArcSDE 10

Connected to instance . . .
ERROR in checking system st_geometry type (1).
Error checking the existence of system tables. Run
the Upgrade Geodatabase tool to install/upgrade system tables (-450).
Refer ArcSDE installation guide for instructions.


ArcSDE 10 and ArcGIS Desktop 10 must be exactly in the same version (service pack).

Bertrand,
Esri France