how to import a dmp file with profiles from a CDB db to a PDB DB instance using impdp in Oracle 12c?

Question

I have a dmp file from a CDB(don't know exactly) in another server from where, I was given a dmp file which was exported using expdp(possibly). I want to import it into a PDB instance on an Oracle 12c server which I have.

I extracted the sql file for the dmp file and there is profile by the name of 'C##APPPROF'. This dmp is of 2 schemas and the import fails where it tries to run 'CREATE USER' because the profile name doesn't exist.

According to Oracle 12c docs, I cannot create a profile for a PDB instance in Oracle 12c starting with 'C##' since that is the convention for creating profiles in a CDB instance.

In a non-CDB, a profile name cannot begin with C## or c##.

This is the command which is currently used.

impdp "sys/Oradoc_db1@ORCLPDB1 as sysdba" directory=MY_DATA_PUMP_DIRECTORY dumpfile=corp.dmp logfile=MY_DATA_PUMP_DIRECTORY:logfile.log exclude=GRANT

Is there a way, I can overcome this ?

Answer 1

With the help of the comment made by pmdba, I created the users in my db before importing the dmp file and then the impdp command as follows

impdp \"sys/Oradoc_db1@ORCLPDB1 as sysdba\" directory=MY_DATA_PUMP_DIRECTORY dumpfile=corp.dmp logfile=MY_DATA_PUMP_DIRECTORY:log.log exclude=GRANT schemas=user1,user2 transform=OID:n transform=segment_attributes:n

The system will still try to create the users and will show error that user cannot be created, but the dmp file will work just fine.

Also, every time I have to reimport a new dmp file or do an import again,

I do the following commands:

DROP USER USER1 CASCADE;
DROP USER USER2 CASCADE;
CREATE USER "USER2" IDENTIFIED BY "PASSWORD123"
      DEFAULT TABLESPACE "CORPORATE"
      TEMPORARY TABLESPACE "TEMP";
CREATE USER "USER1" IDENTIFIED BY "PASSWORD1234"
      DEFAULT TABLESPACE "TB1"
      TEMPORARY TABLESPACE "TEMP";