Oracle 12c Multitenant: impdp fails w/ ORA-31625 and ORA-01031 because of Database Vault

Things are different in Oracle Database 12c with multitenancy option. My most recent example:

I tried to import a schema (new name “NEWSCHEMA”) with datapump IMPDP and REMAP_SCHEMA into the same pluggable database it has been exported from with EXPDP immediately before (name “OLDSCHEMA”), running as SYSTEM. I’m doing things like that with DBA permissions, since my users have lots of grants and stuff in the schemas, and when a DBA does the export and import, all settes right. (See the details for commands and parfiles below.)

But IMPDP fails with
ORA-39083: Object type INDEX failed to create with error:
ORA-31625: Schema NEWSCHEMA is needed to import this object, but is unaccessible
ORA-01031: insufficient privileges

So what? I’m SYSTEM and thus, DBA, and the user NEWSCHEMA is there. And SYSTEM of course has the “IMPORT FULL DATABASE” privilege, it’s a DBA! So you may think.

Try

But maybe… My first attempt to fix this, pre-creating the user NEWSCHEMA and granting “imp_full_database”, did not help either.

Reason

In 12c, you can’t disable Database Vault (MOS Doc ID 948061.1). And with Database Vault, “imp_full_database” does not contain the “BECOME USER” privilege. So a DBA can’t create INDEX objects for another user. Good to know and to keep that in mind…

Solution

Check if that’s really the case:
select *
from role_sys_privs
where role in ('DBA', 'IMP_FULL_DATABASE')
and privilege like 'BECOME%';

If no rows returned, you found the culprit.

Fix it with:
alter session set container=YOUR_PDB_IN_QUESTION;
grant become user to imp_full_database;


==> Oracle says: Works as designed.

I have to misquote Tom Kyte here: “One guy’s feature may be a thousand guy’s bug…”

So keep your eyes open
Martin Klier

Appendix

My DB version: 12.1.0.2 Enterprise Edition on Linux x86_64 (Oracle Enterprise Linux 6)

EXPDP parfile:
DIRECTORY=DATA_PUMP_DIR_INTERNAL
DUMPFILE=expdp_oldschema.dmp
LOGFILE=expdp_oldschema.log
FLASHBACK_TIME=
"TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')"
SCHEMAS=OLDSCHEMA


IMPDP parfile:
DIRECTORY=DATA_PUMP_DIR_INTERNAL
DUMPFILE=expdp_oldschema.dmp
LOGFILE=impdp_newschema.log
TRANSFORM=oid:n
SCHEMAS=OLDSCHEMA
REMAP_SCHEMA=OLDSCHEMA:NEWSCHEMA

My import command:
impdp system@internal parfile=impdp_newschema.par