0

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)...

2
  • It looks like XEPDB1 isn't defined in your tnsnames.ora? Commented Mar 19, 2024 at 15:45
  • I added the tnsnames.ora and found out I have to put in the whole serveradress.. even tho its local Commented Mar 19, 2024 at 17:09

1 Answer 1

0

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
Sign up to request clarification or add additional context in comments.

13 Comments

Thanks for the "grant read, write..." command. I added that. However the output does not change if I use "PDB1" instead of "XEPDB1" :(
Can you locate TNSNAMES.ORA file on your hard disk? What does it contain? There should be database alias we use when establishing connection, providing username/password@database_alias combination.
I attached it in my question above
Right; that should then be just XE. impdp import/123@xe ...
using impdp import/123@xe will tell me that the user/password is invalid; login rejected
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.