Data pump Schema Refresh
Schema refresh is an regular job for any DBA specially during migration projects, so today I decide to post about a schema refresh
Assuming here schema(SCOTT) is refreshed from source(PROD) to Target(TEST) on oracle 11g server
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‘
SQL > grant read, write on directory data_pump_dir to system;
Grant Succeeded.
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 &
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 objects with source
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
No comments:
Post a Comment