Oracle Schema Restore (Data Pump)

Topic Introduction: In this tutorial, we will discuss how to restore a User/Schema to a new User/Schema. Detailed descriptions have been given below.

Restore Schema/User  in Oracle 19c :  

1. Drop User if exist same named user.
1. Create a User for import.
3. We Need to create a Directory to keep the existing backup files.
4. Need to grant read-write privilege to the user.
5. Need to run Restore(impdp) command on command prompt. 

Drop User hrtemp if exist same name Schema:

DROP USER hrtemp CASCADE;

User Creation for Restor DMP File: 

create user hrtemp identified by 123 account unlock;

Directory Creation where DMP has been Kept:

CREATE OR REPLACE DIRECTORY 

MY_BACKUP_IMPDIR AS 

'D:\DBbackup\Import';

Granting Read Write privilege to the user: 

GRANT READ, WRITE ON DIRECTORY MY_BACKUP_IMPDIR TO hrtemp;

To connect a session user must need to connect privilege. so give permission to hrtemp user by sys user.

grant connect, dba, resource to hrtemp;

Run the script to the command prompt for Restore:

impdp hrtemp/123@orclpdb  directory=MY_BACKUP_IMPDIR dumpfile=HRBACK.DMP logfile=HRBACK1.log remap_schema=hr:hrtemp

Check DMP Import :

Connect and check the schema objects that we have imported

Note: 1. Directory needs to create by sys user and granted by another to hrtemp user. 2. remap_schema=hr:hrtemp here hr is the backup file schema name and hrtemp is the new user name.