Wednesday, July 30, 2014

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;

No comments:

Post a Comment