Rman backup from production and restore in test machine

We want to make a test database from production database. And our production database is on ASM and our test machine was linux file sysetm.
see the steps how we restored it.

1) Start the test database in nomount mode

sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 – Production on Tue Oct 2 08:30:29 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 385876488 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14680064 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2) Restore controlfile from backup

[oracle@test rman]$ rmant target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue Oct 2 08:31:07 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: prod (not mounted)
RMAN> restore controlfile from '/rman/rman/controlbkp.ctl';
Starting restore at 02-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=/database/oradata/prod/controlfile/control01.ctl
output filename=/database/oradata/prod/controlfile/control02.ctl
Finished restore at 02-OCT-12
RMAN> exit

Recovery Manager complete.

3) Alter the parameter for ASM to Local file system 

SQL>alter system set db_file_name_convert='+DGROUP1/prod/datafile/','/database/oradata/prod/datafile/','+ARCH/prod/datafile/','/database/oradata/prod/datafile/' scope=spfile;

SQL> conn /as sysdba
Connected.

4) shutdown immediate

5) startup mount again.

SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_file_name_convert string +DGROUP1/prod/datafile/, /database/oradata/prod/datafile/prod/datafile/, +ARCH/prod/datafile/, /database/oradata/prod/datafile/prod/datafile/
log_file_name_convert string
SQL> alter database mount;
Database altered.

[oracle@test rman]$ rman target /

Recovery Manager: Release 10.2.0.5.0 – Production on Tue Oct 2 08:33:07 2012

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: prod (DBID=754922818, not open)

6) catalog the backup 

RMAN> catalog start with '/rman/rman/';
searching for all files that match the pattern /rman/rman/
List of Files Unknown to the Database
=====================================

File Name: /rman/rman/db_795387360_2494_p1
File Name: /rman/rman/db_795387427_2496_p1
File Name: /rman/rman/db_795384073_2493_p1
File Name: /rman/rman/db_795387425_2495_p1
File Name: /rman/rman/db_795384073_2492_p1
File Name: /rman/rman/cf_795391755_2497_p1
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /rman/rman/cf_795391755_2497_p1
File Name: /rman/rman/db_795387360_2494_p1
File Name: /rman/rman/db_795387427_2496_p1
File Name: /rman/rman/db_795384073_2493_p1
File Name: /rman/rman/db_795387425_2495_p1
File Name: /rman/rman/db_795384073_2492_p1

7) Restore database

RMAN> restore database;
Starting restore at 02-OCT-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /database/oradata/prod/datafile/undotbs1.266.775673237
restoring datafile 00006 to /database/oradata/prod/datafile/undotbs1.275.776018139
restoring datafile 00012 to /database/oradata/prod/datafile/users.281.776018445
restoring datafile 00013 to /database/oradata/prod/datafile/users.282.776018461
restoring datafile 00015 to /database/oradata/prod/datafile/audit_data_space.287.776019629
restoring datafile 00018 to /database/oradata/prod/datafile/audit_data_space.290.776019729
restoring datafile 00021 to /database/oradata/prod/datafile/audit_data_space.293.776019841
restoring datafile 00024 to /database/oradata/prod/datafile/prod_data_space.296.776020123
restoring datafile 00027 to /database/oradata/prod/datafile/prod_data_space.299.776020161
restoring datafile 00030 to /database/oradata/prod/datafile/prod_data_space.302.776020199
restoring datafile 00033 to /database/oradata/prod/datafile/prod_data_space.305.776020241
restoring datafile 00036 to /database/oradata/prod/datafile/prod_data_space.308.776020293
restoring datafile 00039 to /database/oradata/prod/datafile/prod_data_space.311.776020479
restoring datafile 00042 to /database/oradata/prod/datafile/prod_data_space.314.776020529
restoring datafile 00045 to /database/oradata/prod/datafile/prod_data_space.317.776020613
restoring datafile 00048 to /database/oradata/prod/datafile/prod_data_space.320.776020675
restoring datafile 00051 to /database/oradata/prod/datafile/prod_data_space.323.776020741
restoring datafile 00054 to /database/oradata/prod/datafile/prod_data_space.326.776020793
restoring datafile 00057 to /database/oradata/prod/datafile/prod_data_space.329.776020865
restoring datafile 00060 to /database/oradata/prod/datafile/prod_data_space.332.776020917
restoring datafile 00063 to /database/oradata/prod/datafile/prod_data_space.335.776020981
restoring datafile 00066 to /database/oradata/prod/datafile/prod_index_space.338.776021257
restoring datafile 00069 to /database/oradata/prod/datafile/prod_index_space.341.776021385
restoring datafile 00072 to /database/oradata/prod/datafile/prod_index_space.344.776021425
restoring datafile 00075 to /database/oradata/prod/datafile/prod_index_space.347.776021505
restoring datafile 00078 to /database/oradata/prod/datafile/prod_index_space.350.776021709
restoring datafile 00081 to /database/oradata/prod/datafile/prod_idx_space.353.776021955
restoring datafile 00084 to /database/oradata/prod/datafile/prod_idx_space.356.776022037
restoring datafile 00087 to /database/oradata/prod/datafile/prod_idx_space.359.776022177
restoring datafile 00090 to /database/oradata/prod/datafile/prod_edi_space.362.776022505
restoring datafile 00093 to /database/oradata/prod/datafile/edi_space.365.776022673
restoring datafile 00096 to /database/oradata/prod/datafile/undotbs1.368.776075057
restoring datafile 00097 to /database/oradata/prod/datafile/audit_data_space.455.778002271
restoring datafile 00100 to /database/oradata/prod/datafile/edi_space.371.779817779
restoring datafile 00103 to /database/oradata/prod/datafile/users.374.779819333
restoring datafile 00106 to /database/oradata/prod/datafile/users.377.779819395
restoring datafile 00109 to /database/oradata/prod/datafile/users.380.779819479
restoring datafile 00112 to /database/oradata/prod/datafile/users.383.779819559
restoring datafile 00115 to /database/oradata/prod/datafile/users.386.779819705
restoring datafile 00118 to /database/oradata/prod/datafile/users01.dbf
restoring datafile 00121 to /database/oradata/prod/datafile/users2.dbf
restoring datafile 00125 to /database/oradata/prod/datafile/edi_space02.dbf
restoring datafile 00128 to /database/oradata/prod/datafile/users6.dbf
restoring datafile 00132 to /database/oradata/prod/datafile/undotbs2.389.787135891
restoring datafile 00135 to /database/oradata/prod/datafile/users.285.790855887
channel ORA_DISK_1: reading from backup piece /rman/rman/db_795384073_2492_p1
channel ORA_DISK_1: restored backup piece 1
piece handle=/rman/rman/db_795384073_2492_p1 tag=WHOLE_DATABASE_OPEN
channel ORA_DISK_1: restore complete, elapsed time: 01:36:06
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /database/oradata/prod/datafile/system.267.775673237
restoring datafile 00005 to /database/oradata/prod/datafile/undotbs2.257.775673301
restoring datafile 00009 to /database/oradata/prod/datafile/undotbs2.278.776018291
restoring datafile 00010 to /database/oradata/prod/datafile/users.279.776018383
restoring datafile 00014 to /database/oradata/prod/datafile/audit_data_space.286.776019565
restoring datafile 00017 to /database/oradata/prod/datafile/audit_data_space.289.776019715
restoring datafile 00020 to /database/oradata/prod/datafile/audit_data_space.292.776019827
restoring datafile 00023 to /database/oradata/prod/datafile/prod_data_space.295.776020085
restoring datafile 00026 to /database/oradata/prod/datafile/prod_data_space.298.776020147
restoring datafile 00029 to /database/oradata/prod/datafile/prod_data_space.301.776020187
restoring datafile 00032 to /database/oradata/prod/datafile/prod_data_space.304.776020227
restoring datafile 00035 to /database/oradata/prod/datafile/prod_data_space.307.776020279
restoring datafile 00038 to /database/oradata/prod/datafile/prod_data_space.310.776020459
restoring datafile 00041 to /database/oradata/prod/datafile/prod_data_space.313.776020515
restoring datafile 00044 to /database/oradata/prod/datafile/prod_data_space.316.776020559
restoring datafile 00047 to /database/oradata/prod/datafile/prod_data_space.319.776020659
restoring datafile 00050 to /database/oradata/prod/datafile/prod_data_space.322.776020729
restoring datafile 00053 to /database/oradata/prod/datafile/prod_data_space.325.776020771
restoring datafile 00056 to /database/oradata/prod/datafile/prod_data_space.328.776020853
restoring datafile 00059 to /database/oradata/prod/datafile/prod_data_space.331.776020893
restoring datafile 00062 to /database/oradata/prod/datafile/prod_data_space.334.776020959
restoring datafile 00065 to /database/oradata/prod/datafile/prod_data_space.337.776021027
restoring datafile 00068 to /database/oradata/prod/datafile/prod_index_space.340.776021367
restoring datafile 00071 to /database/oradata/prod/datafile/prod_index_space.343.776021413
restoring datafile 00074 to /database/oradata/prod/datafile/prod_index_space.346.776021459
restoring datafile 00077 to /database/oradata/prod/datafile/prod_index_space.349.776021695
restoring datafile 00080 to /database/oradata/prod/datafile/prod_idx_space.352.776021867
restoring datafile 00083 to /database/oradata/prod/datafile/prod_idx_space.355.776022023
restoring datafile 00086 to /database/oradata/prod/datafile/prod_idx_space.358.776022117
restoring datafile 00089 to /database/oradata/prod/datafile/prod_edi_space.361.776022435
restoring datafile 00092 to /database/oradata/prod/datafile/prod_edi_space.364.776022537
restoring datafile 00095 to /database/oradata/prod/datafile/undotbs1.367.776072827
restoring datafile 00099 to /database/oradata/prod/datafile/edi_space.370.779817759
restoring datafile 00102 to /database/oradata/prod/datafile/users.373.779819227
restoring datafile 00105 to /database/oradata/prod/datafile/users.376.779819371
restoring datafile 00108 to /database/oradata/prod/datafile/users.379.779819441
restoring datafile 00111 to /database/oradata/prod/datafile/users.382.779819539
restoring datafile 00114 to /database/oradata/prod/datafile/users.385.779819657
restoring datafile 00117 to /database/oradata/prod/datafile/users.1571.784664399
restoring datafile 00120 to /database/oradata/prod/datafile/users.2448.785063775
restoring datafile 00124 to /database/oradata/prod/datafile/users4.dbf
restoring datafile 00127 to /database/oradata/prod/datafile/users5.dbf
restoring datafile 00130 to /database/oradata/prod/datafile/users.1281.786901255
restoring datafile 00131 to /database/oradata/prod/datafile/undotbs2.393.787061983
restoring datafile 00134 to /database/oradata/prod/datafile/audit_data_space.387.790855765
channel ORA_DISK_1: reading from backup piece /rman/rman/db_795384073_2493_p1
channel ORA_DISK_1: restored backup piece 1
piece handle=/rman/rman/db_795384073_2493_p1 tag=WHOLE_DATABASE_OPEN
channel ORA_DISK_1: restore complete, elapsed time: 01:38:26
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /database/oradata/prod/datafile/sysaux.263.775673237
restoring datafile 00004 to /database/oradata/prod/datafile/users.265.775673239
restoring datafile 00007 to /database/oradata/prod/datafile/undotbs1.276.776018223
restoring datafile 00008 to /database/oradata/prod/datafile/undotbs2.277.776018271
restoring datafile 00011 to /database/oradata/prod/datafile/users.280.776018403
restoring datafile 00016 to /database/oradata/prod/datafile/audit_data_space.288.776019699
restoring datafile 00019 to /database/oradata/prod/datafile/audit_data_space.291.776019747
restoring datafile 00022 to /database/oradata/prod/datafile/prod_data_space.294.776019981
restoring datafile 00025 to /database/oradata/prod/datafile/prod_data_space.297.776020135
restoring datafile 00028 to /database/oradata/prod/datafile/prod_data_space.300.776020175
restoring datafile 00031 to /database/oradata/prod/datafile/prod_data_space.303.776020213
restoring datafile 00034 to /database/oradata/prod/datafile/prod_data_space.306.776020255
restoring datafile 00037 to /database/oradata/prod/datafile/prod_data_space.309.776020309
restoring datafile 00040 to /database/oradata/prod/datafile/prod_data_space.312.776020503
restoring datafile 00043 to /database/oradata/prod/datafile/prod_data_space.315.776020545
restoring datafile 00046 to /database/oradata/prod/datafile/prod_data_space.318.776020647
restoring datafile 00049 to /database/oradata/prod/datafile/prod_data_space.321.776020715
restoring datafile 00052 to /database/oradata/prod/datafile/prod_data_space.324.776020755
restoring datafile 00055 to /database/oradata/prod/datafile/prod_data_space.327.776020839
restoring datafile 00058 to /database/oradata/prod/datafile/prod_data_space.330.776020879
restoring datafile 00061 to /database/oradata/prod/datafile/prod_data_space.333.776020935
restoring datafile 00064 to /database/oradata/prod/datafile/prod_data_space.336.776021013
restoring datafile 00067 to /database/oradata/prod/datafile/prod_index_space.339.776021327
restoring datafile 00070 to /database/oradata/prod/datafile/prod_index_space.342.776021397
restoring datafile 00073 to /database/oradata/prod/datafile/prod_index_space.345.776021441
restoring datafile 00076 to /database/oradata/prod/datafile/prod_index_space.348.776021681
restoring datafile 00079 to /database/oradata/prod/datafile/prod_index_space.351.776021723
restoring datafile 00082 to /database/oradata/prod/datafile/prod_idx_space.354.776021975
restoring datafile 00085 to /database/oradata/prod/datafile/prod_idx_space.357.776022079
restoring datafile 00088 to /database/oradata/prod/datafile/prod_edi_space.360.776022359
restoring datafile 00091 to /database/oradata/prod/datafile/prod_edi_space.363.776022519
restoring datafile 00094 to /database/oradata/prod/datafile/edi_space.366.776022729
restoring datafile 00098 to /database/oradata/prod/datafile/edi_space.369.779817709
restoring datafile 00101 to /database/oradata/prod/datafile/edi_space.372.779818523
restoring datafile 00104 to /database/oradata/prod/datafile/users.375.779819355
restoring datafile 00107 to /database/oradata/prod/datafile/users.378.779819417
restoring datafile 00110 to /database/oradata/prod/datafile/users.381.779819507
restoring datafile 00113 to /database/oradata/prod/datafile/users.384.779819621
restoring datafile 00116 to /database/oradata/prod/datafile/users.388.779865697
restoring datafile 00119 to /database/oradata/prod/datafile/users.1222.784984671
restoring datafile 00122 to /database/oradata/prod/datafile/edi_space01.dbf
restoring datafile 00123 to /database/oradata/prod/datafile/users3.dbf
restoring datafile 00126 to /database/oradata/prod/datafile/edi_space03.dbf
restoring datafile 00129 to /database/oradata/prod/datafile/users.1231.786791795
restoring datafile 00133 to /database/oradata/prod/datafile/users.270.788951961
channel ORA_DISK_1: reading from backup piece /rman/rman/db_795387360_2494_p1
channel ORA_DISK_1: restored backup piece 1
piece handle=/rman/rman/db_795387360_2494_p1 tag=WHOLE_DATABASE_OPEN
channel ORA_DISK_1: restore complete, elapsed time: 01:37:46
Finished restore at 02-OCT-12
RMAN> exit

Restore completed successfully.

8) Recover database

9) alter database open reselogs.

10) Use production database for test

 

Advertisements

About Sher khan

Senior Oracle DBA, Oracle 10g , Oracle 11g OCE ,Oracle 12C and Oracle 11g OPN certified Specialist, OCP Certified in Oracle 9i,10g , 11g and 12C. I am working in Oracle technology since 2004. Currently working in U.A.E Email: sher487@hotmail.com
This entry was posted in rman. Bookmark the permalink.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s