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;