Database Refreshment Using EXPDP/IMPDP

DB REFRESH USING LOGICAL BACKUP

DB Refresh steps:
=================

1. take export(expdp) of source database(ctsp).

expdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull.log full=y



2. move the dumpfile to target database(ctsd) using scp.. if both the database running in same server this step is not required.

scp ctsfull.dmp oracle@<Target server IP>:/u02/app/oracle



3. Drop the application users and dont drop database default users like sys,system,dbsnmb,xdb.

If you give below command it will show username,created date. Mostly database default users will be created in same day.

select username,created from dba_users;

drop user raja cascade;



4. Before doing import(impdp) check the used space of source database and freespace in the target database. tablespaces names should 

same between source and target database then Target database each tablespace size should be more than or equal to source database tablespace.

Source:

select tablespace_name,sum(bytes/1024/1024) from dba_segments group by tablespace_name;

Target:

select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;




5. Start the import in taget database.

impdp system/manager directory=expdp_dir1 dumpfile=ctsfull.dmp logfile=ctsfull_impdp.log full=y




6. once competed compare the object counts between source and target databases.

select object_type,count(*) from dba_objects where status='VALID' group by object_type;