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)...
XEPDB1isn't defined in yourtnsnames.ora?tnsnames.oraand found out I have to put in the whole serveradress.. even tho its local