How Can I import or open a .dmp file?

Question

Update:

I tried the impdp command and it's giving me that it cannot create a user. I tried creating the user as well

This is how my .par file looks like

This is a snip of .sh file

I have never used the oracle database before. I have a .dmp file which is 50 GB. I don't know how it was exported or which version it was exported from. I downloaded Oracle 12c release 2 and tried to do an import but I get the error ".dmp may be a Data Pump export dump file". What do I need to do so that I can run SQL queries on it eventually? Please see the attached image.

UPDATE : I tried the command : IMP SYSTEM/Password SHOW=Y FILE=DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.dmp fromuser=SYSTEM touser=SYSTEM

It gave me a message saying import terminated successfully with warnings. what does this do? Also, where can I view the data now if it's imported?

Answer 1

in sqlplus as SYSTEM:

CREATE DIRECTORY IMPDIR as 'C:\Users\negink\Documents\databasewrigley';

back in command line:

impdp SYSTEM/Password DUMPFILE=IMPDIR:DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.dmp logfile=IMPDIR:DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.log FULL=Y

when done, you can remove the DIRECTORY object

in a CDB database (which is your case), this will not work, unless you pre-create all the users and roles in SQLPLUS, after running this command:

alter session set "_ORACLE_SCRIPT"=true;
create user x identified by pwdx;
create user y identified by pwdy;
create role r1;
create role r2;
...

Otherwize, you can create a PDB inside your CDB and import your DMP file into the PDB. In this case, you'll need to modify the connection in the IMPDP command as follows (change SYSTEM/Password to SYSTEM/Password@//localhost/pdb_name) :

impdp SYSTEM/Password@//localhost/pdb_name DUMPFILE=IMPDIR:DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.dmp logfile=IMPDIR:DBO_V7WRIGLEY_PROD_20180201_TECHOPS-5527.log FULL=Y

Answer 2

First of all, you should use impdp instead of imp. And don't forget to take backups before doing anything. Also, you should have your dmp file on your server's local directory. I've seen people trying to import dmp files located on their computer's hard drive. That's not how things work.

I recommend you to drop the schema if you are importing to an existing schema for better results.

To drop an existing schema, login to sqlplus with an admin account

sqlplus username/password@connect_identifier

Then you can use this command to drop the schema:

DROP USER <SCHEMA_NAME> CASCADE;

Query your DB to see if data pump directory is defined

SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DATA_PUMP_DIR'

If directory is not defined use this command to define (btw "D:\orcl12" is my oracle instance path, you should use your own path)

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'D:\orcl12c/admin/<ORA_INSTANCE_NAME>/dpdump/';

Quit sqlplus to command prompt and run impdp with admin credentials (Be sure there's no other logfile with the same name on source directory - if so operation will abort)

impdp username/password@connect_identifier directory=DATA_PUMP_DIR dumpfile=filename.dmp logfile=filename.log

If the operation succeeds you may have to update User-Defined Datatypes manually because they are not importing correctly.