topic Re: SDO_GEORASTER BUG on Oracle 11g R2 in ArcGIS Enterprise Questions

SDO_GEORASTER BUG on Oracle 11g R2

Jean-BernardGariépy — Fri, 23 Nov 2012 16:29:48 GMT

Hi!

Is there anyone that was able to reproduce the following bug ?

http://support.esri.com/en/knowledgebase/techarticles/detail/38940

I tried using a 10.0 ArcGIS Desktop Client and a 10.1 ArcGIS Desktop Client on a Oracle 11g R2 with all the appropriate keywords set in DBTUNE.

A simple copy/paste of a raster stored as a BLOB into an SDO_GeoRaster type make ArcCatalogue to freeze indefinitely (more than 1h) doing nothing. No error message.

Also, I haven't found any post on the web regarding this bug.

We don't mind calling Oracle for this bug... if we can manage to reproduce it.
Any clue on how to reproduce this bug would be greatly apprciated.

Re: SDO_GEORASTER BUG on Oracle 11g R2

MarcoBoeringa — Sat, 24 Nov 2012 13:15:58 GMT

A simple copy/paste of a raster stored as a BLOB into an SDO_GeoRaster type make ArcCatalogue to freeze indefinitely (more than 1h) doing nothing. No error message.

Although you are likely to have hit that particular Oracle bug in the link, there still remains the question whether the process really failed. The copy and paste is probably mostly a server task, so seeing ArcCatalog idle on a local machine, is no clear indication the process really failed, it may just take an excessive amount of time to process the raster copy operation on the Oracle server. The fact you don't receive any error message in ArcCatalog, may also be a small indication the process is actually continuing in the background. Since raster datasets can be extremely big, operations on them can be time consuming, depending on the operation. Have you made absolutely sure there is no activity related to the copy & paste on the server? Oracle probably provides you with enough tools and logging to get a better idea of what is going on on the server.

Sad to see Oracle refusing to tackle such an important issue and demanding a "customer business case" before even starting a move towards a solution.

Re: SDO_GEORASTER BUG on Oracle 11g R2

Jean-BernardGariépy — Mon, 26 Nov 2012 14:46:41 GMT

The copy and paste is probably mostly a server task.

Unfortunatly, to copy/paste a raster within the same Database ArcCatalog needs to read the file through the network on the client machine and send it back to the server. I agree that it would be much faster if ArcCatalog could acheive the same task only on the server. Even better, it would be nice if ESRI could release an SQL API to manage spatial features from the RDBMS. But I'm going away from the question here...

To copy the same raster from BLOB into a BLOB with a different name, it takes about 1 minute. Also, by looking at the network card activity you can clearly see that ArcCatalog is downloading the raster from the server and pushing it back. Also, you can look at your CPU activity and it clearly tells you that ArcCatalog is working on it.

When copying from BLOB to SDO_Geometry, for 1h+, ArcCatalog never even tried to download the file from the server (no network activity what so ever) and no CPU activity.

It clearly fails at the task, without any error message.

On the Oracle Server, my DBA tells me that my session is open, but inactive. I'll ask him to check the logs to see if there is any error message generated.

Finally, there is a Workaround on MetaLink which we cannot apply ourself... maybe ESRI could look at it ?

XmlLoadDom fails to parse and load an XML document when the OCI session is
created with
OCIEnvNlsCreate function. But the same works fine when session is created
with OCIEnvCreate function.

Re: SDO_GEORASTER BUG on Oracle 11g R2

Jean-BernardGariépy — Mon, 26 Nov 2012 16:16:31 GMT

This time I got this Error popup !!

[ATTACH=CONFIG]19527[/ATTACH]

Re: SDO_GEORASTER BUG on Oracle 11g R2

MarcoBoeringa — Mon, 26 Nov 2012 17:56:27 GMT

Unfortunatly, to copy/paste a raster within the same Database ArcCatalog needs to read the file through the network on the client machine and send it back to the server. I agree that it would be much faster if ArcCatalog could acheive the same task only on the server.

Do you have a Direct Connect install with Oracle Client installed on your local ArcGIS client computer, or do you run an ArcSDE/ArcGIS Server service on a server?

Even better, it would be nice if ESRI could release an SQL API to manage spatial features from the RDBMS. But I'm going away from the question here...

ESRI more or less already did that, see the following links:
ArcSDE SDK 10 C and Java API
and
ArcGIS Spatial Data Server REST API

Re: SDO_GEORASTER BUG on Oracle 11g R2

Jean-BernardGariépy — Mon, 26 Nov 2012 18:16:33 GMT

ESRI more or less already did that, see the following links:
ArcSDE SDK 10 C and Java API
and
ArcGIS Spatial Data Server REST API

The tests are made using the Dirrect Connect with Oracle 11g client installed on the client computer.

Those API are great at what they do, but as for using them to manage data in a RDBMS sounds like taking an airplane when you can actually bike to work 😉

Re: SDO_GEORASTER BUG on Oracle 11g R2

MarcoBoeringa — Mon, 26 Nov 2012 18:16:48 GMT

Don't know if it is in anyway related nor of any help, but I saw this old thread on an OTN forum related to the same "OCIEnvNlsCreate" function and issues with it in PHP:

https://forums.oracle.com/forums/thread.jspa?threadID=486961

Re: SDO_GEORASTER BUG on Oracle 11g R2

MarcoBoeringa — Mon, 26 Nov 2012 18:27:43 GMT

The tests are made using the Direct Connect with Oracle 11g client installed on the client computer.

I wonder if the same issue happens with a 3-tier ArcSDE Server install...

Those API are great at what they do, but as for using them to manage data in a RDBMS sounds like taking an airplane when you can actually bike to work 😉

Well, depends, the ride with the bike may turn out cycling through a giant labyrinth and getting lost if you attempted to manage and maintain the full ESRI geodatabase functionality as well (like versioning, topology, geometric networks, Feature Dataset/Class hierarchy) without using these ESRI API's... :eek:

Better to catch the plain to get there in a straight line 😉

Re: SDO_GEORASTER BUG on Oracle 11g R2

Jean-BernardGariépy — Mon, 26 Nov 2012 18:30:22 GMT

Don't know if it is in anyway related nor of any help, but I saw this old thread on an OTN forum related to the same "OCIEnvNlsCreate" function and issues with it in PHP:

https://forums.oracle.com/forums/thread.jspa?threadID=486961

National Language Support do seems to be the issue as also reported by the Metalink Workaround posted earlier. If a simple environment variable could solve the problem!!! that would be amazing !!

Re: SDO_GEORASTER BUG on Oracle 11g R2

Jean-BernardGariépy — Wed, 12 Dec 2012 13:30:16 GMT

I've solved the problems.

We are now able to insert data into SDO_GEORASTER without any problem.
We've never encountered the following but as describe by ESRI :
http://support.esri.com/en/knowledgebase/techarticles/detail/38940

The first problem was that our Oracle SDE Schema was originally created with ArcGIS 10.0. So we've setup a new database with 10.1 SDE Schema.

Second, the regionnal settings of my ArcGIS Desktop computer was French Canadian. So ArcGIS was inserting comas as decimal separators in the raster metadata.
I've found this by using the following to SQL commands:

SDO_GEOR.validateGeoRaster()
SDO_GEOR.schemaValidate()

We are now able to insert data into SDO_GEORASTER using the following geoprocessing tool:
Data Management Tools => Raster => Raster Dataset => Copy Raster

Now we're having other issues bulding the pyramids using the SQL statement SDO_GEOR.generatePyramid() because ArcGIS doesn't assign any SRID to the raster 😕 I will start a new thread for this bug soon as I trouble shoot a bit more.

Re: SDO_GEORASTER BUG on Oracle 11g R2

MarcoBoeringa — Thu, 13 Dec 2012 19:32:21 GMT

Good to hear you made some head way solving the problems. These issues can be tire-some to the point of wanting to give up when debugging / solving (which isn't an option in a professional working environment...).

Re: SDO_GEORASTER BUG on Oracle 11g R2

Jean-BernardGariépy — Thu, 13 Dec 2012 19:48:12 GMT

Thank 😉

Now the issue is the accuracy of article #38940.
http://support.esri.com/en/knowledgebase/techarticles/detail/38940

We never experienced the bug mentionned in that article!

Also, in the "Known limits of using SDO_GEORASTER with a geodatabase" section of the following ArcGIS resources page http://resources.arcgis.com/en/help/main/10.1/index.html#//002n0000006z000000

I am not sure if it is appropriate to say that "You cannot use SDO_GEORASTER storage in an Oracle 11g R2 database as a result of Oracle bug 12537431."

Sure enough oracle bug #12537431 - XMLLOADDOM FAILS WITH LPX-00019 WHEN SESSION IS CREATED WITH OCIENVNLSCREATE is still active on Oracle's Metalink website, but on our side it does not prevent us from using SDO_GEORASTER.

My original post was asking if anyone ever experienced the problem mentionned in Article 38940.
On the web it looks like I'm the only one that tried to reprocude the bug which we where not to do in ArcGIS 10.1.

Does anyone on the Internet ever experience that bug with SDO_GEORASTER in Oracle 11g R2 ??

Re: SDO_GEORASTER BUG on Oracle 11g R2

MarcoBoeringa — Thu, 13 Dec 2012 20:05:03 GMT

Second, the regional settings of my ArcGIS Desktop computer was French Canadian. So ArcGIS was inserting comas as decimal separators in the raster metadata.
I've found this by using the following to SQL commands:

SDO_GEOR.validateGeoRaster()
SDO_GEOR.schemaValidate()

We are now able to insert data into SDO_GEORASTER using the following geoprocessing tool:
Data Management Tools => Raster => Raster Dataset => Copy Raster

Sounds like an issue / bug that may be worth reporting to ESRI, if you haven't done so, nor some ESRI folks have read this. That is, assuming SDO_GEORASTER always need decimal point. (IDK)

I am sure you read it all, but this Oracle link may be useful to some concerning SDO_GEORASTER:

GeoRaster Overview and Concepts

And this link concerning the georeferencing and SRID (Oracle speaks of SRS):

Georeferencing GeoRaster Objects

Now we're having other issues bulding the pyramids using the SQL statement SDO_GEOR.generatePyramid() because ArcGIS doesn't assign any SRID to the raster 😕 I will start a new thread for this bug soon as I trouble shoot a bit more.

Seems you should be able to read in a simple ESRI worldfile to correct the issue you describe of a non-defined SRID/SRS using the "SDO_GEOR.importFrom" command in combination with setting the SRID via the "SDO_GEOR.setModelSRID" command, see link above.

Re: SDO_GEORASTER BUG on Oracle 11g R2

Jean-BernardGariépy — Fri, 14 Dec 2012 13:04:43 GMT

ESRI has been assisting me with this bug and I just got an answer from ESRI.

I looks like I've put the finger on the problem with my regional setting bug... The work around would be to change the regional settings to English!

Regarding the Oracle bug, it actually looks like Oracle has not adequately updated their bug database. Upon further investigation on our bug (NIM069549) that references the Oracle bug, there is an activity logged by one of the Esri Inc support staff. He mentions that he spoke to Oracle in May 2012, and that Oracle has actually closed this Oracle incident (12537431) as NOT a bug. This analyst says that the reason given is because OCIEnvNlsCreate uses the national language setting of the computer (Nls = National Language Setting). OCIEnvNlsCreate is used when creating an sdo_georaster and the failure occurs when the Oracle xml document is created.

Basically ??? the reason that users can experience this Oracle bug is because of regional language settings in either the O/S or Oracle.

We have spoken to Esri Inc and have requested that the following be updated to reflect this finding from Oracle:

NIM069549 -
http://support.esri.com/en/bugs/nimbus/TklNMDY5NTQ5
NOTE: have requested that the alternate solution be changed to state that it can be caused by regional language settings. Also, reason for rejection needs to be changed accordingly

Error: An Oracle LPX-0019 error occurs when inserting data into an SDO_GEORASTER field -http://support.esri.com/en/knowledgebase/techarticles/detail/38940
NOTE: have requested that the solution/workaround be changed to state that it can be caused by regional language settings. Suggested to try changing to English language setting.

ArcGIS and the Oracle Spatial raster type - http://resources.arcgis.com/en/help/main/10.1/index.html#//002n0000006z000000
NOTE: this is the article with the known limitations at the bottom.

Since metadata is in XML format, it is quite possible that the commas you were getting with the French language setting were directly related to this issue. The Oracle XmlLoadDom function returns the erorr, so you will see this error in Oracle, not in ArcMap.

Re: SDO_GEORASTER BUG on Oracle 11g R2

MarcoBoeringa — Fri, 14 Dec 2012 18:21:54 GMT

This still leaves the question where the language "bug" is. Either ESRI or Oracle is not taking into account regional language settings during the insertion of the XML SDO_GEORASTER "metadata", it's starting to look like ESRI, but the call is still out... Interestingly, the OCI referred in the function call name causing the issues, relates to the Oracle Call Interface (OCI), as stated here:

http://www.oracle.com/technetwork/database/features/oci/index.html

As Oracle writes:

"Oracle Call Interface (OCI) is the most comprehensive, high performance, native 'C' language based interface to the Oracle Database that exposes the full power of the Oracle Database. OCI provides the foundation on which various language specific interfaces such as Oracle JDBC-OCI, ODP.Net, Oracle Precompilers, Oracle ODBC and Oracle C++ Call Interface (OCCI) drivers are built."

Reading that page, it seems almost certain that ESRI is using the OCI to support all of ArcSDE's functions related to storage in Oracle. Ah, well, Vince Angelo will chime in I guess... and beat me to death with the true technical details if I have it wrong 😉

Re: SDO_GEORASTER BUG on Oracle 11g R2

MarcoBoeringa — Sun, 16 Dec 2012 10:06:17 GMT

A few addition to this thread:

I noticed the old 9.x ArcSDE Configuration and Tuning Guide for Oracle has chapter 5 dedicated to National Language Support and possible settings needed to be made on server/client:

ArcSDE Configuration and Tuning Guide for Oracle

Don't know how relevant it still is for the latest 10.x editions. Here is a 10.1 Help page though also referring to language setting in case of XML columns in Oracle:

Language settings for ArcSDE XML columns in geodatabases in Oracle

Re: SDO_GEORASTER BUG on Oracle 11g R2

Joeysmith — Mon, 25 Mar 2013 08:15:25 GMT

you can look at your CPU activity and it clearly tells you that ArcCatalog is working on it.