Refresh activity step through RMAN

Refresh activity step through RMAN

Refresh activity from PRODUCTION to TEST

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

Method :    RMAN DUPLICATE command

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 Enviroment.

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;

For check the password in our environment :

·         Go to bdump destination of the database.

·         Run cd..

·         cd scripts/rman

·         username we need to check the script of back

·         There are two hidden files which have password for the catalog and database.

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 datafiles detail.

select ‘set newname for datafile ‘||file#||’ to ”’||

replace(name,’/local/data/oracle/PROD/datafiles/’,'/data10/oradata/TEST/’)||”’;’ from v$datafile ;

E.g :

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_TEST/data/system1.dbf';

set newname for datafile 2 to '/u02/oradata/OA_TEST/data/system2.dbf';

set newname for datafile 3 to '/u02/oradata/OA_TEST/data/system3.dbf';

set newname for datafile 4 to '/u02/oradata/OA_TEST/data/system4.dbf';

set newname for datafile 5 to '/u02/oradata/OA_TEST/data/system5.dbf';

set newname for datafile 6 to '/u02/oradata/OA_TEST/data/system6.dbf';

set newname for datafile 7 to '/u02/oradata/OA_TEST/data/system7.dbf';

set newname for datafile 8 to '/u02/oradata/OA_TEST/data/system8.dbf';

set newname for datafile 9 to '/u02/oradata/OA_TEST/data/system9.dbf';

set newname for datafile 10 to '/u02/oradata/OA_TEST/data/appl_data1.dbf';

set newname for datafile 11 to '/u02/oradata/OA_TEST/data/appl_data2.dbf';

send 'NB_ORA_SERV=PolicyName,NB_ORA_CLIENT=ServerName';

duplicate target database to OA_TEST

logfile '/u02/oradata/OA_TEST/data/redo01.log' size 1000m,

'/u02/oradata/OA_TEST/data/redo02.log' size 1000m,

'/u02/oradata/OA_TEST/data/redo03.log' size 1000m,

'/u02/oradata/OA_TEST/data/redo04.log' size 1000m;

}

9. Run the duprman.sh command to start the activity.

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;