topic Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch in Data Management Questions

Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotchas?

danan — Mon, 31 Jan 2011 16:48:16 GMT

Are there any "gotchas" when upgrading ArcSDE 9.3.1 from Oracle 10g R2 to Oracle 11g R2? The platform is Redhat Linux. Does the upgraded instance require any changes to pre-existing parameters (e.g. those in 10g)? What about new-to-11g instance parameter? Do any of these require non-default values for SDE to continue to function optimally?

Thanks.

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

danan — Thu, 03 Feb 2011 14:27:59 GMT

How about going from SGA_TARGET + PGA_AGGREGATE_TARGET to the new, combined MEMORY_TARGET parameter (manages memory for both the SGA and PGA with one value)? Would there be any adverse effects to SDE with MEMORY_TARGET?

If SDE 9.3.1 is working for us on 10g R2, do we really need to worry at all about changing instance parameters for SDE 9.3.1 on 11g R2? I don't see anything that jumps out at me in the SDE 9.3.1 on Oracle-specific docs for 10g instance parameters vs 11g instance parameters. Does the typical advice apply? (e.g. read Oracle Corp docs and implement 11g Best Practices).

Trying to prevent any common mistakes people might make in upgrading SDE 9.3.1 from running on a 10g to an 11g instance.

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

RaviKrishna — Mon, 07 Feb 2011 10:55:45 GMT

Oracle 11g has a new Feature called the Deferred Segment Creation, by Default the Parameter is enabled and if you have Featureclasses with no records you'd run into ORA-14223: Deferred segment creation is not supported for this table. Review your data and turn off the parameter if required. Good Luck with the upgrade.

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

danan — Tue, 08 Feb 2011 19:55:27 GMT

Oracle 11g has a new Feature called the Deferred Segment Creation, by Default the Parameter is enabled and if you have Featureclasses with no records you'd run into ORA-14223: Deferred segment creation is not supported for this table. Review your data and turn off the parameter if required. Good Luck with the upgrade.

Thanks Ravi. Is this noted anywhere in Esri documentation?

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

danan — Thu, 24 Mar 2011 09:38:11 GMT

When we upgrade from 10g R2 (10.2.0.3.0) to 11g R2, the instance parameter COMPATIBLE will be set to 10.2.0.3.0. We're seen this in our test instances.

With respect to ArcSDE 9.3.1 and how it functions under 11g, at what point should we alter the COMPATIBLE parameter to the same as the upgraded 11g instance (11.2.0.2.0)? Oracle Corp says moving the COMPATIBLE value forward is an irreversible change.

Any gotchas here? What does Esri advise? "Test all applications" then make the change?

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

danan — Fri, 25 Mar 2011 14:24:09 GMT

When we upgrade from 10g R2 (10.2.0.3.0) to 11g R2, the instance parameter COMPATIBLE will be set to 10.2.0.3.0. We're seen this in our test instances.

With respect to ArcSDE 9.3.1 and how it functions under 11g, at what point should we alter the COMPATIBLE parameter to the same as the upgraded 11g instance (11.2.0.2.0)? Oracle Corp says moving the COMPATIBLE value forward is an irreversible change.

Any gotchas here? What does Esri advise? "Test all applications" then make the change?

We're doing an ArcSDE 9.3.1 10gR2 to 11gR2 Oracle instance upgrade on Monday. Anyone have advice on what to do about the COMPATIBLE setting? It's "just" a dev stage, but we'd like to get it right the first time.

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

danan — Thu, 31 Mar 2011 11:06:11 GMT

Oracle 11g has a new Feature called the Deferred Segment Creation, by Default the Parameter is enabled and if you have Featureclasses with no records you'd run into ORA-14223: Deferred segment creation is not supported for this table. Review your data and turn off the parameter if required. Good Luck with the upgrade.

Hi Ravi,

So far, so good with the upgrades. We've decided to set DEFERRED_SEGMENT_CREATION to FALSE. After our 11g upgrade, the value is TRUE (it's the default as you mentioned). This is another one of those "thanks but no thanks" new features from Oracle like the RECYCLEBIN at 10g. Both items should not, in my opinion, be enabled by default. Nice features to have if you want them. Not so nice to have them operating automatically after an upgrade.

But still, I'm curious. When would error ORA-14223 manifest itself? I haven't heard any complaints from users that creating a new feature class is throwing any errors. Would the error only manifest itself if someone tries to import a feature class shell, e.g. do an XML import of the FC structure only (no data). Has anyone here seen this error after upgrading to 11g?

I don't like that, out-of-the-box, any user with CREATE TABLE privs can seemingly create table objects in tablespaces they have no quota on. Errors are only found later when a user tries to perform an INSERT on such a table. Don't think this is the same Oracle errror you referenced. This alone is reason enough to set deferred_segment_creation to FALSE.

http://tkyte.blogspot.com/2011/02/deferred-segment-creation.html

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

AnthonyScilingo — Thu, 16 Jun 2011 20:01:08 GMT

Hi Ravi,

So far, so good with the upgrades. We've decided to set DEFERRED_SEGMENT_CREATION to FALSE. After our 11g upgrade, the value is TRUE (it's the default as you mentioned). This is another one of those "thanks but no thanks" new features from Oracle like the RECYCLEBIN at 10g. Both items should not, in my opinion, be enabled by default. Nice features to have if you want them. Not so nice to have them operating automatically after an upgrade.

But still, I'm curious. When would error ORA-14223 manifest itself? I haven't heard any complaints from users that creating a new feature class is throwing any errors. Would the error only manifest itself if someone tries to import a feature class shell, e.g. do an XML import of the FC structure only (no data). Has anyone here seen this error after upgrading to 11g?

I don't like that, out-of-the-box, any user with CREATE TABLE privs can seemingly create table objects in tablespaces they have no quota on. Errors are only found later when a user tries to perform an INSERT on such a table. Don't think this is the same Oracle errror you referenced. This alone is reason enough to set deferred_segment_creation to FALSE.

http://tkyte.blogspot.com/2011/02/deferred-segment-creation.html

Hi Dana, we're about to do the same here within the next few weeks and I'd like to know how this transition went for you? One thing we've had much trouble with using 10g and ArcSDE 9.3.1 is the service packs seem to kill the ArcSDE service in Windows, I don't know if this has been a problem for you in Linux. Did you have to re-install the ArcSDE service 9.3.1 for Oracle 11G or did the existing version of ArcSDE 9.3.1 for 10G continue working after upgrading your database to 11g?
Thanks

Anthony

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

danan — Mon, 27 Jun 2011 13:05:33 GMT

Hi Dana, we're about to do the same here within the next few weeks and I'd like to know how this transition went for you? One thing we've had much trouble with using 10g and ArcSDE 9.3.1 is the service packs seem to kill the ArcSDE service in Windows, I don't know if this has been a problem for you in Linux. Did you have to re-install the ArcSDE service 9.3.1 for Oracle 11G or did the existing version of ArcSDE 9.3.1 for 10G continue working after upgrading your database to 11g?
Thanks

Anthony

Since we use Direct Connect exclusively, we only have an ArcSDE App Server installed on a virtual server for the exclusive purpose of performing upgrades. The ArcSDE App Server is on a Windows 2008 box. Don't believe we've actually deployed any services there. I don't touch that component. Our Oracle instance is on RedHat Linux.

We did have a few strange issues post-upgrade to ArcSDE 9.3.1 on 11g. That came when applying ArcSDE 9.3.1 SP2.

1) During the SP2 upgrade process, sdesetup -o upgrade deleted all our logfile tables, e.g. the SDE_LOGPOOL_<integer>. We had to recreate those manually by tweaking the number of logfiles with the sdeconfig command. Very weird. The SDE_LOGFILE_POOL table was retained but was empty after the upgrade. Again, tweaking the # of logfiles using sdeconfig also repopulated this table.

2) ArcSDE SP2 is supposed to include the "disassociate statistics" fix to get around the Full Table Scan bug (NIM042325) when ST_GEOMETRY is the data storage type. Although the log file reported this occurring, it didn't take. So we had to manually disassociate stats.

NIM042325 - Slow performance (full table scan) when statistics present in SDE.ST_GEOMETRY_INDEX table with ST_Geometry

You can execute the following to see where you stand re: this issue:

select object_owner, object_name, object_type, statstype_schema, statstype_name, maintenance_type from dba_associations where object_owner = 'SDE'

I believe the above query should return 3 records and not 4 if stats have been successfully disassociated.

Here's the KB article discussing the performance fix:

http://resources.arcgis.com/content/kbase?fa=articleShow&d=38019

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

danan — Mon, 27 Jun 2011 14:11:51 GMT

Hi Dana, we're about to do the same here within the next few weeks and I'd like to know how this transition went for you? One thing we've had much trouble with using 10g and ArcSDE 9.3.1 is the service packs seem to kill the ArcSDE service in Windows, I don't know if this has been a problem for you in Linux. Did you have to re-install the ArcSDE service 9.3.1 for Oracle 11G or did the existing version of ArcSDE 9.3.1 for 10G continue working after upgrading your database to 11g?
Thanks
Anthony

Another thing to be mindful of when going from 10g to 11g is that, out of the box, any new or changed passwords become case sensitive. Set the following parameter to FALSE if you don't want passwords to be case sensitive:

sql> show parameter case

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE

However, even with this value set to TRUE, passwords that haven't been changed since an upgrade to 11g will still be honored in a case sensitive fashion.

There's a new column in DBA_USERS called PASSWORD_VERSIONS. If the value says "10G", the user's password will be treated as case insensitive. If the value says "10G 11G" then the user's password will be case sensitive.

For more details see, for example:
http://www.oracle-base.com/articles/11g/CaseSensitivePasswords_11gR1.php

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

IvanBrown — Wed, 25 Jul 2012 19:06:29 GMT

I am trying to identify the general sequence of steps that occurs when upgrading Oracle only (leaving ArcSDE at 9.3.1 while upgrading Oracle from 10g to 11g). I looked at the SDE command reference and elsewhere. I haven't found anything that explicitly states how this is done. I am assuming that you:

Stop the SDE service and block database connections.

Run whatever you get from Oracle to upgrade the RDBMS to 11g.

Maybe run sdesetup -o upgrade so that the geodatabase knows its Oracle version and gets any needed RDBMS changes, such as underlying stored procedures?

Unblock the DBMS and start the SDE service?

Am I on track?

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

VinceAngelo — Wed, 25 Jul 2012 20:59:30 GMT

Yes, on track, though you might need:
3.3 reconfigure the listener and tnsnames.ora for the new ST_GEOMETRY libraries

and possibly
3.6 Debug the incorrect ST_GEOMETRY library references

Then there's:
5 update all your Direct Connect connection files to use sde:oracle11g

Keep in mind that 9.3.1 is only certified with 64-bit 11.2.0.1, so you may need to
puzzle out library search order issues

- V

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

VaL — Mon, 19 Nov 2012 09:54:27 GMT

Hi all,

I have similar issue to this one.
I will need to upgrade both sde and oracle
From
Sde 10 to sde 10.1
oracle 10g (10.0.2...) to oracle 11g r2

Is there a step by step instruction what is getting updated first?
Or another topic on that scenario?

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

JacobRoderick — Mon, 19 Nov 2012 14:21:30 GMT

We're sort of late to the game -- we have 9.3.1 installed with Oracle 10g and need to upgrade to 11g ASAP.
I could really use step-by-step instructions. ivang does not mention actually installing the 11g executables in his list of steps, but I'm assuming it should be done. They would just replace the old 10g ones in the sdeexe93 directory? Also, does the post-install need to be run?
Regarding vangelo's reference to incorrect ST_GEOMETRY libraries? Don't understand this.

Re: Upgrading ArcSDE 9.3.1 from Oracle 10g R2 to an Oracle 11g R2 instance--any gotch

BirajaNayak — Thu, 29 Nov 2012 17:18:00 GMT

Hi Anna,

As I understand you wanted to upgrade oracle from 10g to 11g for sde 9.3.1.

Do the following steps ( Assuming you have upgraded oracle from 10g to 11g):

1. Delete current sdehome folder.
2. install sde 9.3.1 for 11g.
3. Make the changes to environment variable like sdehome path, oraclehome path etc.
4. run sdesetup -o upgrade command for sde9.3.1 for oracle11g.
5. start sde service using sdemon -o start

Hope this helps.

Regards,
Biraja