What impact can be on ArcGIS after Oracle Upgrade?

772
13
10-08-2013 11:16 PM
bulatmakhmutov
New Contributor
Hi everybody,

I have question,
What impact can be on ArcGIS after Oracle Upgrade?

we have architecture:
ArcGIS 9.3.1 system is installed on Microsoft Windows Server 2003 R2 (32 bit)
with Database: Oracle 10.2.0.3.0.

we are going to make upgrade of oracle on 11.2.0.1.0 version.

So question is,
if there will be any impact  on ArcGIS system?
if it will work uncorrectly?

If yes,
please advice, what steps must be done?
where we can read necessary documentation?
if that operation (upgrade of oracle) involve a lot of changes in ArcGIS?


With regards!
0 Kudos
13 Replies
NidhinKarthikeyan
Occasional Contributor III
Test the new version on a development or test server to ensure that it works with all your client applications.

ArcSDE Oracle Database Requirements

Preparing to Upgrade Oracle Database
Upgrading to the New Release of Oracle Database

It would be a good idea to contact ESRI Support to confirm whether there is any bugs specific to ArcSDE 9.3.1 on Oracle 11.2.0.1.
0 Kudos
bulatmakhmutov
New Contributor
Let me reformulate the question,
what steps must be done in ArcGIS system side, in order Oracle upgrade was done smoothely?

In result point, we need upgraded Oracle RDBMS with working ArcGIS 9.3.1 system.

With regards!
0 Kudos
NidhinKarthikeyan
Occasional Contributor III
Create a complete backup of the database.


1.Stop the SDE service and disconnect database connections.
2.Follow steps in Oracle documentation to upgrade the RDBMS to 11.2.0.1.
3.Delete current sdehome folder.
4.Install ArcSDE 9.3.1 for Oracle 11g
5.Change environment variable (sde home/Oracle home)
6.Run sdesetup -o upgrade so that the geodatabase knows its Oracle version and gets any needed RDBMS changes, such as underlying stored procedures
7.Reconfigure the listener and tnsnames.ora for the new ST_GEOMETRY libraries
8.Debug the incorrect ST_GEOMETRY library references
9.Unblock the DBMS and start the SDE service
10.Update all your Direct Connect connection files (Syntax-11g)

If you have customized any of the files in the etc directory of SDEHOME, make a copy of these files.
0 Kudos
bulatmakhmutov
New Contributor
hi Nidhinkn,
thanks for the answer.

But i am curious,

step 4)
4.Install ArcSDE 9.3.1 for Oracle 11g

question:
wny it is needed to install again?
it is already installed and configured.


step 6)
Run sdesetup -o upgrade so that the geodatabase knows its Oracle version and gets any needed RDBMS changes, such as underlying stored procedures

question:
where to run it?
the command - 'sdesetup -o upgrade'

step 😎
8.Debug the incorrect ST_GEOMETRY library references

question:
how to debug it (incorrect ST_GEOMETRY library references)?

So,
where can i read in detail all listed bullets written by you in ESRI documentation?
did you do upgrade yourself?
don't I lose ArcGIS data (configuration settings, data) after making those actions?

With regards!
0 Kudos
NidhinKarthikeyan
Occasional Contributor III
- The one which is already installed will be ArcSDE for Oracle 10g. Since you are going to upgrade to Oracle 11g, you have to install   ArcSDE for Oracle 11g.

-Run sdesetup -o upgrade on command prompt. Refer ArcSDE command reference.


Test the new version on a test server.
0 Kudos
VinceAngelo
Esri Esteemed Contributor
Note that ArcSDE 9.3.1 did not support Oracle 11gR2 until the SP2, so step 4
above implicitly includes "Apply Service Pack 2 to ArcSDE 9.3.1 for Oracle 11g".

Similarly, the upgrade step implicitly includes validating that your instance
doesn't have any orphaned records (e.g., tables dropped with SQL that still
have SDE or GDB_* references).

There's a big enough difference between 10gR2 and 11gR2 that you should
at least consider copying the data into a new instance on a new server, instead
of doing an in-situ upgrade.  This is also an opportunity to defragment your
data tables and various other performance improvement chores.

Finally, keep in mind that end-of-life for ArcGIS 9.3.1 is also rapidly approaching
(it will be retired 31-Dec-2013).

- V
0 Kudos
bulatmakhmutov
New Contributor
Can't we do on-site upgrade?
0 Kudos
MichaelVolz
Esteemed Contributor
bulat:

I would also be concerned with the Oracle Client software that you have on each computer where ArcGIS Desktop installed.  Are you using the direct connection methodology to connect to the Oracle database?  If so, are you going to upgrade each client to Oracle 11g Client as well.  If this was the methodology that you used, then the 10g connections will be embedded in all your mxds.  You will need to update these connections to 11g connections.  You can have endusers perform this task manually or you can script it out in VB.NET, VBA, or python (not sure how good the python scripting is at 9 as many new methods were added at 10).

This is a task that my organization is currently going through.  We have over 200,000 Oracle SDE connections that need to be updated from 10g to 11g to follow ESRI's Best Practices of having the Oracle Client at the same version as the database itself.  I have an idea on the ideas website (ideas.arcgis.com - Option of Using UNC Path for SDE Connection Files) where you would have the option of embedding the Oracle SDE connection information in the mxd (the current practice where you need to fix all these connections) or have ArcMap go to the .sde connection files outside the mxd to connect to the Oracle database (in this scenario you would just need to update the .sde files instead of all the mxds - for my organization this would be the difference between updating 200 .sde files compared to 200,000 sde connections in over 40,000 mxds).

If you like the below idea, please promote it on the ideas website.
0 Kudos
MichaelVolz
Esteemed Contributor
Vince:

You stated "Finally, keep in mind that end-of-life for ArcGIS 9.3.1 is also rapidly approaching
(it will be retired 31-Dec 2013)."

What does that mean in terms of support for this product?
0 Kudos