ArcSDE Operating system authentication

Operating system authentication

Release 9.3

http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/operati1778508573.htm

Using Windows-authenticated users or groups in SQL Server

Release 9.3

http://webhelp.esri.com/arcgisserver/9.3/java/geodatabases/using_wi-29016506.htm

Oracle changePerm.sh script in versions 9.2.0.8, Oracle 10g

changePerm.sh is a script that allows users that do not belong to the "dba" group to access and execute files in the ORACLE_HOME directory.


The script is located in the ORACLE_HOME/install directory on Unix and Linux systems (there is no equivalent for Windows). This script ships with Oracle Database versions 9.2.0.8, Oracle 10g.

The changePerm.sh file is no more available nor necessary for Oracle database server 11.1.0.x because in 11g, permissions under the Oracle Home (including "others") are set correctly/relaxed. (oracle note 834626.1)


===================================
Before change:
$ ls -ld $ORACLE_HOME/lib
drwxr-x---   3 oracle   dba         5632 Nov 27 09:30 /fs/oracle/product/10204ee/lib

Running script:

$ cd $ORACLE_HOME/install

$ ls -l

total 964

-rwxr-xr-x   1 oracle   dba        11380 Nov 22  2007 changePerm.sh

-rw-r-----   1 oracle   dba            0 Jun  7  2005 createseed.sh

-rw-r-----   1 oracle   dba            0 Jun  7  2005 createseed1.sh

-rw-r-----   1 oracle   dba          618 Nov 26 14:19 envVars.properties

-rw-r-----   1 oracle   dba          131 Nov 26 13:51 envVars.properties.bak

-rwxr-xr-x   1 oracle   dba           38 Apr 19  2005 install.excl

drwxr-x---   2 oracle   dba          512 Nov 26 11:55 jlib

-rw-r-----   1 oracle   dba       437788 Nov 26 14:19 make.log

-rw-r-----   1 oracle   dba          102 Nov 26 14:19 portlist.ini

-rw-r--r--   1 oracle   dba          124 Nov 26 14:19 readme.txt

-rw-r--r--   1 oracle   dba         7609 Jan 12  2007 restrict.lst

-rwxr-xr-x   1 oracle   dba          822 Nov 26 14:16 rootdeletenode.sh

-rw-r--r--   1 oracle   dba        10031 Nov 26 14:16 rootlocaladd

-rw-r-----   1 oracle   dba            0 Jun  7  2005 seed.log

-rw-r--r--   1 oracle   dba         2808 Jul 14  2005 templocal

drwxr-x---   2 oracle   dba          512 Nov 26 14:19 unix

drwxr-x---   2 oracle   dba          512 Nov 26 14:13 utl

$ ./changePerm.sh

-------------------------------------------------------------------------------

Disclaimer: The purpose of this script is to relax permissions on some of the

files in the database Oracle Home so that all clients can access them.

Please note that Oracle Corporation recommends using the most restrictive file

permissions as possible for your given implementation.  Running this script

should be done only after considering all security ramifications.

-------------------------------------------------------------------------------

-n Do you wish to continue (y/n) [n]:

y

Spooling the error log /tmp/changePerm_err.log...

Finished running the script successfully

After change:

$ ls -ld $ORACLE_HOME/lib
drwxr-xr-x   3 oracle   dba         5632 Nov 27 09:40 /fs/oracle/product/10204ee/lib

===================================
Reference: http://www.orafaq.com/wiki/ChangePerm_sh

ArcCatalog error: Failed to connect the specified server. Server library could not be loaded

When trying to make a direct connect to ArcSDE on 64 bit Solaris, the following error occurred:
Failed to connect ot database. Server library could not be loaded.

There is an ESRI document on this:

Error:  Failed to connect the specified server. Server library could not be loaded

Article ID:	36510
Software:	ArcGIS - ArcEditor 9.2, 9.3, 9.3.1 ArcGIS - ArcInfo 9.2, 9.3, 9.3.1 ArcGIS - ArcView 9.2, 9.3, 9.3.1 ArcSDE 9.2, 9.3, 9.3.1
Platforms:	N/A

Error Message

When using direct connect with ArcSDE on a 64-bit operating system, the following error message is encountered:

"Failed to connect the specified server. Server library could not be loaded."

Cause

Because ArcGIS Desktop is a 32-bit operating system application, it cannot work with applications created with 64-bit operating system libraries.

Solution or Workaround

Uninstall the Oracle Net Client 64-bit version and install the Oracle Net Client 32-bit version on the 64-bit operating system. Also, ensure the Oracle Net Client's full version matches the Oracle database's full version (for example, 10.2.0.4).

Still it is very hard to work around the error!

SDE schema import and Oracle error ora-30512

I was importing sde schema from an export taken before a failed sde 10 upgrading. And the following error occurred:


. . importing table                     "VERSIONS"          1 rows imported
IMP-00017: following statement failed with ORACLE error 4020:
 "ASSOCIATE STATISTICS WITH PACKAGES "ST_RELATION_OPERATORS" USING "ST_DOMAIN"
 "_STATS""
IMP-00003: ORACLE error 4020 encountered
ORA-04020: deadlock detected while trying to lock object 30x3A00344700x39ECF02680x3A436F958
IMP-00017: following statement failed with ORACLE error 4020:
 "ASSOCIATE STATISTICS WITH INDEXTYPES "ST_SPATIAL_INDEX" USING "ST_DOMAIN_ST"
 "ATS""
IMP-00003: ORACLE error 4020 encountered
ORA-04020: deadlock detected while trying to lock object 30x3A00344700x39ECF02680x3A436F958
IMP-00017: following statement failed with ORACLE error 4020:
 "ASSOCIATE STATISTICS WITH TYPES "ST_GEOMETRY" USING "ST_DOMAIN_STATS""
IMP-00003: ORACLE error 4020 encountered
ORA-04020: deadlock detected while trying to lock object 30x3A0C6A1300x39ECF02680x3A436F958
About to enable constraints...
IMP-00017: following statement failed with ORACLE error 30512:
 "ALTER TABLE "ST_GEOMETRY_COLUMNS" ENABLE CONSTRAINT "GEOM_SRID_FK1""
IMP-00003: ORACLE error 30512 encountered
ORA-30512: cannot modify SDE.ST_GEOMETRY_COLUMNS more than once in a transaction
Import terminated successfully with warnings.

I googled "ora 30512" and got the following link:

Error:  Underlying "ORA-30512: cannot modify SDE.ST_GEOMETRY_COLUMNS more than once in a transaction" encountered with Oracle Import Utility

And for sure the error can be ignored: It is safe to ignore the Oracle IMP utility error ORA-30512. Even though the error is encountered and reported in the Oracle import log, the GEOM_SRID_FK1 constraint is successfully enabled.

Next time it is better to use IMPDP/EXPDP instead of IMP/EXP.

More Oracle DBA tips, please visit Oracle DBA Tips 

Upgrading ArcSDE 10 and ORA-00942

I got an Ora-00942 error. And the following link explains a good reason:

Problem:  Incorrect dbtune XML_IDX_INDEX_TEXT values cause ArcSDE for Oracle geodatabase upgrades to fail with "ORA-00942: table or view does not exist" (http://support.esri.com/en/knowledgebase/techarticles/detail/38312)

Description

If there is an invalid value for the XML_IDX_INDEX_TEXT parameter in the dbtune, upgrading an ArcSDE for Oracle geodatabase to version 10 fails with following error in the sde_setup.log:

"[....] Error creating GDB_Items table...
[....] ERROR Creating Geodatabase tables
, Error = -37
,EXT_Error = 942
,EXT_ERROR1 = ORA-00942: table or view does not exist"

Cause

This error occurs when trying to create new geodatabase schema tables with an incorrect value in the XML_IDX_INDEX_TEXT parameter of the DEFAULTS DBTUNE keyword similar to the following:

"TABLESPACE TEST"

▪ The value of this parameter can be checked using the following ArcSDE admin command:

sdedbtune -o list -u sde -p sde -i sde:oracle10g:test -k DEFAULTS -P XML_IDX_INDEX_TEXT

ArcSDE 10.0 for Oracle11g Build 685 Fri May 14 12:05:43 2010
Attribute Administration Utility
-----------------------------------------------------
##DEFAULTS

XML_IDX_INDEX_TEXT "TABLESPACE TEST"

END

▪ The following is the actual SQL that fails:

SQL> CREATE INDEX xmldocix5_tx ON sde_xml_doc5 (xml_doc_val) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('TABLESPACE TEST');

CREATE INDEX xmldocix5_tx ON sde_xml_doc5 (xml_doc_val) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('TABLESPACE TEST')
*
ERROR at line 1:
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-11000: invalid keyword TABLESPACE
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

Solution or Workaround

1. Remove the config_string for the XML_IDX_INDEX_TEXT parameter in the DEFAULTS keyword from the DBTUNE.
   
   OR
   
   Make sure its value is valid for Oracle text. See the Oracle documentation for more information - there is a link to the Oracle documentation in the Related Information section below.
2. Re-run the geodatabase upgrade.

More Oracle DBA tips, please visit Oracle DBA Tips 

ESRI ArcSDE reference links

ESRI has released so many versions, releases, service packs of ArcMap, ArcSDE and other ArcGIS Server products that it is a challenge to keep a useful list of reference links! At least here is one I accumulated and am accumulating day by day

ArcSDE ESRI website: http://www.esri.com ESRI White Papers: http://support.esri.com/en/knowledgebase/whitepapers ESRI Training: http://training.esri.com/gateway/index.cfm ESRI Canada: http://www.esricanada.com/ Esri Developer Network: http://edn.esri.com/ ArcGIS Resource Center: http://resources.arcgis.com/ ArcGIS Resource Center - Web-based Help: http://resources.arcgis.com/content/web-based-help ArcGIS Forums: http://forums.arcgis.com/ ArcGIS Desktop 10: http://help.arcgis.com/en/arcgisdesktop/10.0/help/ ArcSDE Installation Guides - ArcSDE 10 for Oracle: http://help.arcgis.com/en/geodatabase/10.0/install_guides/arcsde_for_oracle_installation_and_upgrade_guide/index.html ArcSDE Installation Guides - ArcSDE 10 for SQL Server: http://help.arcgis.com/en/geodatabase/10.0/install_guides/arcsde_for_microsoft_sql_server_installation_and_upgrade_guide/index.html ·         ArcSDE Knowledge Base: http://support.esri.com/en/knowledgebase/techarticles/browse/productid/19/productfamily/0/productVersions/642 ·          ArcSDE 9.1 document: http://edndoc.esri.com/arcsde/9.1/ ·         ArcSDE 9.2 document: http://edndoc.esri.com/arcsde/9.2/ ·         ArcSDE 9.3  Administration Command Reference: http://resources.esri.com/help/9.3/geodatabase/pdf/admincmd.pdf     ArcSDE Microsoft SQL Server Database Requirements 9.3/9.3.1:  http://downloads.esri.com/support/systemrequirements/arcsde_microsoft_sql_server_database_requirements.pdf ·         ArcSDE 10.0 o    Geodatabase and ArcSDE 10.0 Help: http://resources.arcgis.com/content/geodatabases/10.0/geodatabase-and-arcsde-help o    Geodatabase and ArcSDE 10.0 System Requirements: http://resources.arcgis.com/content/geodatabases/10.0/geodatabase-and-arcsde-system-requirements --ArcSDE 10 SQL Server Database Requirements: http://resources.arcgis.com/content/arcsde/10.0/sql-server-system-requirements o    ArcSDE Installation Guides: http://resources.arcgis.com/content/geodatabases/10.0/arcsde-installation-guides o    ArcSDE 10.0 for Oracle Installation Guides: http://help.arcgis.com/en/geodatabase/10.0/install_guides/arcsde_for_oracle_installation_and_upgrade_guide/index.html o    ArcSDE Geodatabase System Tables-10: http://help.arcgis.com/en/arcgisdesktop/10.0/help/002p/pdf/sdesystables_diagram.pdf ·         ArcGIS Server 9.2 Help: http://webhelp.esri.com/arcgisserver/9.2/java/ ·         ArcGIS Server 9.3 Help: http://webhelp.esri.com/arcgisserver/9.3/java/ ·         ArcGIS Server 9.3.1 Help: http://webhelp.esri.com/arcgisserver/9.3.1/java/ ·         ArcGIS Server 10 Help: http://help.arcgis.com/en/arcgisserver/10.0/help/arcgis_server_java_help/index.html ·           ·         Forum: Geodatabase & ArcSDE: http://forums.arcgis.com/forums/32-Geodatabase-amp-ArcSDE ·           What's new in ArcGIS 10: http://help.arcgis.com/en/arcgisdesktop/10.0/pdf/whats_new_in_arcgis_10.pdf ·         ArcGIS Server Product Life Cycle Support Status: http://downloads.esri.com/support/product%20life%20cycle/server_gis/ArcGISServer_PLC.pdf ·         ArcSDE 9.3/9.3.1 Oracle Database Requirements: http://wikis.esri.com/wiki/display/ag93bsr/ArcSDE+Oracle+Database+Requirements ·         ArcSDE 10 Oracle Database Requirements: http://resources.arcgis.com/content/arcsde/10.0/oracle-system-requirements ·         Product Announcements for ArcGIS Server: http://support.esri.com/en/knowledgebase/product-announcements/list/productid/66 ·         Patches and Service Packs: http://resources.arcgis.com/content/patches-and-service-packs ·         Patches and Service Packs for ArcGIS Server: http://resources.arcgis.com/content/patches-and-service-packs?fa=listPatches&PID=66

Patches and Service Packs for ArcSDE ·         ArcSDE 10 Service Pack 4  03-05-2012 - SERVICE PACK ·         ArcSDE 10 Service Pack 3 10-12-2011 - SERVICE PACK ·         ArcSDE 10 Service Pack 2 04-28-2011 - SERVICE PACK ·         ArcSDE 10 Service Pack 1  12-03-2010 - SERVICE PACK ·         ArcSDE 10.0 General Update Patch for SQL Server  08-31-2010 - PATCH ·         ArcSDE 9.3.1 Service Pack 2 Register as Versioned using Oracle Spatial Data Patch 08-11-2011 – PATCH ·         ArcSDE 9.3.1 Service Pack 2 General Update Patch for DB2, Informix, and Oracle 11-29-2010 – PATCH ·         ArcSDE 9.3.1 Service Pack 2 11-04-2010 - SERVICE PACK ·         ArcSDE 9.3.1 SP1 SQL Server Raster Catalog and Raster Field Patch  12-23-2009 - PATCH ·         ArcSDE 9.3.1 Service Pack 1  12-19-2009 - SERVICE PACK ·         ArcSDE 9.3.1 SQL Server 2000 Permissions Patch  06-29-2009 - PATCH ·         ArcSDE 9.3 Service Pack 1 Compress with Concurrent Editors Patch  05-11-2009 – PATCH ·         ArcSDE 9.3 Service Pack 1 Memory Allocation Patch  03-18-2009 – PATCH ·         ArcSDE 9.3 Service Pack 1  03-13-2009 - SERVICE PACK ·         ArcSDE 9.3 Service Pack 1 geodatabase direct connect for 9.2 clients setup  11-07-2008 - SERVICE PACK ·         ArcSDE 9.2 SP 6 SQL Server 2000 Permissions Patch  06-29-2009 - PATCH ·         ArcSDE 9.2 Service Pack 6 Compress with Concurrent Editors Patch  04-22-2009 – PATCH ·         ArcSDE 9.2 Service Pack 6 Memory Allocation Patch  02-09-2009 – PATCH ·         ArcSDE 9.2 Connection Performance and Geodatabase Repair Patch  12-19-2008 – PATCH ·         ArcSDE 9.2 ST_Geometry Spatial Index Patch  08-28-2008 – PATCH ·         ArcSDE 9.2 Service Pack 6  07-18-2008 - SERVICE PACK ·         ArcSDE 9.2 Service Pack 5  05-08-2008 - SERVICE PACK ·         ArcSDE 9.2 Oracle Binary and Lob Type Spatial View Patch  05-02-2008 – PATCH ·         ArcSDE 9.2 for Oracle10gR2 for Tru64 UNIX  01-16-2008 – PATCH ·         ArcSDE 9.2 Service Pack 4  11-16-2007 - SERVICE PACK ·         ArcSDE 9.2 Service Pack 3  07-31-2007 - SERVICE PACK ·         ArcSDE 9.2 SQL Server Case Sensitivity Patch 06-07-2007 – PATCH ·         ArcSDE 9.2 Service Pack 2  04-16-2007 - SERVICE PACK ·         ArcSDE 9.2 Versioning Patch  03-21-2007 – PATCH ·         ArcSDE 9.2 Oracle Patch  02-14-2007 – PATCH ·         ArcSDE 9.2 Service Pack 1  01-04-2007 - SERVICE PACK ·         ArcSDE 9.1 Compress with Concurrent Editors Patch  06-16-2009 – PATCH More Oracle DBA tips, please visit Oracle DBA Tips

An example of spatial index tuning in ArcSDE

Arcsde version: 9.1

Database: Oracle 10gR2

Beginning with ArcSDE 9.2 geodatabases, the spatial index is calculated for you when you create a new dataset and provide a coordinate system in ArcGIS Desktop. You shouldn't need to tune such spatial indexes. However, if you are using an older release of ArcSDE such as 9.1, you can follow the steps below to tune the index.

1. Check the spatial index

C:\ArcSDE91>sdelayer -o si_stats -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

-----------------------------------------------------

Layer 1606 Spatial Index Statistics:

Level 1, Grid Size 50000

|-------------------------------------------------------------------|

| Grid Records: 2680 |

| Feature Records: 174 |

| Grids/Feature Ratio: 15.40 |

| Avg. Features per Grid: 4.73 |

| Max. Features per Grid: 17 |

| % of Features Wholly Inside 1 Grid: 1.72 |

|-------------------------------------------------------------------|

| Spatial Index Record Count By Group |

| Grids: <=4 >4 >10 >25 >50 >100 >250 >500 |

|---------- ------ ------ ------ ------ ------ ------ ------ ------ |

| Features: 51 123 77 30 10 0 0 0 |

| % Total: 29% 71% 44% 17% 6% 0% 0% 0%|

|-------------------------------------------------------------------|

2. Use sdelayer –o load_only_io to drop the spatial index. Switching the feature class to load-only mode drops the spatial index. While the feature class is in load-only mode, no spatial queries or data loading is allowed on this layer.

C:\ArcSDE91>sdelayer -o load_only_io -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

-----------------------------------------------------

Layer is now in LOAD-ONLY I/O mode.

3. Use sdelayer –o alter –g n,n,n to specify new grid sizes. Specify 0 for the second or third grid size if not used.

C:\ArcSDE91>sdelayer -o alter -l my_layer,geometry -g 150000,0,0 -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

-----------------------------------------------------

Change spatial index from SPIDX_GRID,50000

to SPIDX_GRID,150000

Are you sure? (Y/N): y

Successfully Modified the Layer

Successfully changed spatial index parameters and rebuild as needed

4. Use sdelayer –o normal_io to rebuild the spatial index and make the layer accessible again.

C:\ArcSDE91>sdelayer -o normal_io -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

-----------------------------------------------------

Layer is now in NORMAL I/O mode.

5. Check the spatial index

C:\ArcSDE91>sdelayer -o si_stats -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

-----------------------------------------------------

Layer 1606 Spatial Index Statistics:

Level 1, Grid Size 150000

|-------------------------------------------------------------------|

| Grid Records: 675 |

| Feature Records: 174 |

| Grids/Feature Ratio: 3.88 |

| Avg. Features per Grid: 8.44 |

| Max. Features per Grid: 32 |

| % of Features Wholly Inside 1 Grid: 22.41 |

|-------------------------------------------------------------------|

| Spatial Index Record Count By Group |

| Grids: <=4 >4 >10 >25 >50 >100 >250 >500 |

|---------- ------ ------ ------ ------ ------ ------ ------ ------ |

| Features: 134 40 10 0 0 0 0 0 |

| % Total: 77% 23% 6% 0% 0% 0% 0% 0%|

|-------------------------------------------------------------------|

6. Repeat step 2 to 5 until satisfied.

C:\ArcSDE91>sdelayer -o si_stats -l my_layer,geometry -i 5151 -s sde_server -u usera -p pwd

ArcSDE 9.1 Oracle10g Build 391 Tue Oct 24 11:44:47 PDT 2006

Layer Administration Utility

-----------------------------------------------------

Layer 1606 Spatial Index Statistics:

Level 1, Grid Size 425000

|-------------------------------------------------------------------|

| Grid Records: 287 |

| Feature Records: 174 |

| Grids/Feature Ratio: 1.65 |

| Avg. Features per Grid: 17.94 |

| Max. Features per Grid: 77 |

| % of Features Wholly Inside 1 Grid: 58.05 |

|-------------------------------------------------------------------|

| Spatial Index Record Count By Group |

| Grids: <=4 >4 >10 >25 >50 >100 >250 >500 |

|---------- ------ ------ ------ ------ ------ ------ ------ ------ |

| Features: 174 0 0 0 0 0 0 0 |

| % Total: 100% 0% 0% 0% 0% 0% 0% 0%|

|-------------------------------------------------------------------|