How to restore schema in 10g using DataPump.

Introduction
There are the steps we are going to do:

1. Create schema ALEX.
2. Export schema TH_NEW using DataPump and import into ALEX schema.
3. Export schema ALEX using DataPump.
4. Make several changes in schema ALEX.
5. Restore schema ALEX using DataPump Import Utility.
    

Create Schema ALEX
 

1. Connect as administrative user to SQL*Plus console and build a new tablespace :
   
   

   SYS@PINKY> CREATE TABLESPACE alex

              EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO

              DATAFILE ‘/database/PINKY10G/PINKY10g/alex.dbf’ SIZE 1G;

   
   
   
2. Create a new user ALEX:
   
   SYS@PINKY> CREATE USER alex IDENTIFIED BY alex
   2  DEFAULT TABLESPACE alex TEMPORARY TABLESPACE temp
   3  QUOTA UNLIMITED ON alex;
   
   User created.
   
   SYS@PINKY> GRANT DBA TO alex;
   
   Grant succeeded.

 

Export schema TH_NEW using DataPump.
 

1. Using SQL*Plus build directory object:
   
   sys@PINKY> CREATE DIRECTORY DPUMP AS ‘/gibuy/export/PINKY’;
   
   Directory created.
   
2. Build parameter file th_new.par in order to export TH_NEW schema objects:
   
   job_name=th_new_job
   userid=alex/alex@PINKY
   directory=DPUMP
   dumpfile=th_new.dmp
   logfile=th_new.log
   schemas=TH_NEW
   
3. Run the following export command:
   
   oracle@ayevenunxdev1:/gibuy/export/PINKY>expdp parfile=th_new.par
   
4. Build paremeter file alex_imp.par in order to import TH_NEW to ALEX:
   
   job_name=alex_imp_job
   userid=alex/alex@PINKY
   directory=DPUMP
   dumpfile=th_new.dmp
   logfile=alex_imp.log
   remap_schema=th_new:alex
   REMAP_TABLESPACE=th_new:alex
   
5. Run the following import command:
   
   oracle@ayevenunxdev1:/gibuy/export/PINKY>impdp parfile=alex_imp.par
   
6. Check invalid objects in schema ALEX and correct them.

 

Export schema ALEX using DataPump.
 

1. Build parameter file alex_exp.par in order to export ALEX schema objects:
   
   job_name=alex_exp_job
   userid=alex/alex@PINKY
   directory=DPUMP
   dumpfile=alex_exp.dmp
   logfile=alex_exp.log
   schemas=ALEX
   
2. Run the following export command:
   
   oracle@ayevenunxdev1:/gibuy/export/PINKY>expdp parfile=alex_exp.par

 

Make several changes in schema ALEX.
 

1. Check the number of objects in schema ALEX:
   
   sys@PINKY>
     1  select count(*) from dba_objects
     2* where owner = ‘ALEX’ and object_name like ‘TH_HIST_%’;
   
   sys@PINKY>
   
   COUNT(*)
   ———-
             17
   
   1 row selected.
   
2. Drop all above objects
   
   sys@PINKY> select ‘drop table alex.’||table_name||’;’
     2  from dba_tables
     3  where owner = ‘ALEX’ and  table_name like ‘TH_HIST_%’;
   
   ??????.
   
3. Check again the number of objects in schema ALEX:
   
   sys@PINKY>  select count(*) from dba_objects
     2   where owner = ‘ALEX’ and object_name like ‘TH_HIST_%’;
   
   COUNT(*)
   ———–
                0
   
   1 row selected.

 

Restore schema ALEX.
 

1. There are 2 options to restore schema using DataPump Import Utility:
   
   1. TRUNCATE - deletes existing rows and then loads rows from the source.
   2. REPLACE - drops the existing table and then creates and loads it from the source.
   
2. Build parameter file alex_imp_replace.par in order to restore ALEX schema objects using REPLACE clause:
   
   job_name=alex_imp_replace_job
   userid=alex/alex@PINKY
   directory=DPUMP
   dumpfile=alex_exp.dmp
   logfile=alex_imp.log
   table_exists_action=REPLACE
   
3. Build parameter file alex_imp_truncate.par in order to restore ALEX schema objects using TRUNCATE clause:
   
   job_name=alex_imp_truncate_job
   userid=alex/alex@PINKY
   directory=DPUMP
   dumpfile=alex_exp.dmp
   logfile=alex_imp.log
   table_exists_action=TRUNCATE
   
4. Check invalid objects and compile