Data pump Schema Refresh

Schema refresh is an regular job for any DBA specially during migration projects, so today I decide to write a post about how we do a schema refresh using Data pump.

Assuming here schema (SCOTT) is  being refreshed  from source (PROD) to Target (TEST) on oracle 11g server using SYSTEM user (use can do with any privileged user)

SQL>  select  banner  from  v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

PL/SQL Release 11.2.0.2.0 - Production

CORE    11.2.0.2.0      Production

TNS for Linux: Version 11.2.0.2.0 - Production

NLSRTL Version 11.2.0.2.0 - Production

On Source side (PROD) :

Create a directory or use an existing directory (ex: data_pump_dir) and grant read and write permissions on this directory to user ‘SYSTEM‘  --> If you do as SYS user this grant is not required

SQL >   grant  read, write  on  directory  data_pump_dir  to   system;

Grant Succeeded.

NOTE: Always need to make sure there is enough space to accommodate Dump files

Step 1:   Exporting the data from prod(source) 

$   vi   expdp_refresh_schema.sh

$  expdp  system/****@sourcehostname   dumpfile=expdpschema.dmp   Directory=data_pump_dir    logfile=export.log   schemas= scott

$  nohup  sh  expdp_refresh_schema.sh>refresh_schema.out &

Nohup is NOT mandatory as datapump process always runs on the server

Step 2 :  Copying the dumpfiles from source to target

For copying Dumpfiles from one server to another server we can use either Winscp(Graphical tool for copying files from windows to linux and  vice versa),FTP, SFTP, SCP, etc.

$ scp  expdpschema.dmp   system@TargetHostname:/home/oracle/datapump

Here I’m copying dumpfile from source to the target /home/oracle/datapump  location

Step 3 :  Importing data from dumpfile into target database

Before importing dunpfile into target(TEST) make sure you delete or backup all the objects in that schema, to clear all objects from particular schema run the script from here  

$ impdp  system/****@targethostname   dumpfile=expdpschema.dmp   Directory=data_pump_dir    logfile=import.log   remap_schema= scott:newscott

Step 4 :   Verify target database object counts with source db

SQL>   select   count(*)  from  dba_objects   where  owner=’NEWSCOTT’ ;

SQL>   select  count(*)  from  dba_tables  where  owner =’NEWSCOTT’;

The above results  should be same as that of source  ‘scott’  schema

Check More of Datapump.........
To Kill a running Data pump Job :  http://chandu208.blogspot.com/2011/09/data-pump-scenarios.html
About data pump :  http://chandu208.blogspot.com/2011/04/oracle-data-pump.html