sql - impdp 不创建用户/架构

Question

我是 Oracle 菜鸟。我正在尝试将(expdp/impdp)架构(无数据)从一台机器复制到另一台机器。我不想进行任何重新映射。我只想在 targetHOST 上创建空表结构。我得到的错误是:

ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema ZABBIX is needed to import this object, but is unaccessible
ORA-01435: user does not exist

我的理解是，如果执行导入的用户具有“IMPORT FULL DATABASE”权限，它将在 targetHOST 中创建用户/模式。我将其授予“scott”并尝试了同样的错误。

• 数据库版本:SQL*Plus:2013 年 10 月 24 日星期四 14:27:41 发布 11.2.0.1.0 版本
• 如果重要的话，sourceHOST 上的数据库版本是 11.2.0。3
• 操作系统:Linux targetHOST 2.6.18-308.el5 #1 SMP 1 月 27 日星期五 17:17:51 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

导入错误:

(0)oracle@targetHOST$  sqlplus system/manager
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 24 14:27:41 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
IMPORT FULL DATABASE
CREATE SESSION

.......200 other privileges.......

202 rows selected.

SQL> ^D

(0)oracle@targetHOST$ 

(0)oracle@targetHOST$ impdp system/oracle123 directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=impdpZabbix.log

Import: Release 11.2.0.1.0 - Production on Thu Oct 24 14:14:51 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=impdpZabbix.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema ZABBIX is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'XXX.YYY.COM', inst_scn=>'7788478540892');COMMIT; END;
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"ZABBIX"."TABLE1" failed to create with error:
ORA-01918: user 'ZABBIX' does not exist
Failing sql is:
CREATE TABLE "ZABBIX"."TABLE1" ("COLUMN1" VARCHAR2(20 BYTE) NOT NULL ENABLE, "COLUMN2" VARCHAR2(20 BYTE), "COLUMN3" VARCHAR2(20 BYTE)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAU
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 14:14:53

(5)oracle@targetHOST$

导出日志available here. :

Answer 1

所以我自己想出了办法。

If the schema you are remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata and you are importing with enough privileges.

意味着导出架构的oracle用户应该拥有CREATE USER权限。虽然我没有这样重新映射，但导出部分是相关的，因为我的用户 (Zabbix) 是基本用户，而不是 DBA/create-user-privileged。

在我的例子中，我执行了“GRANT CREATE USER TO Zabbix”，再次运行导出，这次处理了一些额外的“对象类型”。

当 zabbix 用户没有“CREATE USER”权限时的旧输出:

(0)oracle@sourceHOST$ expdp zabbix/zabbix schemas=ZABBIX content=METADATA_ONLY directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=expdpZABBIX.log
.......
Starting "ZABBIX"."SYS_EXPORT_SCHEMA_01":  zabbix/******** schemas=ZABBIX content=METADATA_ONLY directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=expdpZABBIX.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table "ZABBIX"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
.......

新输出:

(0)oracle@sourceHOST$ expdp zabbix/zabbix schemas=ZABBIX content=METADATA_ONLY directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=expdpZABBIX.log
.......
Starting "ZABBIX"."SYS_EXPORT_SCHEMA_01":  zabbix/******** schemas=ZABBIX content=METADATA_ONLY directory=TEST_DIR dumpfile=ZABBIX.dmp logfile=expdpZABBIX.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Master table "ZABBIX"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
.......

注意处理对象类型SCHEMA_EXPORT/USER、SCHEMA_EXPORT/SYSTEM_GRANT、SCHEMA_EXPORT/ROLE_GRANT、SCHEMA_EXPORT/DEFAULT_ROLE。

更简单的解决方案:使用“system”来运行 expdp 和 impdp。它有它自己的陷阱，但对我来说这是最好的。