oracle11goracle-xeimpdporacle18cpluggable-database

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


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.

Solution

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