Este procedimiento es bien conocido como Database Point in Time Recovery.
Se debe restaurar el respaldo usando la cláusula "UNTIL TIME .." del comando RESTORE (o SCN / LOG). ¿Qué valor debemos usar? Mirando el contenido del respaldo podemos identificar el último archivelog incluido a continuación del respaldo completo.
Ahora, si queremos poner estos pasos en un script, ¿cómo obtenemos este valor?.
Podemos consultar los datos que almacena RMAN en la base destino después de catalogar el respaldo. Vamos a ver cómo hacerlo sin usar una base de catálogo central, obteniendo estos datos directamente del controlfile (vistas V$BACKUP_*). Usando catálogo habría que usar las vistas RC_*.
Hay varios detalles a tener en cuenta. Lo más importante es poder identificar el respaldo completo que vamos a usar. En este ejemplo, lo identificamos porque tenemos catalogado solamente un respaldo completo (ejecutamos crosscheck después de recuperar el controlfile, y catalogamos solo este respaldo), con esta consulta:
select 'set until sequence '||max_seq||';'
from (
select min(sequence#) min_seq, max(sequence#) max_seq
from v$backup_set s, v$BACKUP_ARCHIVELOG_DETAILS r
where s.set_count = r.id2
and s.set_stamp = r.id1
and s.backup_Type='L'
and r.first_time>=(
select start_time from v$backup_set s
where s.CONTROLFILE_INCLUDED='NO' and s.backup_type='D'
and (set_stamp, set_count) in (
select set_stamp, set_count
from v$backup_piece
where deleted='NO')
)
);
Por más información sobre el contenido de estas vistas se puede ver el manual de cada una: v$backup_piece, v$backup_set y v$BACKUP_ARCHIVELOG_DETAILS.
¿Esto es lo mismo que se obtiene con comandos RMAN?. Vemos un ejemplo que lo confirma:
oracle@oraculo:> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Tue Aug 27 10:27:08 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=3195287633)
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- -------------------- ------- ------- ---------- ---
19056 B F A DISK 08/AUG/2013 02:36:18 17 1 YES TAG20130807T233030
19057 B F A DISK 08/AUG/2013 02:36:24 1 1 NO TAG20130808T023623
19058 B A A DISK 08/AUG/2013 02:41:36 1 1 YES TAG20130808T023635
19059 B F A DISK 08/AUG/2013 03:01:53 1 1 NO TAG20130808T030153
Estos datos los podemos ver en SQLPLUS:
11:27:15 SYS@PROD> select *
from v$backup_set
where set_count in (19056,19057,19058,19059);
RECID STAMP SET_STAMP SET_COUNT BAC CONTROLFI INCREMENTAL_LEVEL PIECES START_TIME
---------- ---------- ---------- ---------- --- --------- ----------------- ---------- -----------------------------
COMPLETION_TIME ELAPSED_SECONDS BLOCK_SIZE INPUT_FIL KEEP KEEP_UNTIL
----------------------------- --------------- ---------- --------- --------- -----------------------------
KEEP_OPTIONS
------------------------------
19017 822452177 822451892 19056 L NO 1 03/AUG/2013 02:51:32
03/AUG/2013 02:56:17 285 512 NO NO
19018 822452458 822452178 19057 L NO 1 03/AUG/2013 02:56:18
03/AUG/2013 03:00:58 280 512 NO NO
19019 822452470 822452470 19058 D YES 1 03/AUG/2013 03:01:10
03/AUG/2013 03:01:10 0 16384 NO NO
19020 822798680 822798381 19059 L NO 1 07/AUG/2013 03:06:21
07/AUG/2013 03:11:20 299 512 NO NO
Para validar que tenemos un respaldo completo, dos del controlfile y uno de archivelogs, vemos sus contenidos:
oracle@oraculo:> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Tue Aug 27 10:36:00 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=3195287633)
RMAN> list backup;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
19056 Full 33.87G DISK 03:05:47 08/AUG/2013 02:36:18
List of Datafiles in backup set 19056
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/system01.dbf
2 Full 3048062425 07/AUG/2013 23:30:32 /u03/oradata/PROD/undotbs01.dbf
3 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/sysaux01.dbf
4 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/users_01.dbf
5 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/datos_prod.dbf
6 Full 3048062425 07/AUG/2013 23:30:32 /u03/oradata/PROD/indices_prod.dbf
7 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/cwmlite01.dbf
8 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/drsys01.dbf
9 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/example01.dbf
10 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/indx01.dbf
11 Full 3048062425 07/AUG/2013 23:30:32 /u02/oradata/PROD/tools01.dbf
Backup Set Copy #1 of backup set 19056
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ -------------------- ---------- ---
DISK 03:05:47 09/AUG/2013 04:55:28 YES TAG20130807T233030
List of Backup Pieces for backup set 19056 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
31790 1 AVAILABLE /backup/database_PROD_20130807_p1_s19095_1
31795 2 AVAILABLE /backup/database_PROD_20130807_p2_s19095_1
31766 3 AVAILABLE /backup/database_PROD_20130807_p3_s19095_1
31789 4 AVAILABLE /backup/database_PROD_20130808_p4_s19095_1
31794 5 AVAILABLE /backup/database_PROD_20130808_p5_s19095_1
31765 6 AVAILABLE /backup/database_PROD_20130808_p6_s19095_1
31775 7 AVAILABLE /backup/database_PROD_20130808_p7_s19095_1
31788 8 AVAILABLE /backup/database_PROD_20130808_p8_s19095_1
31793 9 AVAILABLE /backup/database_PROD_20130808_p9_s19095_1
31792 10 AVAILABLE /backup/database_PROD_20130808_p10_s19095_1
31764 11 AVAILABLE /backup/database_PROD_20130808_p11_s19095_1
31768 12 AVAILABLE /backup/database_PROD_20130808_p12_s19095_1
31787 13 AVAILABLE /backup/database_PROD_20130808_p13_s19095_1
31791 14 AVAILABLE /backup/database_PROD_20130808_p14_s19095_1
31763 15 AVAILABLE /backup/database_PROD_20130808_p15_s19095_1
31767 16 AVAILABLE /backup/database_PROD_20130808_p16_s19095_1
31786 17 AVAILABLE /backup/database_PROD_20130808_p17_s19095_1
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
19057 Full 11.58M DISK 00:00:01 08/AUG/2013 02:36:24
BP Key: 31753 Status: AVAILABLE Compressed: NO Tag: TAG20130808T023623
Piece Name: /backup/c-3195287633-20130808-00
Control File Included: Ckp SCN: 3048178773 Ckp time: 08/AUG/2013 02:36:23
SPFILE Included: Modification time: 15/MAY/2013 11:17:45
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
19058 742.70M DISK 00:04:59 08/AUG/2013 02:41:36
BP Key: 31758 Status: AVAILABLE Compressed: YES Tag: TAG20130808T023635
Piece Name: /backup/archivedlogs_PROD_20130808_p1_s19097_1
List of Archived Logs in backup set 19058
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 70088 3048053610 07/AUG/2013 23:18:01 3048062243 07/AUG/2013 23:30:27
1 70089 3048062243 07/AUG/2013 23:30:27 3048086304 08/AUG/2013 00:01:51
1 70090 3048086304 08/AUG/2013 00:01:51 3048117667 08/AUG/2013 00:50:04
1 70091 3048117667 08/AUG/2013 00:50:04 3048156318 08/AUG/2013 01:24:49
1 70092 3048156318 08/AUG/2013 01:24:49 3048178827 08/AUG/2013 02:36:32
1 70093 3048178827 08/AUG/2013 02:36:32 3048178838 08/AUG/2013 02:36:34
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
19059 Full 11.58M DISK 00:00:00 08/AUG/2013 03:01:53
BP Key: 31754 Status: AVAILABLE Compressed: NO Tag: TAG20130808T030153
Piece Name: /backup/c-3195287633-20130808-01
Control File Included: Ckp SCN: 3048186759 Ckp time: 08/AUG/2013 03:01:53
SPFILE Included: Modification time: 08/AUG/2013 01:00:58
Y vemos lo mismo en SQLPLUS:
11:13:12 SYS@PROD> col handle for a45
11:13:12 SYS@PROD> col tag for a25
11:13:12 SYS@PROD> set linesize 120
11:13:12 SYS@PROD> select p.tag, piece#, handle
from v$backup_set s, v$backup_piece p
where s.CONTROLFILE_INCLUDED='NO' and s.backup_type='D' and p.deleted='NO'
and s.set_stamp=p.set_stamp and s.set_count=p.set_count
order by piece#;
TAG PIECE# HANDLE
------------------------- ---------- ---------------------------------------------
TAG20130807T233030 1 /backup/database_PROD_20130807_p1_s19095_1
TAG20130807T233030 2 /backup/database_PROD_20130807_p2_s19095_1
TAG20130807T233030 3 /backup/database_PROD_20130807_p3_s19095_1
TAG20130807T233030 4 /backup/database_PROD_20130808_p4_s19095_1
TAG20130807T233030 5 /backup/database_PROD_20130808_p5_s19095_1
TAG20130807T233030 6 /backup/database_PROD_20130808_p6_s19095_1
TAG20130807T233030 7 /backup/database_PROD_20130808_p7_s19095_1
TAG20130807T233030 8 /backup/database_PROD_20130808_p8_s19095_1
TAG20130807T233030 9 /backup/database_PROD_20130808_p9_s19095_1
TAG20130807T233030 10 /backup/database_PROD_20130808_p10_s19095_1
TAG20130807T233030 11 /backup/database_PROD_20130808_p11_s19095_1
TAG20130807T233030 12 /backup/database_PROD_20130808_p12_s19095_1
TAG20130807T233030 13 /backup/database_PROD_20130808_p13_s19095_1
TAG20130807T233030 14 /backup/database_PROD_20130808_p14_s19095_1
TAG20130807T233030 15 /backup/database_PROD_20130808_p15_s19095_1
TAG20130807T233030 16 /backup/database_PROD_20130808_p16_s19095_1
TAG20130807T233030 17 /backup/database_PROD_20130808_p17_s19095_1
17 rows selected.
11:14:08 SYS@PROD> select 'set until sequence '||max_seq||';' texto
from (
select min(sequence#) min_seq, max(sequence#) max_seq
from v$backup_set s, v$BACKUP_ARCHIVELOG_DETAILS r
where s.set_count = r.id2
and s.set_stamp = r.id1
and s.backup_Type='L'
and r.first_time>=(
select start_time from v$backup_set s
where s.CONTROLFILE_INCLUDED='NO' and s.backup_type='D'
and (set_stamp, set_count) in (
select set_stamp, set_count
from v$backup_piece
where deleted='NO')
)
);
TEXTO
-------------------------
set until sequence 70093;
1 rows selected.
Con estas consultas y un poco de scripting se puede armar un buen script de recuperación.
Un saludo.
No hay comentarios.:
Publicar un comentario