Database Refresh Activity plans with RMAN

Refresh activity Plan from PRODUCTION to TEST Database with Rman

Environment Detail:

Database Name OA_TEST OA_PROD
Oracle Version 10.2.0.4.0(EE) 10.2.0.4.0(EE)
Server Name TEST PROD

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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.