Restore Oracle db dump using SQL Developer

Follow the following steps to restore .dmp file using Oracle SQL developer studio.

• copy the .dmp file to a folder in the database hosting server (eg: /home/oracle/datapump/WSO2_DATA_DUMP.dmp)
• Run the following command to create a new schema and give the relevant access to the user

CREATE USER ramindu IDENTIFIED BY ramindu;
GRANT CONNECT TO ramindu;
GRANT CONNECT, RESOURCE, DBA TO ramindu;
GRANT UNLIMITED TABLESPACE TO ramindu;

• Run the following commands to create a SQL data pump directory, and give the relevant permissions to the user created above to do DBA related actions

create or replace directory ramindu_data_pump as ‘/home/oracle/datapump’;
GRANT DBA TO ramindu;
grant create session, grant any privilege to ramindu;
grant all privileges to ramindu;
grant read, write on directory synprod_data_pump to ramindu;
grant exp_full_database to ramindu;
grant imp_full_database to ramindu;

• Create a new connection as follows

• add the connection as a DBA connection

• Expand the connection and right click on ‘Data Pump’ and select ‘data Pump Import Wizard’.

• Select the SQL data pump directory name(eg: ramindu_data_pump) given in the previous step’s SQL command. And give the data dump name (eg: WSO2_DATA_DUMP.dmp) as the “File names or URI” column as below

• Click on next in the next window

• In the Re-Map Schemas, click on “add row” and select your schema in the database dump as the source and give the destination as our newly created schema. And click next

• Select the created sql data pump directory to save the log file and click next

• Select job schedule as “Immediately” and click next

• Click on finish

• You will be able to find the tables in the left panel as follows