How to Refresh Database from PROD to TEST

Database refresh is performed from Source Database to Target Database .

There are two types of Database Refresh.

 Cold Refresh
Cold Database refresh is done from staging to Development or Vice-verse (Source will be down during the refresh since archive log is not enabled and we cannot put the database in hot backup mode).

Hot Refresh

Hot database refresh is done from Production databases to staging or development databases.( Source database will be in archive log mode and database will be up and running during the refresh).

How to Refresh Database from PROD to TEST Database.???

Steps:-1

Check Both Server Parameter and compare it. We need to collect below Parameter.

Server Parameter	PROD server (Source)	Test server (Target)
IP/Host	119.5.9.90/PROD.oracle.com	119.5.9.124/ TEST.oracle.com
Database name	IDEA	AIRTEL
Database size	89.2314453 GB	114.29248 GB
OS	SunOS	SunOS
Mount Point space	df -h	df -h
Database version	Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production	Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

 

Steps:-2

How to check upper Server Parameter??

Use below command. Check both server Parameter Source as well as Target.

Check Database name:-

SQL> select name from v$database;

NAME

---------

IDEA

Check Host name:-

oracle@PROD:~$ hostname

PROD.oracle.com

Check Database size:-

SQL> select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

DB Size in GB

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

   89.2314453

Check OS version:-

oracle@PROD:~$ uname

SunOS

oracle@drbrac1p:~$ uname -a

SunOS PROD.oracle.com 5.11 11.1 i86pc i386 i86pc

Steps:-3

Find IMP directory after that give read write permission.

SQL> select * from dba_directories;

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

Grant succeeded.

Steps:-4

Run on PROD Database (Export Full Database)

$ expdp system/SYSfprod2p#1944 full=y directory=PROD_IMP  dumpfile=PROD_exp_12062014.dmp logfile=PROD_exp_12062014.log

Steps:-5

Verify the Data in Source and Target Databases.

Note: In oracle 11g version: 11.2.0.1.0 there are about 44 distinct object_types comparing to previous versions this number is huge.


SQL> select *from v$version;

SQL> select distinct object_type from dba_objects;

SQL> select count(*) from dba_tables;

SQL> select count(*) from dba_users;

Steps:-6

Copy .dmp  file source to Target.

oracle@PROD:/IDEA/Backups$ scp -r PROD_exp_12062014.dmp oracle@119.5.9.124:/AIRTEL/Backups