expdp迁移SDE数据

来源：互联网发布：kali linux 奶瓶编辑：程序博客网时间：2024/06/05 00:52

环境介绍：

源端：

系统：windows_2008server_r2

迁移对象：SDE/WXWF

内存：16G

CPU：2颗

目标端：

系统：windows_2008server_r2_64

导入：SDE/WXWF

内存：32G

CPU：2颗

数据库版本：oracle11.2.0.4_64

SDE版本：SDE11g_64

迁移步骤：

目标端：

1、创建sde\wxwf用户

Set time on

Set timing on

create tablespace sde datafile ' D:\oradata\wxwf\sde.dbf'size 10240M;

create user sde identified by sde defaulttablespace sde temporary tablespace temp;

create user wxwf identified by wxwf defaulttablespace users;

sde用户授权脚本参考：

grant execute on dbms_pipe to public;

grant execute on dbms_lock to public;

connect system/oracle

create tablespace sde

datafile '/oracle/oradata/sde.dbf' size1024M

extent management local uniform size 512K;

create user sde identified by sde

default tablespace sde temporary tablespacetemp;

grant CREATE SESSION to sde;

grant CREATE TABLE to sde;

grant CREATE PROCEDURE to sde;

grant CREATE SEQUENCE to sde;

grant CREATE TRIGGER to sde;

grant CREATE TYPE to sde;

grant CREATE LIBRARY to sde;

grant CREATE PUBLIC SYNONYM to sde;

grant CREATE OPERATOR to sde;

grant CREATE INDEXTYPE to sde;

grant DROP PUBLIC SYNONYM to sde;

grant CREATE VIEW to sde;

grant UNLIMITED TABLESPACE to sde;

grant ADMINISTER DATABASE TRIGGER to sde;

2、创建expdp导出目录

源端、目标端

create or replace directory dump_dir as'D:\jyc';

grant read,write on directory dump_dir tosde;

set line 132 wrap off

select * from dba_directories;

3、导出\导入用户

expdp sde/sde directory=public_dumpschemas=sde dumpfile=sde.dmp logfile=sde.log parallel=4

expdp wxwf/wxwf directory=public_dumpschemas=wxwf dumpfile=wxwf.dmp logfile=wxwf.log parallel=4

impdp sde/sde directory=public_dumpschemas=sde dumpfile=sde.dmp logfile=sde.log parallel=4

impdp wxwf/wxwf directory=public_dumpschemas=wxwf dumpfile=wxwf.dmp logfile=wxwf.log parallel=4

4、编辑失效对象

>@?/rdbms/admin/utlrp.sql

select owner,object_type,status,count(*)from dba_objects where status='INVALID' group by owner,object_type,status orderby owner,object_type;

>shutdown immediate

>startup

5.创建Library路径

UNIX：注意一点使用单引号；

SQL >create or replace libraryST_SHAPELIB as '/home/sde/sdeexe100/lib/libst_shapelib.so';

Window平台：注意一点使用单引号；

SQL >create or replace libraryST_SHAPELIB as 'D:\Arcsde\sde\sdeexe100\bin\st_shapelib.dll';

6、修改sde指向

Set LOCAL=WXWF

然后重启SDE服务；

附录：

1、安装sde服务post默认基表个数

2.ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG问题处理

执行测试语句1、2后报告如下错误：

ORA-28575: unable to open RPC connection to externalprocedure agent

ORA-06512: at"SDE.ST_GEOMETRY_SHAPELIB_PKG", line 12

ORA-06512: at "SDE.ST_LINESTRING",line 58

北京电信SDE测试语句

测试语句1：

INSERT into sde.C3GSTATION
  (fldghbw, fldkplywfzl, fldrpccgl,fldxxcgl,fldjlchenggl,fldsxcgl,fldzzshuxing,FLDID,fldtchzyl,fldtag,fldname2,fldcjzzt,FLDNAME,fldghdj,
   fldxkyhs,shape,   objectid)
values
  ('309925.664123',   '',   '',   '',   '',   '',   'LTE规划属性',   '12061206139433747',   '',   '999905001',   '',   '',   '安德里北街',
   '502573.292591',   '',   sde.st_pointfromtext('point(502573.292591 309925.664123)', 2),   ('101041'))

测试语句2：

select sde.ST_AsText(SDE.ST_Geometry('POINT (10 10)', 0)) from dual;

处理方式：

检查oracle和grid（集群）用户下的$ORACLE_HOME/hs/admin/extproc.ora文件均包含有如下：

SET EXTPROC_DLLS=ANY

然后在运行测试语句1、2

0 0