随着Oracle12c版本的推出,Esri也推出了支持Oracle12c的ArcSDE10.2.1版本。
更多ST_Geometry的配置和使用问题
http://blog.csdn.net/linghe301/article/category/797871
ArcSDE10.2.1在Oracle12c环境的安装
http://blog.csdn.net/linghe301/article/details/19110539
注意:以下操作都是在用户使用PDB基础上,鉴于PDB收费,如果用户使用非PDB的Oracle实例,那么在相关操作与Oracle11g版本基本一致。
用户可以不勾选Create as Container database。就不创建PDB了。
测试环境:
Linux5、Oracle12.1.0.1、Geodatabase10.2.1、ArcGIS10.2.1 for Desktop
CDB实例名:orcl
PDB实例名:pdborcl
你知道吗?
Oracle 12c 数据库的多租户功能将单独收费,售价为 1.75万美元/处理器,而企业版 12c数据库的售价仍为 4.75 万美元/处理器。
----------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------
1:启动Oracle12c,打开pdb
SQL> conn sys/oracle@orcl as sysdba
Connected.
SQL> alter pluggable database pdborcl open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4062262812 PDB$SEED READ ONLY
3 2266793495 PDBORCL READ WRITE
2:查看Oracle监听状态,是否监听pdb
[oracle@oracle12c ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 14-FEB-2014 06:08:17
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 14-FEB-2014 05:23:02
Uptime 0 days 0 hr. 45 min. 14 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/app/oracle/product/12.1.0/dbhome_1/networ k/admin/listener.ora
Listener Log File /home/oracle/app/oracle/diag/tnslsnr/oracle12c/listene r/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.220.203)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=oracle12c)(PORT=5500))(Security=(my _wallet_directory=/home/oracle/app/oracle/product/12.1.0/dbhome_1/admin/orcl/xdb _wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ORCL" has 2 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
3:将ArcGIS10.2.1 for Desktop的空间SQL文件上传到数据库操作系统中的Oracle用户下。并且修改相关的文件操作权限。
文件地址:C:\Program Files (x86)\ArcGIS\Desktop10.2\DatabaseSupport\Oracle\Linux64
上传地址:/home/oracle/app/oracle/product/12.1.0/dbhome_1/lib/libst_shapelib.so
[oracle@oracle12c lib]$ ls -al libst*
-rw-r--r-- 1 oracle oracle 8014669 Oct 29 03:03 libst_shapelib.so
[oracle@oracle12c lib]$ chmod 755 libst_shapelib.so
[oracle@oracle12c lib]$ ls -al libst*
-rwxr-xr-x 1 oracle oracle 8014669 Oct 29 03:03 libst_shapelib.so
[oracle@oracle12c lib]$ pwd
/home/oracle/app/oracle/product/12.1.0/dbhome_1/lib
4:创建sde用户的User Libraries对象
[oracle@oracle12c lib]$ sqlplus sde/sde@pdborcl
SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 14 04:33:19 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Fri Feb 14 2014 04:28:36 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> create or replace library ST_SHAPELIB as '/home/oracle/app/oracle/product/12.1.0/dbhome_1/lib/libst_shapelib.so'
2 /
Library created.
SQL> select file_spec from user_libraries;
FILE_SPEC
--------------------------------------------------------------------------------
/home/oracle/app/oracle/product/12.1.0/dbhome_1/lib/libst_shapelib.so
----------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------
5:配置操作系统用户 Oracle的hs文件引用
设置 SET EXTPROC=ALL
[oracle@oracle12c ~]$ cd /home/oracle/app/oracle/product/12.1.0/dbhome_1/hs/admin/
[oracle@oracle12c admin]$ more extproc.ora
#
# extproc.ora is used by extproc in the default Oracle configuration.
#
# This is a sample extproc init file that contains a name-value(s) pair which
# is same as the value of ENVS parameter in listener.ora file.
#
# Syntax: SET name=value (environment variable name and value)
#
# When specifying the EXTPROC_DLLS environment variable to restrict the DLLs
# that extproc is allowed to load, you may set EXTPROC_DLLS to one of the
# following values:
#
# * ONLY (maximum security)
#
# When EXTPROC_DLLS=ONLY:DLL[:DLL], only the specified DLL(s) can be loaded.
#
# Syntax: SET EXTPROC_DLLS=ONLY:DLL[:DLL]
#
# * NULL (the default value)
#
# When EXTPROC_DLLS=, only the DLL(s) in $ORACLE_HOME/bin and ORACLE_HOME/lib
# can be loaded.
#
# Syntax: SET EXTPROC_DLLS=
#
# * Colon-seperated list of the DLLs
#
# When EXTPROC_DLLS=DLL[:DLL], the specified DLL(s) and the DLLs in
# $ORACLE_HOME/bin and ORACLE_HOME/lib can be loaded.
#
# Syntax: SET EXTPROC_DLLS=DLL:DLL
#
# * ANY
# When EXTPROC_DLLS=ANY, DLL checking is disabled.
#
# Syntax: SET EXTPROC_DLLS=ANY
#
SET EXTPROC_DLLS=ANY
6:修改listener文件
# listener.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.220.203)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/dbhome_1/)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ANY")
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME = ORCL)
)
)
7:修改tnsname文件
# tnsnames.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.220.203)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.220.203)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
#用户手动添加
PDBORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.220.203)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdborcl)
)
)
#用户手动添加
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
8;重启一下Oracle 监听
9:使用ArcGIS客户端导入空间数据
10:测试配置是否成功
测试数据:使用ArcGIS Desktop自带的大洲数据
C:\ProgramFiles (x86)\ArcGIS\Desktop10.2\ArcGlobeData\continent.shp
该数据的特点是一个面状要素包含的点串坐标比较多,近几千个点串。
----------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------
在测试过程中出现了一个问题
a:使用sqlplus查询点串比较多的要素
SQL> select sde.st_astext(shape) from bbb where objectid=1;
ERROR:
ORA-28578: protocol error during callback from an external procedure
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 67
ORA-06512: at "SDE.ST_GEOMETRY_OPERATORS", line 118
no rows selected
b:使用pl/sql查询点串比较多的要素就没有问题
c:在该要素类创建一个三角形,使用sqlplus查询
SQL> select sde.st_astext(shape) from bbb where objectid=9;
SDE.ST_ASTEXT(SHAPE)
--------------------------------------------------------------------------------
POLYGON (( -32.03713528 129.68712966, -52.51989390 110.77996786, -0.52519894 11
1.83036574, -32.03713528 129.68712966))
d:同样数据,在ArcSDE10.1 for Oracle11.2.0.3环境下测试没有问题。
所以,个人感觉导致该问题最大的原因是Oracle12c的问题,该问题正在与美国support沟通。
经过沟通,美国Support已经认定是一个Bug。
NIM099878 The ST_GEOMETRY function returns the error'ORA-28578: protocol error during callback from an external procedure' withlarge number of vertices underlying ArcSDE10.2.1 for oracle 12c.
什么时候进行修正还没有时间,大家可以关注Esri官方补丁发布!
----------------------------------------------------------------------------------
版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!
Blog: http://blog.csdn.net/linghe301
----------------------------------------------------------------------------------