How to import datapump export file from XE 11g to XE 18c

Question

I have datapump single schema export generated on 11g XE. I want to import it to 18c XE.

I'm using command line impdp.

At first, I was getting message:

UDI-01017: operation generated ORACLE error 1017
ORA-01017: invalid username/password; logon denied

Later, after some fixing, I moved to error:

UDI-12154: operation generated ORACLE error 12154
ORA-12154: TNS:could not resolve the connect identifier specified

I haven't found any article that explains all steps I need to do, so, after I found (my) solution, I decided to write this post so others can benefit from it.

It is mandatory to have tns names entry for your pluggable database service. Typical entry:

XEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = computer-name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XEPDB1)
    )
  )

Then, when running impdp, you have two options.

If you write auth data inside command line, it has to be in format username/password@xepdb1 (hostname is optional, for those who need it). If you write auth data at prompt, then first type username@xepdb1, then password.

I was using DATA_PUMP_DIR for dump file (almost). Have in mind, there is subdirectory with the name containing GUID of pluggable database in question, one for each such database. So, I put the dump file into specific GUID-named subdirectory.

Then I executed command line like the one below and typed auth data at prompt.

impdp DUMPFILE=EXP-SERVICEDATA.DMP LOGFILE=EXP-SERVICEDATA.LOG DIRECTORY=DATA_PUMP_DIR SCHEMAS='servicedata' CONTENT=ALL STATUS=15
```none

Now import worked as expected.
Let me know if it worked for you.

Why downvote? I have put the solution into another comment. – some1 Jul 14 '19 at 18:36 — some1, Jul 14 '19 at 18:36

score 0 · Accepted Answer · answered Jul 14 '19 at 18:34

Here is my solution (quote from initial post).

It is mandatory to have tns names entry for your pluggable database service. Typical entry:

XEPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = computer-name)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XEPDB1) ) )

Then, when running impdp, you have two options.

If you write auth data inside command line, it has to be in format username/password@xepdb1 (hostname is optional, for those who need it). If you write auth data at prompt, then first type username@xepdb1, then password.

I was using DATA_PUMP_DIR for dump file (almost). Have in mind, there is subdirectory with the name containing GUID of pluggable database in question, one for each such database. So, I put the dump file into specific GUID-named subdirectory.

Then I executed command line like the one below and typed auth data at prompt. impdp DUMPFILE=EXP-SERVICEDATA.DMP LOGFILE=EXP-SERVICEDATA.LOG DIRECTORY=DATA_PUMP_DIR SCHEMAS='servicedata' CONTENT=ALL STATUS=15

Now import worked as expected.

Tnsnames entry should be in server tnsnames file. – some1 Jul 19 '19 at 21:24 — some1, Jul 19 '19 at 21:24

How to import datapump export file from XE 11g to XE 18c

1 Answers1