How to Restore schema using Impdp

Export, Import are the most commonly used backup and restoration technique available for DBA in Oracle. Since you have already taken backup of your database schema using Expdp. Now, its time to restore backup of the schema

Perquisites for Import:

     Destination Database must be in open mode.
     A directory in database should have created. 

Four Steps to Restore Schema Backup using Impdp:

Database Administrator has to move .dmp backup of source schema from source database to destination database directory. DBA can find existing database directories using:

sql> select * from dba_directories;

1. Set Database Environment variable

Export the sid of database using export command

[oracle@test ~]$ export ORACLE_SID=test01
[oracle@test ~]$ echo $ORACLE_SID
test01

2. Dump restoration scenarios:

In case of dump restoration, there could by n number of scenarios for DBA to consider. Here, I am considering most commonly used two scenarios.

a. Restoring dump on same database Instance.
b. Restoring dump on different database Instance.

Let’s discuss both one by one.

Restoring Schema Dump on Same Database Instance: Usually this type of restoration is done, when Database Administrator wants to make replica of a existing schema on same instance. Since, you are restoring schema on same database, you have to create new database user for dump restoration. Impdp command executed by system user will automatically create new database user with different name given by you but with same privillages. DBA has to just add "remap_schema" parameter with impdp command.

Syntax:
[oracle@test ~]$ impdp {user_name}/{Password} dumpfile={Dump file name} directory={Name of directory } logfile={Log file Name} schemas={Backup schema Name} remap_schema={exising_schema_name:new_schema_name}

Example:
[oracle@test ~]$ impdp system/sys dumpfile=remote_dba.dmp directory=backup logfile=test_schema_imp.log  schemas=remote_dba remap_schema=remote_dba:remote_dba1

Restoring Schema Dump on Different Database Instance: When Database Administrator moves a schema from one database to another database using then he use this approach. Suppose DBA is moveing user "Remote_dba" from server1 to server2. There are two possibilities for database user existence on destination database. Same way there are two possibilities in case of Tablespaces, either all tablespaces as on source database exists on destination database or doesn't exists. Let's see them all in detail.

A. Database User not Exists: If database user (e.g. remote_dba) is not on destination instance proceed with dump restoration, user will be created by dump restoration automatically.

B. Database User Exists: If database user (e.g. remote_dba) is already on destination instance. We need to drop the user and then restore dump. Following are steps to drop a user:

    Connect to the database using sys user and execute following.

    SQL> drop user remote_dba cascade;
    User dropped.

    If you got this message then proceed with restoration, otherwise you may encounter error like

    SQL> drop user remote_dba cascade;
    drop user remote_dba cascade
    *
    ERROR at line 1:
    ORA-01940: cannot drop a user that is currently connected.

    To resolve this connect by sys user and execute following.
   
    SQL> select sid,SERIAL# from v$session where username like 'remote_dba%';
    SID    SERIAL#
    ---------- ----------
    110        654
   
    Now kill all the session connected using
   
    SQL> ALTER SYSTEM KILL SESSION '110,654' immediate;
    System altered.

    Now again execute the drop user command

    SQL> drop user remote_dba cascade;
    User dropped.

    Now you can proceed with dump restoration.

C. Tablesapces exists: If tablespaces with same name as on source exists on destination then no issue with restoration, this is because at time of restoring by default restoration process looks for same name of tablespaces at destination schema.

D. Tablesapces Not exists: If tablespaces with same name as on source does not exists on destination then restoration will return error “ORA-00959: tablespace 'test_Tablesapce' does not exist

To avoid this error ues "remap_tablesape" parameter with impdp command. More than one tablespaces can also be remap by comma separator. like

remap_tablespace={source_tablespace_name: dest_tablespace_name}, {source_tablespace_name1: dest_tablespace_name1}

Syntex:

 [oracle@test ~]$ impdp {user_name}/{Password} dumpfile={Dump file name} directory={Name of directory } logfile={Log file Name} schemas={Backup schema Name} remap_tablespace={source_tablespace_name: dest_tablespace_name}

Example:

[oracle@test ~]$ impdp system/sys dumpfile=remote_dba.dmp directory=backup logfile=test_schema_imp.log  schemas=remote_dba  remap_tablespace= tablespace_name1: tablespace_name2

3. Execute Impdp Command: This output is for a simple case, when DBA is restoring schema on different database instance where all tablespace exists which were available on source database. Destination schema has not any user as "remote_dba", So this impdp will create a new remote_dba user.
[oracle@test opt]$ impdp system/sys dumpfile=remote_dba.dmp directory=backup logfile= test01_imp.log schemas=remote_dba

Import: Release 10.2.0.4.0 - Production on Friday, 20 May, 2011 11:25:06
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=remote_dba.dmp directory=backup logfile= remote_dba_imp.log schemas=remote_dba
------------------------------------------------------------------------------------
/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:31:56
Check restoration log in the directory folder by remote_dba_imp.log.log name.

As explained, this impdp will make a new database user "remote_dba" into this database and this use will be locked by default. So, DBA has to unlock and provide password for this user.

sql> alter user remote_dba identified by {password};
sql> alter user remote_dba account unlock;

4. Some Known Issues:

A. DB links are not created properly: Please reconfigure it.
B. Jobs are not restored: Get job creation script from using and paste it in destination schema.
C. Invalid Objects: Compile all invalid objects.

DBA can also take and Restore whole database backup instead of one schema backup using What Everybody Should Know about Export Database and Import Database In Oracle ?

Are you able to restore your dump with help of this article. Please share !!

10 comments:

  1. Replies
    1. Deepak, Thanks for you appreciation. Don't forget to follow this blog by Email.

      Delete
  2. Very clear article. Related to issue 'B' about importing JOBS, would you know another workaround ? It seems to be a bug in 10.2.0.3 version but I am not sure. Thanks for the info anyway. I'll use scripts meanwhile...

    ReplyDelete
  3. Wow. Awesome Umesh. Thank you for the post

    ReplyDelete
  4. You were there to help always.and even now the same!!! :D

    ReplyDelete
  5. thx you for this good tuto !
    But this link is broken :
    What Everybody Should Know about Export Database and Import Database In Oracle ?

    ReplyDelete