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