I want to recover database from backup using the TAG.
Please note that if use the duplicate command below then it will give you an error
DUPLICATE DATABASE prd1db TO tes1db from tag 'TAG20180319T142455' NOFILENAMECHECK;
Please note that if use the duplicate command below then it will give you an error
DUPLICATE DATABASE prd1db TO tes1db from tag 'TAG20180319T142455' NOFILENAMECHECK;
So what can be done?
The solution is find the scn number attached to controlfile under RMAN backups and us it to create the duplicate database as:
DUPLICATE DATABASE prd1db TO tes1db until SCN 2816323557926 NOFILENAMECHECK;
log in to RMAN using the recovery catalog after setting the env for source database in our case it is prd1db.
$ORACLE_HOME/bin/rman target / catalog rman@rcat
RMAN> list backup;
List of Backup Sets
===================
Please note that above command will list all the backups for the database. You can try getting the list between time stamps if you know.
The output of the above command will be something like
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431944 Incr 0 66.38G SBT_TAPE 00:07:35 19-MAR-18
BP Key: 31431951 Status: AVAILABLE Compressed: NO Tag: TAG20180319T142455
Handle: 1062193_TES1DB_66su69lo_1_1 Media: V_2370411_4083354
List of Datafiles in backup set 31431944
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1362.971174187
3 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/sysaux.1307.971174905
6 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/users.1282.971175289
7 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1361.971174187
10 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1360.971174187
13 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1359.971174189
16 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1358.971174189
19 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1357.971174189
22 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1356.971174189
25 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1355.971174189
28 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/enterprise.1288.971175261
31 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1338.971174499
34 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1337.971174499
39 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1291.971175153
41 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/refmu.1284.971175261
42 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1336.971174499
45 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1335.971174499
48 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1334.971174499
51 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1333.971174501
54 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/rds.1283.971175261
57 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1332.971174501
60 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1331.971174501
63 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1314.971174903
66 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts.1286.971175261
69 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_mds.1287.971175261
70 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_attrstore.1285.971175261
72 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1313.971174905
75 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1312.971174905
78 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1311.971174905
81 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1310.971174905
85 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1309.971174905
88 0 Incr 2816323555946 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1308.971174905
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431945 Incr 0 65.54G SBT_TAPE 00:07:43 19-MAR-18
BP Key: 31431952 Status: AVAILABLE Compressed: NO Tag: TAG20180319T142455
Handle: 1062193_TES1DB_65su69lo_1_1 Media: V_2368252_4083353
List of Datafiles in backup set 31431945
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/undotbs1.1353.971174219
9 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1350.971174219
12 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1348.971174219
15 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1346.971174219
18 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1344.971174219
21 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1342.971174219
24 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1340.971174219
30 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1339.971174219
33 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1323.971174569
36 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1293.971174927
37 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1292.971175151
38 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1322.971174569
44 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1321.971174571
47 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1320.971174571
50 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1319.971174571
56 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1318.971174571
59 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1317.971174571
62 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1315.971174815
71 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1299.971174927
74 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1298.971174927
77 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1297.971174927
80 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1296.971174927
83 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1295.971174927
87 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1294.971174927
89 0 Incr 2816323555942 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1290.971175175
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431946 Incr 0 35.00M SBT_TAPE 00:00:01 19-MAR-18
BP Key: 31431953 Status: AVAILABLE Compressed: NO Tag: TAG20180319T142455
Handle: 1062193_TES1DB_68su6a4b_1_1 Media: V_2370411_4083355
Control File Included: Ckp SCN: 2816323557926 Ckp time: 19-MAR-18
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431947 Incr 0 1.00M SBT_TAPE 00:00:00 19-MAR-18
BP Key: 31431954 Status: AVAILABLE Compressed: NO Tag: TAG20180319T142455
Handle: 1062193_TES1DB_69su6a4d_1_1 Media: V_2370411_4083357
SPFILE Included: Modification time: 19-MAR-18
SPFILE db_unique_name: TES1DBP
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
31431948 Incr 0 67.01G SBT_TAPE 00:06:50 19-MAR-18
BP Key: 31431955 Status: AVAILABLE Compressed: NO Tag: TAG20180319T142455
Handle: 1062193_TES1DB_67su6a4a_1_1 Media: V_2368252_4083356
List of Datafiles in backup set 31431948
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/system.1300.971174923
5 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/undotbs2.1354.971174219
8 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1352.971174219
11 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1351.971174219
14 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1349.971174219
17 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1347.971174219
20 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1345.971174219
23 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1343.971174219
26 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/const.1280.971175313
27 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/cdrnps.1281.971175291
29 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1341.971174219
32 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1330.971174557
35 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1329.971174557
40 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/dart_admin.1275.971175321
43 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1328.971174557
46 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1327.971174557
49 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1326.971174557
52 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1325.971174559
53 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_disco_ptm5_cache.1274.971175339
55 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/repo_disco_pstore.1279.971175321
58 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1324.971174559
61 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1316.971174805
64 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1306.971174923
65 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_default.1277.971175321
67 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_battrstore.1276.971175321
68 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/olts_ct_store.1278.971175321
73 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1305.971174923
76 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1304.971174923
79 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1303.971174923
82 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1302.971174923
84 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/indexes.1289.971175203
86 0 Incr 2816323557924 19-MAR-18 +DATA/TES1DBP/DATAFILE/product.1301.971174923
Life is not easy, in my case the source database had many incarnations so best find the incarnation you want to use
RMAN> list incarnation of database prd1db;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
70111 70159 PRD1DB 2306850230 PARENT 1 17-SEP-11
70111 70112 PRD1DB 2306850230 PARENT 995548 18-FEB-13
70111 72689 PRD1DB 2306850230 PARENT 1250093 21-FEB-13
70111 388729 PRD1DB 2306850230 PARENT 2790127477836 04-JUN-13
70111 390747 PRD1DB 2306850230 PARENT 2790137436622 05-JUN-13
70111 445093 PRD1DB 2306850230 PARENT 2790285891826 19-JUN-13
70111 447758 PRD1DB 2306850230 CURRENT 2790286767652 19-JUN-13
3756286 3756430 PRD1DB 2368813298 PARENT 22644025 02-OCT-13
3756286 3756287 PRD1DB 2368813298 CURRENT 2797234764372 22-JAN-15
7714199 7714327 PRD1DB 2397641027 PARENT 22644025 02-OCT-13
7714199 7714200 PRD1DB 2397641027 CURRENT 2801717021314 15-DEC-15
31308663 31308850 PRD1DB 2469971143 PARENT 1594143 11-MAY-16
31308663 31308664 PRD1DB 2469971143 CURRENT 2816323493241 15-MAR-18
31418462 31418649 PRD1DB 2470302757 PARENT 1594143 11-MAY-16
31418462 31418463 PRD1DB 2470302757 CURRENT 2816323493241 19-MAR-18
RMAN>
things for out interest are in red above. In nutshell my SCN number is 2816323557926 and DBID for the source database is 2470302757
Let's do the duplicate database. Make sure /etc/oratab has the tes1dbp1
Create the init_aux.ora file on the server where you want to create the new database tes1db
-- Create init parameter file to start the dupication
vi /u01/app/oracle/product/12.1.0.2/dbs/init_aux.ora
*.audit_file_dest='/u01/app/oracle/admin/tes1dbp/adump'
*.compatible='12.1.0.2.0'
*.db_create_file_dest='+DATA'
*.db_domain='example.com'
*.db_name='tes1db'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=180G
*.db_unique_name='tes1dbp'
*.standby_file_management='AUTO'
*.sga_target=2399141888
*.job_queue_processes=0
. oraenv
tes1dbp1
cd /u01/app/oracle/product/12.1.0.2/dbs/
sqlplus "/as sysdba"
startup nomount pfile='?/dbs/init_aux.ora';
exit;
create the RMAN session (ofcourse the tnsnames.ora file and listener.ora files are updated and done lsnrctl reload listener as grid)
rman
connect auxiliary sys/<sys password>@tes1dbp_DGMGRL
connect catalog rman/<rman password>@rcat
on RMAN prompt run the following command
run {
set dbid 2470302757
allocate auxiliary channel ch1 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;
allocate auxiliary channel ch2 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;
allocate auxiliary channel ch3 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;
allocate auxiliary channel ch4 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base/libobk.so,BLKSIZE=262144" TRACE 0;
DUPLICATE DATABASE prd1db TO tes1db until SCN 2816323557926 NOFILENAMECHECK;
}
Now the above command will succeed and the prd1db will be duplicated to tes1db up to the SCN 2816323557926 and that SCN is for TAG TAG20180319T142455
No comments:
Post a Comment