nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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?

Vind meer berichten getagd met

Accepted answers

All comments

VinceAngelo

1) The 'DB_Admin_password' is the SDE user password

2) No.

3) In a "Remote Server" configuration, no ArcSDE service is created on the Oracle server.
The DBA can help you make sure that your client install is correct (it should be the same
release as the server [10.2.0.4, in this case]), that your tnsnames.ora is correct (via 'tnsping'),
and that you can successfully connect to the server via SQL*Plus after setting the LOCAL
environment variable (the dbinit.sde should have the same LOCAL [not ORACLE_SID] contents).

4) There certainly is a way to define which port to use -- the "-i <service>" parameter
of 'sdeservice'. You must edit the %SystemRoot%\system32\drivers\etc\services file
to map the mnemonic to a port number. It's not required that all hosts in your network
have the same services file, but it is a good idea. I typically run 15-20 different services
on my networks at home, office and client sites; it just takes a little effort to set up a
naming convention appropriate for the network, then copy/paste the 5151->???? entries
into the services file on each host.

30-sec connection timeouts are generally due to a bad hostname/port combination,
so you want to be sure your LOCAL is pointing to a correct tnsnames.ora entry.

It would probably be best to start an incident with Tech Support so they can walk you
through this configuration (it's tricky the first time, but by the third time you won't even
need the documentation).

- V

ChristinaKepler

Thanks for the help and quick reply Vince -- much appreciated.

I have to respectfully decline to open an incident with tech support since we are a development shop (EDN license only) and pay a premium for each ticket that we open. So we have to preserve those incidents for dire needs only. This issue seems like one that should be solvable with trial and error (and forums 🙂 since the documentation is falling short.

I've tried recreating the service by passing the '-i <port>' since, as you noted, the service name and port # are interchangeable:

sdeservice -o create -p memorial -d ORACLE10G,ORA2 -n -i 5157

I've verified that the %SystemRoot%\system32\drivers\etc\services file has the correct entry:

esri_sde_ora2 5157/tcp #ArcSDE for Oracle

and I corrected the dbinit.sde file after the sdeservice command was run so that the ORACLE_SID entry was commented and the LOCAL entry was added:
___________________________________________________
# Oracle SID for service esri_sde_ora2.
# set ORACLE_SID=ORA2
set LOCAL=ORA2
set ORACLE_HOME=C:\oracle\product\10.2.0\client_1
set SDEHOME=C:\ESRI\ArcGIS\ArcSDE\ora10gexe
___________________________________________________
My Oracle Net service name is 'ORA2' and I can connect via SQLPlus and a SDE direct connection.

When attempting to start the SDE service with the sdemon command:

sdemon -o start -i 5157

I get the error:

Error starting esri_sde_ora2 service(0)
Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and dbinit.sde.

One thing that's confusing to me is that if there is no SDE service created on the remote Oracle server, why is it necessary to have an entry in the services file on that machine that corresponds to the entry on the ArcSDE server? Could that be why the error is occurring?

VinceAngelo

It *isn't* necessary that the Oracle server have a services entry, just good practice
to prevent port collision.

It's probably a mistake to treat service names and ports as if they were interchangable,
especially during service creation (it's often true on the client side, but not on the server).
Try using the service mnemonic for both 'sdeservice -o create' and 'sdemon -o start'.

- V

ChristinaKepler

Thanks for your continued assistance with this Vince.

I tried using the mnemonic for both 'sdeservice -o create' and 'sdemon -o start':

sdeservice -o create -p <password> -d ORACLE10G,ORA2 -n -i esri_sde_ora2
sdemon -o start -i esri_sde_ora2 -p <password>

But am getting the same error:

Error starting esri_sde_ora2 service(0)
Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and dbinit.sde.

I recalled reading somewhere that a udp entry is also required in the Windows services file, so I made that change as well on the SDE server and had our Oracle admin make the change on the Oracle server (also made to SDE services.sde file, but not sure it matters):

esri_sde_ora2 5157/tcp #ArcSDE for Oracle
esri_sde_ora2 5157/udp #ArcSDE for Oracle

Still getting the same error.

Also, not sure if it matters but our Oracle server is Windows 2008 R2, not Windows 2003 as I originally reported. Our SDE server is Windows 2003 SP2 x64.

VinceAngelo

UDP is not used in the ArcSDE client/server connection protocol.

Is your Oracle client 32-bit or 64-bit? Is your ArcSDE install 32-bit or 64-bit?

Does the SDEHOME reported by sdeservice -o list correspond to the ORA2 SDEHOME?

- V

ChristinaKepler

Is your Oracle client 32-bit or 64-bit? Is your ArcSDE install 32-bit or 64-bit?

Does the SDEHOME reported by sdeservice -o list correspond to the ORA2 SDEHOME?

ArcSDE install is v10 SP1 64-bit. Oracle client is 32-bit (v10.2.0.3) which is the only version that enabled a direct connection and the version that I needed to install according to this FAQ. Version 10.2.0.3 of the client is the only 32-bit version that I could find to download on the Oracle Web site (note that Oracle server is v10.2.0.4).

SDEHOME referenced by 'sdeservice -o list' reports 'C:\ESRI\ArcGIS\ArcSDE\ora10gexe\', which is correct. Not sure what is meant by 'ORA2 SDEHOME' -- as specified where? SDEHOME env var on local SDE machine *and* SDEHOME in %SDEHOME%\etc\dbinit.sde both match though.

VinceAngelo

64-bit ArcSDE cannot function with a 32-bit Client (the DLL search fails when
a DLL with the right name but wrong wordsize is found).

It you have a 32-bit Oracle Client for 32-bit ArcGIS, you should be using a
32-bit ArcSDE Server. This isn't a bad thing -- In my testing, 64-bit ArcSDE
did not provide a performance benefit over 32-bit ArcSDE on either 64-bit
Windows 2003 Server or RedHat Linux 4/5 (x64).

The one time I attempted to have both 32-bit and 64-bit Oracle client installs
with both 32-bit and 64-bit ArcSDEs on an x64 2003 host, I toasted my registry
so badly I had to have the virtual machine re-imaged.

Note that ArcGIS 10.1 will be mostly 64-bit, so at that point you'll have more
reason to run 64-bit ArcSDE with a 64-bit Oracle Client.

- V

BTW: You can/should apply the 10.2.0.4 patchset to your 10.2.0.3 client, since
Vince's Second Law of Compatibility says "In a client-server architecture, always
run your clients at least even to the server release" (a little ahead is okay, but
behind is asking for trouble).

PS: If you have multiple ArcSDE application servers running, you really should
have multiple SDEHOME locations.

ChristinaKepler

Vince:

Thanks so much for bearing with me on this...

A few things:

- If the 64-bit ArcSDE can't function w/ a 32-bit Oracle client, I'm confused as to why it's the only configuration that worked for direct connections (?).

- I'll gladly uninstall the 64-bit ArcSDE and install the 32-bit if that will fix my problems. However will that be compatible with the installation of ArcGIS Server on the same machine? It's unclear to me if the ArcGIS Server installation is 32-bit or 64-bit (the installer doesn't differentiate like the SDE installer does, and there doesn't seem to be an easy way to figure it out). Do you think the best course of action at this point is to uninstall ORA SDE 64 and install ORA SDE 32? (What's more, if I have SDE 64 for SQL Server on the same machine, are there any shared DLLs that would hose my installation? If so, then I should also uninstall SQL SDE 64 and install SQL SDE 32?)

- RE: " Does the SDEHOME reported by sdeservice -o list correspond to the ORA2 SDEHOME?
. . .
If you have multiple ArcSDE application servers running, you really should
have multiple SDEHOME locations."

Not sure what is meant by 'ORA2 SDEHOME' -- as specified where?

I think I'm all set, but please let me know if I'm not:
>sdeservice -o list

SDE service Information
--------------------------------------------------------------------
Error querying 5157 service
RDBMS:                  Oracle
Name:                   ArcSde Service(5157)
SDEHOME:                C:\ESRI\ArcGIS\ArcSDE\ora10gexe\
Version:                10.0
Status:

RDBMS:                  Oracle
Name:                   ArcSde Service(esri_sde_ora2)
SDEHOME:                C:\ESRI\ArcGIS\ArcSDE\ora10gexe\
Version:                10.0
Status:                 SERVICE_STOPPED

RDBMS:                  SQLServer
Name:                   ArcSde Service(esri_sde_sqlsvr)
SDEHOME:                C:\ESRI\ArcGIS\ArcSDE\sqlexe
Datasource:             GIS4
SDE_DBA_USER:           sde
Admin_database:         sde
Version:                10.0
Status:                 SERVICE_RUNNING

That instance of 'service(5157)' is certainly curious -- it doesn't show up in my Windows Services.

- What's Vince's *First* Law of Compatibility? 😉

Thanks again.

ChristinaKepler

Two things to add to previous thread (though would appreciate responses to those as well):

- I uninstalled the ORA SDE 64 bit and installed ORA SDE 32 and it installed fine but when attempting to run post-install, got a 'ArcSdeSetupOracle10g.exe - Entry Point Not Found' dialog that said, "The procedure entry point SgShapeGetParametricType could not be loaded in the dynamic link library sg.dll".

Ugh.

I made sure the 64-bit exes were all removed before installing the 32 bit version, and my SDEHOME is correctly set to C:\ESRI\ArcGIS\ArcSDE\ora10gexe\.

- Maybe I can avoid the whole trying to get a 3-tier service-based connection to work if I can just register a spatial view after creating one w/ SQL (i.e. w/o having to run the 'sdelayer �??o register' command).

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.

ora_objects.txt.zip

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

Bertrand_Laurancin

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

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.