topic Re: troubles with arcsde oracle 11g install in Data Management Questions

troubles with arcsde oracle 11g install

jamiehollingsworth — Wed, 25 May 2011 23:48:11 GMT

So it is a long and complicated story but I have oracle 11.1.0.7 (32bit) installed on windows 2003 r2 (32bit) sever. Now i am trying to install arcsde 10 sp1 for oracle 11g (32bit) and after several weeks of trying things only get worse. Currently i can not get passed the connect as oracle sys user in the post install. the error is "Error connecting to Oracle:O contains an incorrect path". I am able to log in as sys user using the SQL/plus. So if someone has time to hold my hand through this i would be very great full.

thanks
jamie

Re: troubles with arcsde oracle 11g install

VinceAngelo — Thu, 26 May 2011 02:30:33 GMT

It would help if you give a *lot* more details on your configuration...
+ What is your ORACLE_HOME?
+ What is your ORACLE_SID?
+ What does listener.ora contain?
+ What does tnsnames.ora contain?
+ What is your SDEHOME?
+ What is in your PATH?
+ Are you using the post-install GUI? If so, what values have you plugged in?
+ Have you tried using the manual post-install proceess?
+ Have you tried executing 'sdesetup -o prerequisite_check'?
+ What is in your dbinit.sde and services.sde files?
+ What is in your sde_setup.log?

It generally takes me five minutes after Oracle install completes to have ArcSDE running.
My process does have some quirks:
+ I *never* install software in a directory with spaces or special characters (e.g., D:\Esri)
+ I copy the install directory to an alternate directory, uniquely named for each instance
(e.g., C:\Esri\sdeora10sp1_mysid) and manually set the SDEHOME, ORACLE_HOME, PATH
and ORACLE_SID appropriately
+ I manually create the SDE tablespace and SDE user, edit the services table with Notepad,
and use the command-line 'sdesetup' and 'sdeservice' utilities to configure ArcSDE in a
command window.

- V

Re: troubles with arcsde oracle 11g install

jamiehollingsworth — Thu, 26 May 2011 16:14:22 GMT

Thanks for taking the time to help out

I have setup similar configurations several times but this one has me stumped

+ What is your ORACLE_HOME?
C:\app\jhollingsworth\product\11.1.0\db_1

+ What is your ORACLE_SID?
gisdata

+ What does listener.ora contain?
# listener.ora Network Configuration File: C:\app\jhollingsworth\product\11.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = anvik)(PORT = 1521))
    )
)

+ What does tnsnames.ora contain?
# tnsnames.ora Network Configuration File: C:\app\jhollingsworth\product\11.1.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

GISDATA =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = anvik)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gisdata)
    )
)

+ What is your SDEHOME?
C:\Program Files\ArcGIS\ArcSDE\ora11gexe

+ What is in your PATH?
C:\app\jhollingsworth\product\11.1.0\client_1\bin;C:\app\jhollingsworth\product\11.1.0\db_1\bin;C:\WINDOWS\System32;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\system32\WBEM;C:\WINDOWS\system32\WindowsPowerShell\v1.0;c:\Program Files\Microsoft SQL Server\90\Tools\binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\app\jhollingsworth\product\11.1.0\db_1\OPatch;C:\Program Files\ArcGIS\ArcSDE\ora11gexe\bin

+ Are you using the post-install GUI? If so, what values have you plugged in?
yes and have not been able to get past entering the sys password -error contains an incorrect path.

+ Have you tried using the manual post-install proceess?
no

+ Have you tried executing 'sdesetup -o prerequisite_check'?
connection to sde failed(0)
Extended error: ORA 12560: TNS:protocol adapter error
error: underlying BDBMS error (-51).
   Error checking geodatabase setup prerequisities.

so then i set ORACLE_SID=gisdata

then i get bad login user(-9)

+ What is in your dbinit.sde and services.sde files?

dbinit is empty

/* $Id: services.sde,v 1.2 1999/01/22 01:01:35 donna Exp $ */
#
# ESRI SDE Remote Protocol
#   Note: uncomment the line below to use ESRI's default port
#
#esri_sde               5151/tcp
#

+ What is in your sde_setup.log?
no setup log can not get past step one of post install

It generally takes me five minutes after Oracle install completes to have ArcSDE running.
My process does have some quirks:

i agree it is very simple most of the time but this time not even sure where to start other than over.

+ I *never* install software in a directory with spaces or special characters (e.g., D:\Esri)
will try but has not been a problem in the past

+ I copy the install directory to an alternate directory, uniquely named for each instance
(e.g., C:\Esri\sdeora10sp1_mysid) and manually set the SDEHOME, ORACLE_HOME, PATH
and ORACLE_SID appropriately

+ I manually create the SDE tablespace and SDE user, edit the services table with Notepad,
and use the command-line 'sdesetup' and 'sdeservice' utilities to configure ArcSDE in a
command window.

have always used the GUI

thanks
jamie

Re: troubles with arcsde oracle 11g install

VinceAngelo — Thu, 26 May 2011 16:48:19 GMT

If "sqlplus sys/password@gisdata as sysdba" doesn't work, there's no hope that the GUI will
work for you.

Try using the manual process. It will break the steps down so you can see what's happening.
In truth, there's no reason to trust the GUI to create tablespaces and users for you, especially
when the commands are so basic. You also need hands-on access to configure ST_GEOMETRY
for remote use (which is a good habit to get into).

There's no reason to have Opatch in the PATH after the patch has been applied; extraneous
PATH entries can only cause trouble in the long run.

I'll often create the service *before* doing 'sdesetup', since it populates the dbinit.sde
with an initial value (which needs to be altered if your server is remote).

Tech Support has a lot more experience walking folks through installs than I do (I've done
so many that I can't break down the individual steps anymore).

- V

Re: troubles with arcsde oracle 11g install

jamiehollingsworth — Thu, 26 May 2011 17:36:02 GMT

so i see

when i go to the start>programs>oracle ora11g_home1>application development>sqlplus and enter "sys/password@gisdata as sysdba" i can log in and everything seems to work just fine

but if i open a command prompt and type "sqlplus sys/password@gisdata as sysdba" it does not work

i get either a ora-12560 or an ora-12154 and i am not able to log in.

thanks
jamie

Re: troubles with arcsde oracle 11g install

VinceAngelo — Thu, 26 May 2011 18:21:47 GMT

The first thing to try is bouncing the listener --
Stop the database instance
Stop the listener
Start the listener
Start the database instance

Then try 'tnsping gisdata' and the sqlplus command-line.

Try explicitly setting ORACLE_HOME and moving the Oracle and ArcSDE directories
to the front of the PATH
set ORACLE_HOME=...
set PATH=%ORACLE_HOME%\bin;%SDEHOME%\bin;%PATH%

It's possible the %ORACLE_HOME%\network\admin\sqlnet.ora authentication setting
is causing issues... Try setting it to NONE

Do you have an Oracle DBA resource to help?

- V

Re: troubles with arcsde oracle 11g install

jamiehollingsworth — Thu, 26 May 2011 21:50:44 GMT

so after weeks of doing and redoing and making what i thought was a mess of things. I seem to have fixed it my problem by deinstalling oracle client.

thanks for the help v

jamie

Re: troubles with arcsde oracle 11g install

VinceAngelo — Thu, 26 May 2011 23:53:01 GMT

Ahh! Two Oracle installs, but only one tnsnames.ora? That drives me batty at times, too.
You also need LOCAL (or TWO_TASK, on Linux) instead of ORACLE_SID if the ORACLE_HOME
is different from the database.

- V