topic Re: UpgradeGDB : Could not update server tables and stored procedures in Geodatabase Questions

UpgradeGDB : Could not update server tables and stored procedures

MarkChilcott — Thu, 18 Aug 2016 02:47:18 GMT

Hi Peoples,

A quick note on an upgrade issue I just came across. May help others out in the future.

We are doing an upgrade to one of our ArcSDE instances. This has been bought about by the need to do an Oracle upgrade. Given the number of versions we are behind, we have had to create a new virtual machine on an updated OS, with a new instance of Oracle at 12c, then export the entire database from the old instance to the new instance. Once the new Oracle database came across, then do the ArcSDE upgrade from 10.1 to 10.3.1. So a fair bit going on here.

We did this all last week on a test instance – and went swimmingly well.

So to today.

To connect to the new Oracle instance, there are a few option in relation to the TNSnames.ora file.

Option A
We can add a new entry for the new database
Option B
we can use the same name in the TNS, but alter the server details.
Option B in theory means you can make one change to the TNSnames, and then all your connections from ArcGIS, FME, SQL Developer etc just continue to work. No need to alter all your projects with the new connection.

So we went with option B.

I was able to connect to ArcSDE as the SDE user, and to perform the per-requisite checks. So all looked good in relation to connections.

When the upgrade ran : this was the result:

Executing: UpgradeGDB "Database Connections\rmcdb.dpipwe.tas.gov.au@sde@DC.sde" PREREQUISITE_CHECK UPGRADE

Start Time: Thu Aug 18 08:57:52 2016

Paused the database for upgrade.

There are no other active connections.

User has privileges required to upgrade.

Connected RDBMS instance is setup for ST_GEOMETRY configuration.

Running Pre-Requisite check (C:\Users\mchilcott\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\rmcdb.dpipwe.tas.gov.au@sde@DC.sde).

Instance supports XML type.

Updating server tables and stored procedures.

Could not update server tables and stored procedures.

Refer to upgrade logfiles for more details:

C:\Users\mchilcott\AppData\Local\Esri\Desktop10.3\GDBUpgrade.log

(null)

Unpaused the database.

Failed to execute (UpgradeGDB).

Failed at Thu Aug 18 08:57:52 2016 (Elapsed Time: 0.28 seconds)

Nothing in the UpgradeGDB.log

Nothing in the sdesetup.log

These gave some hint as to the issue:

http://support.esri.com/en/bugs/nimbus/TklNMDg0OTg3

http://support.esri.com/technical-article/000011653

Going back over the TNSnames.ora file, I found an extra bracket.

Also, I am not sure if the TNS is case dependent, as we altered rmcdb.dpipwe.tas.gov.au > RMCDB.DPIPWE.TAS.GOV.AU

Fixing the tnsnames.ora file, and creating a new ArcMap database connection - and it worked.

It looks to me there is a bug in here somewhere.

If the TNS was incorrect, or the connection file in SDE is slightly different –then the connection should have failed, or the per-requisite should have failed. To have the connection succeed, and the per-requisite succeed, only to have the upgrade fail is strange. This leads you to think it is a server issue, rather than a client issue.

Bottom line - if you see this, then check your TNSnames.ora file, and recreate your connections. Maybe even attempt the upgrade from another computer.

Re: UpgradeGDB : Could not update server tables and stored procedures

ChristianWells — Thu, 18 Aug 2016 18:41:45 GMT

Can you provide a sample of the TNS file that caused the issue? I would be interested to see if this can be reproduced on any Oracle geodatabase.

Re: UpgradeGDB : Could not update server tables and stored procedures

MarkChilcott — Thu, 18 Aug 2016 22:26:26 GMT

Hi Christian,

The tnsnames.ora with the extra bracket.

RMCDB.DPIPWE.TAS.GOV.AU =
   (DESCRIPTION =
     (LOAD_BALANCE = off)
     (ADDRESS = (PROTOCOL = TCP)(HOST = box1)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = box2)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = SIDName)
     )
   )
)

Cheers,

Mark

Re: UpgradeGDB : Could not update server tables and stored procedures

AlexanderBrown5 — Tue, 23 Aug 2016 20:42:25 GMT

Mark,

I have experienced this type of problem before. The solution here at the end of this post helped me upgrade from 10.1 SDE to 10.3.1 when I worked at the City of Philadelphia.

Problem in Upgrading Geodatabase from 9.3.1 to 10.2

If it is possible, I would simplify your TNS alias to one word:

RMCDB =
   (DESCRIPTION =
     (LOAD_BALANCE = off)
     (ADDRESS = (PROTOCOL = TCP)(HOST = rmcdb1.dpipwe.tas.gov.au)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST = rmcdb2.dpipwe.tas.gov.au)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = rmcdb.dpipwe.tas.gov.au)
     )
   )
)

This wouldn't affect your databases, but it would make it easier to connect:

Also, is your database on both servers? I see you have two addresses in your TNS names file. Do you have two databases (production/staging), does the second refer to a fail over?

I would connect using the instructions from the link above. If possible limit the TNS connection to your production database server only.

Let me know if this helps.

~Alex

Re: UpgradeGDB : Could not update server tables and stored procedures

MarkChilcott — Tue, 23 Aug 2016 22:24:13 GMT

Hi Alexander,

The two hosts are due to using Oracle Data Guard with the Primary and the Fail Over servers.

We have a Dev, Test and Prod environment - the Test is basically more a staging set up that is a mirror install of Prod using virtual machines etc.

Cheers,

Mark

Re: UpgradeGDB : Could not update server tables and stored procedures

AlexanderBrown5 — Sun, 12 Dec 2021 09:23:47 GMT

Mark,

Okay that clarifies things, I thought it was referring to your fail over but wanted to make sure. I suggest for future upgrades:

1. Update your TNSNAMES.ora to something simpler, directly against your production database. Since you have DataGuard, once you upgrade your production database, the upgrade will push to your DataGuard on your next scheduled backup. I would double check with your Oracle DBA for exact methodology, could be different than how Philadelphia utilized DataGuard.

RMCDB =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = rmcdb1.dpipwe.tas.gov.au)(PORT = 1521))
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = rmcdb.dpipwe.tas.gov.au)
     )
   )
  )‍‍‍‍‍‍‍‍‍
‍‍‍‍‍‍‍‍‍

2. Create a new ArcCatalog connection utilizing just:

3. Attempt your upgraded

Just for clarification, when you said: "Fixing the tnsnames.ora file, and creating a new ArcMap database connection - and it worked." indicating the upgrade did work for you, right?

~Alex

Re: UpgradeGDB : Could not update server tables and stored procedures

MarkChilcott — Thu, 25 Aug 2016 04:53:59 GMT

Hi Alex,

All good once I fixed the TNS. I just like to place this sort of information into the forum to help other people out should they come across the issue.

Cheers,

Mark

Re: UpgradeGDB : Could not update server tables and stored procedures

JohnAnderson1 — Tue, 01 May 2018 22:14:50 GMT

This problem may occur even if there are no problems with the tnsnames.ora file.

If your tnsnames.ora file is ok, then (as suggested above) deleting and recreating the .sde connection before the upgrade should fix the problem.

It should be noted that older sde connection files store the connection details in a slightly different way - this is probably the cause of the problem. If ArcObjects is used to look at the connection properties of an old .sde connection there will be a parameter called INSTANCE which will store a string with something like: sde:oracle11g:rmcdb.dpipwe.tas.gov.au, whereas .sde connection files created with more recent versions of ArcGIS Desktop will have a parameter called DB_CONNECTION_PROPERTIES that stores the same string. There may also be a few more discrepancies between old and new .sde connections. This problem isn't obvious because newer .sde connections look exactly the same as older legacy connections to a user who is browsing the connections in ArcCatalog. In these situations, recreating all legacy .sde files in ArcCatalog would be a good idea.