Escenario 2
El escenario es el siguiente, luego de un upgrade sobre una base 10.2.0.4 para actualizar a 11.1.0.6, la base presento un fallo irrecuperable, y hubo que restaurar del backup tomado con anterioridad.
La base esta en modo NOARCHIVELOG, y la estrategia de backup es incremental.
Obtener la estructura de archivos de la base de datos
SQL> SELECT file_name FROM dba_data_files;
/TGEN/data1/sys_01_TGEN.dbf
/TGEN/data1/admin_01_TGEN.dbf
/TGEN/data1/developer_01_TGEN.dbf
/TGEN/data1/uyadmin_01_TGEN.dbf
/TGEN/data1/pyadmin_01_TGEN.dbf
/TGEN/data1/undo_01_TGEN.dbf
/TGEN/data1/sysaux_01_TGEN.dbf
/TGEN/data1/odm_01_TGEN.dbf
/TGEN/data1/developer_02_TGEN.dbf
/TGEN/data2/imei_data_01_TGEN.dbf
/TGEN/data2/tools_02_TGEN.dbf
/TGEN/data2/ENGAGECLARO_DATA01.DBF
/TGEN/data2/ENGAGECLARO_INDX01.DBF
/TGEN/data2/ENGAGECLARO_BLOB01.DBF
/TGEN/data1/tools_01_TGEN.dbf
/TGEN/data1/rbsg_01_TGEN.dbf
/TGEN/data2/ENGAGECLARO_WKFD01.DBF
/TGEN/data1/testpcm_01_TGEN.dbf
/TGEN/data1/owb_01_TGEN.dbf
/TGEN/data2/ENGAGECLARO_WKFD_INDX01.DBF
/TGEN/data2/ENGAGECLARO_DSST01.DBF
/TGEN/data2/ENGAGECLARO_DSST_INDX01.DBF
/TGEN/data2/ENGAGECLARO_MD01.DBF
/TGEN/data2/ENGAGECLARO_MD_INDX01.DBF
/TGEN/data2/ENGAGECLARO_SD01.DBF
/TGEN/data2/ENGAGECLARO_SD_INDX01.DBF
/TGEN/data2/ENGAGECLARO_ETL01.DBF
/TGEN/data2/ENGAGECLARO_ETL_INDX01.DBF
/TGEN/data2/ENGAGECLARO_EB01.DBF
/TGEN/data2/ENGAGECLARO_EB_INDX01.DBF
/TGEN/data2/dpwc_01_TGEN.dbf
/TGEN/data1/dpwc_02_TGEN.dbf
/TGEN/data2/pwc_01_TGEN.dbf
33 rows selected.
SQL> SELECT group#, member FROM v$logfile;
4 /TGEN/online/log_04_TGEN.dbf
3 /TGEN/online/log_03_TGEN.dbf
2 /TGEN/online/log_02_TGEN.dbf
1 /TGEN/online/log_01_TGEN.dbf
SQL> SELECT name FROM v$controlfile;
/TGEN/data1/ctrl_01_TGEN.ctl
/TGEN/data1/ctrl_02_TGEN.ctl
Verificar los backups y estado de los mismos
Ahora necesitamos conocer cuál fue el ultimo INCREMENTAL LEVEL 0 que se realizó de la base.
Contamos con un backup incremental level 0, y 3 backups incrementales level 1. La recuperacion sera entonces, un restore al level 0, y un recover al tercer level 1.
Detalle del backup necesario para hacer el restore
Vemos en detalle el ultimo backup incremental level 0
RMAN> list backup tag=’TAG20110729T192422′; –bkp level 0
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
41636395 Incr 0 3.94G SBT_TAPE 00:04:19 29-JUL-11
BP Key: 41636399 Status: AVAILABLE Compressed: NO Tag: TAG20110729T192422
Handle: eqmim4v7_1_1 Media:
List of Datafiles in backup set 41636395
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
4 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/tools_01_TGEN.dbf
7 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/undo_01_TGEN.dbf
9 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/sysaux_01_TGEN.dbf
10 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/odm_01_TGEN.dbf
13 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/tools_02_TGEN.dbf
20 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/owb_01_TGEN.dbf
22 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_WKFD_INDX01.DBF
25 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_MD01.DBF
28 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_SD_INDX01.DBF
30 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_ETL_INDX01.DBF
33 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/dpwc_01_TGEN.dbf
37 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/pwc_01_TGEN.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
41636396 Incr 0 17.33G SBT_TAPE 00:04:20 29-JUL-11
BP Key: 41636400 Status: AVAILABLE Compressed: NO Tag: TAG20110729T192422
Handle: epmim4v7_1_1 Media:
List of Datafiles in backup set 41636396
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/sys_01_TGEN.dbf
2 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/admin_01_TGEN.dbf
3 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/rbsg_01_TGEN.dbf
6 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/pyadmin_01_TGEN.dbf
11 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/developer_02_TGEN.dbf
12 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/imei_data_01_TGEN.dbf
19 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/testpcm_01_TGEN.dbf
21 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_WKFD01.DBF
23 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_DSST01.DBF
26 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_MD_INDX01.DBF
27 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_SD01.DBF
31 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_EB01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
41636397 Incr 0 15.76G SBT_TAPE 00:02:45 29-JUL-11
BP Key: 41636401 Status: AVAILABLE Compressed: NO Tag: TAG20110729T192422
Handle: ermim57h_1_1 Media:
List of Datafiles in backup set 41636397
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
5 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/uyadmin_01_TGEN.dbf
8 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/developer_01_TGEN.dbf
16 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_DATA01.DBF
17 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_INDX01.DBF
18 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_BLOB01.DBF
24 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_DSST_INDX01.DBF
29 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_ETL01.DBF
32 0 Incr 7967655692154 29-JUL-11 /TGEN/data2/ENGAGECLARO_EB_INDX01.DBF
35 0 Incr 7967655692154 29-JUL-11 /TGEN/data1/dpwc_02_TGEN.dbf
RMAN> list backup tag=’TAG20110812T195748′; –bkp level 1
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
41867698 Incr 1 88.75M SBT_TAPE 00:02:09 12-AUG-11
BP Key: 41867702 Status: AVAILABLE Compressed: NO Tag: TAG20110812T195748
Handle: f5mjr45u_1_1 Media:
List of Datafiles in backup set 41867698
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/sys_01_TGEN.dbf
2 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/admin_01_TGEN.dbf
3 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/rbsg_01_TGEN.dbf
6 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/pyadmin_01_TGEN.dbf
11 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/developer_02_TGEN.dbf
12 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/imei_data_01_TGEN.dbf
19 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/testpcm_01_TGEN.dbf
21 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_WKFD01.DBF
23 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_DSST01.DBF
26 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_MD_INDX01.DBF
27 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_SD01.DBF
31 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_EB01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
41867699 Incr 1 735.50M SBT_TAPE 00:02:19 12-AUG-11
BP Key: 41867703 Status: AVAILABLE Compressed: NO Tag: TAG20110812T195748
Handle: f6mjr4a5_1_1 Media:
List of Datafiles in backup set 41867699
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
4 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/tools_01_TGEN.dbf
7 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/undo_01_TGEN.dbf
9 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/sysaux_01_TGEN.dbf
10 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/odm_01_TGEN.dbf
13 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/tools_02_TGEN.dbf
20 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/owb_01_TGEN.dbf
22 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_WKFD_INDX01.DBF
25 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_MD01.DBF
28 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_SD_INDX01.DBF
30 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_ETL_INDX01.DBF
33 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/dpwc_01_TGEN.dbf
37 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/pwc_01_TGEN.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
41867700 Incr 1 53.25M SBT_TAPE 00:02:15 12-AUG-11
BP Key: 41867704 Status: AVAILABLE Compressed: NO Tag: TAG20110812T195748
Handle: f7mjr4en_1_1 Media:
List of Datafiles in backup set 41867700
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
5 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/uyadmin_01_TGEN.dbf
8 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/developer_01_TGEN.dbf
16 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_DATA01.DBF
17 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_INDX01.DBF
18 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_BLOB01.DBF
24 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_DSST_INDX01.DBF
29 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_ETL01.DBF
32 1 Incr 7973231608225 12-AUG-11 /TGEN/data2/ENGAGECLARO_EB_INDX01.DBF
35 1 Incr 7973231608225 12-AUG-11 /TGEN/data1/dpwc_02_TGEN.dbf
Restaurar la bd desde el backup
Primero validamos:
run
{
allocate CHANNEL t1 type ‘SBT_TAPE’
parms ‘ENV=(tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
restore database validate;
}
allocated channel: t1
channel t1: sid=159 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting restore at 19-AUG-11
channel t1: starting validation of datafile backupset
channel t1: reading from backup piece eqmim4v7_1_1
channel t1: restored backup piece 1
piece handle=eqmim4v7_1_1 tag=TAG20110729T192422
channel t1: validation complete, elapsed time: 00:01:25
channel t1: starting validation of datafile backupset
channel t1: reading from backup piece epmim4v7_1_1
channel t1: restored backup piece 1
piece handle=epmim4v7_1_1 tag=TAG20110729T192422
channel t1: validation complete, elapsed time: 00:04:15
channel t1: starting validation of datafile backupset
channel t1: reading from backup piece ermim57h_1_1
channel t1: restored backup piece 1
piece handle=ermim57h_1_1 tag=TAG20110729T192422
channel t1: validation complete, elapsed time: 00:01:55
Finished restore at 19-AUG-11
released channel: t1
Ahora si, ejecutamos el restore:
run
{
allocate CHANNEL t1 type ‘SBT_TAPE’
parms ‘ENV=(tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
restore Database;
}
{
allocate CHANNEL t1 type ‘SBT_TAPE’
parms ‘ENV=(tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
restore Database;
}
allocated channel: t1
channel t1: sid=159 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting restore at 19-AUG-11
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /TGEN/data1/tools_01_TGEN.dbf
restoring datafile 00007 to /TGEN/data1/undo_01_TGEN.dbf
restoring datafile 00009 to /TGEN/data1/sysaux_01_TGEN.dbf
restoring datafile 00010 to /TGEN/data1/odm_01_TGEN.dbf
restoring datafile 00013 to /TGEN/data2/tools_02_TGEN.dbf
restoring datafile 00020 to /TGEN/data1/owb_01_TGEN.dbf
restoring datafile 00022 to /TGEN/data2/ENGAGECLARO_WKFD_INDX01.DBF
restoring datafile 00025 to /TGEN/data2/ENGAGECLARO_MD01.DBF
restoring datafile 00028 to /TGEN/data2/ENGAGECLARO_SD_INDX01.DBF
restoring datafile 00030 to /TGEN/data2/ENGAGECLARO_ETL_INDX01.DBF
restoring datafile 00033 to /TGEN/data2/dpwc_01_TGEN.dbf
restoring datafile 00037 to /TGEN/data2/pwc_01_TGEN.dbf
channel t1: reading from backup piece eqmim4v7_1_1
channel t1: restored backup piece 1
piece handle=eqmim4v7_1_1 tag=TAG20110729T192422
channel t1: restore complete, elapsed time: 00:09:26
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /TGEN/data1/sys_01_TGEN.dbf
restoring datafile 00002 to /TGEN/data1/admin_01_TGEN.dbf
restoring datafile 00003 to /TGEN/data1/rbsg_01_TGEN.dbf
restoring datafile 00006 to /TGEN/data1/pyadmin_01_TGEN.dbf
restoring datafile 00011 to /TGEN/data1/developer_02_TGEN.dbf
restoring datafile 00012 to /TGEN/data2/imei_data_01_TGEN.dbf
restoring datafile 00019 to /TGEN/data1/testpcm_01_TGEN.dbf
restoring datafile 00021 to /TGEN/data2/ENGAGECLARO_WKFD01.DBF
restoring datafile 00023 to /TGEN/data2/ENGAGECLARO_DSST01.DBF
restoring datafile 00026 to /TGEN/data2/ENGAGECLARO_MD_INDX01.DBF
restoring datafile 00027 to /TGEN/data2/ENGAGECLARO_SD01.DBF
restoring datafile 00031 to /TGEN/data2/ENGAGECLARO_EB01.DBF
channel t1: reading from backup piece epmim4v7_1_1
channel t1: restored backup piece 1
piece handle=epmim4v7_1_1 tag=TAG20110729T192422
channel t1: restore complete, elapsed time: 00:10:16
channel t1: starting datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to /TGEN/data1/uyadmin_01_TGEN.dbf
restoring datafile 00008 to /TGEN/data1/developer_01_TGEN.dbf
restoring datafile 00016 to /TGEN/data2/ENGAGECLARO_DATA01.DBF
restoring datafile 00017 to /TGEN/data2/ENGAGECLARO_INDX01.DBF
restoring datafile 00018 to /TGEN/data2/ENGAGECLARO_BLOB01.DBF
restoring datafile 00024 to /TGEN/data2/ENGAGECLARO_DSST_INDX01.DBF
restoring datafile 00029 to /TGEN/data2/ENGAGECLARO_ETL01.DBF
restoring datafile 00032 to /TGEN/data2/ENGAGECLARO_EB_INDX01.DBF
restoring datafile 00035 to /TGEN/data1/dpwc_02_TGEN.dbf
channel t1: reading from backup piece ermim57h_1_1
channel t1: restored backup piece 1
piece handle=ermim57h_1_1 tag=TAG20110729T192422
channel t1: restore complete, elapsed time: 00:09:16
Finished restore at 19-AUG-11
released channel: t1
Recover de la bd a partir de los backups incrementales
Para asegurar que la base se restaure exactamente al punto del ultimo backup incremental level 1, podemos especificarle el SCN, obtenido en el detalle del ultimo backup incremental level 1.
run
{
allocate CHANNEL t1 type ‘SBT_TAPE’
parms ‘ENV=(tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
Recover Database Until Scn 7973231608225;
}
allocated channel: t1
channel t1: sid=159 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting recover at 19-AUG-11
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /TGEN/data1/sys_01_TGEN.dbf
destination for restore of datafile 00002: /TGEN/data1/admin_01_TGEN.dbf
destination for restore of datafile 00003: /TGEN/data1/rbsg_01_TGEN.dbf
destination for restore of datafile 00006: /TGEN/data1/pyadmin_01_TGEN.dbf
destination for restore of datafile 00011: /TGEN/data1/developer_02_TGEN.dbf
destination for restore of datafile 00012: /TGEN/data2/imei_data_01_TGEN.dbf
destination for restore of datafile 00019: /TGEN/data1/testpcm_01_TGEN.dbf
destination for restore of datafile 00021: /TGEN/data2/ENGAGECLARO_WKFD01.DBF
destination for restore of datafile 00023: /TGEN/data2/ENGAGECLARO_DSST01.DBF
destination for restore of datafile 00026: /TGEN/data2/ENGAGECLARO_MD_INDX01.DBF
destination for restore of datafile 00027: /TGEN/data2/ENGAGECLARO_SD01.DBF
destination for restore of datafile 00031: /TGEN/data2/ENGAGECLARO_EB01.DBF
channel t1: reading from backup piece etmimaiv_1_1
channel t1: restored backup piece 1
piece handle=etmimaiv_1_1 tag=TAG20110729T210013
channel t1: restore complete, elapsed time: 00:01:26
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /TGEN/data1/tools_01_TGEN.dbf
destination for restore of datafile 00007: /TGEN/data1/undo_01_TGEN.dbf
destination for restore of datafile 00009: /TGEN/data1/sysaux_01_TGEN.dbf
destination for restore of datafile 00010: /TGEN/data1/odm_01_TGEN.dbf
destination for restore of datafile 00013: /TGEN/data2/tools_02_TGEN.dbf
destination for restore of datafile 00020: /TGEN/data1/owb_01_TGEN.dbf
destination for restore of datafile 00022: /TGEN/data2/ENGAGECLARO_WKFD_INDX01.DBF
destination for restore of datafile 00025: /TGEN/data2/ENGAGECLARO_MD01.DBF
destination for restore of datafile 00028: /TGEN/data2/ENGAGECLARO_SD_INDX01.DBF
destination for restore of datafile 00030: /TGEN/data2/ENGAGECLARO_ETL_INDX01.DBF
destination for restore of datafile 00033: /TGEN/data2/dpwc_01_TGEN.dbf
destination for restore of datafile 00037: /TGEN/data2/pwc_01_TGEN.dbf
channel t1: reading from backup piece eumimang_1_1
channel t1: restored backup piece 1
piece handle=eumimang_1_1 tag=TAG20110729T210013
channel t1: restore complete, elapsed time: 00:00:03
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /TGEN/data1/uyadmin_01_TGEN.dbf
destination for restore of datafile 00008: /TGEN/data1/developer_01_TGEN.dbf
destination for restore of datafile 00016: /TGEN/data2/ENGAGECLARO_DATA01.DBF
destination for restore of datafile 00017: /TGEN/data2/ENGAGECLARO_INDX01.DBF
destination for restore of datafile 00018: /TGEN/data2/ENGAGECLARO_BLOB01.DBF
destination for restore of datafile 00024: /TGEN/data2/ENGAGECLARO_DSST_INDX01.DBF
destination for restore of datafile 00029: /TGEN/data2/ENGAGECLARO_ETL01.DBF
destination for restore of datafile 00032: /TGEN/data2/ENGAGECLARO_EB_INDX01.DBF
destination for restore of datafile 00035: /TGEN/data1/dpwc_02_TGEN.dbf
channel t1: reading from backup piece evmimas2_1_1
channel t1: restored backup piece 1
piece handle=evmimas2_1_1 tag=TAG20110729T210013
channel t1: restore complete, elapsed time: 00:00:04
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /TGEN/data1/uyadmin_01_TGEN.dbf
destination for restore of datafile 00008: /TGEN/data1/developer_01_TGEN.dbf
destination for restore of datafile 00016: /TGEN/data2/ENGAGECLARO_DATA01.DBF
destination for restore of datafile 00017: /TGEN/data2/ENGAGECLARO_INDX01.DBF
destination for restore of datafile 00018: /TGEN/data2/ENGAGECLARO_BLOB01.DBF
destination for restore of datafile 00024: /TGEN/data2/ENGAGECLARO_DSST_INDX01.DBF
destination for restore of datafile 00029: /TGEN/data2/ENGAGECLARO_ETL01.DBF
destination for restore of datafile 00032: /TGEN/data2/ENGAGECLARO_EB_INDX01.DBF
destination for restore of datafile 00035: /TGEN/data1/dpwc_02_TGEN.dbf
channel t1: reading from backup piece f3mj8lou_1_1
channel t1: restored backup piece 1
piece handle=f3mj8lou_1_1 tag=TAG20110805T195651
channel t1: restore complete, elapsed time: 00:01:15
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /TGEN/data1/sys_01_TGEN.dbf
destination for restore of datafile 00002: /TGEN/data1/admin_01_TGEN.dbf
destination for restore of datafile 00003: /TGEN/data1/rbsg_01_TGEN.dbf
destination for restore of datafile 00006: /TGEN/data1/pyadmin_01_TGEN.dbf
destination for restore of datafile 00011: /TGEN/data1/developer_02_TGEN.dbf
destination for restore of datafile 00012: /TGEN/data2/imei_data_01_TGEN.dbf
destination for restore of datafile 00019: /TGEN/data1/testpcm_01_TGEN.dbf
destination for restore of datafile 00021: /TGEN/data2/ENGAGECLARO_WKFD01.DBF
destination for restore of datafile 00023: /TGEN/data2/ENGAGECLARO_DSST01.DBF
destination for restore of datafile 00026: /TGEN/data2/ENGAGECLARO_MD_INDX01.DBF
destination for restore of datafile 00027: /TGEN/data2/ENGAGECLARO_SD01.DBF
destination for restore of datafile 00031: /TGEN/data2/ENGAGECLARO_EB01.DBF
channel t1: reading from backup piece f1mj8lg5_1_1
channel t1: restored backup piece 1
piece handle=f1mj8lg5_1_1 tag=TAG20110805T195651
channel t1: restore complete, elapsed time: 00:01:36
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /TGEN/data1/tools_01_TGEN.dbf
destination for restore of datafile 00007: /TGEN/data1/undo_01_TGEN.dbf
destination for restore of datafile 00009: /TGEN/data1/sysaux_01_TGEN.dbf
destination for restore of datafile 00010: /TGEN/data1/odm_01_TGEN.dbf
destination for restore of datafile 00013: /TGEN/data2/tools_02_TGEN.dbf
destination for restore of datafile 00020: /TGEN/data1/owb_01_TGEN.dbf
destination for restore of datafile 00022: /TGEN/data2/ENGAGECLARO_WKFD_INDX01.DBF
destination for restore of datafile 00025: /TGEN/data2/ENGAGECLARO_MD01.DBF
destination for restore of datafile 00028: /TGEN/data2/ENGAGECLARO_SD_INDX01.DBF
destination for restore of datafile 00030: /TGEN/data2/ENGAGECLARO_ETL_INDX01.DBF
destination for restore of datafile 00033: /TGEN/data2/dpwc_01_TGEN.dbf
destination for restore of datafile 00037: /TGEN/data2/pwc_01_TGEN.dbf
channel t1: reading from backup piece f2mj8lkd_1_1
channel t1: restored backup piece 1
piece handle=f2mj8lkd_1_1 tag=TAG20110805T195651
channel t1: restore complete, elapsed time: 00:00:35
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /TGEN/data1/sys_01_TGEN.dbf
destination for restore of datafile 00002: /TGEN/data1/admin_01_TGEN.dbf
destination for restore of datafile 00003: /TGEN/data1/rbsg_01_TGEN.dbf
destination for restore of datafile 00006: /TGEN/data1/pyadmin_01_TGEN.dbf
destination for restore of datafile 00011: /TGEN/data1/developer_02_TGEN.dbf
destination for restore of datafile 00012: /TGEN/data2/imei_data_01_TGEN.dbf
destination for restore of datafile 00019: /TGEN/data1/testpcm_01_TGEN.dbf
destination for restore of datafile 00021: /TGEN/data2/ENGAGECLARO_WKFD01.DBF
destination for restore of datafile 00023: /TGEN/data2/ENGAGECLARO_DSST01.DBF
destination for restore of datafile 00026: /TGEN/data2/ENGAGECLARO_MD_INDX01.DBF
destination for restore of datafile 00027: /TGEN/data2/ENGAGECLARO_SD01.DBF
destination for restore of datafile 00031: /TGEN/data2/ENGAGECLARO_EB01.DBF
channel t1: reading from backup piece f5mjr45u_1_1
channel t1: restored backup piece 1
piece handle=f5mjr45u_1_1 tag=TAG20110812T195748
channel t1: restore complete, elapsed time: 00:01:15
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /TGEN/data1/tools_01_TGEN.dbf
destination for restore of datafile 00007: /TGEN/data1/undo_01_TGEN.dbf
destination for restore of datafile 00009: /TGEN/data1/sysaux_01_TGEN.dbf
destination for restore of datafile 00010: /TGEN/data1/odm_01_TGEN.dbf
destination for restore of datafile 00013: /TGEN/data2/tools_02_TGEN.dbf
destination for restore of datafile 00020: /TGEN/data1/owb_01_TGEN.dbf
destination for restore of datafile 00022: /TGEN/data2/ENGAGECLARO_WKFD_INDX01.DBF
destination for restore of datafile 00025: /TGEN/data2/ENGAGECLARO_MD01.DBF
destination for restore of datafile 00028: /TGEN/data2/ENGAGECLARO_SD_INDX01.DBF
destination for restore of datafile 00030: /TGEN/data2/ENGAGECLARO_ETL_INDX01.DBF
destination for restore of datafile 00033: /TGEN/data2/dpwc_01_TGEN.dbf
destination for restore of datafile 00037: /TGEN/data2/pwc_01_TGEN.dbf
channel t1: reading from backup piece f6mjr4a5_1_1
channel t1: restored backup piece 1
piece handle=f6mjr4a5_1_1 tag=TAG20110812T195748
channel t1: restore complete, elapsed time: 00:00:25
channel t1: starting incremental datafile backupset restore
channel t1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00005: /TGEN/data1/uyadmin_01_TGEN.dbf
destination for restore of datafile 00008: /TGEN/data1/developer_01_TGEN.dbf
destination for restore of datafile 00016: /TGEN/data2/ENGAGECLARO_DATA01.DBF
destination for restore of datafile 00017: /TGEN/data2/ENGAGECLARO_INDX01.DBF
destination for restore of datafile 00018: /TGEN/data2/ENGAGECLARO_BLOB01.DBF
destination for restore of datafile 00024: /TGEN/data2/ENGAGECLARO_DSST_INDX01.DBF
destination for restore of datafile 00029: /TGEN/data2/ENGAGECLARO_ETL01.DBF
destination for restore of datafile 00032: /TGEN/data2/ENGAGECLARO_EB_INDX01.DBF
destination for restore of datafile 00035: /TGEN/data1/dpwc_02_TGEN.dbf
channel t1: reading from backup piece f7mjr4en_1_1
channel t1: restored backup piece 1
piece handle=f7mjr4en_1_1 tag=TAG20110812T195748
channel t1: restore complete, elapsed time: 00:00:07
starting media recovery
unable to find archive log
archive log thread=1 sequence=25926
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/19/2011 14:40:41
RMAN-06054: media recovery requesting unknown log: thread 1 seq 25926 lowscn 7973231608225
Abrir la bd con la opcion resetlogs
Finalmente,
SQL> select open_mode from v$database;
OPEN_MODE
———-
MOUNTED
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
Importante saber luego de abrir la bd con la opcion resetlogs
En caso de querer recuperar nuevamente la BD con la misma estrategia, puede ocurrir el siguiente error en el recover:
RMAN> run
{
allocate CHANNEL t1 type ‘SBT_TAPE’
parms ‘ENV=(tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
Recover Database Until Scn 7973231608225;
}2> 3> 4> 5> 6>
allocated channel: t1
channel t1: sid=156 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting recover at 23-AUG-11
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/23/2011 11:19:09
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20208: UNTIL CHANGE is before RESETLOGS change
Esto ocurre porque la opcion RESETLOGS, inserta una nueva encarnacion de la base, veamos esto en detalle:
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
69619 69637 TGEN 1258027179 PARENT 2012336260921 25-SEP-01
69619 69638 TGEN 1258027179 PARENT 2155408815219 27-MAR-07
69619 69620 TGEN 1258027179 PARENT 2155408895244 27-MAR-07
69619 42060472 TGEN 1258027179 CURRENT 7973231608226 19-AUG-11
La encarnacion del backup es la marcada en rojo.
Para retornar, ejecutamos:
RMAN> RESET DATABASE TO INCARNATION 69620;
database reset to incarnation 69620
RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 683671552 bytes
Fixed Size 2058656 bytes
Variable Size 603981408 bytes
Database Buffers 75497472 bytes
Redo Buffers 2134016 bytes
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
69619 69637 TGEN 1258027179 PARENT 2012336260921 25-SEP-01
69619 69638 TGEN 1258027179 PARENT 2155408815219 27-MAR-07
69619 69620 TGEN 1258027179 CURRENT 2155408895244 27-MAR-07
69619 42060472 TGEN 1258027179 ORPHAN 7973231608226 19-AUG-11
Una vez hecho esto, podemos ejecutar nuevamente el recover.
Es posible que tambien tengamos que recuperar los controlfiles, debido al siguiente error:
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: ‘/TGEN/data1/sys_01_TGEN.dbf’
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 1: ‘/TGEN/data1/sys_01_TGEN.dbf’
RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 683671552 bytes
Fixed Size 2058656 bytes
Variable Size 603981408 bytes
Database Buffers 75497472 bytes
Redo Buffers 2134016 bytes
RMAN> RUN
{
allocate CHANNEL t1 type ‘SBT_TAPE’
parms ‘ENV=(tdpo_optfile=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)’;
RESTORE Controlfile FROM tag=’TAG20110812T200520′;
}2> 3> 4> 5> 6>
allocated channel: t1
channel t1: sid=156 devtype=SBT_TAPE
channel t1: Data Protection for Oracle: version 5.4.1.0
Starting restore at 23-AUG-11
channel t1: starting datafile backupset restore
channel t1: restoring control file
channel t1: reading from backup piece c-1258027179-20110812-00
channel t1: restored backup piece 1
piece handle=c-1258027179-20110812-00 tag=TAG20110812T200520
channel t1: restore complete, elapsed time: 00:00:03
output filename=/TGEN/data1/ctrl_01_TGEN.ctl
output filename=/TGEN/data1/ctrl_02_TGEN.ctl
Finished restore at 23-AUG-11
released channel: t1
Restore con RMAN: Un escenario común para recuperar por Gisela Velazco se encuentra bajo una Licencia Creative Commons Atribución-NoComercial-SinDerivadas 3.0 Unported.
Gracias por el aporte. Un saludo.
Hola Gisela.
Mi nombre es David Rodríguez
Mil gracias por el árticulo, no fácilmente encuentras información tan completa.
Aprovechando, tengo una duda, Tengo el escenario en donde tengo un ambiente donde debo de estar restaurando mis respaldos incrementales con rman montado y sin catálogo. ¿Con que comando debería de estar actualizando mi base de datos mediante incrementales?
Muchas gracias nuevamente
Hola David, gracias por tu comentario.
A que le llamas actualizar tu BD mediante incrementales?
Saludos.