中文

操作方法:执行 Oracle 企业级地理数据库的方案恢复

摘要

本文介绍使用 Data Pump 导入实用程序 (impdp) 按方案导入 Oracle Data Pump 文件时要执行的一系列步骤。 这些步骤一次运行一个。

支持版本:
  • EGDB:全部
  • DBMS:Oracle
  • DBMS 版本:11g 及以上

过程

  1. 创建一个新的 Oracle 数据库。
  2. 通过 SQL*Plus 以 SYS 身份连接。
sqlplus sys/syspassword@server/sid as sysdba
  1. 授予 Oracle 地理数据库公共权限。
GRANT EXECUTE ON dbms_pipe TO public;
GRANT EXECUTE ON dbms_lock TO public;
GRANT EXECUTE ON dbms_lob TO public;
GRANT EXECUTE ON dbms_utility TO public;
GRANT EXECUTE ON dbms_sql TO public;
GRANT EXECUTE ON utl_raw TO public;
  1. 获取旧版企业级地理数据库中数据的所有者列表。
select distinct owner from sde.layers
union
select distinct owner from sde.st_geometry_columns
union
select distinct owner from sde.table_registry
order by 1;
  1. 创建所有必需的表空间。
注:
这是一个自动扩展的默认表空间。
CREATE TABLESPACE SDETBS DATAFILE '/home/oracle/orcl/oradata/banner/sde.dbf'
SIZE 100M AUTOEXTEND ON NEXT 51200K MAXSIZE UNLIMITED EXTENT MANAGEMENT
LOCAL UNIFORM SIZE 320K LOGGING ONLINE SEGMENT SPACE MANAGEMENT MANUAL;
  1. 创建所有必需的用户/方案。
create user sde identified by sdepassword default tablespace sdetbs;
  1. 授予新用户所有必要的权限。
grant CREATE SESSION to sde;
grant CREATE TABLE to sde;
grant CREATE TRIGGER to sde;
grant CREATE SEQUENCE to sde;
grant CREATE PROCEDURE to sde;

grant EXECUTE ON DBMS_CRYPTO to sde;
grant CREATE INDEXTYPE to sde;
grant CREATE LIBRARY to sde;
grant CREATE OPERATOR to sde;
grant CREATE PUBLIC SYNONYM to sde;
grant CREATE TYPE to sde;
grant CREATE VIEW to sde;
grant DROP PUBLIC SYNONYM to sde;
grant ADMINISTER DATABASE TRIGGER to sde;

grant ALTER ANY INDEX to sde;
grant ALTER ANY TABLE to sde;
grant CREATE ANY INDEX to sde;
grant CREATE ANY TRIGGER to sde;
grant CREATE ANY VIEW to sde;
grant DROP ANY INDEX to sde;
grant DROP ANY VIEW to sde;
grant SELECT ANY TABLE to sde;

grant ALTER SESSION to sde;
grant ANALYZE ANY to sde;
grant SELECT ANY DICTIONARY to sde;
grant CREATE DATABASE LINK to sde;
grant CREATE MATERIALIZED VIEW to sde;
grant RESTRICTED SESSION to sde;
grant UNLIMITED TABLESPACE to sde;
grant ALTER SYSTEM to sde;
grant SELECT_CATALOG_ROLE to sde;
  1. 为系统用户创建访问目录。
create directory DPUMP1 as '/home/oracle/dpump';
  1. 授予系统用户 R/W 权限,以便读取目录中的文件。
GRANT READ, WRITE ON DIRECTORY DPUMP1 to system;
exit
  1. 在 CMD 窗口中为每个用户方案运行 impdp 实用程序。
  • 示例 1:SDE 方案
impdp system/sys directory=dpump1 logfile=sde_imp.log dumpfile=backup.DMP schemas=sde
  • 示例 2:GIS 方案
impdp system/sys directory=dpump1 logfile=GIS_imp.log dumpfile=backup.DMP schemas=GIS
  1. 登录 SQL*Plus 并检查是否存在无效对象。
sqlplus sys/sys@server/sid as sysdba
Select count(*) from dba_objects where owner='SDE' and status='INVALID';
  1. 对任何无效的方案对象进行编译。
exec dbms_utility.compile_schema(schema => 'SDE');

相关信息