Refresh activity Plan from PRODUCTION to TEST Database with Rman

Environment Detail:

Execution plan for Refresh Activity with help of RMAN from production to TEST Environment

1. Check the database size of PRODUCTION database.

select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from dba_data_files;

2. Check the availability of disk on TEST environment.
Note: If sufficient space available then proceed otherwise raise request to Linux/Unix team

3. Check connectivity on TEST environment

E.g check TNSPING PROD, RMAN CATALOG

4. Take full backup of TEST environment.

E.g Cold backup/EXP full/EXPDP full

5. Match the user of TEST environment with the PRODUCTION Environment.

Note: If user same for both TEST and PROD Environment, then proceed further.
If USER are different take backup of TEST database user separately with expdp.
select count(*) from dba_users;

6. Keep the list of user password of TEST Environment.

--Use in 10G database:
select 'Alter user '||username||' identified by values '''||password||''' Default tablespace '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE ' ||TEMPORARY_TABLESPACE||' '||case when account_status= 'OPEN' then ';' else 'LOCKED;' end from dba_users;Use the command in 11g database:
select 'Alter user '||a.username||' identified by values '''||b.password||''' Default tablespace '||DEFAULT_TABLESPACE||' TEMPORARY TABLESPACE ' ||TEMPORARY_TABLESPACE||' '||case when account_status= 'OPEN' then ';' else 'LOCKED;' end from dba_users a,sys.user$ b where b.name = a.username;

7. Check RMAN connectivity :

rman catalog user/password@rman target username/password@OA_PROD auxiliary /

If connected then save below in duprman.sh file:

rman catalog username/password@rman target username/password@OA_PROD auxiliary / cmdfile duprefresh.rcv log duprefresh.log

8. Make duprefresh.rcv command file:
Go to production database run the following script
Change the location in replace function according to test. It will give you all data files detail.

select 'set newname for datafile '||file#||' to "'||
replace(name,'/local/data/oracle/PROD/datafiles/','/data10/oradata/TEST/')||"';' from v$datafile ;

9. Script for rman refresh activity:

run {
set until time "to_date('2013/02/20 03:25:30','yyyy/mm/dd HH24:MI:SS')";
allocate auxiliary channel C1 device type 'sbt_tape';
allocate auxiliary channel C2 device type 'sbt_tape';
allocate auxiliary channel C3 device type 'sbt_tape';
allocate auxiliary channel C4 device type 'sbt_tape';
set newname for datafile 1 to '/u02/oradata/OA/system1.dbf';
set newname for datafile 2 to '/u02/oradata/OA/system2.dbf';
set newname for datafile 3 to '/u02/oradata/OA/sysaux1.dbf';
set newname for datafile 4 to '/u02/oradata/OA/USER01.dbf';
set newname for datafile 5 to '/u02/oradata/OA/appl_data1.dbf';
set newname for datafile 6 to '/u02/oradata/OA/appl_data2.dbf';
send 'NB_ORA_SERV=PolicyName,NB_ORA_CLIENT=ServerName';
duplicate target database to OA_TEST
logfile '/u02/oradata/OA/redo01.log' size 1000m,
'/u02/oradata/OA/redo02.log' size 1000m,
'/u02/oradata/OA/redo03.log' size 1000m,
'/u02/oradata/OA/redo04.log' size 1000m;
}

10. Track the duprman.log file.

11. After restore completed change the database id.

nid target=/

12. Restore the user passwords according to TEST environment;

13. Register in RMAN catalog with following command:

rman> register database;