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

danan · ‎01-31-2011

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.

danan · ‎02-03-2011

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.

RaviKrishna · ‎02-07-2011

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.

danan · ‎02-08-2011

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?

danan · ‎03-24-2011

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?

danan · ‎03-25-2011

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.

danan · ‎03-31-2011

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

AnthonyScilingo · ‎06-16-2011

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

danan · ‎06-27-2011

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

danan · ‎06-27-2011

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