import dump in oracle database

Question

I need to import a dump-file into an Oracle Database.

I set myself up with a test VM and installed the latest Oracle Database 21c Express Edition and used:

impdp system/123 directory=C:/dump dumpfile=test.dmp logfile=import.log full=y

Which output an error message that it can not find the directory and logfiles can not be created.

I found that I can either set up a directory in Oracle or move this to a different location. So I moved everything to "C:\app\textVM\product\21c\admin\XE\dpdump" and ran said import command again...

It came back with error messages about users can not be created and all the tables can not be created cause of missing users. (I translated errors into english)

Objekttype SCHEMA_EXPORT/USER is being processed
ORA-39083: Objekttype USER:"UserA" could not be created, Error:
ORA-65096: Invalid common user or role name

Wrong SQL is:
 CREATE USER "UserA" IDENTIFIED BY VALUES 'S:123...456;78..90' DEFAULT TABLESPACE "UserA" TEMPORARY TABLESPACE "TEMP"

ORA-39083: Objekttype USER:"UserB" could not be created, Error:
ORA-65096: Invalid common user or role name

Wrong SQL is:
 CREATE USER "UserB" IDENTIFIED BY VALUES 'S:123...456;78..90' DEFAULT TABLESPACE "UserB" TEMPORARY TABLESPACE "TEMP"

ORA-39083: Objekttype USER:"UserC" could not be created, Error:
ORA-65096: Invalid common user or role name

Wrong SQL is:
 CREATE USER "UserC" IDENTIFIED BY VALUES 'S:123...456;78..90' DEFAULT TABLESPACE "UserC" TEMPORARY TABLESPACE "TEMP"

Objekttype SCHEMA_EXPORT/SYSTEM_GRANT is being processed
ORA-39083: Objekttype SYSTEM_GRANT could not be created, Error:
ORA-01917: User or function UserB does not exist

...
More Errors all stating the User or function does not exist

I need to figure out how to fix this... Apparently I can not create any Users. Did some digging and I guess I can not Import with the System Account but have to make a different user account to import something. Correct?

So I tried creating a import user to make an import. Not working, same Error. Looked it up and I guess I can not create User in a CDB but have to do that in a PDB... Did some googling and I found a way to create a user. I opened sqlplus as system/123

>alter session set container=XEPDB1;
Session altered
>create user import identified by 123;
User created
>grant connect to import;
User access (Grant) has been granted.
>grant all privileges to import identified by 123;
User access (Grant) has been granted.
>create directory importDir as C:/dump
Directory created

Flawless I guess. Ok so I can now make the import on my new user right?

impdp import/123 directory=importDir dumpfile=test.dmp logfile=import.log full=Y

Not so fast said Oracle:

UDI-01017: Process invoked Oracle Error 1017
ORA-01017: Username/Password not correct; Login rejected

So Google again and it said define the PDB with it?

impdp import/123@XEPDB1 directory=importDir dumpfile=test.dmp logfile=import.log full=Y

Now it said:

UDI-12154: Process invoked Oracle Error 12154
ORA-12154: TNS: Specified Connect Identifier could not be resolved

So what am I missing? Is the whole process wrong? All I want is to connect to my Oracle Server and send a whole bunch of SQL Queries.

---

Here is the tnsnames. I haven't touched it after installing:

# tnsnames.ora Network Configuration File: C:\app\oracleVM\product\21c\homes\OraDB21Home1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

LISTENER_XE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.15)(PORT = 1521))


ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

I found out that it doesn't say localhost and after searching on how to connect to an PDB the import should be:

impdp import/123@//10.0.2.15:1521/xepdb1 directory=importDir dumpfile=test.dmp logfile=import.log full=y

The login works and it says the import has been successful but it only took 2 seconds. So I was curious and in SQLDeveloper I can not see the Tables nor can I see any new Users (UserA, UserB and UserC)...

Answer 1

After this:

create directory importDir as C:/dump

you should have also ran

grant read, write on directory importdir to import

Otherwise, import user won't be able to access any file in that directory. If it (import user) won't write anything there, then read privilege is enough.

(BTW, I wouldn't name user import; although allowed, it causes confusion as it looks as the import database utility.)

As of (the last) error you got: should've probably been

impdp import/123@PDB1 directory=importDir ...
                 ----
                 just PDB1, not XEPDB1