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;