impdp does not create user/schema

Question

I'm an Oracle noob. I'm trying to copy (expdp/impdp) schema (no data) from one machine to another. I do not want any remapping. I just want empty table structure created on targetHOST. Error I get is:

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

My understanding is that if the user performing import has 'IMPORT FULL DATABASE' privilege, it'll create the users/schemas in the targetHOST. I granted it to 'scott' and tried with that too, same error.

• DB version: SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 24 14:27:41 2013
• If it matters the DB version on sourceHOST is 11.2.0.3
• OS: Linux targetHOST 2.6.18-308.el5 #1 SMP Fri Jan 27 17:17:51 EST 2012 x86_64 x86_64 x86_64 GNU/Linux

Import errors:

(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$

Export log available here.:

Solution

So I figured it out on my own.

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.

Meaning the oracle user that exported the schema, should have had CREATE USER privilege. Although I'm not remapping as such, export part is relevant as my user (ZABBIX) was a basic user and not DBA/create-user-privileged.

I did 'GRANT CREATE USER TO ZABBIX' in my case, ran the export again and this time some additional "object types" were processed.

OLD OUTPUT when zabbix user didn't have the 'CREATE USER' privilege:

(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
******************************************************************************
.......

NEW OUTPUT:

(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
******************************************************************************
.......

Note the Processing object type SCHEMA_EXPORT/USER, SCHEMA_EXPORT/SYSTEM_GRANT, SCHEMA_EXPORT/ROLE_GRANT, SCHEMA_EXPORT/DEFAULT_ROLE.

Much simpler solution: Use 'system' to run expdp and impdp. It has its own pitfalls, but for my case that's the best.

Comments

[Erik Anderson]

So, what is the better approach here? 1) Grant the specific user "CREATE USER" and then remember to remove it? 2) Execute the EXPDP as SYSDBA? It seems to me #1 is a security hole while #2 is not recommended by Oracle.

[Kashyap]

@ErikAnderson not sure why #2 is not recommended. I see no reason to NOT run as sysdba when you know your export import requires to create schema. Anyway, I dunno of any option #3, so take your pick. :-)

[miracle173]

@Kashyap Oracle actually says not to uses 'AS SYSDBA' for export except Oracle support advises you to do so. But in your answer you propose to use the user SYSTEM, which has the DBA role and not the SYSDBA privilege. SYSTEM is a very good choice to do an export.

[miracle173]

@ErikAnderson this answer proposes to do the export as SYSTEM user not to do it AS SYSDBA.

[QkiZ]

I'm using system user and still i'm getting this error

[Kashyap]

@QkiZ post a question with detailed info on what you did and what error you get. I'm sure someone can help you out.