ArcSDE启动发生GEOMETRY_COLUMNS相关错误的解决办法

同事去客户现场安装调试ArcSDE时遇到问题，SDE安装过程很顺利，显示成功安装。但是在启动SDE时却报了一个错误。

[sde@SDE_SERVER bin]$ ./sdemon -o start -p sde@sdedbsid

-------------------------------------------------------
ArcSDE 9.2  for oracle10g Build 1081 Sun Sep 17 16:01:22  2006
-------------------------------------------------------

ST_Geometry Schema Owner: (SDE) Type Release: 1007

Instance initialized for ((sde)) . . .

Connected to instance . . .

Inconsistent data type in GEOMETRY_COLUMNS table.

Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and dbinit.sde.

同事照着安装文档重新安装SDE几次，都是安装成功，到启动时就报这个错误，搞得很郁闷。我去看了$SDEHOME/etc目录下的log文件，也没有发现什么问题，于是上网查。

找到了几条Inconsistent data type in GEOMETRY_COLUMNS table类似的网页，基本上都是在ESRI社区上的。其中一条是：

Error:  Inconsistent data type in GEOMETRY_COLUMNS table
Article ID: 29292
Software:  ArcSDE 9.0, 9.1, 9.2
Platforms: N/A

Error Message
The following error may occur when attempting to start the ArcSDE Application Server for ArcSDE 9.x for oracle:

>sdemon -o start
esri_sde service failed during initialization.
Please check event log or error log files.
Error starting esri_sde service(997)
Could not start ArcSDE -- Check Network, $SDEHOME disk, DBMS settings and
dbinit.sde.

The SDE error log file in $SDEHOME/etc may contain the following error:

-------------------------------------------------------
ArcSDE 9.0 oracle9i Build 1093 Fri Mar 19 13:28:50 PST 2004
-------------------------------------------------------
db_get_dbms_release::Current Release 9.2.0.4.0 is assumed to be compatible with 9.0.0.0.0
Instance initialized for SDE . . .
Connected to instance . . .
Inconsistent data type in GEOMETRY_COLUMNS table.

Cause
This error can occur if an upgrade to the oracle software on the RDBMS server was not completely successful, for example an upgrade from oracle 9.2.0.0 to oracle 9.2.0.5.

This error can also occur if the oracle software was upgraded successfully, but the required upgrade of the oracle database following the software upgrade was either omitted or not completed successfully.

Solution or Workaround
Reapply the oracle software upgrade and/or redo the database upgrade procedures as required to make sure that the both the software upgrade and the database upgrade were successful. Consult the oracle documentation or contact oracle Technical Support if assistance is required.

This error can also occur with fresh installations of oracle and ArcSDE during ArcSDE's Post-Installation procedures when it attempts to start the Application Server for the first time. For these cases, the following procedure should resolve the problem:

Drop the oracle database.
If ArcSDE is running on a Windows platform, perform the following step. Otherwise, proceed to step 3.

Delete the ArcSDE service using the sdeservice -o delete command. Refer to the ArcSDE Developer Help for information on the usage of this command.
Reapply the oracle software upgrade.
Build a new oracle database.

Run the ArcSDE Post-Install procedure as described in the ArcSDE Installation Guide for your server platform.

Created: 5/18/2005
Last Modified: 9/8/2008

我们的Oracle数据库和SDE都是全新安装，不存在数据库版本升级的问题，但是这使得我们把注意力集中到数据库这边来，认为出现这个问题的原因很可能就是Oracle数据库安装或者配置有问题。
于是让同事再次重新安装Oracle，重建database，结果还是出现那样的问题！这时候，我在ESRI Surport Center上看到了这样的回答：

Checked the NLS_NCHAR_CHARACTERSET, is it UTF8?

(原文地址：http://forums.esri.com/Thread.asp?c=158&f=2285&t=232603)

难道是Oracle数据库字符集的问题？于是以sysdba身份登陆数据库，在SQLPLUS里执行相关SQL语句：

SQL>select * from nls_database_parameters
SQL> /

PARAMETER                                     VALUE                                  
------------------------------ ----------------------------------------
NLS_LANGUAGE                              SIMPLIFIED CHINESE                     
NLS_TERRITORY                              CHINA                                  
NLS_CURRENCY                              ?                                      
NLS_ISO_CURRENCY                      CHINA                                  
NLS_NUMERIC_CHARACTERS         .,                                     
NLS_CHARACTERSET                      ZHS16CGB231280                         
NLS_CALENDAR                             GREGORIAN                              
NLS_DATE_FORMAT                       DD-MON-RR                              
NLS_DATE_LANGUAGE                   SIMPLIFIED CHINESE                     
NLS_SORT                                      BINARY                                 
NLS_TIME_FORMAT                        HH.MI.SSXFF AM                         
NLS_TIMESTAMP_FORMAT              DD-MON-RR HH.MI.SSXFF AM               
NLS_TIME_TZ_FORMAT                   HH.MI.SSXFF AM TZR                     
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR           
NLS_DUAL_CURRENCY                   ?                                      
NLS_COMP                                     BINARY                                 
NLS_LENGTH_SEMANTICS              BYTE                                   
NLS_NCHAR_CONV_EXCP              FALSE                                  
NLS_NCHAR_CHARACTERSET         UTF8                                   
NLS_RDBMS_VERSION                   10.2.0.1.0                             

20 rows selected.

果然，数据库的字符集设置与系统及相关软件不匹配。
于是让他drop掉database，重新建立一个，数据库国家字符集和数据库字符集分别设置为ZHS16GBK和AL16UTF16，重新安装SDE后，启动终于正常了！

这时再执行上面的查询：

SQL>select * from nls_database_parameters
SQL> /

PARAMETER                                   VALUE
------------------------------ ---------------------------------------------------------------------
NLS_LANGUAGE                            SIMPLIFIED CHINESE
NLS_TERRITORY                            CHINA
NLS_CURRENCY                             ?
NLS_ISO_CURRENCY                     CHINA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET                     ZHS16GBK
NLS_CALENDAR                             GREGORIAN
NLS_DATE_FORMAT                       DD-MON-RR
NLS_DATE_LANGUAGE                   SIMPLIFIED CHINESE
NLS_SORT                                      BINARY
NLS_TIME_FORMAT                        HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT              DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT                  HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY                   ?
NLS_COMP                                    BINARY
NLS_LENGTH_SEMANTICS             BYTE
NLS_NCHAR_CONV_EXCP             FALSE
NLS_NCHAR_CHARACTERSET        AL16UTF16
NLS_RDBMS_VERSION                  10.2.0.1.0

20 rows selected.

顺便说一下，我们的系统是RedHat Linux AS4 update7，SDE版本是ArcSDE 9.2，Oracle版本是Oracle Database 10g Release 2 (10.2.0.1.0) Enterprise Edition for Linux x86。

看来Oracle字符集设置还是非常重要的。